Khan Muhammad Kawser
In this tutorial, I am going to show you 4 effective ways to make a yield to maturity calculator in excel. You can quickly use these methods even in large datasets to calculate Yield to Maturity value. Throughout this tutorial, you will also learn some important excel tools and functions which will be very useful in any excel related task.
Download Free Calculator
You can download the free calculator from here.
What Is Yield to Maturity?
Yield to Maturity is the measure of the total return where the bond is held for a maturing period. We can express it as an annual rate of return. It is also known as Book Yield or Redemption Yield. This is different from the Current Yield in that it takes into account the present value of a future bond. So Yield to Maturity is more complicated than the Current Yield. The Yield to Maturity can help us to decide whether we should invest in a bond or not.
Yield to Maturity Formula
We can use the below formula to calculate Yield to Maturity value:
YTM=(C+(FV-PV)/n)/(FV+PV/2)
Where:
C= Annual Coupon Amount
FV= Face Value
PV= Present Value
n= Years to Maturity
4 Effective Ways to Make a Yield to Maturity Calculator in Excel
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 2 columns. Initially, we formatted all the cells containing dollars in Accounting format and other cells in Percentage format where needed.
1. Using RATE Function
The RATE function is one of the financial functions in excel which can calculate the amount of interest on a loan. This function can be helpful in calculating yield to maturity. Let us see how to use this function.
Steps:
- First, go to cell C9 and insert the following formula:
=RATE(C8,C7,-C6,C4)*C5
- Now, press Enter and this will calculate the Yields to Maturity value in percentage.
Read More: How to Calculate Present Value of a Bond in Excel (3 Easy Ways)
2. Applying IRR Function
The IRR function is also an excel financial function similar to the RATE function. It denotes the Internal Rate of Return for specific cash flows. We can easily use this in financial modeling like calculating yield to maturity. Follow the steps below to achieve this.
Steps:
- To begin with, double-click on cell C10 and enter the below formula:
=IRR(C5:C9)
- Next, press the Enter key and you should get the IRR value for a period.
- Then, go to cell C12 and type in the formula below:
=C10*C11
- Finally, press Enter and you should now see the Yield to Maturity value in cell C12.
Read More: How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)
Similar Readings
- Preparing Bond Amortization Schedule in Excel (with Easy Steps)
- How to Calculate the Issue Price of a Bond in Excel
3. Utilizing YIELD Function
As a Financial function in excel, the YIELD function is very useful to determine bond yield. So, we can also use it to calculate yield to maturity value. Let us see the exact steps to do this.
Steps:
- To begin this method, double-click on cell C11 and insert the formula below:
=YIELD(C6,C7,C5,C10,C4,C8)
- Next, press the Enter key and consequently, this will find the Yield to Maturity value inside cell C11.
Read More: How to Calculate Clean Price of a Bond in Excel (3 Easy Ways)
4. Calculating Yield to Maturity by Direct Formula
If we do not want to use any excel function to calculate yield to maturity in excel, then we can use the direct formula instead to get the same result. Below are the steps on how to do this.
Steps:
- To start this method, navigate to cell C8 and type in the following formula:
=(C6+((C4-C5)/C7))/(C4+C5/2)
- After that, press the Enter key or click on any blank cell.
- Immediately, this will give you the percentage value of Yield to Maturity.
Read More: How to Calculate Price of a Semi Annual Coupon Bond in Excel (2 Ways)
Things to Remember
- Remember to add a ‘-’ sign before cell C6 inside the RATE function otherwise, it will not work properly.
- You might see a #NUM! error if you forget the negative sign.
- If you insert any non-numeric data inside any input, you will get a #VALUE! error in that case.
- The arguments inside the IRR function should have at least one positive and one negative value.
- The IRR function ignores any empty cells and text values.
Conclusion
I hope that you were able to apply the above methods to make a yield to maturity calculator in excel. Make sure to download the workbook provided to practice and also try these methods with your own datasets. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
This is a great work, clear and easy to understand.
Thanks
Great work. Thanks for sharing
Nice to hear that you found this article helpful. Thanks for the feedback!
Best regards
Great job will put all templates to work,
how ever looking for template for my “Dividend Tracking Portfolio” of 5~6 k with very few MANUAL entry love to download free if available or for reasonable price. keep up good work
I have a puzzling situation
Rate function does not work for some combination of numbers.
For example:
PV = -890
FV = 1000
pmt = 200
Nper = 31
There are other combinations too The answer is #NUM!
Any help?
Hi GABRIEL!
Thank you for your comment.
The RATE function does not work for some combinations. In these cases, the #NUM! error occurs. To solve this error simply add the Type and the Guess arguments in the RATE function. For Type, 0 or omitted is used for at the end of the period and 1 is used for at the beginning of the period. If RATE does not converge, try different values for the guess. RATE usually converges if the guess is between 0 and 1.
Using these two arguments, you can solve the #NUM! Error. Look at the below screenshot.
Please download the Excel file for your practice.
https://www.exceldemy.com/wp-content/uploads/2022/09/Calculate-Yield-to-Maturity.xlsx
Regards
Md. Abdur Rahim Rasel (Exceldemy Team)
I don’t understand your YTM premise. Its a 10 year bond, two coupons per year, and you input 30 coupons? Something is missing or I am missing something. Thanks in advance.
Hello Bruce,
Thank you for your feedback and for pointing out the error. In this case, instead of Coupons it should be Coupon Payment which in this case has been considered $30.0 as shown in the updated picture below.
Hopefully, this clears out any confusion and we are sorry for this error.
Regards,
ExcelDemy