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.

**Table of Contents**hide

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

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