Bond duration measures how sensitive a bond’s price is to changes in interest rates, expressed in years. The most common version, Macaulay duration, calculates the weighted average time until you receive all of a bond’s cash flows. From there, you can derive modified duration to estimate exactly how much a bond’s price will move when rates shift. Here’s how to work through each calculation.
Macaulay Duration: The Core Calculation
Macaulay duration answers a straightforward question: on average, how long do you wait to get your money back from a bond, weighted by the size of each payment? A bond that pays most of its value early (through large coupons) has a shorter duration than one that pays most of its value at maturity.
The formula looks like this:
Macaulay Duration = Sum of (t × PV of each cash flow) ÷ Current Bond Price
Where t is the time period when each payment arrives, and PV means present value. The variables you need are:
- t = the period number (1, 2, 3, etc.)
- C = the periodic coupon payment
- y = the periodic yield (annual yield divided by the number of payments per year)
- n = the total number of periods until maturity
- M = the face value (par value) paid at maturity
To calculate it step by step, start by listing every cash flow the bond will pay and when. For a bond with a $1,000 face value, a 6% annual coupon, and 3 years to maturity paying annually, you’d have three cash flows: $60 in year 1, $60 in year 2, and $1,060 in year 3 (the final coupon plus the face value).
Next, calculate the discount factor for each period. The discount factor is 1 ÷ (1 + y)^t, where y is your periodic yield. If the bond yields 6% annually, the discount factor for year 1 is 1 ÷ 1.06 = 0.9434, for year 2 it’s 1 ÷ 1.06² = 0.8900, and for year 3 it’s 1 ÷ 1.06³ = 0.8396.
Multiply each cash flow by its discount factor to get the present value. Then multiply each present value by its period number. Finally, add up all those weighted values and divide by the bond’s current price. If the bond is priced at par ($1,000), the math for our example works out to roughly 2.83 years. That tells you the average time, weighted by dollars, until you collect the bond’s cash flows.
A Shortcut for Zero-Coupon Bonds
Zero-coupon bonds skip all this math. Because they make no coupon payments and return everything at maturity, their Macaulay duration equals their time to maturity. A zero-coupon bond maturing in 10 years has a Macaulay duration of exactly 10 years. This also makes zero-coupon bonds the most interest-rate-sensitive bonds at any given maturity, since no early payments pull the weighted average forward.
Modified Duration: Measuring Price Sensitivity
Macaulay duration tells you a weighted-average time, but investors usually care more about a practical question: if interest rates move by 1%, how much will this bond’s price change? That’s what modified duration answers.
The formula is simple once you have Macaulay duration:
Modified Duration = Macaulay Duration ÷ (1 + YTM / n)
Here, YTM is the bond’s annual yield to maturity and n is the number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).
Suppose a bond has a Macaulay duration of 2.753 years, a yield to maturity of 5%, and pays coupons annually. Its modified duration is 2.753 ÷ 1.05 = 2.62. That number tells you the bond’s price will move approximately 2.62% in the opposite direction for every 1 percentage point change in interest rates. If rates rise by 1%, the bond’s price drops about 2.62%. If rates fall by 1%, the price rises about 2.62%.
This inverse relationship is the key insight. Higher duration means more volatility. A bond with a modified duration of 7 will swing roughly 7% in price for a 1% rate move, making it far more sensitive than a bond with a duration of 2.
Effective Duration: Handling Callable Bonds
Macaulay and modified duration assume a bond’s cash flows are fixed. But callable bonds, putable bonds, and mortgage-backed securities have embedded options that can change when and how much the bond pays. A company might call its bond early if rates drop, cutting off your future coupon payments. For these securities, you need effective duration.
Effective duration uses a different approach. Instead of working from individual cash flows, it estimates price sensitivity by modeling what happens to the bond’s price under two scenarios: rates go up a small amount, and rates go down the same amount.
The formula is:
Effective Duration = (P₁ − P₂) ÷ (2 × P₀ × ΔY)
- P₀ = the bond’s current price (per $100 of par value)
- P₁ = the estimated price if yields decrease by ΔY
- P₂ = the estimated price if yields increase by ΔY
- ΔY = the assumed change in yield (expressed as a decimal)
For example, say a callable bond is currently priced at $98. If yields drop by 0.25%, a pricing model estimates the bond would be worth $99.50. If yields rise by 0.25%, the bond would be worth $96.00. Plugging those in: (99.50 − 96.00) ÷ (2 × 98 × 0.0025) = 3.50 ÷ 0.49 = 7.14. The effective duration is 7.14 years.
One important detail: when a callable bond’s coupon rate is well below current market rates, there’s no incentive for the issuer to call it. In that situation, the bond behaves like a regular fixed-rate bond, and its effective duration will be close to its modified duration. The two measures diverge most when rates are near or below the coupon rate, making a call more likely.
Calculating Duration in Excel
Excel has built-in functions that handle the math for you. The DURATION function returns Macaulay duration, and the MDURATION function returns modified duration. Both use the same inputs:
=MDURATION(settlement, maturity, coupon, yld, frequency, [basis])
- Settlement = the date you buy (or are analyzing) the bond
- Maturity = the date the bond matures
- Coupon = the annual coupon rate as a decimal (enter 0.06 for 6%)
- Yld = the annual yield to maturity as a decimal
- Frequency = number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly)
- Basis = optional day-count convention (0 or omitted for the standard US 30/360 method; 1 for actual/actual)
Enter dates using Excel’s DATE function (e.g., DATE(2025,1,15)) rather than typing date strings, which can cause errors depending on your regional settings. The function will return an error if the coupon or yield is negative, if the settlement date is on or after the maturity date, or if the frequency isn’t 1, 2, or 4.
For a quick example: =MDURATION(DATE(2025,6,1), DATE(2030,6,1), 0.05, 0.06, 2) would give you the modified duration of a 5-year bond with a 5% coupon, yielding 6%, paying semiannually. Excel does not have a built-in effective duration function, so for bonds with embedded options you’ll need to calculate the three price scenarios and apply the formula manually.
What Duration Tells You in Practice
Duration is most useful as a comparison tool. When choosing between bonds or bond funds, a higher duration number means more interest rate risk. If you expect rates to fall, longer-duration bonds will gain more value. If you expect rates to rise, shorter-duration bonds will lose less.
Keep in mind that duration gives you a linear approximation. It works well for small rate changes (say, 0.25% to 1%), but becomes less accurate for larger moves. For big rate swings, a second measure called convexity adjusts for the curvature in the price-yield relationship. For most individual investors comparing bonds or evaluating a bond fund’s sensitivity, duration alone provides a reliable estimate of how much price risk you’re taking on.