# How to Calculate YTM of a Bond in Excel (4 Suitable Methods)

Get FREE Advanced Excel Exercises with Solutions!

Every company needs to buy or sell bonds for its financial activities. For these activities, every company needs to calculate Yield to Maturity (YTM) of the bonds. However, this YTM can be calculated easily in Excel. In this article, I will show you how to calculate the YTM of a Bond in Excel with 4 handy ways with necessary steps and illustrations. I hope you will find the article interesting.

## 4 Suitable Ways to Calculate YTM of a Bond in Excel

Here I will consider a dataset of Bond Information of ABC Traders. You can see the dataset has 2 columns and 6 rows. All the necessary information is given in the dataset. We need to calculate only the Yields to Maturity. So, without any further delay, let’s jump into the problem and solve it.

### 1. Calculating YTM of a Bond Using RATE Function

This is the first method to get the YTM of a Bond. I will use the RATE Function here. This is a very easy and simple process. The RATE Function will return YTM after taking all the arguments from the dataset and multiplying them with the Par Value of the Bond. Let’s follow the steps given below for this method. The necessary illustrations will also help you to understand the whole procedure.

Steps:

• First, select the C9 cell.
• Then, write down the following function in the Formula Bar.
`=RATE(C8,C7,-C6,C4)*C5`

• After that, press the Enter
• Consequently, you will find the result in the picture given below.

### 2. Use Direct Formula to Calculate YTM of a Bond in Excel

This is the second method of this article. I will use a natural formula here to calculate YTM of a Bond in Excel. We know that there is a formula to Calculate YTM. The formula is given below.

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

Here,

C= Annual Coupon Amount

FV= Face Value

PV= Present value

n= Years to Maturity

For this method, I will consider a new dataset given below, Let’s follow the steps of this method to get the YTM of a Bond in Excel.

Steps:

• Select the C8 Cell first.
• Then, write down the following formula in that cell.
`=(C6+((C4-C5)/C7))/((C4+C5)/2)`

• Hence, press the Enter key.
• As a result, you will find the Yields to Maturity for the dataset.

### 3. Calculating YTM of a Bond in Excel by Applying YIELD Function

This is another easy way to calculate the YTM of a Bond in Excel. Here, I will consider a new dataset for this method mentioned in the following picture. However, I will apply the YIELD Function in this method. Moreover, the YIELD Function takes all the values from the dataset as arguments and returns the value of the YTM in the desired cell. Let’s follow the method step by step.

Steps:

• First, select the C11 .
• Next, copy the following formula in the selected cell.
`=YIELD(C6,C7,C5,C10,C4,C8)`

• After that, click the Enter key.
• As a consequence, you will find the result in the following picture.

### 4. Utilize IRR Function to Calculate YTM of a Bond

Let’s consider another dataset for this method. The dataset is shown in the next picture. I will use IRR Function to get the YTM value of a Bond. The IRR Function returns the internal rate of return by taking values from the dataset as arguments. Then, the YTM will be found after multiplying no of coupons per year with the IRR value. Follow the instructions step by step mentioned below.

Steps:

• Select the C10 cell first.
• Then copy the following formula in the C10
`=IRR(C5:C9)`

• Press Enter.
• Consequently, you will find IRR for a period.

• Then, Select the C12 cell.
• Hence, copy the following formula in C12 Cell:
`=C10*C11`

• Then, press the Enter key.
• At last, you will get the result like the picture given below.

## Things to Remember

• I used different datasets for different methods. You should bear in mind that one dataset will be ok for practicing if the dataset has all the information.

## Conclusion

In this article, I have tried to explain how to Calculate YTM of a Bond in Excel. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. Moreover, You will find such interesting blogs on our website Exceldemy.com. Hence, I hope you have enjoyed the whole tutorial. If you have any kind of queries feel free to ask me in the comment section. Don’t forget to give us your feedback.

## Related Articles

Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

1. Thanks v much for your teaching and I enjoy and I learn v much.

There is a typo however : when calculate the approximate YTM, the formula should be :

• Hello WU,
Thank you for bringing that to my attention. You are correct that the proper formula for calculating the approximate yield to maturity (YTM) of a bond is:

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

I appreciate your attention to detail, and I will ensure to update my article accordingly. Thank you for being a part of our community. If you have any further questions or require assistance, please don’t hesitate to post on our Forum.

Regards,
Priti
Exceldemy Team

Advanced Excel Exercises with Solutions PDF