Quick Navigation
MDURATION Function
Summary
The Excel MDURATION function calculates the modified Macauley duration for a security assuming a $100 par value. This financial metric measures a bond's price sensitivity to interest rate changes, helping investors assess interest rate risk.
Syntax
MDURATION(settlement, maturity, coupon, yld, frequency, [basis])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| settlement | Date |
Yes | Security settlement date (post-issue trade date) |
| maturity | Date |
Yes | Security maturity/expiration date |
| coupon | Number |
Yes | Annual coupon interest rate |
| yld | Number |
Yes | Annual yield to maturity |
| frequency | Number |
Yes | Coupon payment frequency per year |
| basis | Number |
No | Day count basis (optional) |
Using the MDURATION Function
MDURATION is crucial for bond investors and portfolio managers. It quantifies how much a bond's price will change for a 1% change in yield, enabling better risk assessment and portfolio optimization strategies.
Common MDURATION Examples
Basic Bond Duration Calculation
=MDURATION(DATE(2008,1,1), DATE(2016,1,1), 0.08, 0.09, 2, 1)Calculates modified duration for 8% coupon bond maturing in 2016, yield 9%, semiannual payments using actual/actual basis. Result: approximately 5.736 years.
Corporate Bond Analysis
=MDURATION(A2, B2, C2, D2, 2)Analyzes corporate bond using cell references for settlement date (A2), maturity (B2), coupon (C2), and yield (D2) with semiannual payments.
Frequently Asked Questions
Common Errors and Solutions
#NUM!
Cause: Settlement date equals or after maturity date
Solution: Ensure settlement < maturity
#NUM!
Cause: Frequency not 1, 2, or 4
Solution: Use only 1 (annual), 2 (semiannual), or 4 (quarterly)
#VALUE!
Cause: Invalid date format for settlement or maturity
Solution: Use DATE() function: DATE(2008,1,1)
#NUM!
Cause: Negative coupon rate or yield
Solution: Use positive decimal values (0.08 = 8%)
Notes
- Always use DATE(year,month,day) for dates to avoid text parsing issues
- Duration increases with time to maturity and decreases with higher coupon rates
- Excel stores dates as serial numbers (Jan 1, 1900 = 1)
- Lower modified duration indicates lower interest rate risk
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+