Excel like a finance pro.
Financial function
DURATION
Returns the Macaulay duration of a bond with periodic interest payments.
When to use it
Macaulay duration: the weighted average time to a bond's cash flows, in years. A measure of interest-rate sensitivity.
The shape of it
- Syntax
=DURATION(settlement, maturity, coupon, yld, frequency, [basis])
Worked examples
Coupon bond
=DURATION(DATE(2008,1,1),DATE(2016,1,1),0.08,0.09,2,1) → 5.99
An eight-year 8% bond yielding 9%.
Zero coupon
=DURATION(DATE(2025,1,1),DATE(2030,1,1),0,0.05,1) → 5
A zero-coupon bond's duration is its maturity.
Ten-year at par
=DURATION(DATE(2025,1,1),DATE(2035,1,1),0.05,0.05,1) → 8.11
A ten-year 5% annual bond at par.
Worth knowing
- Higher coupons and yields shorten duration.
- MDURATION divides by (1+yield/frequency) for price sensitivity.
- Frequency 2 for semiannual.
Where it goes wrong
- #NUM! for invalid dates, negative coupon or yield.
- Text dates return #VALUE!.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.