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
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
Arguments
| Argument | Required? | What it does |
|---|---|---|
| settlement | Yes | The date the buyer takes ownership (after the issue date). |
| maturity | Yes | The date the security is repaid. |
| coupon | Yes | The annual coupon rate. |
| yld | Yes | The annual yield. |
| frequency | Yes | Coupon payments per year: 1 = annual, 2 = semiannual, 4 = quarterly. |
| basis | No | Day 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%.
| A | B | |
|---|---|---|
| 1 | Settlement | 3/15/2026 |
| 2 | Maturity | 11/15/2033 |
| 3 | Coupon | 5% |
| 4 | Yield | 5.8% |
| 5 | Frequency | 2 |
| 6 | Basis | 0 |
| 7 | Modified duration | 6.14 |
Example 2 — Estimating a price move
Approximate new price if yields rise 0.5%: price × (1 − modified duration × change).
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.

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.