MDURATION Function
Calculates the modified duration of a bond.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| settlement | Date the security is purchased. | Required |
| maturity | Date the security expires. | Required |
| coupon | Annual coupon rate. | Required |
| yld | Annual yield to maturity. | Required |
| frequency | Number of coupon payments per year (1, 2, or 4). | Required |
| basis | Day-count convention (0 = US 30/360, default). | Optional |
Basic Example
Modified duration of a bond
=MDURATION(DATE(2024,4,1), DATE(2034,4,1), 0.05, 0.06, 2)For a 5% coupon bond yielding 6% with semi-annual payments, this returns the modified duration.
Advanced Examples
Example 1: Estimate price sensitivity
Interest rate riskIf MDURATION is 7.5, a 1% yield increase causes roughly a 7.5% price drop.
=MDURATION(DATE(2024,4,1), DATE(2034,4,1), 0.05, 0.06, 2, 1)How MDURATION Works
MDURATION adjusts Macaulay duration for yield to measure price sensitivity. Modified duration = Macaulay duration / (1 + yld/frequency).
Important Notes & Limitations
Settlement must be before maturity.
Frequency must be 1, 2, or 4.
Modified duration is a linear approximation; convexity improves accuracy for large yield changes.
Common Errors & Fixes
#NUM! errorInvalid dates, rates, or frequency.Fix: Verify all inputs.
#VALUE! errorDates are not recognized.Fix: Use DATE function or valid date serial numbers.
Download Practice File
MDURATION
mdurationPractice MDURATION with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the MDURATION function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps