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.
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:
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
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.
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.
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
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).
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:
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.
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%).
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.
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 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%).
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 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%.
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.
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.
Sorry, you cannot add comments while on a mobile device or while printing.