|PV of Interest Payments|
Using Excel’s PV Function to Calculate a Bond Price
|PV of Principal|
Excel’s PV function can calculate the price of a bond. In order to produce a positive bond price, the coupon payment annuity amount is input as a negative value for the pmt argument. The principal payment (−1000) is entered as the fv argument. The function in cell D1 references the arguments in cells B1 to B4. The function in cell D5 uses hardcoded numerical values. In both cases, the bond values produced by the PV function are identical to the calculator solution.
Concept of Yield to Maturity
In the previous example, the yield to maturity that was used as the discount rate was 10 percent. The yield to maturity, or discount rate, is the rate of return required by bondholders. The bondholder, or any investor for that matter, will allow three factors to influence his or her required rate of return:
1. The required real rate of return—This is the rate of return the investor demands for giving up the current use of the funds on a noninflation-adjusted basis. It is the financial “rent” the investor charges for using his or her funds for one year, five years, or any given period. Although it varies from time to time, historically the real rate of return demanded by investors has been about 2 to 3 percent.
2. Inflation premium—In addition to the real rate of return discussed above, the investor requires a premium to compensate for the eroding effect of inflation on the value of the dollar. It would hardly satisfy an investor to have a 3 percent total rate of return in a 5 percent inflationary economy. Under such circumstances, the lender (investor) would be paying the borrower 2 percent for use of the funds, or in other words, losing 2 percent in purchasing power. This would represent an irrational action. No one wishes to pay another party to use his or her fund. The inflation premium added to the real rate of return ensures that this will not happen. The size of the inflation premium will be based on the investor’s expectations about future inflation. In the last two decades, the inflation premium has been 2 to 4 percent. In the late 1970s, it was in excess of 10 percent.
If one combines the real rate of return (part 1) and the inflation premium (part 2), the risk-free rate of return is determined. This is the rate that compensates the investor for the current use of his or her funds and for the loss in purchasing power due to inflation, but not for taking risks. As an example, if the real rate of return were 3 percent and the inflation premium were 4 percent, we would say the risk-free rate of return is 7 percent.3
3. Risk premium—We must now add the risk premium to the risk-free rate of return. This is a premium associated with the special risks of a given investment. Of primary interest to us are two types of risk: business risk and financial risk. Business risk relates to the inability of the firm to hold its competitive position and maintain stability and growth in its earnings. Financial risk relates to the inability of the firm to meet its debt obligations as they come due. In addition to the two forms of risk mentioned above, the risk premium will be greater or less for different types of investments. For example, because bonds possess a contractual obligation for the firm to pay interest to bondholders, they are considered less risky than common stock where no such obligation exists.4
The risk premium of an investment may range from as low as zero on a very-short-term U.S. government–backed security to 10 to 15 percent on a gold mining expedition. The typical risk premium is 2 to 6 percent. Just as the required real rate of return and the inflation premium change over time, so does the risk premium. For example, high-risk corporate bonds (sometimes referred to as junk bonds) normally require a risk premium of about 5 percentage points over the risk-free rate. However, in September 1989 the bottom fell out of the junk bond market as Campeau Corp., International Resources, and Resorts International began facing difficulties in making their payments. Risk premiums almost doubled. The same phenomenon took place in the fall of 2008 in reaction to the U.S. financial crisis and in the spring of 2010 in reaction to the debt crisis in Greece, Portugal, Ireland, Italy, and Spain. As is emphasized in many parts of the text, there is a strong correlation between the risk the investor is taking and the return the investor demands. Supposedly, in finance as in other parts of business, “There is no such thing as a free lunch.” As you take more risk hoping for higher returns, you also expose yourself to the possibility of lower or negative returns on the other end of the probability curve.
We shall assume that in the investment we are examining the risk premium is 3 percent. If we add this risk premium to the two components of the risk-free rate of return developed in parts 1 and 2, we arrive at an overall required rate of return of 10 percent.
|+ Real rate of return||3%|
|+ Inflation premium||4|
|= Risk-free rate||7%|
|+ Risk premium||3|
|= Required rate of return||10%|
In this instance, we assume we are evaluating the required return on a bond issued by a firm. If the security had been the common stock of the same firm, the risk premium might be 5 to 6 percent and the required rate of return 12 to 13 percent.
Finally, in concluding this section, you should recall that the required rate of return on a bond is effectively the same concept as required yield to maturity.
Changing the Yield to Maturity and the Impact on Bond Valuation
In the earlier bond value calculation, we assumed the interest rate was 10 percent ($100 annual interest on a $1,000 par value bond) and the yield to maturity was also 10 percent. Under those circumstances, the price of the bond was basically equal to par value. Now let’s assume conditions in the market cause the yield to maturity to change.
Increase in Inflation Premium For example, assume the inflation premium goes up from 4 to 6 percent. All else remains constant. The required rate of return would now be 12 percent.
|+ Real rate of return||3%|
|+ Inflation premium||6|
|= Risk-free rate||9%|
|+ Risk premium||3|
|= Required rate of return||12%|
With the required rate of return, or yield to maturity, now at 12 percent, the price of the bond will change.5 A bond that pays only 10 percent interest when the required rate of return (yield to maturity) is 12 percent will fall below its current value of approximately $1,000. The new price of the bond is $850.61.
We can calculate the bond price by using the calculator keystrokes shown in the margin or by using the time-value equations as follows:
Present Value of Interest Payments We take the present value of a $100 annuity for 20 years. The discount rate is 12 percent.
Present Value of Principal Payment at Maturity We take the present value of $1,000 after 20 years. The discount rate is 12 percent.
Total Present Value
|Present value of interest payments||$746.94|
|Present value of principal payment at maturity||103.67|
|Total present value, or price, of the bond||$850.61|
In this example, we assumed increasing inflation caused the required rate of return (yield to maturity) to go up and the bond price to fall by approximately $150. The same effect would occur if the business risk increased or the demanded level for the real rate of return became higher.
Decrease in Inflation Premium The opposite effect would happen if the required rate of return went down because of lower inflation, less risk, or other factors. Let’s assume the inflation premium declines and the required rate of return (yield to maturity) goes down to 8 percent.
The 20-year bond with the 10 percent interest rate (coupon rate) would now sell for $1,196.36 as shown in the calculator keystrokes in the margin or using the following calculations:
Present Value of Interest Payments
Present Value of Principal Payment at Maturity
Total Present Value
|Present value of interest payments||$ 981.81|
|Present value of principal payment at maturity||214.55|
|Total present value, or price, of the bond||$1,196.36|
The bond is now trading at $196.36 over par value. This is certainly the expected result because the bond is paying 10 percent interest when the yield required in the market is only 8 percent. The 2 percentage point differential on a $1,000 par value bond represents $20 per year. The investor will receive this differential for the next 20 years. The present value of $20 for the next 20 years at the current market rate of interest of 8 percent is approximately $196.36. This explains why the bond is trading at $196.36 over its stated, or par, value.
The further the yield to maturity on a bond changes from the stated interest rate on the bond, the greater the price change effect will be. This is illustrated in Table 10-1 for the 10 percent coupon rate, 20-year bonds discussed in this chapter.
Table 10-1 Bond price table
We clearly see the impact that different yields to maturity have on the price of a bond.
Time to Maturity
The impact of a change in yield to maturity on valuation is also affected by the remaining time to maturity. The effect of a bond paying 2 percentage points more or less than the going rate of interest is quite different for a 20-year bond than it is for a 1-year bond. In the latter case, the investor will only be gaining or giving up $20 for one year. That is certainly not the same as having this $20 differential for an extended period. Let’s once again return to the 10 percent interest rate bond and show the impact of a 2 percentage point decrease or increase in yield to maturity for varying times to maturity. The values are shown in Table 10-2 and graphed in Figure 10-2. The upper part of Figure 10-2 shows how the amount (premium) above par value is reduced as the number of years to maturity becomes smaller and smaller. Figure 10-2 should be read from left to right. The lower part of the figure shows how the amount (discount) below par value is reduced with progressively fewer years to maturity. Clearly, the longer the maturity, the greater the impact of changes in yield.
Determining Yield to Maturity from the Bond Price
Until now we have used yield to maturity as well as other factors, such as the interest rate on the bond and number of years to maturity, to compute the price of the bond. We shall now assume we know the price of the bond, the interest rate on the bond, and the years to maturity, and we wish to determine the yield to maturity. Once we have computed this value, we have determined the rate of return that investors are demanding in the marketplace to provide for inflation, risk, and other factors.
Table 10-2 Impact of time to maturity on bond prices
|(10% Interest Payment, Various Times to Maturity)|
|Time Period in Years to Maturity||Bond Price with 8% Yield to Maturity||Bond Price with 12% Yield to Maturity|
Figure 10-2 Relationship between time to maturity and bond price*
Let’s once again present Formula 10-1:
We now determine the value of Y, the yield to maturity, that will equate the interest payments (It) and the principal payment (Pn) to the price of the bond (Pb).
Consider the following timeline for payments on a 15-year bond that pays $110 per year (11 percent of the face amount) in interest and $1,000 in principal repayment after 15 years. The current price of the bond is $931.89.
We wish to compute the yield to maturity, or discount rate, that equates future flows with the current price. It turns out that there is no algebraic formula that allows us to solve for the yield to maturity directly. Once upon a time, this presented a difficult puzzle that required tedious trial-and-error estimations to be checked before a solution could be found.
Fortunately, our tools have improved. Both Excel and financial calculators are able to do these calculations so rapidly that the user is frequently left unaware that they are using the same trial-and-error process that was once done by hand.
Let us start by reorganizing the timeline in an Excel spreadsheet as shown in Table 10-3. The Excel function RATE(n, pmt, pv, fv) shown at the bottom of the spreadsheet can also be used to find the yield to maturity, but the full spreadsheet has the advantage of making all the steps transparent to the reader. The spreadsheet also introduces Excel’s very flexible “Goal Seek” feature, which has many uses in addition to finding yields to maturity.
In the spreadsheet, the time (n) of each payment is shown in column B, and each payment amount is shown in column C. The last two payments are at time n = 15 when both the last coupon payment and the principal are paid. In column D, we see a “PV factor” that is used to find the present value of each payment. The general equation for each factor is shown in the first comment box that points to cell D2. The comment box pointing to cell D4 shows the actual Excel equation and syntax for that cell. Each of the PV factor cells references the discount rate in cell D$1, which is also the yield to maturity. The dollar sign in the cell ensures that each row in the D column is referencing cell D1. Column E shows the present value of each payment, and the sum of the present value of all these payments is shown in cell E20. This is the bond price. Once you have created the spreadsheet and entered the data and appropriate equations, you are ready to use “Goal Seek.”
The yield to maturity of Y = 12.00% is shown in red in cell D1. This cell was calculated using the “Goal Seek” function in Excel. Goal Seek is used when you know the result that you want for a formula, but you are not sure what input value the formula needs to get the result. In the case of the yield to maturity, we know the bond price should be $931.89, but we do not know the discount rate that produces that price.
The Goal Seek function can be found in the most recent version of Excel on the Data tab, in the Data Tools group, under What-If Analysis. See Figure 10-3 for a picture of the Excel Ribbon location. Earlier versions of Excel also include Goal Seek, but the feature may be in a menu or toolbar instead of on the Excel Ribbon. The financial calculator keystrokes function much like Excel’s RATE(nper, pmt, pv,(fv)) function. These keystrokes are shown in the margin near Table 10-3.
Table 10-3 Excel functions for YTM
Figure 10-3 Finding the Goal Seek function in Excel
These are the steps used to find the yield to maturity:
1. Make sure that you have calculated an arbitrary bond price by putting an interest rate in cell D1. Any rate should work, but using 11% is a good place to start because you already know that, at 11%, the price of the bond should be $1,000 since the coupon payment of $110 is 11% of the principal.
2. Open the Goal Seek feature.
3. In the “Set cell” box, enter the reference for the cell containing the formula for the bond price.
4. In the “To value” box, type the value 931.89, which is the price of the bond.
5. In the “By changing cell” box, enter the reference for the cell that contains the discount rate that you wish to find. This is cell D1 for this example.
6. Click “OK.”
Goal Seek runs and produces the result in cell D1: Y = 12%. The RATE (nper, pmt, pv,(fv)) function in cell A22 also produces a value of 12%.
Semiannual Interest and Bond Prices
We have been assuming that interest was paid annually in our bond analysis. In actuality, most bonds pay interest semiannually. Thus a 10 percent interest rate bond may actually pay $50 twice a year instead of $100 annually. To make the conversion from an annual to semiannual analysis, we follow three steps:
1. Divide the annual interest rate by 2.
2. Multiply the number of years by 2.
3. Divide the annual yield to maturity by 2.
Assume a 10 percent, $1,000 par value bond has a maturity of 20 years. The annual yield to maturity is 12 percent. In following the three steps above, we would show this:
1. 10%/2 = 5% semiannual interest rate; therefore, 5% × $1,000 = $50 semiannual interest.
2. 20 × 2 = 40 periods to maturity.
3. 12%/2 = 6% yield to maturity, expressed on a semiannual basis.
The calculator solution for this problem is shown in the margin.
The answer of $849.54 is slightly below what we found previously for the same bond, assuming an annual interest rate ($850.61). This value was initially shown on page 301. In terms of accuracy, the semiannual analysis is a more acceptable method and is the method used in bond tables. As is true in many finance texts, we present the annual interest rate approach first for ease of presentation, and then the semiannual basis is given. In the problems at the back of the chapter, you will be asked to do problems on both an annual and semiannual interest payment basis.