Financial Functions: Calculating the Yield-to-Maturity Indicator in Excel

Overview

The yield-to-maturity (YTM) is an indicator specific to bonds, that is used to measure the rate of return of a bond, if the instrument will be held until its maturity date.
This indicator takes into account the market value, the coupon value, the remaining time until maturity, face value and the coupon payment frequency of the bond. The calculation formula for this indicator is considered to be the most accurate method for anticipating the bonds' return, therefore it is important for every financial analyst to know how to apply the formula in an electronic spreadsheet.

Calculating YTM

To start calculating the YTM of a bond instrument, you need to set up your spreadsheet. First, you need to allocate five cells of your sheet for writing the names of the elements for the formula. You can organize your sheet as follows:

• On the first cell type Face Value, and write the value below the cell. 
• On the second cell type Coupon Rate, and write the value below the cell. Note that the rate must be annualized and expressed in percentage.
• On the third cell type Remaining Maturity, and write the remaining time until maturity below cell. Note that the value must be in years. 
• On the fourth cell type Payment Frequency, and write the value below the cell. Note that the frequency must be expressed in coupon payments per year. 
• On the last cell type Market Value, and write the value below the cell. To correctly valuate a bond instrument, you need to determine the present value of the cash flows generated by the bond. Once you calculate these, add them and you will get the value of the bond. 

Let's assume an example of a bond instrument with the following parameters:

• The face value is 1000$
• The coupon rate is 7%
• There are four remaining years until the bond reaches maturity
• The bond issuer is paying twice a year.  
• The market value is 950$

The Excel function used for determining the YTM of the bond is RATE. To compute the YTM, follow the steps below:

1. Click outside of the table array.
2. Clickto display the Insert Function dialog box.
3. Select RATE function and click OK. The Function Arguments window will be displayed.
4. On the Nper field, multiply the number of years until maturity with the payment frequency value.
5. On the Pmt field, divide the coupon rate with the payment frequency and multiply with the face value. 
6. On the Pv field, write the negative market value. In our example, -950 will be written on this field. 
7. On the Fv field, specify the cell that contains the face value. 
8. Press Enter and multiply the formula with the payment frequency. 

The formula should look like in the following screenshot:

The rate of return for the bond, if it is held until maturity, will be 9%. 

Thanks for watching this tutorial! Feel free to post a comment if you have any feedback or tutorials suggestions.