DURATION Function

Returns the Macaulay duration of a bond — the weighted average time to its cash flows.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A duration in years

Want to practice DURATION in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

Duration measures how long, on average, you wait for a bond's cash, weighting each coupon and the redemption by its present value. It's always shorter than the time to maturity for a coupon bond.

Longer duration means the price is more sensitive to interest-rate changes.

Syntax

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

Arguments

ArgumentRequired?What it does
settlementYesThe date the buyer takes ownership (after the issue date).
maturityYesThe date the security is repaid.
couponYesThe annual coupon rate.
yldYesThe annual yield.
frequencyYesCoupon payments per year: 1 = annual, 2 = semiannual, 4 = quarterly.
basisNoDay count: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Examples

Example 1 — Duration of the corporate bond

Northstar's treasury buys a corporate bond: 5% coupon paid twice a year, maturing 11/15/2033, settling 3/15/2026. Yield 5.8%.

B7fx=DURATION(B1,B2,B3,B4,B5,B6)
AB
1Settlement3/15/2026
2Maturity11/15/2033
3Coupon5%
4Yield5.8%
5Frequency2
6Basis0
7Duration (years)6.32
8Modified duration6.14

The bond matures in about 7.7 years, but its cash arrives on average after 6.32.

Common mistakes

  • #VALUE! or #NUM!. A date isn't a real date, settlement is on or after maturity, or frequency isn't 1, 2 or 4.

Notes

  • Enter dates with DATE or as cells holding real dates. Text dates like "3/15/2026" typed straight into the formula can be misread on computers with other regional settings.
  • Related functions: MDURATION, PRICE, YIELD.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.