CI Market Dashboard

We have put together a collection of market indicators and track them to help you gauge the direction of the market.


CI Analysis Worksheets

Interactive analysis templates covering DuPont analysis (return on equity), valuing stocks the Warren Buffett way, and more to come.


CI Blog Feed

Bullish Sentiment Hits 2014 High
posted 17 hours ago by Wayne Thorp

CI Market Dashboard: Two New Bullish Signals This Week
posted 3 days ago by Wayne Thorp

Free Computational Investing Course @ Coursera
posted 6 days ago by Hareesh Jayanthi

Potential Accounting Scandal at Alibaba As It Plans IPO
posted 6 days ago by Wayne Thorp

Bearish Sentiment Has Biggest Weekly Drop Since August 2013
posted 7 days ago by Wayne Thorp



Computerized Investing > Second Quarter 2013

Bond Duration & Convexity

PRINT | | | | COMMENTS (4) | A A   Reset

by Wayne A. Thorp, CFA

The last two installments of Spreadsheet Corner provided templates on how to calculate bond prices, returns and price sensitivity to changes in interest rates and time to maturity (Fourth Quarter 2012 and First Quarter 2013 issues; available online at ComputerizedInvesting.com). These articles laid the groundwork for a discussion of more advanced, and accurate, measures of bond volatility.

As bond investors, we need to be aware of the impact that changing interest rates (yields) can have on the value of bonds. Volatility measures allow us to compare bonds of differing maturities and coupon rates to find the ones that fit our investment horizon and our forecast of future interest rates. Recall that there is an inverse relationship between yield and bond prices. If interest rates go up, the value of the fixed interest and principal payment you expect to receive from the bond goes down. Conversely, if interest rates fall, the value of bonds will increase. In other words, the bond market rallies when interest rates fall (and, conversely, it falls when interest rates rise).

Long-term bonds are much more sensitive to changes in yields than short-term bonds. A longer term to maturity means it takes longer for investors to recover their initial investment, so the market value of the bond is more sensitive to changes in interest rates. Recall, too, that the coupon rate is the interest rate on the bond that is established at the time the bond is issued. The higher the coupon rate, the less the value of the bond will change in response to interest rate changes. In contrast, the lower the coupon rate, the more the value of the bond will vary with changes in interest rates. Figure 1 from the Fourth Quarter 2012 Spreadsheet Corner article illustrated the relationship between market interest rates and bond prices. Since bond values change inversely to the change in interest rates, a long-term, low-coupon-rate bond will suffer a very great decrease in value when interest rates increase; a short-term, high-coupon-rate bond will change relatively little in value.

When considering different bonds, it is useful to have a measure of price sensitivity to interest rate changes that takes into account both maturity and coupon effects. One such measure is known as duration.

Duration

In a book written for the National Bureau of Economic Research in 1938, Frederick Macaulay first developed the concept of bond duration (although it wasn’t until the 1970s that duration came into common use). Duration basically measures the weighted average amount of time it takes to receive the present value of all the cash flows from a bond, measured in years, such that:

DMac = [Σ tCt ÷ (1 + YTM)t] ÷ [Σ Ct ÷ (1 + YTM)t]

Where:

  • t = the time period in which the cash payment is received
  • Ct = the cash payment received at time t
  • YTM = the per period (typically semiannual) yield to maturity

If the yield to maturity is semiannual, the duration we arrive at is actually the number of semiannual periods. So, in this case, we would divide the result by the payment frequency (two, for semiannual) to arrive at an annual figure.

The fractional term of the duration formula is the present value of each cash flow as a percentage of the bond’s price, which serves as the weighting in the average, and t is the number of periods until each cash flow is received.

Generally speaking:

  • Bond duration is less than term to maturity and will equal the final maturity if and only if there is a single payment at maturity (a zero-coupon bond);
  • There is an inverse relationship between duration and coupon rate; and
  • There is an inverse relationship between duration and yield to maturity.

Excel has a built-on function called DURATION:

DURATION(settlement,maturity,coupon,yld,frequency,basis)

Such that:

  • Settlement is the bond’s settlement date, which is the date after the issue date when the security is traded to the buyer;
  • Maturity is the bond’s maturity data;
  • Coupon is the bond’s annual coupon rate;
  • Yld is the bond’s annual yield;
  • Frequency is the number of coupon payments per year—for annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4; and
  • Basis is the type of day count basis to use (typically 0 for U.S. bonds).

If this function is not available in your version of Excel, and returns the #NAME? error, you can download and install the Analysis ToolPak add-in from the Microsoft Office website.

Figure 1 illustrates the Macaulay duration for two different bonds with the same time to maturity (20 years) and yield (5%), but differing coupon rates (4.0% for Bond 1 and 2.0% for Bond 2) and the impact of an increase in yield from 5% to 6%. For Bond 1, the duration formula entered in cell B19 is:

=DURATION(B2,B3,B5,B6,B9,B10)

We then copied this formula to C19 to find the duration of Bond 2.

Duration works best only for small changes in yield, as we will discuss later, so in this example we are only using a 1% change from 5% to 6%. The duration of Bond 1 is 13.47 years, and for Bond 2 it is 15.42 years. Since Bond 2 has the longer duration, it should be more sensitive to interest rate changes than Bond 1. To verify this, we calculated the values of the bonds at yields of both 5% and 6% in cells B14:C15 using the PRICE function we’ve discussed in the two previous Spreadsheet Corner articles. Increasing the yield by 1% led to a 12.08% drop in price for Bond 1 and a 13.75% decline for Bond 2. So, Bond 2 is more sensitive to changes in yield, as its duration indicated. Using this template, you can see the impact on duration when changing the coupon rates and maturity dates.

Modified Duration

A variation on Macaulay’s duration is modified duration, which is a price sensitivity measure that is the percentage derivative (rate of change) of price with respect to yield. Referring back to our previous example, we see that the Macaulay duration approximates the percentage change in the bond’s price. Changing the yield from 5% to 6% caused a 12.08% price decline for Bond 1, whereas its duration is 13.47 years. Therefore, the Macaulay duration can be used to estimate the percentage change in the value of a bond for a 1% change in interest rates.

Because they affect yield, fluctuating interest rates will affect duration, so the modified duration shows how much the duration changes for each percentage change in yield.

The modified duration is calculated as follows:

DMod = DMac ÷ (1 + YTM ÷ m)

Where:

  • DMac = the Macaulay duration
  • YTM = yield to maturity
  • m = the payment frequency (number of coupon payments per year)

Excel has a built-in function called MDURATION to calculate the modified duration:

MDURATION(settlement,maturity,coupon,yld,frequency,basis)

Such that:

  • Settlement is the bond’s settlement date, which is the date after the issue date when the security is traded to the buyer;
  • Maturity is the bond’s maturity date;
  • Coupon is the bond’s annual coupon rate;
  • Yld is the bond’s annual yield;
  • Frequency is the number of coupon payments per year—for annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4; and
  • Basis is the type of day count basis to use (typically 0 for U.S. bonds).

If this function is not available, and returns the #NAME? error, you can download and install the Analysis ToolPak add-in from the Microsoft Office website.

Figure 1 also shows the modified duration calculations for Bonds 1 and 2, using the same inputs from our example for Macaulay duration. In cell B20 we entered the following formula for Bond 1:

=MDURATION(B2,B3,B5,B6,B9,B10)

We then copied this formula to C20 to find the modified duration of Bond 2.

In both cases, the modified duration is a bit closer to the percentage change figures in cells B17:C17. The modified duration for Bond 1 is 13.14 years and 15.05 years for Bond 2.

While the modified duration offers a closer measure of the percentage change in the bond’s price for a change in yield, it is still only accurate for small changes in yield. This is because duration is a linear approximation to a nonlinear function (more on this a little later).

Using the modified duration, we can approximate the percentage change in a bond’s price for a change in yield:

% Change in Price ≈ -DMod × ΔYTM

In Figure 2, we replicated this formula in cell B22, this time assuming that the yield increased from 5.0% to 5.1% (cells A14:A15):

=–B20*($A15-$A14)

For Bond 1, the predicted price change for this 0.1% increase in yield is a drop of 1.30% (cell B22, which equals the percentage change in cell B17). The same goes for Bond 2 when comparing cell C22 and cell C17.

A Visual Representation

As we mentioned earlier, modified duration only works for small changes in the yield since it is a linear approximation to a nonlinear function. Modified duration is equal to the partial derivative of the price function with respect to the yield, divided by the price of the bond. The slope of any bond value/yield curve can be calculated by multiplying the modified duration by the price of the bond, such that:

Slope = DMod × P = Rise ÷ Run

Figure 3 illustrates the price/yield curve of Bond 1 from our previous examples. We arrived at this curve by calculating the value of Bond 1 across a range of yields to maturity, assuming a 20-year time to maturity, 4% coupon rate paid semiannually, 5% yield and $1,000 redemption value. From cell B11, Bond 1 has a current value of $874.49, which we calculate using the PRICE function we discussed in the previous two articles (indicated by the red horizontal dotted line on the chart) given its 5% yield (indicated by the green vertical dotted line on the chart). By constructing a line with the same slope that is tangent to the curve at the point representing the current price and yield, we can visualize the modified duration of the bond.

Figure 4 contains the data necessary to plot the blue tangent line in Figure 3. In cell I2 we calculate the slope of the line using the formula we provided earlier:

=MDURATION(B2,B3,B5,B6,B9,B10)*B11

We then derive three points through which the line passes. The x values are simply the three yields in cells I3:I5. The midpoint is the current yield of Bond 1, 5%, and the others are arbitrarily set three percentage points above and below. In J3, we entered the formula for the slope-intercept straight line equation (y = mx + b) where m is the slope, x is the x-intercept and b is the y-intercept):

= $I$2+$B$11*($I$4-I3)

We then copied the formula down for cells J4:J5. This shows the prices on the line with the slope that was calculated in cell I2.

We now have a visual representation of modified duration, which we can change by altering the yield in cell B6. When the yield decreases, modified duration will increase, creating a steeper line. Increasing the yield will lead to a flatter line, which implies a lower modified duration.

Convexity

As stated, using modified duration to calculate the change in price for a given change in yield only works for small changes in yield. Figure 3 helps explain this. The price/yield curve is just that: curved. To be specific, it is convex to the origin. Comparing the straight line to that of the price/yield function, we can see how the straight line departs from the price/yield function for large changes in yield. A measure of this curvature is called convexity, which we can use to improve the approximation of the change in price. Convexity can be calculated as:

= [1 ÷ (1 + YTM)2] × [Σ Ct (t2 + t) ÷ (1 + YTM)t]

Where:

  • Ct = the cash payment received at time t
  • t = the time period in which the cash payment is received
  • YTM = the per period (typically semiannual) yield to maturity

As we had to do with the Macaulay duration, we must divide convexity by the payment frequency to convert it to an annual number.

Mathematically speaking, convexity is the second derivative of the price function with respect to the yield, divided by the price of the bond. In other words, you can think of it as the rate of change in a bond’s duration. Simply put, however, it is the measure of the degree of curvature of the price/yield function presented in Figure 3. The higher the convexity, the more curved the function.

Unlike Macaulay duration and modified duration, there is not a convexity function built into Excel. However, we have been using the Famefncs.xlam add-in from Timothy R. Mayes, a professor at the Metropolitan State University of Denver and co-author of “Financial Analysis with Microsoft Excel” (South-Western College Pub, 2011). After downloading this add-in to your PC, you can use its FAME_CONVEXITY function:

FAME_CONVEXITY(settlement,maturity,FV,coupon,yld,frequency)

Such that:

  • Settlement is the bond’s settlement date, which is the date after the issue date when the security is traded to the buyer;
  • Maturity is the bond’s maturity date;
  • FV is the face or redemption value of the bond;
  • Coupon is the bond’s annual coupon rate;
  • Yld is the bond’s annual yield; and
  • Frequency is the number of coupon payments per year—for annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.

Referring back to Figure 1, we entered this formula into cell B21:

=FAME_CONVEXITY(B2,B3,B7,B5,B6,B9)

We then copied this formula into cell C21 for Bond 2.

The convexity of Bond 1 is 226.61 and 275.49 for Bond 2. With these values, we can improve on the approximate percentage change in price, as it now accounts for the curvature of the price/yield function. Using convexity (C), the change in price is calculated as follows:

≈ -DMod × ΔYTM + 0.5C × ΔYTM2

We entered this formula into cell B22:

=-B20*($A15-$A14)+0.5*B21*($A15-$A14)^2

Comparing the Predicted % Change values in cells B22:C22 to the percentage change values in cells B17:C17, the difference is rather negligible and is more accurate for larger changes in the yield.

Beyond improving the predicted percentage change in bond prices, convexity is also useful for comparing bonds of equal durations and yields. In these instances, the more convex a bond is, the less it's affected by interest rates. Therefore, we would prefer a bond with more convexity.

Summary

Our series on bond analysis has examined how to calculate the price of a bond and its yield and how to judge its sensitivity to changes in interest rates. We have put these somewhat complex concepts into templates to aid in your bond analysis.

Click here to download the spreadsheet containing the calculations discussed here.


Discussion

William Hutchinson from AZ posted about 1 year ago:

Interesting article, especially since even tho I have studied the SEC bond pricing publication,"Bond Pricing Calculations", and written formulas for pricing bonds, I had never heard of "duration", "modified duration", "convexity". However if these parameters appear in EXCEL, they must be in use. I have to say tho that the presentations in the first 2 articles giving price and YTM calculations are much more useful. I still don't understand why anyone cares about "duration". Maybe a discussion of the significance of this calculation relative to price and YTM would be useful.

The formulae for yield and price are available in many math reference sources. They are however easily derived from the present value formula,(1+r)^n and a rudimentary knowledge of series. A brief follow on for those with some math background might be of interest.


Joel Shwimer from Massachusetts posted about 1 year ago:

Rather than FAME_CONVEXITY, the spreadsheet in cells B21 and C21 contain DURATION as the function. What am I missing?

Joel


Michael Smith from TN posted 5 months ago:

Downloaded spreadsheet uses external reference:

'C:\Users\wayne.AAIILOCAL\Desktop\FameFncs.xlam'!FAME_CONVEXITY(B2,B3,B7,B5,B6,B9)

How do you acquire this function set?


Michael Smith from TN posted 5 months ago:

There's also no way to download a PDF of the Article.


You need to log in as a registered AAII user before commenting.
Create an account

Log In