This function calculates the modified duration for fixed-interest securities.
Syntax:
MDURATION(Settlemen; Maturity; Nominal_Interest; Yield; Frequency; Basis)
Arguments:
- Settlement (required) – The date when ownership of the security is transferred.
- Maturity (required) – The date when the loan (represented by the security) is repaid.
- Nominal_Interest (required) – The annual coupon rate (agreed interest rate) of the security.
- Yield (required) – The market interest rate on the settlement date, used to discount future cash flows in the duration calculation.
- Frequency (required) – The number of interest payments per year. Valid options:
- 1 = Annual
- 2 = Semiannual
- 4 = Quarterly
- Basis (optional) – The day-count convention. If omitted, Excel defaults to Basis = 0.
Notes:
- Date arguments are truncated to integers (no time component).
- Frequency and Basis must be integers (decimal places are truncated).
- Errors:
- #VALUE! – Invalid dates or non-numeric inputs where required.
- #NUM! – Invalid numbers for non-date arguments.
Background:
Modified duration measures a bond’s price sensitivity to interest rate changes, crucial for risk management. Unlike stocks, fixed-income securities see reduced price volatility as maturity nears because redemption value is fixed.
Mathematically:
- Duration = Macaulay Duration (see DURATION()).
- MDURATION() returns the scaling factor for estimating relative price change due to interest rate shifts (unsigned).
- For multiple annual payments, the yield is distributed evenly across periods.
Example:
A 4.5% federal bond (issued 2003) had:
- Yield (Aug 31, 2010): 0.61%
- Maturity: Jan 4, 2030
- Price: $109.027
Scenario:
- If yield rises +0.5% (to 1.11%), the price drops to $107.800 (–1.13%).
Using MDURATION():
- Modified Duration = 2.208
- Estimated price change = 2.208 × 0.5% ≈ 1.1% decline
- New price ≈ $107.824 (vs. actual $107.800)
Conclusion:
Modified duration helps investors quickly assess risk without complex recalculations.