آموزش تابع COUPPCD در اکسل

معرفی تابع COUPPCD

تابع COUPPCD در اکسل برای محاسبه «تاریخ کوپن قبلی» (Previous Coupon Date) در اوراق بهادار با پرداخت سود دوره‌ای (مثل اوراق قرضه/اوراق با درآمد ثابت) استفاده می‌شود. یعنی اگر شما تاریخ تسویه (Settlement) و تاریخ سررسید (Maturity) یک ورقه را داشته باشید، اکسل می‌تواند مشخص کند آخرین تاریخی که قبل از تاریخ تسویه، پرداخت کوپن انجام شده (یا باید انجام می‌شده) چه روزی بوده است.

این تابع در تحلیل‌های مالی مثل محاسبه بهره انباشته (Accrued Interest)، زمان‌بندی پرداخت‌های سود، و کنترل تقویم کوپن‌ها کاربرد زیادی دارد.

مثال ساده: فرض کنید یک ورقه، هر 6 ماه یک‌بار سود می‌دهد. تاریخ تسویه 2025/02/10 است و سررسید 2027/12/31. می‌خواهیم ببینیم «کوپن قبلی» نسبت به تاریخ تسویه چه تاریخی است.

=COUPPCD("2025/02/10","2027/12/31",2,0)

کاربردهای اصلی تابع COUPPCD

• به‌دست آوردن تاریخ آخرین پرداخت سود قبل از تاریخ تسویه
• ساخت تقویم پرداخت‌های کوپن و کنترل دوره‌ها
• پایه‌گذاری برای محاسبه بهره انباشته (در کنار توابعی مثل ACCRINT)
• کنترل صحت داده‌های اوراق (مثلاً این‌که تسویه داخل یک دوره کوپن معتبر باشد)
• تحلیل خرید/فروش اوراق و تشخیص فاصله تا کوپن بعدی

ساختار (Syntax)

=COUPPCD(argument1, argument2, ...)

=COUPPCD(تاریخ_تسویه;تاریخ_سررسید;تعداد_پرداخت_در_سال;مبنای_روز_شمار)

آرگومان‌ها

Settlement (تاریخ_تسویه) / تاریخ تسویه معامله

تاریخی که معامله روی ورقه بهادار تسویه می‌شود (معمولاً بعد از تاریخ معامله). این تاریخ باید قبل از تاریخ سررسید باشد.

Maturity (تاریخ_سررسید) / تاریخ سررسید

تاریخی که ورقه در آن سررسید می‌شود (اصل سرمایه بازپرداخت می‌شود).

Frequency (تعداد_پرداخت_در_سال) / تعداد دفعات پرداخت کوپن در سال

فقط یکی از مقادیر زیر قابل قبول است:

• 1 = سالانه
• 2 = شش‌ماهه
• 4 = سه‌ماهه

Basis (مبنای_روز_شمار) / مبنای محاسبه روزها

روش Day Count Convention برای محاسبات تاریخ/روز. مقادیر رایج:

• 0 = US (NASD) 30/360
• 1 = Actual/Actual
• 2 = Actual/360
• 3 = Actual/365
• 4 = European 30/360

اگر دقیقاً نمی‌دانید کدام را انتخاب کنید، در بسیاری از سناریوهای عمومی از 0 یا 1 استفاده می‌شود (اما در کار واقعی باید مطابق استاندارد همان بازار/اوراق باشد).

مثال‌های ساده و پایه

مثال 1: محاسبه کوپن قبلی با ورودی سلولی

فرض کنید:

• A2 = تاریخ تسویه: 2025/02/10
• B2 = تاریخ سررسید: 2027/12/31
• C2 = تعداد پرداخت در سال: 2
• D2 = مبنا: 0

فرمول:

=COUPPCD(A2;B2;C2;D2)

نتیجه: یک تاریخ برمی‌گرداند که نشان می‌دهد آخرین تاریخ کوپن قبل از 2025/02/10 چه روزی بوده است. (نمایش آن به فرمت تاریخ سلول وابسته است.)

مثال 2: اگر پرداخت‌ها سالانه باشد

اگر ورقه سالی یک‌بار سود بدهد:

=COUPPCD("2025/02/10";"2027/12/31";1;1)

نتیجه: تاریخ کوپن قبلی در چرخه سالانه محاسبه می‌شود.

مثال‌های کاربردی و واقعی

مثال 1: تشخیص این‌که تسویه دقیقاً بعد از کوپن قبلی است یا نه (کنترل ساده)

گاهی می‌خواهید بررسی کنید آیا تاریخ تسویه برابر با همان تاریخ کوپن قبلی است (مثلاً در گزارش‌گیری یا کنترل داده‌ها).

فرض کنید A2=Settlement و B2=Maturity و C2=Frequency و D2=Basis

=IF(A2=COUPPCD(A2;B2;C2;D2);"Settlement دقیقا روی تاریخ کوپن است";"Settlement بین دو کوپن است")

مثال 2: محاسبه فاصله روز بین کوپن قبلی و تسویه

این کار در تحلیل بهره انباشته بسیار رایج است (فقط فاصله روز را می‌گیریم):

=A2-COUPPCD(A2;B2;C2;D2)

نتیجه: تعداد روزهایی که از کوپن قبلی تا تاریخ تسویه گذشته است (به شرط اینکه سلول‌ها تاریخ واقعی اکسل باشند).

مثال 3: ترکیب با AND/OR برای اعتبارسنجی ورودی‌ها

مثلاً بررسی کنیم تاریخ‌ها معتبرند و Frequency یکی از 1/2/4 است:

=IF(AND(A2ورودی‌ها نامعتبر است")

مثال 4: گرفتن پارامتر Frequency از جدول با XLOOKUP و سپس محاسبه COUPPCD

فرض کنید در F2 کد ورقه را دارید و در جدول H:K اطلاعات زیر است:

• H:H = کد ورقه
• I:I = تاریخ سررسید
• J:J = Frequency
• K:K = Basis

و A2 تاریخ تسویه است. فرمول:

=IF(AND(A2<B2, OR(C2=1, C2=2, C2=4)), COUPPCD(A2, B2, C2, D2), "ورودی‌ها نامعتبر است")

مثال 5: شمارش تعداد رکوردهایی که کوپن قبلی‌شان در یک ماه مشخص افتاده (COUNTIF روی خروجی کمکی)

فرض کنید در ستون E تاریخ کوپن قبلی را برای هر ردیف محاسبه کرده‌اید (E2:E100). حالا می‌خواهید تعداد مواردی که در ماه 2025/01 هستند را بشمارید. یک راه ساده این است که بازه تاریخ شروع/پایان ماه را داشته باشید:

• G2 = 2025/01/01
• H2 = 2025/02/01

=COUNTIFS(E2:E100;">="&G2;E2:E100;"

<“&H2)

ترکیب تابع COUPPCD با فرمول‌های دیگر

• ترکیب با IF برای پیام‌دهی و کنترل وضعیت تاریخ تسویه

  =IF(A2

  <COUPPCD(A2;B2;C2;D2);”خطا در تاریخ”;”OK”)

• ترکیب با AND/OR برای اعتبارسنجی Frequency و ترتیب تاریخ‌ها

  =IF(AND(A2

  <B2;OR(C2=1;C2=2;C2=4));”مجاز”;”غیرمجاز”)

• ترکیب با XLOOKUP برای خواندن پارامترهای اوراق از دیتابیس و سپس محاسبه COUPPCD

  =COUPPCD(A2;XLOOKUP(F2;H:H;I:I);XLOOKUP(F2;H:H;J:J);XLOOKUP(F2;H:H;K:K))

• ترکیب با SUM برای سناریوهای تجمیعی (مثلاً جمع فاصله روز از کوپن قبلی برای چند ورقه)

  =SUM(A2:A10-E2:E10)

خطاهای رایج و روش رفع آنها

1) خطای #VALUE!

علت‌های رایج: یکی از تاریخ‌ها به‌صورت متن وارد شده، یا فرمت تاریخ توسط اکسل به تاریخ معتبر تبدیل نشده است.

راه‌حل:

• تاریخ‌ها را به Date واقعی تبدیل کنید (مثلاً با وارد کردن صحیح تاریخ یا استفاده از DATE).
• اگر داده از سیستم/CSV آمده، با Text to Columns یا DATEVALUE آن را اصلاح کنید.

2) خطای #NUM!

علت‌های رایج:

• Settlement بزرگ‌تر یا مساوی Maturity است.
• Frequency عددی غیر از 1 یا 2 یا 4 است.
• Basis خارج از بازه 0 تا 4 وارد شده است.

راه‌حل:

• حتماً Settlement را قبل از Maturity بگذارید.
• Frequency را فقط 1، 2 یا 4 تنظیم کنید.
• Basis را بین 0 تا 4 انتخاب کنید.

3) نتیجه تاریخ «غیرمنتظره» (ولی بدون خطا)

علت‌های رایج: انتخاب Basis اشتباه، یا برداشت نادرست از Frequency و زمان‌بندی واقعی کوپن‌ها.

راه‌حل:

• مشخصات واقعی ورقه (Day Count و تعداد پرداخت) را از امیدنامه/سیستم مالی بررسی کنید.
• برای اطمینان، تاریخ کوپن قبلی و بعدی را با هم چک کنید (مثلاً با COUPNCD برای کوپن بعدی).

نکات حرفه‌ای و ترفندهای مهم

• تاریخ‌ها را همیشه «تاریخ واقعی اکسل» نگه دارید: اگر تاریخ‌ها متن باشند، خروجی‌ها قابل اتکا نیستند یا خطا می‌گیرید.
• Frequency را اعتبارسنجی کنید: بهترین کار این است که برای سلول Frequency، Data Validation بگذارید تا فقط 1،2،4 انتخاب شود.
• Basis را مستندسازی کنید: در فایل‌های مالی، یک ستون/یادداشت بگذارید که Basis انتخابی دقیقاً بر اساس چه استانداردی است.
• خوانایی فرمول‌ها: اگر چندین COUP* دارید، پارامترها را در سلول‌های جدا (Settlement/Maturity/Frequency/Basis) نگه دارید تا فرمول ساده و قابل ردیابی شود.
• کنترل خروجی با توابع مکمل: معمولاً COUPPCD به‌تنهایی کافی نیست؛ برای کنترل چرخه، تاریخ کوپن بعدی را هم محاسبه کنید.

تفاوت تابع COUPPCD با توابع مشابه

• COUPPCD: تاریخ «کوپن قبلی» قبل از Settlement را برمی‌گرداند.
• COUPNCD: تاریخ «کوپن بعدی» بعد از Settlement را برمی‌گرداند (برای پیدا کردن پرداخت بعدی سود).
• COUPNUM: تعداد کوپن‌های باقی‌مانده بین Settlement و Maturity را می‌دهد (برای شمارش دوره‌ها).
• COUPDAYS / COUPDAYBS / COUPDAYSNC: تعداد روزهای دوره کوپن یا روزهای گذشته/باقی‌مانده را می‌دهند (برای محاسبات بهره انباشته دقیق‌تر).

سازگاری با نسخه‌های مختلف اکسل

تابع COUPPCD جزء توابع مالی اکسل است و در نسخه‌های مدرن اکسل (از جمله Excel 2007 به بعد) وجود دارد. در Microsoft 365 نیز قابل استفاده است.

• Excel 2007، 2010، 2013، 2016، 2019، 2021: پشتیبانی می‌شود
• Microsoft 365: پشتیبانی می‌شود

نکته مهم: در فایل‌های با تنظیمات منطقه‌ای مختلف، جداکننده آرگومان‌ها ممکن است متفاوت باشد. در این مقاله فرمول‌ها با جداکننده فارسی (;) نوشته شده‌اند.

سؤالات پرتکرار درباره تابع COUPPCD

COUPPCD دقیقاً چه چیزی را برمی‌گرداند؟

یک مقدار تاریخ (Date) که نشان‌دهنده آخرین تاریخ کوپن قبل از تاریخ Settlement است.

اگر Frequency را اشتباه وارد کنم چه می‌شود؟

اگر غیر از 1،2،4 باشد معمولاً خطای #NUM! می‌گیرید.

Basis چه اثری روی COUPPCD دارد؟

Basis در قراردادهای مالی برای محاسبات روزشمار مهم است. در بسیاری از موارد روی توابعی که «تعداد روز» را محاسبه می‌کنند اثر پررنگ‌تری دارد، اما بهتر است در COUPPCD هم مطابق استاندارد ورقه تنظیم شود تا سازگاری کامل با سایر محاسبات حفظ شود.

آیا COUPPCD برای سهام هم کاربرد دارد؟

معمولاً برای اوراق با پرداخت کوپن دوره‌ای طراحی شده است (ابزارهای بدهی). برای سهام عادی معمولاً سناریوی کوپن به این شکل وجود ندارد.

جمع‌بندی و پیشنهاد یادگیری بعدی

تابع COUPPCD یکی از توابع کلیدی مالی اکسل است که تاریخ کوپن قبلی را نسبت به تاریخ تسویه محاسبه می‌کند. اگر با اوراق درآمد ثابت، سود دوره‌ای، و تحلیل‌های مربوط به تاریخ‌های پرداخت سروکار دارید، این تابع به شما کمک می‌کند تقویم پرداخت‌ها را دقیق‌تر بسازید و پایه محاسباتی خوبی برای بهره انباشته و گزارش‌گیری داشته باشید.

پیشنهاد یادگیری بعدی: بعد از COUPPCD، سراغ توابع COUPNCD (کوپن بعدی)، COUPNUM (تعداد کوپن‌ها)، و COUPDAYS/COUPDAYBS/COUPDAYSNC بروید تا بتوانید کل چرخه کوپن و محاسبات روزشمار را کامل و حرفه‌ای انجام دهید.