How to Make a Yield to Maturity Calculator in Excel

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.

yield to maturity excel


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

RATE function to Make a Yield to Maturity Calculator in Excel

  • 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)

IRR function to Make a Yield to Maturity Calculator in Excel

  • Next, press the Enter key and you should get the IRR value for a period.

IRR function to Make a Yield to Maturity Calculator in Excel

  • 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


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)

yield function to Make a Yield to Maturity Calculator in Excel

  • 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)

direct formula to Make a Yield to Maturity Calculator in Excel

  • 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

Kawser

Kawser

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 how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

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

  2. Great work. Thanks for sharing

  3. 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. 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!
      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.
      calculate yield to maturity
      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)

Leave a reply

ExcelDemy
Logo