# Bootstrapping the yield curve

#### waptrick

##### New Member
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:

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
follows:

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

#### lushukai

##### Well-Known Member
Subscriber
Hi @waptrick ,

For the 0.5 year spot rate,

99.5 = 100 / (1+r_0.5)^0.5

The 0.5 year spot rate r_0.5 is annualised.

99 = (0.875/2) / (1+r_0.5)^0.5 + (100 + 0.875/2) / (1+r_1)

The 1.0 year spot rate r_1 is annualised. You can repeat for the rest of the period rates. The process I just showed is bootstrapping.

I'm honestly not sure about the second question, I guess it might be the sum of the coupon returns and capital appreciation of the bond price?

Last edited:

#### waptrick

##### New Member
Thanks Lushukai!

Just wondering what rate to lock in for the second part of the question?

#### lushukai

##### Well-Known Member
Subscriber
Hi @waptrick,

I'm not sure what you mean by "what rate to lock in". This is because, from what I understand about the question, after one year, the 0.5 and 1.0 year coupons have paid out and the bond itself is also worth different as the discount curve has changed.

So this holding period return seems to be the sum of the coupons and capital difference (the new bond price should be less as it is missing two coupons).

I'm honestly not sure about this question as I am making a lot of assumptions. I would have to think up of a framework to solve this.