MDURATION Function

Returns modified duration — roughly the % price change for a 1% change in yield.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A modified duration

Want to practice MDURATION 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

Modified duration is Macaulay DURATION ÷ (1 + yield ÷ frequency). Traders use it as a quick sensitivity: a modified duration of 6.14 means about a 6.14% price drop if yields rise one percentage point.

It's an approximation that works best for small rate moves.

Syntax

=MDURATION(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 — Interest-rate risk on the 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=MDURATION(B1,B2,B3,B4,B5,B6)
AB
1Settlement3/15/2026
2Maturity11/15/2033
3Coupon5%
4Yield5.8%
5Frequency2
6Basis0
7Modified duration6.14

Example 2 — Estimating a price move

Approximate new price if yields rise 0.5%: price × (1 − modified duration × change).

=95.10*(1-B7*0.5%) Explain

About 92.18.

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: DURATION, PRICE.
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.