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

WSJ: Ukrainian Conflict Hurting Tech Sector
posted 9 hours ago by Wayne Thorp

Report: Apple Readying Production of iPhone 6
posted 11 hours ago by Wayne Thorp

Thomson Reuters Creates “Alpha Now” Website
posted 12 hours ago by Jaclyn McClellan

CI Market Dashboard: No Changes This Week
posted 1 day ago by Wayne Thorp

Google Inc. Beats on Revenues, Misses on Earnings
posted 5 days ago by Jaclyn McClellan



Computerized Investing > First Quarter 2013

Bond Prices, Returns and Volatility

PRINT | | | | Add your comment! | A A   Reset

by Wayne A. Thorp, CFA

In the Fourth Quarter 2012 installment of Spreadsheet Corner (available online at the Computerized Investing website), we started a discussion on using a spreadsheet to calculate the volatility of bond prices given changes in coupon rate and time to maturity.

The coupon rate is the interest rate on the bond that is established at the time it is issued. Recall that there is an inverse relationship between bond values and interest rates: If market interest rates go up, the value of the fixed interest and principal payments you expect to receive goes down. Conversely, if interest rates fall, the value of the bonds will increase. In other words, the bond market rallies when interest rates fall.

In that previous article, we created a table showing how bond prices change across different coupon rates and maturities. As we mentioned at the time, this is a simplistic approach to evaluating bond price volatility. But we did see that long-term bonds and those with low(er) coupon rates are much more sensitive to changes in interest rates than short-term bonds and those with higher coupon rates. Essentially, a higher-coupon bond provides more cash flow and provides it earlier, so it acts as an anchor by reducing the impact of interest rate changes on the value (price) of bonds. Furthermore, a longer time to maturity means that it takes longer for investors to recover their initial investment, so the market value (price) of the bond is more sensitive to changes in market interest rates.

We intended this second article to be dedicated to duration, an advanced measure of bond price volatility. However, we want to first introduce some of Excel’s bond valuation functions that can be substituted for the calculations we used in our fourth-quarter column.

Calculating Bond Value

In last issue’s article, we calculated bond prices using the present value (PV) function in Excel. Recall that the present value of a financial instrument is the present value of all its cash flows through maturity. Using the number of years until maturity, the principal amount (face value) of the bond and the bond’s coupon rate, the PV function calculates the value of a coupon-bearing bond.

However, Excel provides the PRICE function to calculate the value of a coupon-bearing bond, which is defined as:

=PRICE(Settlement, Maturity, Rate, Yld, Redemption, Frequency, Basis)

Where:

  • Settlement is the date on which the bond was purchased;
  • Maturity is the date on which the last coupon payment is made and the principal is returned;
  • Rate is the annual coupon rate;
  • Yld is the market interest rate or required rate of return (yield to maturity);
  • Redemption is the amount to be received per $100 of the face value of the bond when it is redeemed (this value can differ from the face value of the bond, especially if the bond is callable; for non-callable bonds, the redemption value is 100);
  • Frequency is the number of coupon payments made each year; typically, bonds pay twice a year, but this function will support values of 1, 2 or 4 (monthly coupon payments are not supported by the PRICE function);
  • Basis describes the assumption regarding the number of days in a month or year; in the U.S., corporate and municipal bonds use a basis of 0 for 30 days in a month and 360 days in a year and a basis of 1 for Treasury bonds for the actual number of days in each month and year.

Figure 1 is an illustration of how to use the PRICE function in Excel. Here we are assuming a bond that matures in 20 years, with a settlement date of 12/1/2012 and maturity date of 12/1/2032; with a 4% coupon rate; and required rate of return of 5%. To find the current value of the bond, we entered this function in cell B9:

=PRICE(B2,B3,B4,B5,B6/B6*100,B7,B8)/100*B6

To download the spreadsheet, click here.

The PRICE function returns the price of a bond as a percentage of the face value, so we must divide it by 100 and then multiply it by the face value to convert it to a dollar amount. In this example, the value of the bond is $874.49. As we would expect, this matches the value of cell H12 from Table 1 in last issue’s Spreadsheet Corner, which was derived using the PV (present value) function. As in this example here, the bond had a maturity of 20 years, a coupon rate of 4% and the market interest rate was 5%. Also, to confirm that your calculation is correct, setting the coupon rate and required rate of return (YLD in the formula) to the same rate should return a value of $1,000.

Measuring Bond Returns

At this point, it may be worthwhile to take a step back and address another element: return. As with other investments, return is an important consideration when selecting a bond. However, you are apt to run across multiple return measures for bonds, so it is important to know how they differ and how they are calculated. Here we focus on three of the most common bond return measures: current yield, yield to maturity and yield to call.

Current Yield

The current yield of a bond is derived by dividing the annual coupon payment by the current price of the bond:

Current yield = annual coupon payment ÷ bond value

It is similar to the dividend yield of a stock, which is the indicated dividend (the expected dividend payment over the next 12 months) divided by the current share price. The current yield is a rough measure of the return the bond will generate over the next year. However, it ignores the compounding of the coupon payment as well as changes in the price of the bond that may occur over the bond’s life.

In Figure 2, cell B13, we entered this formula for current yield:

=(B5*B7)/B11

To download the spreadsheet, click here.

The formula first calculates the annual coupon payment by multiplying the coupon rate (B5) by the face value of the bond (B7) and then dividing the product by the current price of the bond (B11). In this example, the current yield of the bond is 4.57%, which is the return you would earn over the next year if you received the $40 in interest on an investment of $874.49.

However, even if interest rates remain unchanged over the year, the value of the bond will rise to $878.26 (we arrive at this value by changing the settlement date in A2 to 12/1/2013). The current yield calculation ignores the capital gain of $3.77 ($878.26 – $874.49).

Yield to Maturity

A measure of bond return somewhat superior to current yield is yield to maturity, or YTM. Unlike current yield, yield to maturity accounts for both the interest payments and capital gains of a bond.

There is no direct way to calculate the yield to maturity by hand; it involves a series of trial-and-error calculations, taking the bond price as given and solving the valuation equation for the required return (YTM). This is where the power of Excel is useful, as the program has a built-in YIELD function:

=YIELD(Settlement, Maturity, Rate, Pr, Redemption, Frequency, Basis)

Where:

  • Settlement is the date on which the bond was purchased;
  • Maturity is the date on which the last coupon payment is made and the principal is returned;
  • Rate is the annual coupon rate;
  • Pr is the current price of the bond as a percentage of the face value (par);
  • Redemption is the amount to be received per $100 of the face value of the bond when it is redeemed (this value can differ from the face value of the bond, especially if the bond is callable; for non-callable bonds, the redemption value is 100);
  • Frequency is the number of coupon payments made each year; typically, bonds pay twice a year, but this function will support values of 1, 2 or 4 (monthly coupon payments are not supported by the PRICE function);
  • Basis describes the assumption regarding the number of days in a month or year; in the U.S., corporate and municipal bonds use a basis of 0 for 30 days in a month and 360 days in a year and a basis of 1 for Treasury bonds for the actual number of days in each month and year.

You may notice that this formula is nearly identical to the PRICE function we used earlier, with the only difference that Yld is replaced by the current price of the bond as a percentage of par (Pr).

To calculate the YTM of our bond we entered this formula into cell B14 (in Figure 2):

=YIELD(B2,B3,B5,B11/B7*100,B7/B7*100,B9,B10)

In this example, we have to convert the bond price from B11 back to a percentage of par by dividing it by the face value (B7) and multiplying it by 100.

As we would expect, the yield to maturity for this bond is 5%.

It is worth noting that there are a few key assumptions underlying yield to maturity. Chief among them are the assumptions that you hold the bond until it matures and that you reinvest all the cash flows over the life of the bond at a rate equal to the yield to maturity.

Yield to Call

The final bond return measure we want to discuss is yield to call. Many bond issuers reserve the right to buy back their bonds before they mature. This usually happens if interest rates drop, at which point the company would save money by buying back their bonds and reissuing new bonds at the lower rate.

In Figure 2, you can see that after the settlement and maturity dates, we have added a first call date (B4), which is the first date at which the company has the option of calling the bond. Furthermore, there is a call price (B8), which is the price at which the company can call the bond. Typically, the issuer pays a premium over par if the bond is called. In this example, the company will pay a 5% premium over par value, or $1,050.

To calculate the yield to call for our bond, we enter the following formula into cell B15:

=YIELD(B2,B4,B5,B11/B7*100,B8/B7*100,B9,B10)

This is the same formula we used for yield to maturity, except that we used the first call date (B4) instead of the maturity date (B3) and the call price (B8) instead of the face value (B7).

In this example, the yield to call is 7.92%, as the call premium and earlier receipt of the face value of the bond has boosted the return.

It is important to note that, under these circumstances, a company would not call its bonds, as interest rates have risen above the coupon rate of the bond (5% versus 4%).

Comparing Bond Price Sensitivities

Beyond the current price and yield of a bond, bond investors must be aware of how changing interest rates will impact the price of bonds. In the Fourth Quarter 2012 Spreadsheet Corner, we provided tables that showed the dollar change in bond price and percentage change in price over a variety of interest rates and maturities.

Using Excel functions, we can also do a side-by-side comparison of multiple bonds.

Bonds With Different Maturities

We begin by comparing bonds with identical coupon rates and returns but with different maturities. In Figure 3 we present two bonds, one with a 20-year maturity and the other with 10 years to maturity, both of which have 4% coupon rates and 5% required returns (yields to maturity). In cells B9 and C9, we calculate the price of the bonds using the PRICE function and their respective inputs. All else being equal, the shorter-maturity bond is worth more because we recover all of our cash flows more quickly than with the longer-maturity bond.

To download the spreadsheet, click here.

To see the price impact of a change in yields on these two bonds, we enter differing yields in cells A12:A13 and calculate the bond prices in cells B12:C13. In B12 we use this formula:

=PRICE(B$2,B$3,B$4,$A12,B$6/B$6*100,B$7,B$8)/100*B$6

Once we have entered the formula in B12, we can copy it into cells C12, B13 and C13. Using the ‘$’ sign in the formula locks the reference cell for copying purposes.

Cells B14:C14 are for calculating the dollar changes in prices, so we use this formula in B14 (and copy it to C14):

=B13-B12

Lastly, to calculate the percentage change in bond prices, we enter this formula in cell B15 and, again, copy it to cell C15:

=B13/B12-1

In this example, we assume that we purchase both bonds when rates are at 5%, and rates subsequently rise to 6%. The price of Bond 1 (the longer-maturity bond) would fall by 12.08%, while the price of Bond 2 would fall 7.68%. Since Bond 1 has a longer maturity than Bond 2, we would expect its price to fall more than that of Bond 2. Using this same template, if we changed A13 to 4%, so that market rates fall, the price of Bond 1 would rise more than that of Bond 2 (14.35% versus 8.45%).

To download the spreadsheet, click here.

Using this same template, we can also see that longer-term bonds are more sensitive to changes in yields (market interest rates). For Figure 4, we copied the contents of Column C to Column D for Bond 3. Keeping the coupon rate and market interest rate the same, but extending this bond’s maturity to 12/1/2042 (making it a 30-year bond), its price would fall by 14.46% if yields rose from 5% to 6%, while its price would rise by 18.28% if yields were to fall to 4% from 5%.

Bonds With Different Coupons

Bonds also react differently to yield changes depending on their coupon rate, with higher-coupon bonds being less sensitive than lower-coupon bonds. Modifying the template in Figure 3, we can see this play out. In Figure 5, we change the maturity date for Bond 2 so it matches that of Bond 1. However, we lower Bond 2’s coupon rate to 2%, compared to 4% for Bond 1. In A12:A13, we assume that yields fall from 5% to 4%.

To download the spreadsheet, click here.

In this example, Bond 1’s value increased by $125.51, versus a $102.99 gain for Bond 2. However, on a percentage basis, Bond 2’s price increased 16.52% versus 14.35% for Bond 1. If we change the yield in A13 to 6%, we see that Bond 2’s price falls by 13.75%, compared to a 12.08% decline in price for Bond 1. These examples confirm that lower-coupon bonds are more sensitive to changes in yield.

Conclusion

In this article, we have introduced Excel’s advanced bond functions, which will aid you in calculating the value of bonds. This is a more direct method than that introduced previously; however, the results are the same. Furthermore, we reviewed several popular return measures investors can use when evaluating a bond.

We have now laid the groundwork for a more advanced discussion of bond price sensitivity, specifically duration and convexity. These will be the topics of the Second Quarter 2013 installment of Spreadsheet Corner.


Discussion

No comments have been added yet. Add your thoughts to the discussion!

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

Log In