How to Calculate the YTM of a Bond in Excel: Easy Guide

YTM stands for yield to maturity. It is a common term used in the financial industry. Whether you are buying or selling a bond then, you need to calculate YTM. Bonds are mostly sold by companies, financial institutions, and even Governments. As an investor, it is important to calculate the YTM of a bond in order to find out its projected returns. One of the best ways to calculate the yield to maturity of a bond is by using Excel.

In this tutorial, I will guide you on different ways to calculate YTM in Excel.

Method 1: Use direct Formula to calculate the YTM of a bond

This method we the actual YTM of a bond formula and apply it to Excel. The actual Formula to calculate YTM can be written as Follows.



C: is the Annual Coupon Amount

FV: is the Face Value

PV: is the Present Value

N: refers to years to maturity

Let’s use the following data to illustrate how to calculate YTM using direct Formula

Face value = $2000

Present Value = $2100

Annual Coupon = $40

Years of Maturity = 15

Yield to maturity =?

Follow these steps to calculate in Excel.

1. Launch your Excel program and create a new worksheet

2. Create all the filled we have created above and fill in the values in the adjacent cells.

How to calculate YTM in Excel using Direct Formula

3. Click on cell B8 and type in the following formula


Excel YTM formula

4. Press enter, and Excel will compute the Yield to Maturity automatically

5. In case you get an answer like the one below, then you need to format the cell as a percentage

6. Right Click on it and then select format cells

7. On the new pop-up window, select percentage, then click ok

8. Now your answer should appear as shown in the image below

Method 2: Use the Rate() function to calculate YTM in Excel

The simplest way to calculate Yield to Maturity in Excel is by using the inbuilt Rate function. This function features arguments that are computed to produce YTM. Follow these simple steps:

1. Arrange the following fields in your Excel file

Par Value of Bond: $3000

Coupons Per Year: 6

Current Bond Price: $1900

Number of Coupons: 50

Periods: 20

Yield To Maturity (YTM):

2. Type the following Formula on cell 9B


How to calculate YTM using Rate function in Excel

3. Press the enter button to apply the Rate formula

As you can see from the image above, we have utilized the rate function to calculate YTM

Method 3: Calculate the YTM of a Bond using the IRR function

We can get the YTM of a bond by multiplying the by the number of coupons per year. In this method, I will show you how to use the IRR function to calculate YTM. The IRR function takes the values that we have provided to calculate the internal Rate of return.

1. Arrange the field and their respective values as follows.

2. Write the following Formula in the IRR section


How to calculate Yield To maturity using IRR formula in Excel

3. Press enter

4. Write the coupons per year

5. Multiply the IRR by the number of coupons per year to get the YTM


Method 4: Use the YIELD function

The inbuilt Excel YIELD() function can help users calculate YTM. To calculate yield to maturity using the YIELD function, you need the following fields.

SD: Settlement Date

MD: Maturity Date

Rate: Coupon Rate

PR: Market value

Redemption: Call Price, also known as Par Value

Frequency: Frequency of payments

Basis: Day Count basis

The Yield function can be written as

= YIELD (SD, MD, Rate, pr, redemption, frequency, [basis])

Follow these steps

1. Arrange your fields and values as shown in the image below.

2. Enter the following Formula on cell 11B


How to calculate YTM of a bond Using the Yield Function

3. Press Enter, and Excel will compute the YTM of your values automatically

Similar Read: How to calculate per capita in Excel

Method 5: Using VBA code to calculate YTM in Excel

Excel is one of the best finance software thanks to the expandable features using VBA. It stands for Visual Basic Application. You can use it to create Macro programs that run inside Excel Software. Therefore, you can write a VBA code Tailored to calculate the YTM of a bond. Follow these simple steps.

1. Open an existing or create a new Excel File

2. Click on the developer tab, then click on Visual Basic (In case you don’t see the developer tab on your Excel menu. Click on File>Options>Customize Ribbon and Add the Developer tab)

3. After clicking Visual Basic, a new window will pop up. Click on Insert>Module

4. Copy and Paste the VBA code below

Function YTM(F As Double, P As Double, C As Double, N As Integer, Optional Guess As Double = 0.1) As Double
    'This function calculates Yield to Maturity (YTM) using Excel's built-in RATE function.
    YTM = WorksheetFunction.Rate(N, C, -P, F, Guess)
End Function

5. Head over the back to the worksheet and calculate your BOND using the function that we have just created

For example, if you have a bond with a face value of $1000, a price of $950, a coupon rate of 5%, a coupon frequency of semi-annual, and five years to maturity, you can use the following Formula on the Excel cell


How to calculate YTM using VBA code in Excel

As you can see from the image above, we have used the VBA code to calculate the YTM of a bond

YTM Excel Calculator Download

Download the YTM Excel calculator template for free

Final Thoughts

Use any of the five methods we have discussed above to calculate YTM. You can choose any method that conforms to the values that we have. You can use the free template provided for practice. In case you have any questions, let us know in the comment section.

Leave a Reply

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading