# Calculate Yield to Maturity (Excel Formula YIELDMAT)

The Yield to Maturity is a very valuable financial metric used to assess the returns offered by debt instruments until maturity.

It is widely used in the world of finances and trading. The concept is somehow technical but very interesting. In this tutorial, I will walk you through different methods to calculate YTM in Microsoft Excel 🚶♀️

Here’s your free practice workbook so that you can practice as you read. Download it and come along with me till the end.

## What is Yield to Maturity

Yield to Maturity (YTM) is the total return anticipated on a bond if it is held until it matures.

It measures the yield or return offered by a bond that has been issued already and is now being sold in the market 🤝

YTM is expressed as an annual rate and takes into account the bond’s current market price, par value, coupon interest rate, and time to maturity.

For example, Company ABC issued 7-year bonds having a face value of $1000 and a coupon rate of 5%. It was purchased by Mr. A as soon as it was issued.

After 2 years have elapsed since it was issued, Mr. A now wants to trade it on the exchange for $950. There are still 5 years to this bond’s maturity. What annual return does this bond offer? In other words, what is the YTM for this bond?

To know this, we calculate the Yield to Maturity for bonds using the techniques explained in the next section of this tutorial 👇

## How to calculate Yield to Maturity in Excel

In this section, we will look into two financial functions of Excel that can be used to calculate Yield to Maturity for a given instrument in Excel. Using these, you can readily calculate the YTM for any bond/instrument super easily and quickly.

### Using the YTM function

The YIELDMAT function in Excel calculates the annual yield of a security that pays interest at maturity. This might not be the phenomenon with all the securities that the interest on them is not paid until maturity 📝

Most of the securities pay interest yearly or periodically during their tenure. So, the YIELDMAT function is only relevant for you if the security in question pays all the interest at maturity.

The syntax of the YIELDMAT function is as follows.

Where:

**Settlement:**The security’s settlement date. This is the date when you buy the security from the market.**Maturity:**The security’s maturity date.**Issue:**The security’s issue date, when the security was originally issued.**Rate:**The security’s annual coupon rate.**PR:**The security’s price per $100 face value.**Basis:**The day count basis to use. An optional argument that you can omit.

The Basis argument can take the following forms:

**0 or omitted:**US(NASD) 30/360**1:**Actual/actual**2:**Actual/360**3:**Actual/365**4:**European 30/360

To use the YIELDMAT function to calculate YTM, we have the following example.

**Step 1)** Begin writing the YIELDMAT function as follows:

**Step 2)** Refer to the settlement date for this bond as the first argument.

**Step 3)** Refer to the maturity date for this bond as the second argument.

**Step 4)** The third argument is the issuance date.

**Step 5)** Specify the coupon/interest rate for the bond as the rate argument.

Next for the PR, here is how we have calculated it. It is just the purchase price expressed in terms of $100 face value. Divide the market price with the par value and multiply it by 100 to have the PR calculated 💸

**Step 6)** Add the pr for this bond as the fifth argument.

**Step 7)** Leave the basis argument omitted.

**Step 8)** Press Enter to have the YTM his bond calculated.

**Step 9)** Select the result, go to the home tab > Number group > Percentage icon to convert it into a percentage.

The YTM for this bond comes out to be 10.14%.

The YEILDMAT function is an easy and interesting one to use. However since it only caters to bonds that pay interest at maturity together with the redemption, it can only be used to calculate the YTM for certain bonds 💰

Then how do you calculate YTM for other bonds that have different terms, particularly those that pay interest periodically?

Check out the next section to see how you can use the RATE function to calculate the YTM for such bonds.

### Using the RATE function

Here are the details of a bond listed on the stock exchange.

The selling price for the bond in the market today is $906.5. It pays 10% interest on its par value for the coming 5 years and will be redeemed for its par value of $1,000.

What is the Yield to Maturity of this bond? In other words, what return does it give us as compared to the market rate of return? 🚀

We are going to find the YTM for this bond using the RATE function.

**Step 1)** Begin writing the RATE function as follows:

**Step 2)** As the nper argument, give the number of years before the bond matures.

**Step 2)** As the pmt argument, we will refer to the periodic interest payment by multiplying the Par Value of the bond with the Coupon rate and the payment frequency per year (annually so 1).

**Step 3)** As the pv argument, refer to the price for the bond that you’ve paid today to purchase it.

Note that the purchase price appears as a negative value since it represents a cash outflow (the amount that you’ve paid to purchase the bond). If either of the values in the RATE function is not negative (the pmt or the pv), the RATE function will return a #NUM! Error 🤔

**Step 4)** The fv refers to the redemption value on maturity.

The FV (future value) represents the amount that will be redeemed upon the bond’s maturity. Since this is the value that we will receive (a cash inflow) it appears as a positive number.

**Step 5)** The bond payments are at the end of each year, so either set ‘0’ as the type argument or leave it omitted.

**Step 6)** The guess argument is also optional so omitting it too.

**Step 7)** Press enter to have the YTM calculated.

The YTM for this bond comes out as 12.64%. This means that considering the price you’ve paid to purchase the bond, this bond yields you a 12.64% return per year.

Which is evident enough, that the bond’s coupon rate is 10%. But since we have purchased the bond for $906.5 only (less than its Par value of $1000), we yield more than its coupon rate of 10% before the bond matures.

### The Math behind Yield to Maturity (YTM)

If you’re interested in seeing what goes on behind the math of YTM, this section is for you.

YTM is used by investors to understand the value of debt instruments. As discussed above, YTM is a concept relevant for instruments already floating in the market, that have been issued and are now available in the market for buying and selling 💪

Unless equity securities (shares and stocks etc.), debt instruments do not have any surge in the stock prices, or any dividends driven by exceptional performance to offer. All they offer is guaranteed, periodic, and pre-determined interest payments and redemption at maturity.

So, as an investor, how do you decide if you should or should not buy a bond at a given market price?

By comparing its price to the return offered by other similar instruments in the market 💡

Assume the market rate of return is 12.64% (what other bonds of similar characteristics in the market are offering), so how do you know what a justifiable price for this bond is considering its terms?

By discounting the cashflows it offers (the interest and redemption money) at the market rate of return.

Like here:

This calculates the PV of this bond as $906.45.

Now if this bond is sold for more than $906, you know it’s overpriced, and vice versa. Similarly, once a bond is priced, investors work it around to find the YTM for it (like we did above).

This YTM is then compared with the market rate of return for other bonds to see how good or bad of a return this bond offers and if it is worth buying 🛒

## Conclusion

Yield to Maturity (YTM) is a key measure for investors to understand the potential returns of a bond.

In the tutorial above, we have been to the depths of the concept of Yield to Maturity – what is it and when do you use it? Plus, we have seen how you can calculate it in Excel using different methods.

- If you enjoyed learning this, you’ll enjoy learning about other financial functions of MS Excel too. Check out the following Excel tutorials by Spreadsheeto.
- How to Calculate APR in Excel (Easy Examples)
- How to Use the XIRR Function in Excel (Explained Easy)
- How to Use the RATE Function in Excel (Interest Rate)