Excel Problems

Excel Problems for chapter 13: Bond Pricing and Duration Using Data Tables and Holding Period Immunization

A. Bond Pricing and Duration Using Data Tables - Click here to download the spreadsheet (96.0K)

Bond pricing and duration calculations can be cumbersome. The calculations are set-up in a spreadsheet that is available on this page. The models allow you to calculate the price and duration for bonds of different maturities. The models also demonstrate a methodology to examine the sensitivity of the calculations to changes in coupon rate and yield to maturity.

Questions:

1. Using the methodology described for the sample bond in the duration table spreadsheet, calculate the price and duration for a 20-year bond that has a coupon rate of 4.5% and is selling at a yield to maturity of 6.0%. Prepare two one-way tables, one that calculates the price and one that calculates the duration, which allows the yield to maturity to range from 0.5% to 11.0% in increments of 0.5%. Prepare two two-way tables, one that calculates the price and one that calculates the duration, which allows the coupon rate and yield to maturity to range between 1.0% and 12.0% in increments of 1.0%.
2. Using the methodology described for the sample bond in the duration table spreadsheet, calculate the price and duration for a 30-year bond that has a coupon rate of 4.5% and is selling at a yield to maturity of 6.5%. Prepare two one-way tables, one that calculates the price and one that calculates the duration, which allows the yield to maturity to range from 0.5% to 11.0% in increments of 0.5%. Prepare two two-way tables, one that calculates the price and one that calculates the duration, which allows the coupon rate and yield to maturity to range between 1.0% and 12.0% in increments of 1.0%.

B. Holding Period Immunization - Click here to download the spreadsheet (41.0K)

The above spreadsheet is useful to understand the concept of holding period immunization. The spreadsheet is constructed to calculate duration and holding period returns on bonds for any given maturity. The value of a bond, its duration and holding period returns are calculated through formulas so that the individual cash flows for each bond need not be constructed. The model is based on annual compounding.

The initial sample bond has a maturity of seven years and is selling at an annual yield to maturity of 11.58%. Its coupon rate is 14% and its par value is $1,000. As is shown is the spreadsheet its market price would be $1111.93.

The duration of the bond is found using a closed-end formula. For the initial conditions, the bond has duration of slightly over 5 years. The spreadsheet demonstrates the impact on holding period return of a single increase or decrease in rates of 100 or 200 basis points. To illustrate the concept it is assumed that interest rates change only once and the rate change is assumed to take place shortly after the bond is purchased.

If the bond is liquidated at its duration of 5 years, the investor locks in the initial yield of approximately 11.58%. To achieve exact immunization, some rebalancing would have to occur since the bond’s duration changes with the change in rates so the realized return from investment does not exactly equal the 11.58% promised yield to maturity. As you can see, the results are still very close. The investor gains in reinvestment (price) and those gains are offset by losses in price (reinvestment).

If you were to hold the bond to maturity, the variation in realized returns. If rates rise and you hold the bond to maturity, holding period equal 7 years, you would realize higher returns. The higher reinvestment income is not offset by the lower sales price. If rates fall, the reinvestment income is lower and not offset by the higher relative sales price. If the bond is held to maturity, the investor will get the par value of the bond and will not receive the earlier sales proceeds.

Questions:

1. Using the IMMEXM spreadsheet calculate the duration and holding period returns for a 14-year bond with a coupon rate of 14% that is selling at 13.163% yield to maturity. Assume an initial holding period of seven years. Contrast these results with the assumption that the bond is held until maturity.
2. Using the IMMEXM spreadsheet calculate the duration and holding period returns for a 30-year bond with a coupon rate of 15.75% that is selling at 13.845% yield to maturity. Assume an initial holding period of eight years. Contrast these results with the assumption that the bond is held until maturity.