How to calculate YTM in Excel

How to calculate YTM in Excel

YTM means yield to maturity. It is also known as the internal rate of return. If you are dealing with finances, this is one of the values you have to calculate when dealing with compound interest. For you to calculate YTM using Excel formulas, there are some values you need to have. These include the initial principal amount invested, the interest rate to be paid yearly, the time duration the principal amount has been invested, and the rate of daily, monthly or quarterly payments. With all these values available, Excel will calculate for you the YTM value using either of the following; RATE function or IRR function.

This article gives you step-by-step instructions on calculating YTM in Excel workbooks using the bond yield calculator.

Steps to follow when calculating YTM in Excel

1.Launch the Microsoft Excel program on your computer.

2.In the blank worksheet, type in the column headings and data labels. Starting from the first cell, type in the following consistently; Bond Yield Data, Face Value, Coupon Rate, Required Return, Years to Maturity, Years to Call, Call Premium, Payment Frequency. Here you will skip one cell and type Value of Bond, skip another cell and finish up with Bond Yield Calculations, Current Yield, Yield to Maturity, and Yield to call. The entries will be in cells A1 through A15.

3.Format the column width in the excel sheet so that it is wide enough to accommodate all characters.

4.Format the column headings. Select the cell that holds Face value and the next cell to the right. Hold the Ctrl key and click on the Bond Yield Calculations cell plus its subsequent cell. Click the Merge cells button and then click on the Center Text button. After this, click on the Borders button and select All Borders.

5.Select the numerical formatting for column B. select cells B2 and B12, and click on the Currency button. The button is still in the Format cells dialog box: choose cells B3, B4, B7, B13, B14, and B15. Click the percent button.

6.Enter the bond yield formulas.

In cell B13 type =(B3*B2)/B10

Cell B14 type the formula=RATE(B5*B8, B3/B8*B2, -B10, B2)*B8

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

7.Type in the formula's values. In the other remaining cells, type in your values in column B. For example, in cell B2, enter the Face Value amount initially invested (principal). If you invested $20000, the value in cell B2 would be 20000.00. if your annual interest rate is 6%, the value in cell B4 will be 0.06

8.After entering your values, the formulas entered in cells B13, B14, and B15 will give you the answer values. By clicking the Enter button, your expected values will be displayed in the cells.

Conclusion

The steps above are the manual way of calculating YTM in an Excel worksheet. It is a straightforward and easy process to accomplish once you know your values and the formula to apply.

In case you find it a challenge using the above method, you can calculate YTM using the RATE and IRR functions in Excel.