# How to Create a Yield to Maturity Calculator in Excel – 4 Methods

## 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. It is different from the Current Yield as it takes into account the present value of a future bond.

## Yield to Maturity Formula

You can use the formula  below to calculate the Yield to Maturity value:

YTM=(C+(FV-PV)/n)/(FV+PV/2)

C=  Annual Coupon Amount

FV= Face Value

PV= Present Value

n= Years to Maturity

The sample dataset contains 6 rows and 2 columns. Cells contain dollars in Accounting format and in Percentage format.

### Method 1 – Using the RATE Function

Steps:

• Go to C9 and insert the following formula:
=RATE(C8,C7,-C6,C4)*C5

• Press Enter to calculate the Yields to Maturity value in percentage.

Read More: How to Calculate Coupon Rate in Excel

### Method 2 – Applying the IRR Function

Steps:

• Double-click C10 and enter the formula:
=IRR(C5:C9)

• Press Enter to get the IRR value for a period.

• Go C12 and use the formula below:
=C10*C11

• Press Enter to see the Yield to Maturity value in C12.

### Method 3 – Utilizing the YIELD Function

Steps:

• Double-click C11 and enter the formula below:
=YIELD(C6,C7,C5,C10,C4,C8)

• Press Enter and find the Yield to Maturity value in C11.

### Method 4 – Calculating the Yield to Maturity by using a Direct Formula

Steps:

• In C8, enter the following formula:
=(C6+((C4-C5)/C7))/(C4+C5/2)

• Press Enter or click any blank cell.
• The percentage value of the Yield to Maturity will be displayed.

## Things to Remember

• Remember to add a ‘-’ sign before C6 inside the RATE function.
• You might see a #NUM! error if you forget the negative sign.
• If you insert any non-numeric data, you will get a #VALUE! error.
• The arguments in the IRR function must have at least one positive and one negative value.
• The IRR function ignores empty cells and text values.

## Related Articles

<< Go Back to Bond Price Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

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

2. Great work. Thanks for sharing

Best regards

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?

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

Advanced Excel Exercises with Solutions PDF