Reference no: EM132343346 , Length: word count:1100

Case - Walt Disney Company's Sleeping Beauty Bonds- Duration Analysis

Questions

1. What are the cash payments associated with the Sleeping Beauties? Who gets how much and when, per $100 of bonds issued?

2. Open the Sleeping Beauty Excel Workbook, which contains a number of worksheets. Double-click on the Basic Spreadsheet . It contains a list of years and payments made each year on the Sleeping Beauties. (For simplicity, we are ignoring the fact that US debentures, by convention, pay interest semiannually.) The NPV function in Excel calculates values of cash flow streams for a given interest rates. Cell E6 titled Present Value contains the formula, and shows the resulting price of the bonds. What interest rate was used to calculate the price? Was it higher or lower than 7.55%?

3. Suppose on the day after the Sleeping Beauties were sold, the prevailing interest rate increased one percentage point, i.e., from 7.55% to 8.55%. What would be the new price of the Sleeping Beauties? If the interest rate dropped by one percentage point, what would the price of the Sleeping Beauties become?

4. What is the formula for the present value of a single cash flow received n years from today? In the space provided (column G) use this formula to calculate the present value of each year's cash flow from the Sleeping Beauty bonds.

5. Use the Excel Chart function (Insert; Chart; As a new sheet...) to create pictures of:

• The "raw" cash flows from the Sleeping Beauty bond;

• The present values of the individual cash flows from the Sleeping Beauty bond. (We call this picture the present value pattern of the bond. We will use present value patterns as an aid to reasoning throughout the course.)

Compare the two pictures. Do they look as you expect? Could you have drawn them freehand ahead of time?

6. The next spreadsheet in the Workbook is called Interest Rates. Column D in the Interest Rates spreadsheet contains a list of interest rates, ranging from 2% to 1000%. Use the NPV function in Excel to calculate the value of the Sleeping Beauties for each of the interest rates shown.

7. The next sheet in the Workbook is called "Maturity." The first three columns are identical to the Basic Spreadsheet. The next column contains cash flows for a bond that is just like Sleeping Beauty, but lasts only 10 years ("Napping Beauty"). Column F contains the same list of interest rates as in the Basic Spreadsheet, excluding the very high ones. Columns G and H contain present values for the Sleeping and Napping bonds that correspond to the different interest rates. (You can check your answers to Question 6 against the values in Column G.)

• Compare the prices of the Sleeping and Napping bonds at the initial interest rate of 7.55%. Why are they the same?

• What does this say about the expected price path of the Sleeping Beauties as time passes, if interest rates remain around 7.55%?

• Suppose interest rates fluctuate wildly during the next two years and then stabilize again at around 7.55%. What do you predict would happen to the price of each of the bonds?

8. Use the Excel Chart function to create a picture of the present value pattern of the 10-year bond.

9. Compare the value of the Sleeping and Napping bonds for interest rates greater than 7.55%.

• Which is worth more? Why?

• Do the same for interest rates below 7.55%. Which bond is more sensitive to interest rate fluctuations? Why?

• Flip to the next sheet of the Workbook, called "Chart", to see the graphed values for the Sleeping and Napping bonds.

10. The next sheet in the Workbook is called "30-Year Bonds." It shows the cash flows to a 30-year bond in Column C, and the present value of each year's cash flow for this bond (at a 7.55% interest rate) in Column D.

Column E contains all zeros, except in year 30. The cash flow in that year is the value of $100 compounded forward for 30 years at 7.55%. This is known as a "zero-coupon bond." Column F shows the present value of each year's cash flow for this bond.

• Bonds are always quoted in terms of repayment amounts. What is the price of the 30-year zero-coupon bond per $100 repaid in 2023?

• How will the price of the 30-year zero change over time, if interest rates remain the same as when the bond was issued?

11. Use the Excel Chart function to create separate pictures of the present value patterns of each 30- year bond. Compare these pictures to the present value patterns of the Sleeping Beauty bond and the 10-year "Napping Beauty" bond, which you constructed in Questions 5 and 8 above.

• For each bond, what is the "mid-point" of the present value pattern? That is, in what year is there approximately as much present value "weight" on the left side of the pattern as on the right.

• Eyeballing the charts, estimate this "average year" for each of the four bonds (100-year, 10-year, 30-year, 30-year zero).

12. The next sheet is called "Duration." For each of the four bonds, it shows the present value of cash flows received in Columns G, I, K and M. (The cash flows themselves are hidden in Columns C- F.)

• For each bond, in the column to the right of its present values, multiply the relative year in which a cash flow is received (Column B) times the present value of the cash flow.

• Now, in the boxed cells at the top of each column, sum these values and divide the sum by 100 (the value of each bond).

• You have calculated the Duration of each bond, a kind of present-value-weighted average of the times at which the bonds make payments.

• What is the duration of each bond? Compare these calculated durations to your "eyeball estimates" from the present value patterns. How close were you?
• Are you surprised that the Sleeping Beauties have a shorter duration than the 30- Year zeros?

13. Suppose interest rates go up 1% to 8.55%.

• Calculate the values of the four bonds at this new interest rate. (The next spreadsheet, called "Sensitivity Up" should make this very easy to do!)

• Which bond is the most sensitive to an interest rate change? Which is least sensitive?

• Calculate the percent change in value for each bond: (New Value-Old Value)/Old Value. The Old Value was 100, also known as "par". Fill in those numbers in the boxes provided.

14. Now suppose interest rates fall to 6.55%.

• Use the "Sensitivity Down" spreadsheet to calculate the values and the percent change in value for the four bonds at this interest rate.

15. Turn to the last spreadsheet, called "Relation". If your calculations on the previous spreadsheets were correct, the Duration expressed in years should close to the percentage changes (ignore the minus signs).

This is a general, important, and useful relationship.

It is important because it can be used to hedge portfolios: instead of "cash flow" matching, portfolio managers can hedge out interest rate risk by "duration matching."

It is useful, because with a little practice, you can learn to "eyeball" a duration from a present value pattern, or even from a mental picture of the cash flows. Knowing the duration, lets you know the interest rate sensitivity of the asset, which is an important parameter of risk for both debt and equity securities.