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

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.


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.

Dataset of how to calculate ytm in excel


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

Calculating YTM of a Bond Using RATE Function

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

Calculating YTM of a Bond Using RATE Function

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.

Use Direct Formula to Calculate 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.

Use Direct Formula to Calculate YTM of a Bond in Excel

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


Similar Readings


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.

Apply YIELD Function to Calculate YTM of a Bond in Excel

Steps:

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

Apply YIELD Function to Calculate YTM of a Bond in Excel

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

Apply YIELD Function to Calculate YTM of a Bond in Excel

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.


Related Articles

Souptik Roy

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo