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

**Read More: **Calculate Price of a Semi Annual Coupon Bond in Excel

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

**Read More: **Calculate Bond Price with Negative Yield in Excel

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

**Download Free Calculator**

You can download the free calculator here.

## Related Articles

- How to Calculate Clean Price of a Bond in Excel
- Calculate Face Value of a Bond in Excel
- Calculate the Issue Price of a Bond in Excel
- Calculate Duration of a Bond in Excel
- How to Calculate Bond Payments in Excel
- How to Calculate Present Value of a Bond in Excel

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

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

Couponsit should beCoupon Paymentwhich in this case has been considered$30.0as shown in the updated picture below.Hopefully, this clears out any confusion and we are sorry for this error.

Regards,

ExcelDemy