# 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.

YTM=(C+(FV-PV)/n)/(FV+PV/2)

Where

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.

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

=(B6+((B4-B5)/B7))/(B4+B5/2)

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

=RATE(B8,B7,-B6,B4)*B5

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

=IRR(C5:C9)

3. Press enter

4. Write the coupons per year

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

=C10*C11

## 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])

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

2. Enter the following Formula on cell 11B

=YIELD(B6,B7,B5,B10,B4,B8)

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

==YTM(1000,950,0.05,5)

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