Zero-Coupon Yields from Coupon-Bearing Yields in Excel

A zero-coupon bond is a bond that does not pay any interest during its lifetime. It only pays the face value at maturity. For example, you buy a zero-coupon bond for $900 that will pay you $1000 in one year. The difference between the purchase price and the face value is your profit.

A coupon-bearing bond is a bond that pays regular interest payments, called coupons, until maturity. It also pays the face value at maturity. For example, you buy a coupon-bearing bond for $950 that will pay you $50 every six months and $1000 at the end of two years. The coupons and the difference between the purchase price and the face value are your profit.

The yield of a bond is the annualized return that you get from investing in the bond. It depends on the purchase price, the face value, the coupon rate, and the time to maturity. The yield of a zero-coupon bond is the same as the zero-coupon rate, which is the interest rate for borrowing or lending money for a specific period of time. The yield of a coupon-bearing bond is the same as the yield to maturity, which is the interest rate that makes the present value of the bond’s cash flows equal to the purchase price.

The relationship between zero-coupon yields and coupon-bearing yields depends on the shape of the yield curve, which is the graph of the zero-coupon rates for different maturities. If the yield curve is flat, meaning that the zero-coupon rates are the same for all maturities, then the zero-coupon yields and the coupon-bearing yields are also the same for all maturities. If the yield curve is upward sloping, meaning that the zero-coupon rates increase with maturity, then the zero-coupon yields are higher than the coupon-bearing yields for the same maturity. This is because the coupon-bearing bond pays some interest before maturity, which reduces the effective interest rate. If the yield curve is downward sloping, meaning that the zero-coupon rates decrease with maturity, then the zero-coupon yields are lower than the coupon-bearing yields for the same maturity. This is because the coupon-bearing bond pays some interest before maturity, which increases the effective interest rate.

Basic Theory:

1. Zero-Coupon Yield (YTM): It is the rate of return anticipated on a bond that is held until
   maturity, assuming all coupon and principal payments are made as scheduled.
2. Coupon-Bearing Yield: This represents the yield on a bond that pays periodic interest.

The relationship between these yields can be expressed mathematically, allowing us to calculate the zero-coupon
yield from the coupon-bearing yield.

Procedures:

1. Understand the Bond Price Formula: The bond price (P) can be calculated using the present
   value of future cash flows formula:

Where:

• is the periodic coupon payment,
• is the periodic yield,
• is the face value of the bond, and
• is the number of periods to maturity.

2. Use Excel Formulas: Excel provides functions like PV() to calculate present value. For a
   bond with periodic coupon payments, use the formula:

3. Calculate Zero-Coupon Yield: To find the zero-coupon yield, set the bond price equal to the
   present value of the face value discounted at the zero-coupon yield:

Solve for using Excel’s Goal Seek or Solver functions.

Comprehensive Explanation:

Let’s consider a scenario:

• Face Value (): $1,000
• Annual Coupon Payment (): $50
• Time to Maturity (): 5 years
• Coupon-Bearing Yield (): 6%

Using the bond price formula:

Applying this in Excel, we get a bond price of $977.38.

Now, to find the zero-coupon yield:

Using Goal Seek or Solver, we find that the zero-coupon yield () is approximately 2.57%.

Excel Table:

Result:

The zero-coupon yield is approximately 2.57%.

Other Approaches:

1. Yield Function in Excel: Excel also provides the YIELD() function, which can directly
   calculate the yield of a security that pays periodic interest.
2. Macaulay Duration: Another approach involves using Macaulay Duration to estimate the
   sensitivity of bond prices to changes in yield.