BOND YIELD CALCULATION IN EXCEL

Bond Yield Calculation Using Microsoft Excel

One of the key variables in choosing any investment is the expected rate of return. We try to find assets that have the best combination of risk and return. In this section we will see how to calculate the rate of return on a bond investment. If you are comfortable using the built-in time value functions, then this will be a simple task. If not, then you should first work through my Microsoft Excel as a Financial Calculatortutorials.

Please note that this tutorial works for all versions of Excel, including Excel 2007. Furthermore, the functions presented here should also work in other spreadsheets (such as Open Office Calc).

You can download a spreadsheet that accompanies this tutorial, or create your own as you work through it. Since we will use the same example as in my tutorial on calculating bond values using Microsoft Excel, the spreadsheet is the same.

The expected rate of return on a bond can be described using any (or all) of three measures:

• Current Yield
• Yield to Maturity
• Yield to Call

We will discuss each of these in turn below. In the bond valuationtutorial, we used an example bond that we will use again here. The bond has a face value of $1,000, a coupon rate of 8% per year paid semiannually, and three years to maturity. We found that the current value of the bond is $961.63. For the sake of simplicity, we will assume that the current market price of the bond is the same as the value. (You should be aware that intrinsic value and market price are different, though related, concepts.)

If you haven't downloaded the example spreadsheet, create a new workbook and enter the data as shown in the picture below:

The Current Yield

The current yield is a measure of the income provided by the bond as a percentage of the current price:

There is no built-in function to calculate the current yield, so you must use this formula. For the example bond, enter the following formula into B13:

=(B3*B2)/B10

The current yield is 8.32%. Note that the current yield only takes into account the expected interest payments. It completely ignores expected price changes (capital gains or losses). Therefore, it is a useful return measure primarily for those who are most concerned with earning income from their portfolio. It is not a good measure of return for those looking for capital gains. Furthermore, the current yield is a useless statistic for zero-coupon bonds.

The Yield to Maturity on a Payment Date

Unlike the current yield, the yield to maturity (YTM) measures both current income and expected capital gains or losses. The YTM is the internal rate of return of the bond, so it measures the expected compound average annual rate of return if the bond is purchased at the current market price and is held to maturity. In this section, the calculations will only work on a coupon payment date. If you wish, you can jump ahead to see how to use the Yield() function to calculate the YTM on any date.

In the case of our example bond, the current yield understates the total expected return for the bond. As we saw in the bond valuation tutorial, bonds selling at a discount to their face value must increase in price as the maturity date approaches. The YTM takes into account both the interest income and this capital gain over the life of the bond.

There is no formula that can be used to calculate the exact yield to maturity for a bond (except for trivial cases). Instead, the calculation must be done on a trial-and-error basis. This can be tedious to do by hand. Fortunately, the Rate() function in Excel can do the calculation quite easily. Technically, you could also use the IRR() function, but there is no need to do that when the Rate() function is easier and will give the same answer.

To calculate the YTM (in B14), enter the following formula:

=RATE(B5*B8,B3/B8*B2,-B10,B2)

You should find that the YTM is 4.75%.

But wait a minute! That just doesn't make any sense. We know that the bond carries a coupon rate of 8% per year, and the bond is selling for less than its face value. Therefore, we know that the YTM must be greater than 8% per year. You need to remember that the bond pays interest semiannually, and we entered Nper as the number of semiannual periods (6) and Pmt as the semiannual payment amount (40). So, when you solve for the Rate the answer is a semiannual yield. Since the YTM is always stated as an annual rate, we need to double this answer. In this case, then, the YTM is 9.50% per year. Change your formula in B14 to:

=RATE(B5*B8,B3/B8*B2,-B10,B2)*B8

So, always remember to adjust the answer you get from Rate() back to an annual YTM by multiplying by the number of payment periods per year.

The Yield to Call on a Payment Date

Many bonds (but certainly not all), whether Treasury bonds, corporate bonds, or municipal bonds are callable. That is, the issuer has the right to force the redemption of the bonds before they mature. This is similar to the way that a homeowner might choose to refinance (call) a mortgage when interest rates decline. In this section, the calculations will only work on a coupon payment date. If you wish, you can jump ahead to see how to use the Yield() function to calculate the YTC on any date.

Given a choice of callable or otherwise equivalent non-callable bonds, investors would choose the non-callable bonds because they offer more certainty and potentially higher returns if interest rates decline. Therefore, bond issuers usually offer a sweetener, in the form of a call premium, to make callable bonds more attractive to investors. A call premium is an extra amount in excess of the face value that must be paid in the event that the bond is called before maturity.

The picture below is a screen shot (from the FINRA TRACE Web site on 8/17/2007) of the detailed information on a bond issued by Union Electric Company. Notice that the call schedule shows that the bond is callable once per year, and that the call premium declines as each call date passes without a call. If the bond is called after 12/15/2015 then it will be called at its face value (no call premium).

It should be obvious that if the bond is called then the investor's rate of return will be different than the promised YTM. That is why we calculate the yield to call (YTC) for callable bonds.

The yield to call is identical, in concept, to the yield to maturity, except that we assume that the bond will be called at the next call date, and we add the call premium to the face value. Let's return to our example:

Assume that the bond may be called in one year with a call premium of 3% of the face value. What is the YTC for the bond?

I have already entered this additional information into the spreadsheet pictured above. The formula in B15 will be the same as for the YTM, except that we need to use 2 periods for NPer, and the FV will include the 3% call premium:

=RATE(B6*B8,B3/B8*B2,-B10,B2*(1+B7))*B8

Remember that we are multiplying the result of the Rate() function by the payment frequency (B8) because otherwise we would get a semiannual YTC. Note that the yield to call on this bond is 15.17% per year.

Now, ask yourself which is more advantageous to the issuer: 1) Continuing to pay interest at a yield of 9.50% per year; or 2) Call the bond and pay an annual rate of 15.17%? Obviously, it doesn't make sense to expect that the bond will be called as of now since it is cheaper for the company to pay the current interest rate.

The YTM and YTC Between Coupon Payment Dates

As noted above, a major shortcoming of the Rate() function is that it assumes that the cash flows are equally distributed over time (say, every 6 months). However, bonds only pay interest twice a year, so there are only 2 days per year that the Rate() function will give the correct answer. On any other date, you need to use the Yield() function. Note that this function (as was the case with the Price() function in the bond valuation tutorial) is built into Excel 2007. However, if you are using Excel 2003 or earlier, you need to make sure that you have the Analysis ToolPak add-in installed and enabled (go to Tools » Add-ins and check the box next to Analysis ToolPak).

The Yield function is defined as:

YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)

where settlement is the date that you take ownership (typically 3 business days after the trade date), maturity is the maturity date, rate is the annual coupon rate, pr is the current market price as a percentage of the face value, redemption is the amount that will be paid by the issuer at maturity as a percentage of the face value, frequency is the number of coupon payments per year, and basis is the day count basis to use. Note that the dates must be valid Excel dates, but they can be formatted any way you wish. Also, both pr and redemption are percentages entered in decimal form. That is, 96 indicates 96% so don't enter 0.96 even if you format it as a percentage.

Our worksheet needs a little more information to use the Yield() function, so set up a new worksheet that looks like the one in the picture below:

Note that I've had to add exact dates for the settlement date and the maturity date, rather than just entering a number of years as we did before. Also, since industry practice (which the Yield() function uses) is to quote prices as a percentage of the face value, I have added 100 for the redemption value in B3. Finally, I have added a row (B11) to specify the day count basis. In this case, we are using the 30/360 day count methodology, which Excel specifies as 0.

With that additional information, using the Yield() function to calculate the yield to maturity on any date is simple. Insert the following function into B18:

=YIELD(B6,B7,B4,B13,B3,B10,B11)

and you will find that the YTM is 9.50%. Notice that we didn't need to make any adjustments to account for the semiannual payments. The Yield() function takes annual arguments, and uses the Frequency argument to adjust them automatically. It also returns an annualized answer.

Calculating the yield to call is done in the same way, except that we need to add the call premium to the redemption value, and use the next call date in place of the maturity date. Enter the following function into B19:

=YIELD(B6,B8,B4,B13,B3*(1+B9),B10,B11)

You should find that the YTC is 15.17%.

As noted, the nice thing about the Yield() function is that it works correctly on any day of the year. To see this, change the settlement date to 12/15/2007 (halfway through the current coupon period). You should find that the YTM is still 9.50%, but the YTC is now 17.14%.

I hope that you have found this tutorial to be helpful. If you wish,