How to Calculate Yield to Maturity in Excel
If you are dealing with finances, you may have to calculate compound interest on a number of occasions. In order to calculate yield to maturity in Microsoft Office Excel, you need to know the amount of principal initially invested, interest rate per year, the rate of daily, monthly, or quarterly compounding and the duration for which initial principal has been invested. If you know all the required values, a computer with Microsoft Office Excel installed on it is all you need. Here is how you can calculate yield to maturity in Excel.
Instructions
-
1
Open a new Excel workbook and save it on your computer’s hard drive.
-
2
Now change the width of Column A in the excel sheet so that the column is wide enough to accommodate 40 characters. If you are using ‘Calibri’ as the font and the font size is 11, 42 units will be the ideal column width.
-
3
Select cells B1, B2, B3 and B4 by clicking on each of these cells while holding down the Ctrl key. Right click and choose ‘Format Cells...’ from the list of options that appears.
-
4
From the ‘Format Cells’ window, choose ‘Number’ under the ‘Category’ section and click the ‘OK’ button.
-
5
Right click on cell B5, choose ‘Format Cells...’ and then choose ‘Currency’ under the ‘Category’ section. Confirm your selection by clicking the ‘OK’ button.
-
6
For cells A1 to A5, enter the following text.
In cell A1: Principal
In cell A2: Interest Rate (as a decimal)
In cell A3: Number of interest periods per year
In cell A4: Number of conversion periods to elapse
In cell A5: Value at maturity
-
7
In cell B5, type ‘=B1*(1+B2/B3)^B4’ without the quotes.
-
8
Enter the amount of principal initially invested in cell B1. In cell B2, enter the interest rate per year. For instance, if you invested $1000 at the start, the value in B1 will be 1000.00. If the annual interest rate was 5%, the value in B2 will be 0.05.
-
9
In B3, you have to enter the number of time interest is compounded in a year. For example, if the interest is compounded every day, the value in B3 will be 365.00
-
10
In B4, you need to enter the number of interest periods. If you want to know the value of yield to maturity after two years and the interest is compounded every day, the number of interest periods would be 730 and thus the value in B4 would be 730.00.
-
11
Once you are done entering all the values, B5 will give you the result. In the example used here, the value at maturity comes out be 1105.16. The value of yield would be 105.16.
