# How to Make a Yield to Maturity Calculator in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

## Related Articles Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

1. Reply This is a great work, clear and easy to understand.
Thanks

2. Reply Great work. Thanks for sharing

• Reply Best regards

3. Reply 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

4. Reply 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?

• Reply Md. Abdur Rahim Rasel Sep 4, 2022 at 4:29 PM

Hi GABRIEL!
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. Regards
Md. Abdur Rahim Rasel (Exceldemy Team)

5. Reply 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.

• Reply 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 Advanced Excel Exercises with Solutions PDF  