This function calculates the Macauley Duration (named after its developer) of a fixed-interest security, representing the weighted average time until all cash flows (interest and principal) are received.
Syntax
DURATION(Settlement; Maturity; Nominal_Interest; Yield; Frequency; [Basis])
Arguments
- Settlement (required)
The date of purchase for the security. Time values are truncated. - Maturity (required)
The maturity date when the principal is repaid. Time values are truncated. - Nominal_Interest (required)
The annual coupon rate (e.g., 0.0325 for 3.25%). Must be ≥ 0. - Yield (required)
The annual yield to maturity (market discount rate). Must be ≥ 0. - Frequency (required)
Number of coupon payments per year:- 1 = Annual
- 2 = Semi-annual
- 4 = Quarterly
- Basis (optional)
Day-count convention. Defaults to 0 (US (NASD) 30/360).
Error Handling
- #VALUE! if dates or required numbers are invalid.
- #NUMBER! if negative values are entered for Nominal_Interest, Yield, or Frequency.
Background
Macauley Duration measures a bond’s interest rate sensitivity by calculating the weighted average time to receive all cash flows, discounted at the bond’s yield.
- Key Insight: Bonds with shorter durations are less sensitive to interest rate changes.
- Immunization: If duration matches the investment horizon, price and reinvestment risks offset each other.
Example
Comparison of Two Federal Securities (August 30, 2010)
| Security | Nominal Interest | Maturity | Price | Yield | Duration |
| Federal Loan of 2005 | 3.25% | July 4, 2015 | 109.040 | 1.31% | 4.54 years |
| Federal Medium-Term Bond Series 157 | 2.25% | April 10, 2015 | 104.500 | 1.24% | 4.40 years |
A calculation of the duration returns the following result:
| Security | Duration |
| Federal loan of 2005 | 4.54 years |
| Federal medium-term bond series 157 | 4.40 years |
The federal medium-term bond is preferable. However, the difference regarding the duration is very small. There is also another risk advantage for other debtors, and other terms as well as in regard to tax-related aspects (nominal interest must be reduced depending on the rate of taxation).
Interpretation:
- The medium-term bond (4.40 years) is preferable due to its shorter duration (lower risk).
- Limitations: Market conditions, credit risk, and tax implications may also influence decisions.