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.

**Table of Contents**hide

## Download the Practice Workbook

Please download the workbook to practice yourself

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

**Read More: ****How to Calculate Bond Price in Excel (4 Simple Ways)**

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

**Read More: ****How to Calculate Face Value of a Bond in Excel (3 Easy Ways)**

**Similar Readings**

**How to Calculate Price of a Semi Annual Coupon Bond in Excel (2 Ways)****Calculate Clean Price of a Bond in Excel (3 Easy Ways)****Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)****How to Calculate Bond Payments in Excel (2 Easy Methods)**

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

**Read More: ****Calculate Bond Price from Yield in Excel (3 Easy Ways)**

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

**Read More: ****How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)**

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

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 :

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

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