تابع DURATION در اکسل

تابع DURATION در اکسل برای محاسبه «مدت زمان مکالی» (Macaulay Duration) یک اوراق قرضه با پرداخت‌های دوره‌ای بهره به کار می‌رود. این مقدار نشان می‌دهد که میانگین زمان وزن‌دار دریافت جریان‌های نقدی (بهره‌ها و اصل) تا موعد سررسید چقدر است و برای سنجش حساسیت قیمت اوراق به تغییرات بازده (yield) کاربرد گسترده‌ای دارد.

فرمول و آرگومان‌ها

نحو کلی تابع:

=DURATION(settlement, maturity, coupon, yld, frequency, [basis])

تعریف پارامترها به صورت خلاصه:

settlement: تاریخ تسویه (تاریخی که خریدار اوراق را دریافت می‌کند).
maturity: تاریخ سررسید اوراق (بازپرداخت اصل).
coupon: نرخ سالانه کوپن (به صورت اعشاری، مثلاً 0.05 برای 5%).
yld: بازده سالانه تا سررسید (به صورت اعشاری).
frequency: تعداد پرداخت‌های سود در سال: 1 (سالیانه)، 2 (نیم‌سالیانه)، 4 (فصلی).
basis (اختیاری): قرارداد شمارش روزها؛ مقادیر مجاز: 0 تا 4.

مقادیر پایه (basis)

basis	معنی
0	US (NASD) 30/360
1	Actual/Actual
2	Actual/360
3	Actual/365
4	European 30/360

مثال‌های عملی (فرمول اکسل)

فرض کنید اوراقی داریم با تاریخ تسویه 1 ژانویه 2025 و سررسید 1 ژانویه 2030، کوپن سالیانه 5%، بازده جاری 4% و پرداخت‌های سود نیم‌سالیانه. برای محاسبه مدت مکالی:

=DURATION(DATE(2025,1,1), DATE(2030,1,1), 0.05, 0.04, 2, 0)

این فرمول مدت مکالی اوراق را بر حسب سال بازمی‌گرداند. با مقادیر بالا، مقدار تابع معمولاً در محدودهٔ حدود 4.5 تا 4.7 سال است (بسته به مبنای شمارش روزها). این عدد نشان می‌دهد میانگین زمان دریافت جریان‌های نقدی تا سررسید حدوداً 4.6 سال است.

محاسبه مدت اصلاح‌شده (Modified Duration)

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

ModifiedDuration = DURATION(...) / (1 + yld / frequency)

مثال عملی (همان داده‌های قبل):

=DURATION(DATE(2025,1,1), DATE(2030,1,1), 0.05, 0.04, 2, 0) / (1 + 0.04/2)

این مقدار به ما می‌گوید درصد تغییر قیمت اوراق در برابر تغییر 100 واحد پایه (1%) در بازده تقریباً چقدر است. به‌طور تقریبی: تغییر قیمت ≈ −ModifiedDuration × تغییر در بازده.

موارد و نکات مهم

ترتیب تاریخ‌ها: مقدار settlement باید قبل از maturity باشد؛ در غیر این صورت خطای #NUM! دریافت خواهید کرد.
مقادیر frequency فقط 1، 2 یا 4 مجاز است؛ استفاده از مقادیر دیگر خطا تولید می‌کند.
اگر اوراق بدون کوپن (zero-coupon) باشد، مدت مکالی برابر زمان باقی‌مانده تا سررسید است؛ در مدل‌های اکسل، بهتر است این حالت را جدا محاسبه کنید یا از فرمول‌های تحلیلی ساده‌تر برای اجتناب از خطا استفاده نمایید.
تابع DURATION مدت مکالی را به سال بازمی‌گرداند؛ اگر نیاز دارید این مقدار را در واحد دوره (مثلاً پرداخت‌های نیم‌سالی) داشته باشید، آن را در frequency ضرب کنید.
برای دقت بیشتر در محاسبات بین‌المللی به مبنای شمارش روزها (basis) توجه کنید؛ انتخاب basis اشتباه می‌تواند خطای قابل توجهی ایجاد کند، به‌خصوص برای اوراق کوتاه‌مدت یا نرخ‌های بهره بالا.

خطاهای رایج

#VALUE! معمولاً به دلیل فرمت نادرست تاریخ یا آرگومان غیرعددی رخ می‌دهد.
#NUM! اگر settlement ≥ maturity یا پارامترهای نامعتبر در آرگومان‌ها باشد.
نتایج غیرمنتظره ممکن است به خاطر وارد کردن نرخ‌ها به صورت درصد (مثلاً 5 به جای 0.05) رخ دهد—همیشه نرخ‌ها را به صورت اعشاری وارد کنید.

استفاده‌ها و نمونه‌های عملی در تحلیل مالی

تابع DURATION در کاربردهای زیر بسیار مفید است:

مدیریت ریسک نرخ بهره در سبد اوراق: محاسبه حساسیت کل پرتفوی با وزن‌دهی مدت هر اوراق.
پرتفوی سرمایه‌گذاری: مقایسه اوراق با سررسیدها و کوپن‌های متفاوت از منظر «ریسک مدتی».
مدل‌سازی قیمت‌گذاری: ترکیب با تابع PRICE یا YIELD برای تحلیل سناریو و استرس تست تغییر نرخ.

مثال ترکیبی: قیمت تغییرات با استفاده از مدت اصلاح‌شده

فرض کنید قیمت فعلی اوراق 1000 واحد باشد و می‌خواهیم تأثیر افزایش 50 واحد پایه (0.50%) در بازده را تقریب بزنیم. اگر مدت اصلاح‌شده برابر 4.4 باشد:

تقریب تغییر قیمت ≈ −4.4 × 0.005 × 1000 = −22 واحد

یعنی قیمت تقریبا از 1000 به 978 خواهد رسید (تقریب خطی—برای تغییرات بزرگ‌تر از خطا استفاده نکنید).

خلاصه و توصیه‌ها

تابع DURATION ابزار ساده و قدرتمندی برای محاسبه مدت مکالی اوراق در اکسل است و در مدیریت ریسک و تحلیل حساسیت نرخ بهره اهمیت زیادی دارد. هنگام استفاده:

همیشه آرگومان تاریخ را با توابع تاریخ (مثل DATE) وارد کنید تا از اشتباه فرمت جلوگیری شود.
برای تحلیل حساسیت، مدت اصلاح‌شده را محاسبه کنید و نتیجه را به عنوان تقریب خطی تغییر قیمت به کار ببرید.
برای اوراق بدون کوپن یا شرایط غیرمعمول، بررسی‌های دستی و فرمول‌های تحلیلی انجام دهید تا خطای محاسباتی رخ ندهد.

آیا این مطلب برای شما مفید بود ؟

خیر

بله