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