Souptik Roy
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.
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