Understanding Bond Simple Yield to Maturity (YTM) Calculation in Excel

Bond simple yield to maturity (YTM) is an estimate of the annual return that an investor will earn by buying a bond at its current market price and holding it until it matures. It is also known as the book yield or redemption yield.

The simple YTM is calculated by adding the annual coupon payment (the interest that the bond pays) and the average annual capital gain or loss (the difference between the bond’s face value and its current price, divided by the number of years to maturity). Then, this sum is divided by the average of the bond’s face value and its current price.

For example, suppose a bond has a face value of $1,000, a coupon rate of 10%, a current price of $900, and five years to maturity. The annual coupon payment is $100 (10% of $1,000). The average annual capital gain is $20 (($1,000 – $900) / 5). The simple YTM is ($100 + $20) / (($1,000 + $900) / 2) = 0.13 or 13%.

The simple YTM is an approximation that does not account for the compounding effect of reinvesting the coupon payments at the same rate as the YTM. It also assumes that the bond will not be called (redeemed by the issuer before maturity) or put (sold back to the issuer by the holder before maturity). To calculate the exact YTM, a trial-and-error method or a solver function in Excel is needed.

The YTM is important for investors because it allows them to compare the returns of different bonds with different prices, coupon rates, and maturities. It also helps them to evaluate whether a bond is fairly priced, overpriced, or underpriced in the market. Generally, a bond is considered to be a good investment if its YTM is higher than the prevailing interest rate or the required rate of return of the investor.

Theory:

The yield to maturity is essentially the internal rate of return (IRR) of a bond, considering its current market price, face value, coupon interest payments, and the time to maturity. It is expressed as an annual percentage rate (APR) and represents the total return an investor can expect from a bond if held until maturity.

Basic Components:

1. Face Value (FV): The nominal or par value of the bond.
2. Current Market Price (P): The market value of the bond.
3. Annual Coupon Payment (C): The fixed interest payment made annually.
4. Years to Maturity (T): The remaining time until the bond matures.

Formula for YTM:

Procedures in Excel:

1. Enter Data:
   • Create a new Excel spreadsheet.
   • Label cells for Face Value (FV), Current Market Price (P), Annual Coupon Payment (C), and Years to Maturity (T).
   • Input the respective values into the cells.
2. Use YIELD Function:
   • Excel has a built-in function called YIELD, which can be used to calculate the YTM.
   • The syntax is

        

   • In this case, ‘rate’ is the YTM.
3. Calculate YTM:
   • Enter the YIELD formula, referencing the appropriate cells for settlement, maturity, rate, pr, redemption, frequency, and basis.
   • The result will be the YTM expressed as a decimal.

Example Scenario:

Let’s consider a bond with:

• Face Value (FV): $1,000
• Current Market Price (P): $950
• Annual Coupon Payment (C): $50
• Years to Maturity (T): 5 years

The calculated YTM using Excel’s YIELD function is approximately 5.79%.

Excel Table:

Other Approaches:

1. Solver Add-in:
   • You can use the Solver add-in to find the YTM by setting the YIELD formula as the objective function and specifying constraints.
2. Manual Iteration:
   • Utilize Excel’s Goal Seek feature to iteratively approach the YTM by changing the rate until the formula matches the market price.