Financial
Macaulay Duration and Modified Duration Bond Formulas (DURATION & MDURATION)
Compute present-value-weighted cash-flow maturity (Macaulay Duration) and yield sensitivity (Modified Duration) directly from coupon schedules without opaque Analysis ToolPak black boxes.
Macaulay Duration vs. Modified Duration
Macaulay Duration (Excel's DURATION) measures the present-value-weighted average time in years until a bond's coupon and principal cash flows are received: sum(t_years * PV_t) / Price. A zero-coupon bond always has a Macaulay Duration exactly equal to its maturity in years.
Modified Duration (Excel's MDURATION) divides Macaulay Duration by (1 + y / m), where y is annual yield to maturity and m is coupons per year, giving the first-order percentage bond price change for a 100-basis-point change in yield.
| Period (k) | Time (Years) | Cash Flow ($) | Discounted PV ($) | Time-Weighted PV |
|---|---|---|---|---|
| 1 | 0.5 | 30.00 | 29.2683 | 14.6341 |
| 2 | 1.0 | 30.00 | 28.5544 | 28.5544 |
| 3 | 1.5 | 30.00 | 27.8580 | 41.7870 |
| 4 | 2.0 | 1,030.00 | 933.1292 | 1,866.2583 |
| Totals / Metrics | — | Price = 1,018.8099 | MacDur = 1.9152 yrs | ModDur = 1.8685 |
Macaulay Duration Formula (Years)
Divides total time-weighted present value in E2:E5 by total bond present value in D2:D5.
=ROUND(SUM(E2:E5) / SUM(D2:D5), 4)Modified Duration Formula (Yield Sensitivity)
Divides Macaulay Duration by (1 + Annual_YTM / Frequency) to measure percentage price sensitivity per unit yield shift.
=ROUND((SUM(E2:E5) / SUM(D2:D5)) / (1 + G4 / G5), 4)Try the example
Use the original values or change them to test your understanding.