Bond Duration in Excel: Calculate Modified & Macaulay (2026)

A fixed income portfolio manager once lost $34 million in a single trading day because her analyst confused modified duration with Macaulay duration on a $2 billion bond position. The difference was small on paper — less than half a year — but when ten-year yields jumped 80 basis points, it translated into a wrong-sized hedge and a very uncomfortable risk committee meeting. Bond duration in Excel is not a theoretical CFA topic. It is the single most important number fixed income analysts calculate every day, and getting the mechanics right separates a junior analyst from someone you can trust with real capital.

This guide walks through every bond duration calculation you need — Macaulay, modified, effective, and portfolio duration — plus convexity, all built from scratch in Excel with working formulas you can drop into your next trade desk model.

What Is Bond Duration and Why Does It Matter?

Bond duration measures a bond's price sensitivity to interest rate changes, expressed in years. It tells you approximately how much a bond's price will move for a 1% change in yield. A 7-year duration means a roughly 7% price drop if rates rise 1%, and a 7% price gain if rates fall 1%. Duration is the foundation of interest rate risk management, hedging ratios, and liability-driven investing.

There are four duration measures every fixed income analyst must know, and they are not interchangeable. Using the wrong one in a P&L estimate or a hedge calculation produces results that can be off by multiple percentage points on a large book.

Duration Type	What It Measures	When to Use
Macaulay Duration	Weighted-average time to receive cash flows	Immunization, matching liabilities
Modified Duration	% price change for 1% yield change	Interest rate risk, hedging
Effective Duration	Price sensitivity for bonds with options	Callables, MBS, putable bonds
Dollar Duration (DV01)	$ price change for 1bp yield change	Portfolio-level risk budgeting

ℹ️ Note: The DURATION and MDURATION functions in Excel return annualized figures regardless of the bond's coupon frequency. Always confirm your yield input is stated in the same frequency convention as your coupon payments.

How Do You Calculate Macaulay Duration in Excel?

Macaulay duration is the weighted-average time until a bondholder receives the bond's cash flows, where each time period is weighted by the present value of the cash flow received at that time, divided by the bond's price. In Excel, you can calculate it two ways: using the built-in DURATION function or building it from first principles with a cash flow table.

Method 1: The Built-In DURATION Function

Excel's DURATION function handles standard coupon bonds in a single formula:

=DURATION(settlement, maturity, coupon, yld, frequency, [basis])

Where:

• settlement — the bond's purchase date
• maturity — the bond's maturity date
• coupon — the annual coupon rate (decimal, e.g., 0.05 for 5%)
• yld — the annual yield to maturity (decimal)
• frequency — 1 (annual), 2 (semi-annual), or 4 (quarterly)
• basis — day count convention (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360)

Example: a 5-year bond with a 4% annual coupon, 3.5% YTM, semi-annual payments, settled today:

=DURATION(DATE(2026,4,22), DATE(2031,4,22), 0.04, 0.035, 2, 0)

This returns approximately 4.60 years.

Method 2: Building Macaulay Duration From Scratch

When you need full visibility into the math — for audits, interviews, or non-standard bonds — build a cash flow table:

Column	Contents	Formula
A	Period (1 to N)	1, 2, 3...
B	Time in years	=A2/frequency
C	Cash flow	Coupon × Face / freq (plus Face at maturity)
D	Discount factor	=1/(1+YTM/freq)^A2
E	PV of cash flow	=C2*D2
F	Weighted time	=B2*E2

Macaulay duration is then:

=SUM(F:F)/SUM(E:E)

The SUM(E:E) equals the bond price (the sum of discounted cash flows), and SUM(F:F) is each cash flow's present value multiplied by the time at which it is received. The ratio is the time-weighted average — the textbook Macaulay definition. This present-value-weighted cash flow approach shares the same mathematical foundation as a DCF model in Excel — the key difference is that bond cash flows (coupons and par) are contractually fixed, while a DCF projects and discounts uncertain free cash flows from an operating business.

💡 Pro Tip: If your cash flow table and the DURATION function return different numbers, the culprit is almost always the day count convention. The built-in function uses the basis argument, while a hand-built table uses simple year fractions. Match them or expect a gap of 2–5 basis points on duration.

How Does Modified Duration Differ From Macaulay Duration?

Modified duration adjusts Macaulay duration to express sensitivity as a percentage price change for a 1% change in yield. The formula is:

Modified Duration = Macaulay Duration / (1 + YTM / frequency)

In Excel, use the MDURATION function, which takes the same arguments as DURATION:

=MDURATION(DATE(2026,4,22), DATE(2031,4,22), 0.04, 0.035, 2, 0)

This returns roughly 4.52 years for the earlier example. The interpretation: if yields rise by 1%, the bond's price will fall by approximately 4.52%.

For quick sanity checks, you can derive modified duration from Macaulay in one cell:

=macaulay_duration/(1+ytm/frequency)

The Price Change Formula Every Trader Uses

Once you have modified duration, estimating price change from a yield move is trivial:

% Price Change ≈ -Modified Duration × ΔYield

For a bond with modified duration of 7.2 and a 25bp yield increase:

=-7.2*0.0025

This returns -1.8%, meaning a roughly 1.8% price drop. For a $10 million position, that is $180,000 of mark-to-market loss — the kind of number you want to get right before the 9 AM risk meeting.

To stress-test that exposure across a range of yield scenarios, build a two-way sensitivity table in Excel with yield shocks on one axis and position sizes on the other — the risk matrix your committee expects to see.

⚠️ Warning: Modified duration is a linear approximation. It breaks down for large yield moves — typically anything over 50bp — because the price-yield relationship is curved, not linear. For large shocks, you must add a convexity adjustment (covered below) or your P&L estimate will be systematically wrong.

How Do You Calculate Bond Convexity in Excel?

Bond convexity measures the curvature of the price-yield relationship — the second derivative of price with respect to yield. Excel has no built-in convexity function, so you have to build it. The standard formula is:

Convexity = (1 / (P × (1+y)²)) × Σ [CFt × t × (t+1) / (1+y)^t]

Building Convexity in a Cash Flow Table

Add two columns to the Macaulay duration table:

Column	Contents	Formula
G	t × (t+1)	=A2*(A2+1)
H	Convexity contribution	=E2*G2

Then, where P is the bond price and y is the periodic yield:

Convexity = SUM(H:H) / (P × (1+y)^2 × frequency^2)

In Excel:

=SUM(H:H)/(SUM(E:E)*(1+ytm/frequency)^2*frequency^2)

The frequency^2 term annualizes convexity — a common pitfall for analysts building this from scratch for the first time. Miss it, and your convexity is off by a factor of 4 for semi-annual bonds.

Example: A 10-year, 5% coupon bond with 4% YTM and semi-annual payments has a Macaulay duration of ~7.94 years, modified duration of ~7.79, and annualized convexity of ~75.4. These three numbers fully characterize the bond's interest rate behavior for typical market moves.

Convexity Adjustment to Price Change

With convexity in hand, the full second-order price estimate is:

% Price Change ≈ (-Modified Duration × ΔYield) + (0.5 × Convexity × ΔYield²)

In Excel:

=-mod_duration*delta_yield + 0.5*convexity*delta_yield^2

For the same 10-year bond if yields jump 100bp (1%):

• Duration estimate alone: -7.79%
• Convexity adjustment: +0.5 × 75.4 × 0.01² = +0.377%
• Combined estimate: -7.41%

On a $100 million position, that 0.38% convexity adjustment is $380,000 — the difference between a decent hedge and a blown budget.

What Is Effective Duration and When Do You Use It?

Effective duration measures price sensitivity when cash flows themselves change with interest rates — as they do for callable bonds, putable bonds, mortgage-backed securities, and any bond with embedded optionality. Macaulay and modified duration assume fixed cash flows, so they systematically understate risk for these instruments.

The effective duration formula uses a numerical shock to the yield curve:

Effective Duration = (P- − P+) / (2 × P0 × Δy)

Where:

• P- = bond price if yields fall by Δy
• P+ = bond price if yields rise by Δy
• P0 = bond price at current yields
• Δy = size of the yield shock (e.g., 0.0025 for 25bp)

In Excel, build three pricing columns — one at the base yield, one at yield + Δy, one at yield - Δy — then compute:

=(price_down - price_up)/(2*price_base*delta_y)

For a callable bond, you'll notice effective duration is typically shorter than modified duration. That's because the call option caps upside when rates fall, truncating the price response. Ignoring this for a callable-heavy portfolio will leave you over-hedged and under-performing in a rally.

graph TD
    A[Bond Type] --> B{Has Embedded Options?}
    B -->|No - Fixed Cash Flows| C[Use Modified Duration]
    B -->|Yes - Callable/Putable/MBS| D[Use Effective Duration]
    C --> E[MDURATION function]
    D --> F[Price under yield shocks]
    F --> G[= P- minus P+ / 2 × P0 × Δy]
    E --> H[Add Convexity for large moves]
    G --> H
    H --> I[Hedge Ratio Calculation]

How Do You Calculate Portfolio Duration in Excel?

Portfolio duration is the market-value-weighted average of the durations of the individual bonds in the portfolio. It is not weighted by face value, par value, or book value — common mistakes that lead to misaligned hedges.

The formula:

Portfolio Duration = Σ (wi × Di)

Where wi is bond i's share of total portfolio market value and Di is bond i's modified duration.

In Excel, set up columns for each bond:

Column	Contents
A	Bond CUSIP/Name
B	Market Value
C	Modified Duration
D	Weight = =B2/SUM($B$2:$B$100)
E	Weighted Duration = =C2*D2

Portfolio duration:

=SUM(E:E)

Or in a single cell with SUMPRODUCT:

=SUMPRODUCT(B2:B100, C2:C100)/SUM(B2:B100)

DV01 — The Dollar-Weighted Version

Institutional desks often work in DV01 (dollar value of a basis point) rather than duration. DV01 is the actual dollar change in bond price for a 1bp yield move:

DV01 = Modified Duration × Price × 0.0001

In Excel:

=mod_duration*market_value*0.0001

DV01 is additive across a portfolio — unlike duration, which must be value-weighted. Summing individual bond DV01s gives the portfolio DV01 directly, which is why risk managers prefer it for hedge-sizing — and why it feeds naturally into portfolio optimization models in Excel that allocate across fixed income and equity. For credit analysts evaluating the issuing company's debt capacity rather than the investor's price sensitivity, a debt schedule in Excel models the same fixed-income positions from the borrower's perspective — using DSCR coverage ratios rather than duration to assess risk.

💡 Pro Tip: When building a duration-matched hedge with Treasury futures, size the hedge as (Portfolio DV01) / (Futures DV01) rounded to the nearest contract. Don't match by duration alone — DV01 bakes in position size and avoids the classic error of under-hedging a large, low-duration book.

Common Mistakes When Calculating Bond Duration in Excel

Even experienced analysts trip over these issues. Every one of them has cost a real desk real money.

1. Mixing Coupon Frequency and Yield Frequency

If your bond pays semi-annually, your YTM must be stated semi-annually (or converted). The MDURATION function expects annualized inputs; a hand-built table may need periodic inputs. Pick one convention and be ruthless.

2. Wrong Day Count Convention

Treasuries use actual/actual (basis = 1). Corporate bonds typically use 30/360 (basis = 0). Using the wrong basis shifts duration by several basis points — small but enough to fail an audit.

3. Forgetting Accrued Interest in Price

DURATION and MDURATION calculate based on the clean price (no accrued interest). If you're reconciling to a dirty-price trade ticket, subtract the accrued coupon first or your numbers won't tie out.

4. Ignoring Convexity on Large Yield Shocks

For a 2% yield move, the duration-only estimate can be off by 100bp or more. Always include the convexity term when shocks exceed 50bp.

5. Using Modified Duration on Callable Bonds

This is the most expensive mistake in the list. A callable corporate with modified duration of 8 might have effective duration of 3. Hedging off the wrong number leaves you badly exposed when the bond is actually called.

graph LR
    A[Get Bond Inputs] --> B[Verify Day Count]
    B --> C[Check Coupon Frequency]
    C --> D{Embedded Options?}
    D -->|No| E[MDURATION / DURATION]
    D -->|Yes| F[Effective Duration]
    E --> G[Add Convexity Adjustment]
    F --> G
    G --> H[Compute DV01]
    H --> I[Portfolio Aggregation]

Each of these mistakes traces back to a convention mismatch that manual review misses. The six-phase financial model audit checklist for Excel — covering formula consistency checks, hardcode detection, and precision tests — applies directly to rate-sensitive workbooks and catches issues like these before they reach a risk committee.

How Do You Build a Reusable Bond Duration Calculator in Excel?

A polished calculator accepts any bond inputs and returns all four duration measures, convexity, and estimated price changes. Structure it as follows:

Inputs Tab

• Settlement date (cell B2)
• Maturity date (cell B3)
• Coupon rate (cell B4)
• Yield to maturity (cell B5)
• Face value (cell B6)
• Payment frequency (cell B7)
• Day count basis (cell B8)
• Yield shock in basis points (cell B9)

Output Cells

Use cell names for readability — highlight B2:B9, press Ctrl+Shift+F3, and create names from the left column:

Macaulay_Duration   =DURATION(Settlement, Maturity, Coupon, YTM, Frequency, Basis)
Modified_Duration   =MDURATION(Settlement, Maturity, Coupon, YTM, Frequency, Basis)
Bond_Price          =PRICE(Settlement, Maturity, Coupon, YTM, 100, Frequency, Basis)
DV01                =Modified_Duration * Bond_Price * 0.0001
Est_Price_Change    =-Modified_Duration * (Shock_BPS/10000)

For convexity, anchor a 60-row cash flow table referencing the named inputs. A single-sheet calculator like this becomes the desk's standard — every new trade gets dropped into it and the key risk numbers pop out instantly.

Wrap It in a LAMBDA for Maximum Reusability

If you use Excel 365, encapsulate the full price-change estimation in a named LAMBDA function:

BOND_PCT_CHG = LAMBDA(mod_dur, conv, dy, -mod_dur*dy + 0.5*conv*dy^2)

Now any cell can call =BOND_PCT_CHG(7.8, 75.4, 0.01) and return the duration-plus-convexity price estimate without rebuilding the formula every time.

Frequently Asked Questions

What is the difference between modified duration and effective duration?

Modified duration assumes fixed cash flows and measures price sensitivity mathematically from a bond's YTM. Effective duration uses numerical yield shocks to capture the true sensitivity when cash flows themselves change — as with callable bonds or MBS. For option-free bonds, the two are essentially identical. For bonds with embedded options, effective duration is the only correct measure.

Can I calculate duration for zero-coupon bonds in Excel?

Yes — and for zero-coupon bonds, Macaulay duration exactly equals the bond's time to maturity, because 100% of the cash flow occurs at the end. Use =DURATION() with a coupon of 0, and you'll see this directly. Modified duration is then simply =maturity_years/(1+YTM/frequency).

Why does my bond duration not match Bloomberg?

The usual suspects are day count basis (Bloomberg often uses actual/actual for Treasuries), settlement convention (T+1 vs T+2), and whether the number is modified or effective duration. Bloomberg's OAS duration for callables is effective duration — if you compare it to MDURATION, you'll see differences of several years for deeply callable bonds.

Is duration additive across a portfolio?

No — duration is market-value-weighted, not additive. However, DV01 is additive, which is why institutional risk systems report DV01 at every level of aggregation. If you need a single number that sums cleanly from bond to sector to portfolio, use DV01.

What is key rate duration and how does it differ from modified duration?

Key rate duration measures sensitivity to a twist in specific points of the yield curve (e.g., 2-year, 5-year, 10-year, 30-year), not a parallel shift. Modified duration assumes every yield on the curve moves by the same amount. For curve-steepening or flattening scenarios, key rate durations capture the real risk that a single modified duration number hides entirely.

Final Thoughts

Bond duration is deceptively simple on a whiteboard and frustratingly easy to get wrong in production Excel. The formulas in this guide — Macaulay, modified, effective, portfolio, and convexity — cover 95% of what a fixed income analyst, PM, or risk officer does day-to-day. Nail these mechanics, name your cells, wrap common calculations in LAMBDA or templates, and you'll spend less time checking math and more time making decisions.

If you're building duration-heavy workbooks regularly, VeloraAI's Excel add-in can generate these cash flow tables, DURATION/MDURATION formulas, and convexity adjustments from plain-English prompts — useful when you need to spin up a new trade model fast without rebuilding the same calculator for the hundredth time.

Next step: open your last fixed income model, rebuild the duration calculation from a cash flow table, and reconcile to the built-in function. Any gap you find is almost always day count or frequency — and finding it once means you'll never miss it again.