Financial
Bond Duration & DV01 formulas: Macaulay Duration, Modified Duration & rate shock
Calculate PV-weighted Macaulay Duration (years), interest-rate sensitivity Modified Duration, and DV01 dollar risk per 1 basis point (0.01%) yield shift.
Why a 3-year coupon bond has a 2.86-year Macaulay Duration (and zero-coupon = 3.00!)
Macaulay Duration is the Present-Value-weighted average time (in years) until you receive a bond's cash flows: MacDur = SUMPRODUCT(Years, CashFlowPVs) / BondPrice! On a 3-year $1,000 par bond paying 5% annual coupons ($50, $50, $1,050) discounted at YTM = 5.0%, Bond Price = $1,000.00 and Macaulay Duration = 2.8594 years—shorter than 3.00 years because early $50 coupons pull the center of gravity forward (whereas a 3-year zero-coupon bond has MacDur = 3.0000 years exact)!
For annual compounding, divide Macaulay duration by 1+y to obtain modified duration. The linear estimate is relative price change ≈ -ModifiedDuration × change in yield. DV01 here is the positive magnitude Price × ModifiedDuration × 0.0001; a yield increase implies an approximately negative price change. Larger shocks require full repricing or a convexity adjustment.
| Fixed-Income Risk Metric ($1,000 Par, 5% Coupon, 3 Yr) | Formula | Result |
|---|---|---|
| Bond Price (Sum of Cash-Flow PVs) | =SUM(C2:C4) | $1,000.00 |
| Macaulay Duration (Years) | =SUMPRODUCT(A2:A4, C2:C4) / SUM(C2:C4) | 2.8594 years (Zero-coupon = 3.0000) |
| Modified Duration (% per 1% Δy) | =MacDur / (1 + E2) | 2.7232 |
| DV01 ($ per 1 bp = 0.01% Δy) | =Price * ModDur * 0.0001 | $0.2723 per bp; approximately -$27.23 for a +100 bp yield change |
Macaulay Duration, Modified Duration, and DV01 formulas
Column C discounts each annual coupon/principal row C_i = B_i / ((1 + $E$2) ^ A_i).
=ROUND(SUMPRODUCT(A2:A4, C2:C4) / SUM(C2:C4), 4)Modified Duration and DV01 (Dollar Value of 1 basis point)
This cash-flow schedule is annual, valued at time zero with whole years to each payment. Merely halving E2 does not convert it to a semiannual bond. For m payments per year, provide the actual coupon payments and times t in years, discount by (1+y/m)^(m*t), and divide Macaulay duration by 1+y/m.
Assume fixed nonnegative cash flows with positive total present value, numeric times, and annual yield greater than -100%. This example does not model accrued interest between coupons, callable cash flows, credit changes or settlement day-count rules.
=ROUND((SUMPRODUCT(A2:A4, C2:C4) / SUM(C2:C4)) / (1 + E2), 4)Try the example
Use the original values or change them to test your understanding.