Understanding Bond Yield in the Final Coupon Period: Excel Formula and Calculation

Bond yield is the rate of return that a bond investor can expect to earn from holding a bond until it matures. It depends on the bond’s coupon rate, face value, current price, and time to maturity.

The coupon rate is the annual interest payment that the bond issuer pays to the bondholder, expressed as a percentage of the bond’s face value. The face value is the amount that the bond issuer promises to pay back to the bondholder at maturity. The current price is the amount that the bondholder pays to buy the bond in the market. The time to maturity is the number of years left until the bond matures.

There are different ways to measure bond yield, such as current yield, yield to maturity, and yield to call. Current yield is the annual coupon payment divided by the current price of the bond. Yield to maturity is the annualized rate of return that the bondholder will receive if they hold the bond until it matures, taking into account the difference between the current price and the face value. Yield to call is the annualized rate of return that the bondholder will receive if they hold the bond until it is called by the issuer, which means that the issuer pays back the bond before maturity at a specified price.

In the final coupon period, the bond yield is equal to the coupon rate plus the capital gain or loss from selling the bond at maturity. For example, suppose a bond has a face value of $1,000, a coupon rate of 6%, and a current price of $950. The bond pays a semiannual coupon of $30 every six months and matures in one year. The current yield is 6.32% ($30 / $950 x 2). The yield to maturity is 8.65%, which is the annualized rate that makes the present value of the bond’s cash flows equal to its current price. The yield to maturity can be calculated using a financial calculator or an online tool. The yield in the final coupon period is 9.47%, which is the coupon rate (6%) plus the capital gain ($50 / $950 x 100%). The capital gain is the difference between the face value ($1,000) and the current price ($950) of the bond.

Basic Theory:

The yield of a bond is the annualized return an investor can expect to receive, considering both the periodic coupon payments and the potential capital gain or loss at maturity. In the final coupon period, since there are no more future coupon payments, the yield is solely dependent on the bond’s current price, face value, and the remaining period to maturity.

Procedures:

1. Understand Bond Terms:
   • Face Value (FV): The nominal or par value of the bond.
   • Current Price (P): The market price at which the bond is currently trading.
   • Remaining Periods to Maturity (n): The number of periods left until the bond matures.
2. Determine the Annual Coupon Payment:
   • In the final coupon period, the annual coupon payment is equal to the stated interest rate multiplied by the face value.
3. Calculate the Yield:
   • The yield can be computed using the formula: Yield = (Annual Coupon Payment + (Face Value - Current Price) / Remaining Periods to Maturity) / ((Face Value + Current Price) / 2)

Excel Table Explanation:

Let’s consider a scenario with the following details:

Scenario Calculation:

1. Annual Coupon Payment:
   • = $1,000 * 5% = $50
2. Yield Calculation:
   • = ($50 + ($1,000 - $950) / 2) / (($1,000 + $950) / 2) = 5.79%

Therefore, the bond yield in the final coupon period is approximately 5.79%.

Other Approaches:

1. Yield Function in Excel:
   • Excel provides the YIELD function, simplifying the calculation. In our scenario: =YIELD(B2, B1, B5, B4, B1, 2, 1)
2. IRR Function:
   • Another approach is to use the IRR (Internal Rate of Return) function. Assuming the investor holds the bond until maturity: =IRR({-B2, B5, B5, B5 + B1}, 0.05)