Financial
DURATION Function in Excel
Returns the annual duration of a security with periodic interest payments.
Syntax
- =DURATION(settlement, maturity, coupon, yld, frequency, [basis])
Arguments
- settlement (required): Settlement date
- maturity (required): Maturity date
- coupon (required): Annual coupon rate
- yld (required): Annual yield
- frequency (required): Payments per year
- basis (optional): Day count basis
Examples
- =DURATION("1/1/2024", "1/1/2034", 0.08, 0.09, 2) - Macaulay duration - Result: Years
Excel DURATION function documentation
- DURATION returns Macauley duration (in years) for a security with periodic interest payments.
- Syntax: =DURATION(settlement, maturity, coupon, yld, frequency, [basis]).
- Use duration to estimate bond price sensitivity to interest-rate moves.
- Duration analytics often complement [PRICE](/functions/price/), [YIELD](/functions/yield/), [PV](/functions/pv/), and [Financial functions](/categories/financial/).
DURATION parameter guide
- settlement and maturity define the cash-flow horizon.
- coupon and yld are annualized rates used in weighted present-value timing.
- frequency selects coupon periods per year.
- basis controls day-count assumptions and should match instrument specs.
Step-by-step: rate sensitivity check for a bond
- Step 1 — Enter bond terms and current yield assumptions.
- Step 2 — Compute base duration with =DURATION(...).
- Step 3 — Recalculate [PRICE](/functions/price/) under +50 bps and -50 bps yield shocks.
- Step 4 — Compare duration estimate vs actual price shift for convexity intuition.
- Step 5 — Use this as a quick risk screen before deeper fixed-income modeling.
DURATION vs MDURATION
- DURATION returns Macauley duration (time-weighted cash-flow horizon).
- MDURATION returns modified duration, directly tied to percentage price sensitivity.
- Both depend on yield, coupon, and maturity assumptions.
- Choose MDURATION when you need immediate dP/P approximation for small yield changes.
Worked examples to copy
- Basic duration: =DURATION(DATE(2026,6,30),DATE(2034,6,30),0.04,0.05,2,0).
- Annual coupon case: =DURATION(settle,mat,coupon,yield,1,1).
- Short maturity typically means lower duration.
- Low coupon usually increases duration, all else equal.
People also ask
- Can duration be greater than maturity? — Not for standard fixed-coupon bonds.
- Why does duration drop over time? — Remaining cash-flow horizon shortens.
- Is duration enough for large shocks? — Add convexity for larger rate moves.
- How to benchmark projects? — For non-bond projects, use [NPV](/functions/npv/) and [IRR](/functions/irr/).
Common errors
- #NUM! if parameters invalid
Use cases
- Interest rate risk
- Bond analysis
- Portfolio management
Frequently asked questions
- What is DURATION (Macaulay duration)? DURATION calculates the weighted average time to receive a bond's cash flows, measured in years. It measures interest rate sensitivity - higher duration means greater price sensitivity to rate changes. Essential for bond portfolio management.
- How do I interpret duration? Duration of 5 years means: (1) weighted average time to receive cash flows is 5 years, and (2) approximately, a 1% rate increase causes ~5% price decrease. Longer duration = more interest rate risk. Zero-coupon bonds have duration equal to maturity.
- What's the difference between DURATION and MDURATION? DURATION is Macaulay duration (in years). MDURATION is Modified duration = Macaulay / (1 + yield/frequency). Modified duration directly estimates percentage price change: ΔPrice% ≈ -MDURATION × Δyield. Use MDURATION for price sensitivity calculations.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use DURATION
- Interest rate risk — common Financial scenario for DURATION.
- Bond analysis — common Financial scenario for DURATION.
- Portfolio management — common Financial scenario for DURATION.
DURATION in the Financial category
- Browse all Financial functions at /categories/financial/ for related formulas.
- DURATION syntax: =DURATION(settlement, maturity, coupon, yld, frequency, [basis])
- settlement (required): Settlement date
- maturity (required): Maturity date
- coupon (required): Annual coupon rate
- yld (required): Annual yield
- Confirm DURATION arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm DURATION arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- MDURATION (/functions/mduration/): Returns the modified Macaulay duration for a security.
- PRICE (/functions/price/): Returns the price per $100 face value of a security that pays periodic interest.
- YIELD (/functions/yield/): Returns the yield on a security that pays periodic interest.
Errors to watch for
- #NUM! if parameters invalid — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter DURATION on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
DURATION worked examples to copy
- =DURATION("1/1/2024", "1/1/2034", 0.08, 0.09, 2) — Macaulay duration. Expected result: Years.
DURATION reference summary for crawlers and offline review
- DURATION belongs to the Financial category in Excel. Returns the annual duration of a security with periodic interest payments.
- Full syntax: =DURATION(settlement, maturity, coupon, yld, frequency, [basis]). Open /functions/duration/ for parameters, FAQs, and related pages.
- Common mistakes: #NUM! if parameters invalid
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.