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

معرفی تابع ODDFYIELD

تابع ODDFYIELD در اکسل برای محاسبه «بازده (Yield)» اوراق قرضه/اسناد بدهی استفاده می‌شود که اولین دوره پرداخت سود آن‌ها کوتاه یا بلند (Odd First Period) است؛ یعنی فاصله بین تاریخ انتشار (Issue) تا اولین تاریخ پرداخت سود (First coupon) مثل دوره‌های معمولی کوپن (مثلاً هر ۶ ماه) نیست.

این تابع بیشتر در تحلیل‌های مالی، ارزش‌گذاری اوراق بدهی و بررسی نرخ بازده سرمایه‌گذاری در ابزارهای درآمد ثابت کاربرد دارد؛ مخصوصاً وقتی اوراق تازه منتشر شده و اولین پرداخت سود، نامنظم است.

مثال ساده فارسی: فرض کنید یک اوراق با ارزش اسمی ۱۰۰٬۰۰۰ تومان در تاریخ ۱۴۰۳/۰۱/۱۵ منتشر شده، تاریخ تسویه خرید ۱۴۰۳/۰۲/۰۱ است، اولین پرداخت سود ۱۴۰۳/۰۴/۱۵ انجام می‌شود و سررسید ۱۴۰۵/۰۱/۱۵ است. نرخ کوپن سالانه ۱۸٪ و قیمت خرید ۹۷٬۵۰۰ تومان است. می‌خواهید بازده تقریبی این خرید را حساب کنید.

=ODDFYIELD(DATE(2024,4,20),DATE(2026,4,4),DATE(2024,4,3),DATE(2024,7,5),DATE(2024,4,3),0.18,97500,100000,2,0)

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

• محاسبه بازده اوراق قرضه با «اولین دوره کوپن نامنظم» (کوتاه/بلند)
• مقایسه نرخ بازده اوراق مختلف با تاریخ‌های پرداخت متفاوت
• تحلیل سناریوهای خرید اوراق در تاریخ‌های مختلف تسویه (Settlement)
• کنترل و اعتبارسنجی قیمت‌گذاری اوراق (Price ↔ Yield) در گزارش‌های مالی
• استفاده در مدل‌های مالی برای درآمد ثابت (Fixed Income Modeling)

ساختار (Syntax)

نسخه انگلیسی:

=ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,redemption,frequency,[basis])

نسخه توضیح فارسی آرگومان‌ها:

=ODDFYIELD(تاریخ_تسویه,تاریخ_سررسید,تاریخ_انتشار,اولین_تاریخ_کوپن,نرخ_کوپن,قیمت,ارزش_بازخرید,تعداد_پرداخت_در_سال,[مبنای_روزشماری])

آرگومان‌ها

settlement / تاریخ تسویه

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

maturity / تاریخ سررسید

تاریخی که اصل پول اوراق بازپرداخت می‌شود.

issue / تاریخ انتشار

تاریخ صدور اوراق.

first_coupon / اولین تاریخ کوپن

اولین تاریخی که سود (کوپن) پرداخت می‌شود؛ این تاریخ باعث می‌شود دوره اول «نامنظم» باشد.

rate / نرخ کوپن

نرخ سود سالانه اوراق (مثلاً 0.18 برای ۱۸٪).

pr / قیمت

قیمت اوراق به ازای هر 100 واحد ارزش اسمی یا مطابق قرارداد بازار شما (در اکسل معمولاً به ازای 100 در نظر گرفته می‌شود). در عمل باید با مقدار redemption هم‌مقیاس باشد.

redemption / ارزش بازخرید

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

frequency / تعداد پرداخت در سال

تعداد دفعات پرداخت کوپن در سال:

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

basis / مبنای روزشماری (اختیاری)

روش محاسبه تعداد روزها:

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

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

مثال 1: محاسبه بازده با داده‌های داخل سلول‌ها

فرض کنید این داده‌ها را در شیت وارد کرده‌اید:

• A2: تاریخ تسویه
• B2: تاریخ سررسید
• C2: تاریخ انتشار
• D2: اولین تاریخ کوپن
• E2: نرخ کوپن
• F2: قیمت
• G2: ارزش بازخرید
• H2: تعداد پرداخت در سال
• I2: مبنای روزشماری

حالا فرمول بازده:

=ODDFYIELD(A2,B2,C2,D2,E2,F2,G2,H2,I2)

نتیجه چه چیزی است؟ خروجی این تابع «نرخ بازده سالانه» را برمی‌گرداند (عدد اعشاری). اگر سلول را درصدی (Percentage) کنید، بازده به صورت درصد نمایش داده می‌شود.

مثال 2: وقتی basis را وارد نکنیم

اگر مبنای روزشماری را وارد نکنید، اکسل به طور پیش‌فرض معمولاً از basis=0 استفاده می‌کند.

=ODDFYIELD(A2,B2,C2,D2,E2,F2,G2,H2)

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

مثال 1: کنترل صحت ورودی‌ها با AND و جلوگیری از خطا

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

=IF(AND(A2>C2,A2<B2,D20,F2>0,G2>0,OR(H2=1,H2=2,H2=4)),ODDFYIELD(A2,B2,C2,D2,E2,F2,G2,H2,I2),"ورودی‌ها نامعتبر است")

مثال 2: استخراج قیمت از جدول با XLOOKUP و سپس محاسبه بازده

فرض کنید در جدول K2:L100 «نماد اوراق» و «قیمت» دارید، و در J2 نماد انتخابی کاربر است. قیمت را پیدا کنید و بازده را حساب کنید.

=ODDFYIELD(A2,B2,C2,D2,E2,XLOOKUP(J2,K2:K100,L2:L100),G2,H2,I2)

مثال 3: شمارش اوراقی که بازده‌شان از یک حد بیشتر است (COUNTIF)

فرض کنید بازده چند اوراق را در ستون M (M2:M200) محاسبه کرده‌اید. می‌خواهید تعداد اوراقی که بازده‌شان بالاتر از 20٪ است را بشمارید.

=COUNTIF(M2:M200,">0.2")

مثال 4: جمع‌زدن بازده‌ها (SUM) برای گزارش‌گیری سریع

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

=SUM(M2:M20)

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

• IF برای نمایش پیام خطای قابل فهم به جای #NUM! یا #VALUE!

  =IFERROR(ODDFYIELD(A2,B2,C2,D2,E2,F2,G2,H2,I2),"خطا در محاسبه بازده")

• AND/OR برای اعتبارسنجی تاریخ‌ها و مقادیر قبل از محاسبه

  =IF(AND(E2>0,F2>0,G2>0,OR(H2=1,H2=2,H2=4)),ODDFYIELD(A2,B2,C2,D2,E2,F2,G2,H2,I2),"پارامترها درست نیست")

• XLOOKUP برای دریافت خودکار قیمت/نرخ از جداول

  =ODDFYIELD(A2,B2,C2,D2,E2,XLOOKUP(J2,K2:K100,L2:L100),G2,H2,I2)

• ROUND برای نمایش بازده با تعداد اعشار مشخص

  =ROUND(ODDFYIELD(A2,B2,C2,D2,E2,F2,G2,H2,I2),4)

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

1) #NUM!

این خطا معمولاً وقتی رخ می‌دهد که ورودی‌ها از نظر مالی/منطقی نامعتبر باشند؛ مثل:

• settlement ≥ maturity
• frequency عددی غیر از 1 یا 2 یا 4 باشد
• rate ≤ 0 یا pr ≤ 0 یا redemption ≤ 0
• تاریخ‌ها ترتیب درست نداشته باشند (مثلاً first_coupon قبل از issue)

راه‌حل: ترتیب تاریخ‌ها را بررسی کنید، frequency را فقط 1/2/4 بگذارید و نرخ/قیمت/بازخرید را مثبت وارد کنید. برای کنترل سریع از IF+AND استفاده کنید.

2) #VALUE!

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

راه‌حل: تاریخ‌ها را با DATE بسازید یا فرمت سلول را Date کنید و مطمئن شوید مقدار واقعی تاریخ است، نه متن. برای عددی کردن مقدارهای متنی می‌توانید یک‌بار ضربدر 1 کنید یا از VALUE استفاده کنید.

3) نتیجه غیرواقعی (خیلی بزرگ/خیلی کوچک)

این مورد الزاماً خطای اکسل نیست؛ معمولاً از «مقیاس اشتباه قیمت و ارزش بازخرید» یا «basis نامناسب» می‌آید.

راه‌حل: مطمئن شوید pr و redemption در یک مقیاس هستند (مثلاً هر دو بر مبنای 100). همچنین basis مناسب قرارداد/بازار خود را انتخاب کنید (مثلاً Actual/Actual در بسیاری از اوراق رایج است).

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

• دقت در تاریخ‌ها: تاریخ‌ها را تا حد امکان با DATE بسازید تا مشکل تاریخِ متنی پیش نیاید.
• frequency را استاندارد نگه دارید: فقط 1، 2 یا 4 مجاز است؛ هر عدد دیگری معمولاً #NUM! می‌دهد.
• basis را مستند کنید: در مدل‌های مالی، کنار سلول basis توضیح کوتاه بنویسید تا در آینده مشخص باشد روزشماری بر چه مبنایی است.
• IFERROR برای تجربه کاربری بهتر: اگر فایل را برای همکار/مشتری می‌فرستید، خطاها را با پیام قابل فهم جایگزین کنید.
• فرمت درصدی: خروجی ODDFYIELD عدد اعشاری است؛ برای نمایش درست، سلول را Percentage کنید.

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

• ODDFYIELD مخصوص اوراقی است که «اولین دوره کوپن» نامنظم است.
• YIELD برای اوراقی است که دوره‌های کوپن منظم هستند (بدون Odd First/Last).
• ODDLYIELD برای حالتی است که «آخرین دوره کوپن» نامنظم است (Odd Last Period).
• ODDFPRICE اگر به جای بازده، «قیمت» اوراق با دوره اول نامنظم را بخواهید.

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

• تابع ODDFYIELD جزء توابع مالی (Financial) اکسل است و در نسخه‌های دسکتاپ اکسل سال‌های جدید (از جمله 2007 به بعد) معمولاً وجود دارد.
• در Excel for the web (نسخه آنلاین) نیز معمولاً پشتیبانی می‌شود، اما در برخی محیط‌های سازمانی ممکن است محدودیت‌هایی وجود داشته باشد.
• نکته مهم: جداکننده آرگومان‌ها ممکن است بسته به تنظیمات منطقه‌ای سیستم شما «,» یا «;» باشد.

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

آیا ODDFYIELD نرخ بازده را سالانه برمی‌گرداند؟

بله، خروجی بازده سالانه است (Annual Yield) و معمولاً به صورت عدد اعشاری نمایش داده می‌شود.

اگر اوراق دوره اول نامنظم نداشته باشد، باز هم از ODDFYIELD استفاده کنیم؟

بهتر است در حالت دوره‌های منظم از تابع YIELD استفاده کنید تا مدل شما استانداردتر و قابل فهم‌تر باشد.

کدام basis را انتخاب کنم؟

بستگی به قرارداد/بازار دارد. اگر مطمئن نیستید، از مستندات اوراق یا رویه حسابداری/بازار همان ابزار استفاده کنید. گزینه‌های رایج: 0 یا 1.

چرا با تغییر کوچک در قیمت، بازده خیلی تغییر می‌کند؟

ممکن است تاریخ‌ها، frequency، یا مقیاس pr و redemption درست نباشد. همچنین در اوراق نزدیک سررسید یا با کوپن خاص، حساسیت بازده به قیمت بیشتر می‌شود.

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

تابع ODDFYIELD زمانی به کار می‌آید که می‌خواهید بازده اوراق بدهی با اولین دوره کوپن نامنظم را محاسبه کنید. اگر تاریخ‌ها، frequency، basis و مقیاس قیمت/بازخرید را درست وارد کنید، می‌توانید تحلیل‌های دقیق‌تری برای خریدوفروش اوراق و مقایسه سرمایه‌گذاری‌ها انجام دهید.

پیشنهاد یادگیری بعدی: برای تکمیل مهارت‌های درآمد ثابت در اکسل، سراغ توابع YIELD، ODDLYIELD، PRICE، ODDFPRICE و همچنین تکنیک‌های XLOOKUP و IFERROR برای ساخت گزارش‌های حرفه‌ای بروید.