Quick Navigation
DURATION Function
Summary
The DURATION function calculates the Macaulay duration for a bond assuming a $100 par value. This advanced financial metric measures a bond's price sensitivity to yield changes by computing the weighted average time to receive cash flows.
Syntax
DURATION(settlement, maturity, coupon, yld, frequency, [basis])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| settlement | Date |
Yes | The date the security is delivered to or by the buyer |
| maturity | Date |
Yes | The date the security reaches maturity |
| coupon | Number |
Yes | Security's annual coupon interest rate |
| yld | Number |
Yes | Security's annual yield to maturity |
| frequency | Number |
Yes | Number of coupon payments per year |
| basis | Number |
No | Day count basis for interest calculation |
Using the DURATION Function
DURATION is crucial for bond investors and portfolio managers. Macaulay duration helps predict how bond prices will change with interest rate movements. Longer duration bonds are more sensitive to yield changes, making this function essential for risk assessment and immunization strategies.
Common DURATION Examples
Calculate Duration for Semiannual Coupon Bond
=DURATION("7/1/2018","1/1/2048",0.08,0.09,2,1)Returns approximately 10.92 years for a 30-year bond with 8% coupon, 9% yield, semiannual payments using actual/actual basis
Annual Coupon Bond Duration
=DURATION(DATE(2024,6,15),DATE(2034,6,15),0.05,0.045,1,0)Calculates duration for 10-year annual coupon bond using US 30/360 convention
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: Settlement or maturity not recognized as valid dates
Solution: Use DATE function or ensure cells contain proper date values
#NUM!
Cause: Coupon or yield negative, frequency not 1/2/4, or settlement >= maturity
Solution: Verify coupon/yield > 0, frequency valid, settlement before maturity
#NUM!
Cause: Basis outside range 0-4
Solution: Use basis values: 0,1,2,3, or 4 only
Notes
- Always use DATE() function for dates to avoid text parsing issues
- Excel truncates settlement, maturity, frequency, basis to integers
- Duration increases with time to maturity and decreases with higher yields
- Function assumes $100 par value regardless of actual bond face value
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003 and earlier
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+