Bootstrapping the yield curve

Hey everyone,

I found this question in an old item set and wanted to know if anyone could help explain how to bootstrap the curve in excel. I have my workings in an excel file for anyone that’s interested. But I am mostly struggling with questions a and c in this item set:

You observe the prices for the following four US Treasury bonds:


a. Compute the yield curve (i.e., spot rate curve) for maturities from 6months to 2 years (intervals of 6 months).Note:US Treasuries use semiannual compounding, and coupons are paid every six months.

Now assume that, one year later, the zero coupon yield curve is as


What is the total return (or “holding period return”) over the year on the 2-year coupon bond identified in

The 6-month spot rate should be easy; it’s 0.5025% semiannual, effective, or 1.0050% BEY.

For the 1-year rate, solve:

99 = \frac{0.875 / 2}{1 + 0.5025\%} + \frac{100 + 0.875/2}{\left(1 + s_1\right)^2}

That will give you the semiannual, effective 1-year spot rate; double it to get the BEY.

Continue in a like manner for the others.

Thanks Magician!

How would you do this in excel?

I have done the first two rates correctly but the last two don’t look correct from my screenshot:

The rates should be:

  • s1 = 1.0050%
  • s2 = 1.8911%
  • s3 = 1.9752%
  • s4 = 3.9064%

For s3, are you subtracting the present value of two coupons from the price of 98.2?

Thanks Magician.

I rechecked my formula in excel and I am now seeing the correct rates:

Just wondering about the second part which requires total return (or “holding period return”) over the year on the 2-year coupon bond.

If I reinvested the coupons by locking in the forward rate between the 6 month and 1 year rate?

The holding period return will comprise:

  • Price change on the bond
  • Coupons received
  • Interest on the first coupon received

You can get the ending price using the new spot rates. The coupons received should be obvious. For the interest on the first coupon, assume that it was reinvested for 6 months at the original 6-month spot rate (as we have no better information on which to base the assumption).