DURATION Function
Calculates the Macaulay 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
Macaulay duration of a bond
=DURATION(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 Macaulay duration.
Advanced Examples
Example 1: With actual/actual basis
Treasury bond analysisUse basis 1 for actual/actual
=DURATION(DATE(2024,4,1), DATE(2034,4,1), 0.05, 0.06, 2, 1)How DURATION Works
DURATION calculates the Macaulay duration, which is the present-value-weighted average time until cash flows are received. Higher duration means greater sensitivity to interest rate changes.
Important Notes & Limitations
Settlement must be before maturity.
Frequency must be 1, 2, or 4.
Does not measure modified duration or convexity.
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
DURATION
durationPractice DURATION with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the DURATION function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps