MDURATION function
MDURATION returns the modified duration of a security in years, which is Macaulay duration divided by one plus the periodic yield. It estimates the percentage price change for a one-point change in yield.
Example
Read it as a sentence: find the modified duration of a bond from January 1, 2024 to January 1, 2029, with an 8% coupon and 9% yield, paying twice a year, which gives 4.01806536102434.
Syntax
MDURATION(settlement, maturity, coupon, yld, frequency, [basis])
Arguments in [square brackets] are optional.
-
settlement(required) - Date the security is bought (after issue, before maturity).
-
maturity(required) - Date the security expires and is repaid.
-
coupon(required) - Annual coupon rate.
-
yld(required) - Annual yield.
-
frequency(required) - Coupon payments per year: 1 annual, 2 semiannual, 4 quarterly.
-
basis(optional) - Day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.
Returns: Modified duration in years.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,2,0) | 6.817646373 | Typical |
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,2) | 6.817646373 | Basis omitted |
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,1,0) | 6.72768278 | Frequency 1 |
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,4,0) | 7.011174 | Frequency 4 |
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,2.9,0) | 6.817646373 | Frequency 2.9 truncates |
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,2,0) | 6.817646373 | Basis 0 |
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,3,0) | #NUM! | Frequency 3 |
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,0,0) | #NUM! | Frequency 0 |
Watch out for
- Pass dates with DATE() or cell references; text dates can misbehave and invalid dates return #VALUE!.
Where it works
- Excel
- MDURATION is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .