Excel Problems

Spot and Forward Yields - Click here to download the spreadsheet (108.0K)

The spreadsheet presented above can be used to estimate spot rates from coupon bonds and to calculate the forward rates for both single-year and multi-year bonds. The spreadsheet demonstrates a methodology to bootstrap spot rates from coupon bonds. The model sequentially solves for the spot rates that are associated with each of the periods. The methodology is similar but slightly different than the regression methodology described in the text. Spot yields are derived from the yield curve of bonds that are selling at their par value, also referred to as the current coupon yield curve.

Once the spot rates are estimated, individual spot rates are then used to discount each period’s cash flow. The sum of these cash flows would be the price of the bond. Once we know the price of the bond, the bond’s yield to maturity can be found even though the underlying discounting process is using spot rates. If you were to error and choose the on-the-run yield to maturity as the appropriate rate to discount each of the bond’s coupons, the price is much different. That difference is calculated in the worksheet.

The spreadsheet uses the individual spot rates to estimate forward rates that we should observe under the Expectations Theory for the Term Structure of Interest Rates. Forward rates are estimated for 1-yr and multi-year bonds. The forward rates can be used to understand how market expectations would affect future rates.

Questions:
Suppose that you observe the following yields to maturity for the par bond yield curve. All of the bonds in question are selling at their par value of $1,000.

Maturity In Years	Yield to Maturity (in decimal form)
1	0.05
2	0.051
3	0.052
4	0.0535
5	0.0545
6	0.0555
7	0.0567
8	0.0577
9	0.0578
10	0.059
11	0.06
12	0.0611
13	0.0622
14	0.0633
15	0.0644
16	0.0655
17	0.0666
18	0.0677
19	0.0688
20	0.0699

1. Using the Annual Spot Yield Excel Worksheet, calculate the spot rates implied by the par bond yield curve.
2. Compare the value of a 20-year, 7.5% coupon bond if you were to incorrectly use the Yield to Maturity for the 20-year Par Value Bond as opposed to the individual spot rates to discount the bond’s cash flows.
3. What are the forward rated for 1-yr securities in years three through six that are implied the spot rates?
4. According to the expectations theory, what rate would the market expect on a 4-year zero coupon bond in two years?
5. According to the expectations theory, what rate would the market expect on a 10-year zero coupon bond in one year?