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. 

Using VLOOKUP and INDEX Functions in MS Excel

In this tutorial I will explain the basic differences between VLOOKUP and INDEX functions in Excel, highlighting the strengths and weaknesses of each function.
The VLOOKUP function helps you to find data related to other information in a table's columns. Additionally, if you want to perform the same function horizontally, at the row level, you can use HLOOKUP.
The INDEX function returns the relevant data from a given range. It's basically doing the same thing but there are important differences that makes INDEX the preferred function instead of the lookup function category.
The syntax of the VLOOKUP function is the following:

• lookup_value — This parameter represents the value that you want to look up. The parameter is required to generate valid results, otherwise, the function will not work. This value must be in the first column of the range of cells selected for the table_array parameter.
• table_array — This parameter contains the range of cells in which the function will seek for the value inputted for the lookup_value parameter. 
• col_index_num — This parameter contains the number of the column, starting with 1 for the left-most column of the table. The selected column must contain the return value. 
• range_lookup — This parameter is option and have the role to force the function to look for an exact or an approximate match  of the lookup value, in the selected array. The parameter supports logical values such as TRUE or FALSE, and by default the TRUE value is selected.

Let's assume that we have the following set of data:

Supposing that I want to display the total amount sold on 10/14/15. To perform the VLOOKUP function, do the following:

1. Click outside of the table array.
2. Clickto display the Insert Function dialog box.
3. Select VLOOKUP function and click OK. The Function Arguments window will be displayed.
4. On the Lookup_Value field, write the date that we are looking for, that is 10/14/15.
5. On the Table_array field, select the table array. 
6. On the Col_index_num field, select the number of the column where the total amount is stored. 
7. On the Range_lookup field, select False to search for an exact match.

The result should look like in the following screenshot:

The issue with this function is that if you choose to move your table, including the data used by the function, the function will return a non-value. 

The index function has a simpler syntax, and can contain both VLOOKUP and HLOOKUP in just one function. The following parameters can be configured for INDEX function:

• array — This parameter contains the range of cells where the data can be found. If the array contains only one row, or column, that specific parameter, correspondent to the row or column, is optional. If the table contains more than one column or row, and only one parameter is used, depending if you want to return a value from either a column or a row, the function will return an range of the entire row or column in array.  
• Row_num — This optional parameter selects the row in array from which the value must be returned. If you don't use this parameter, the Column_num parameter is mandatory.
• Column_num — This optional parameter selects the column in array from which the value must be returned. If this parameter is not used, the Row_num parameter is mandatory.

Let's suppose that I want to display the total sales of the East region registered on 4/1/2014. To do this, follow the next steps:

Click outside of the table array.

1. Clickto display the Insert Function dialog box.
2. Select INDEX function and click OK. The Function Arguments window will be displayed.
3. On the Array field, select the entire table. 
4. On the Row_num field select the number of the row that corresponds to our criteria (East region and 4/1/2014). The number of the row is 7.
5. On the Column_num field select the column correspondent to the total sales, that is 7.
6. Click OK and check the results.

Thank you for viewing this tutorial. If you have suggestions of other tutorials related to other applications, don't hesitate to post a comment.