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.

Table of Contents

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 👇

Pro Tip!

This is important to know about Yield to Maturity. It is primarily relevant for existing (already issued) securities rather than those being issued.

This is because it is used to assess the expected return of a bond purchased at the current market price, considering all future coupon payments and the repayment of the face value at maturity. It gives investors insights into the bond’s profitability if held to maturity.

Whereas, for bonds being issued, the main area of focus is the coupon rate and the issue price rather than YTM. For newly issued bonds, the coupon rate (interest rate) is set based on prevailing market interest rates and the issuer’s credit quality. Since newly issued bonds are typically sold at or near their face value, the coupon rate and YTM are often the same or very close.

The concept of YTM is of importance in the secondary market, where bonds are bought and sold after their initial issuance. The market price of these bonds fluctuates based on interest rate changes, credit ratings, and other market conditions. Here is where investors use YTM to compare the returns of different bonds and evaluate whether a bond is underpriced or overpriced relative to others in the market to make informed investment decisions.

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.

Pro Tip!

The nper and the guess argument (if not omitted) must be coherent. The guess argument is your guess of what the RATE would be (can be any rate and you can also leave it omitted).

For example, if the bond pays semi-annual interest for 5 years, your nper argument would be 10 (5*2).

Accordingly, supply the guess argument as a semi-annual coupon rate (if you guess it to be 9% annually, supply it as 9%/2 = 4.5%.

Here’s another catch – the RATE that Excel would calculate for you would also be coherent with the nper supplied by you. If you have supplied it for quarterly payments, Excel will return the quarterly rate.

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

Pro Tip!

The pmt argument represents the periodic payments made on the bond. The bond pays 10% interest each year until maturity.

Here’s a key for you: financial instruments always pay the coupon rate on their par value.

We have the par value of this bond as $1000 and the bond pays interest yearly (i.e., once per year).

So, the yearly payments on this bond are $1000* 10% *1 = $100.

The bond pays $100 as annual coupon payments.

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)