Learn to Calculate Yield to Maturity in MS Excel

Understanding a bond's yield to maturity (YTM) is an essential task for fixed-income investors. But to fully grasp YTM, we must first discuss how to price bonds in general. The price of a traditional bond is determined by combining the present value of all future interest payments (cash flows), with the repayment of principal (the face value or par value) of the bond at maturity.

The rate used to discount these cash flows and principal is called the "required rate of return," which is the rate of return required by investors who are weighing the risks associated with the investment. 

Key Takeaways

  • To calculate a bond's maturity (YTM), it's vital to understand how bonds are priced and evaluated.
  • It combines the present value of all future interest payments (cash flows) with the repayment of the principal (the face value or par value) of the bond at maturity.
  • The pricing of a bond largely depends on the difference between the coupon rate, which is a known figure, and the required rate, which is inferred.
  • Coupon rates and required returns frequently do not match in the subsequent months and years following an issuance because market events impact the interest rate environment. 

How to Price a Bond

The formula to price a traditional bond is: 

PV = P ( 1 + r ) 1 + P ( 1 + r ) 2 + + P + Principal ( 1 + r ) n where: PV = present value of the bond P = payment, or coupon rate × par value ÷ number of payments per year r = required rate of return ÷ number of payments per year Principal = par (face) value of the bond n = number of years until maturity \begin{aligned} &\text{PV} = \frac { \text{P} }{ ( 1 + r ) ^ 1 } + \frac { \text{P} }{ ( 1 + r ) ^ 2 } + \cdots + \text{P} + \frac { \text{Principal} }{ ( 1 + r ) ^ n } \\ &\textbf{where:} \\ &\text{PV} = \text{present value of the bond} \\ &\text{P} = \text{payment, or coupon rate} \times \text{par value} \div \text{number of} \\ &\text{payments per year} \\ &r = \text{required rate of return} \div \text{number of payments} \\ &\text{per year} \\ &\text{Principal} = \text{par (face) value of the bond} \\ &n = \text{number of years until maturity} \\ \end{aligned} PV=(1+r)1P+(1+r)2P++P+(1+r)nPrincipalwhere:PV=present value of the bondP=payment, or coupon rate×par value÷number ofpayments per yearr=required rate of return÷number of paymentsper yearPrincipal=par (face) value of the bondn=number of years until maturity

The pricing of a bond is therefore critically dependent on the difference between the coupon rate, which is a known figure, and the required rate, which is inferred.

Suppose the coupon rate on a $100 bond is 5%, meaning the bond pays $5 per year, and the required rate—given the risk of the bond—is 5%. Because these two figures are identical, the bond will be priced at par, or $100.

This is shown below (note: if tables are hard to read, hover over the graphic and select the magnification icon):

Pricing a Bond After It's Issued

Bonds trade at par when they are first issued. Frequently, the coupon rate and required return don't match in the subsequent months and years because events impact the interest rate environment. A failure of these two rates to match causes the price of the bond to appreciate above par (trade at a premium to its face value) or decline below par (trade at a discount to its face value) in order to compensate for the rate difference.

Take the same bond as above (5% coupon, pays out $5 a year on a $100 principal) with five years left until maturity. If the current Federal Reserve rate is 1%, and other similar-risk bonds are at 2.5% (they pay out $2.50 a year on a $100 principal), this bond looks very attractive: offering 5% in interest—double that of comparable debt instruments.

Given this scenario, the market will adjust the price of the bond proportionally, in order to reflect this difference in rates. In this case, the bond would trade at a premium amount of $111.61. The current price of $111.61 is higher than the $100 you will receive at maturity, and that $11.61 represents the difference in the present value of the extra cash flow you receive over the life of the bond (the 5% versus the required return of 2.5%). 

In other words, in order to get that 5% interest when all other rates are much lower, you must buy something today for $111.61 that you know in the future will only be worth $100. The rate that normalizes this difference is the yield to maturity. 

Calculating the Yield to Maturity in Excel

The above examples break out each cash flow stream by year. This is a sound method for most financial modeling because best practices dictate that the sources and assumptions of all calculations should be easily auditable. However, when it comes to pricing a bond, we can make an exception to this rule because of the following truths:

  • Some bonds have many years (decades) to maturity, and a yearly analysis, like that shown above, may not be practical
  • Most of the information is known and fixed: We know the par value, we know the coupon, and we know the years to maturity.

For these reasons, we'll set up the calculator as follows:

In the above example, the scenario is made slightly more realistic by using two coupon payments per year, which is why the YTM is 2.51—slightly above the 2.5% required rate of return in the first examples.

How Can an Investor Realize a Bond's Yield to Maturity?

For the YTM to be actualized, bondholders must commit to holding the bond until maturity.

Can I Calculate a Bond's YTM By Hand?

Yes, it is possible; however, it is far easier to use software like Excel or a financial calculator to do so. This is especially true for bonds with longer times to maturity, since you must take into account the present values of reinvented coupon payments at each period, which can quickly become overly complex.

How Is YTM Used by Investors?

YTM is an annualized rate that assumes an investor holds a bond to maturity if it is purchased at its current market price. This provides a standardized yield that can then allow comparisons across different fixed-income investments of various types. Using YTM, one could, for instance, compare the relative attractiveness of bonds from different issuers, among coupon and zero-coupon bonds, and those with different maturity.

Article Sources
Investopedia requires writers to use primary sources to support their work. These include white papers, government data, original reporting, and interviews with industry experts. We also reference original research from other reputable publishers where appropriate. You can learn more about the standards we follow in producing accurate, unbiased content in our editorial policy.
  1. Investor.gov. "Bonds."

  2. U.S. Department of Treasury. "Treasury Yield Curve."