Calculating Bond Yield to Maturity and Yield to Call

Yield to Maturity (YTM) and Yield to Call (YTC) are two essential bond metrics that help investors estimate the returns on bonds if held until maturity or called before maturity. These calculations consider a bond’s current price, coupon rate, and time to maturity or call date. Excel provides tools to calculate both YTM and YTC, allowing for effective bond investment analysis.

Why Calculate YTM and YTC?

• Yield to Maturity (YTM): Estimates the total return on a bond if held to maturity, considering both coupon payments and any capital gain or loss.
  

• Yield to Call (YTC): Measures the yield if the bond is called (redeemed) by the issuer before maturity, common in callable bonds.
  

Yield to Maturity (YTM) Calculation

The YTM calculation is complex as it involves solving for the interest rate in the bond price formula. Excel’s RATE function can simplify this calculation.

Formula for Bond Price with YTM:

Bond Price=∑(Coupon Payment)/[(1+YTM)]^t + (Face Value)/[(1+YTM)]^n

Step-by-Step Guide to Calculate YTM in Excel

Step 1: Set Bond Parameters

Suppose we have a bond with:

• Face Value = ₹1,000
• Current Price = ₹950
• Coupon Rate = 5%
• Maturity = 10 years

Step 2: Calculate Annual Coupon Payment

The coupon payment is:

=Face Value * Coupon Rate

For this example:

=1000 * 5% = ₹50

Step 3: Use Excel’s RATE Function for YTM

In Excel, use the RATE function to calculate YTM:

=RATE(Maturity, -Coupon Payment, Current Price, Face Value)

For this example:

=RATE(10, -50, -950, 1000)

Result: The YTM is approximately 5.57%, indicating the bond’s annual return if held to maturity.

Yield to Call (YTC) Calculation

For callable bonds, YTC estimates the return if the bond is redeemed before maturity.

Step-by-Step Guide to Calculate YTC in Excel

Assume the bond has a call date of 5 years with a call price of ₹1,020.

Step 1: Adjust Call Date and Call Price

Step 2: Use Excel’s RATE Function for YTC

Use the RATE function with the call date and call price:

=RATE(Call Date, -Coupon Payment, Current Price, Call Price)

For this example:

=RATE(5, -50, -950, 1020)

Result: The YTC is approximately 3.68%, reflecting the bond’s yield if called in 5 years.

Benefits of YTM and YTC in Excel

• Scenario Comparison: Compare YTM and YTC to understand potential returns for callable bonds.

• Decision Support: Assess which bonds align with your yield expectations.

• Automated Calculation: Excel’s RATE function simplifies complex yield calculations.

Key Takeaways:

• YTM provides an estimate of returns if the bond is held to maturity.

• YTC indicates the potential yield if the bond is called before maturity.

• Excel makes YTM and YTC calculations efficient, aiding in bond investment decisions.

Conclusion

Calculating YTM and YTC in Excel gives investors a complete view of potential bond returns, supporting informed decision-making.

Next Chapter Preview: In the next chapter, we’ll cover Coupon Payment and Accrued Interest Calculations. This will include determining periodic coupon payments and calculating accrued interest, which is essential for bond pricing between coupon dates. Stay tuned!