Get instant access to this case solution for only $19
Walt Disney Companys Sleeping Beauty Bonds Duration Analysis Case Solution
A zero coupon bond is called zero coupon bond because it pays zero coupon during the maturity period of the bond. The only inflow for this type of bond is the principal repayment at maturity. Therefore, price of a $100 face with 30 years in maturity will simply be the present value of $100 over a period of 30 years. Using interest rate of 7.55%, this present value comes out to be $11.26. The formula for price calculation of a zero coupon bond is Principal / (1+interest rate) ^n. Here, “n” represents the number of years left until the maturity of the bond. So, as the number of years left decrease with the passage of time, denominator decreases and hence, the price of the zero coupon bond increases. If the interest rate and the principal payment and held constant, price of zero coupon bond will be inversely proportional to number of years left “n”. Therefore, the price of 30year zero coupon bond will keep increasing until, after 30 years, n=0 and price of the bond will be equal to principal i.e. $100.
Following questions are answered in this case study solution

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

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%?

Suppose one 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?

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.

Use the Excel Chart function to create pictures of the “raw” cash flows from the Sleeping Beauty bond and present values of the individual cash flows from the Sleeping Beauty bond. Compare the two pictures. Do they look as you expect? Could you have drawn them freehand ahead of time?

Use the NPV function in Excel to calculate the value of the Sleeping Beauties for each of the interest rates, ranging from 2% to 1000%.

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 value of the Sleeping and Napping bonds for interest rates greater than 7.55%.Which is more? Why? Do the same for interest rates below 7.55%. Which bond is more sensitive to interest rate fluctuations? Why?

At 7.55%, what is the price of the 30year zerocoupon bond per $100 face value? How will the price of the 30year zerocoupon bond change over time if interest rates remain at 7.55%?

Eyeballing the charts, estimates the “average year” for each of the four bonds (100year, 10year, 30year, 30year zero).

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 30year zeros?

Suppose interest rates go up 1% to 8.55%. Calculate the values of the four bonds at this new interest rate. Which bond is the most sensitive to an interest rate change? Which is least sensitive? Calculate the percent change in value for each bond

Now suppose interest rates fall to 6.55% calculate the values and the percent change in value for the four bonds at this interest rate.

If your calculations on the previous spreadsheets were correct, the Duration expressed in years should be close to the percentage changes
Case Analysis for Walt Disney Companys Sleeping Beauty Bonds Duration Analysis
1. What are the cash payments associated with the Sleeping Beauties? Who gets how much and when, per $100 of bonds issued?
Sleeping beauty bonds were issued for 100 years for a total amount of $300 million. The bonds were issued for a $100 par value. Therefore, total number of bonds that were issued by Disney Company was three million. Bonds were issued at an interest rate of 7.55%, payable semiannually by the company to bond holders. Therefore, 3.775% (7.55%/2) of the par value of bond were paid after every six months to the bond holders by the company. Coupon payments in dollar terms paid by the company to bond holders were $3.775 per bond. The bond will mature after 100 years in 2093, and bondholders will get their principal payment of $100 per bond along with last coupon payment. However, if interest rates are less than 7.55% after 2023, company is very likely to exercise the call option and repay bond holders by paying 103.02% of the face value of bonds. In that case, bond holders will get $103.02 per bond, if the company chooses to exercise the call option.
2. 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%?
The interest rate that was used to calculate the price of the bond was 10% per annum. Although, Disney pays the coupon payments semiannually, coupon payments have been assumed to be paid annually at a rate of 7.55% per annum for simplicity of calculations. The interest rate that has been used to calculate the present value per bond is higher than the coupon rate. That is why present value calculated in “Basic Spreadsheet” tab is less than par value of the bond because the discount rate is higher than the coupon rate.
3. Suppose one 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?
Initially Sleeping Beauty bonds were issued by Disney Company at a par value of $100 per bond and hence, price at that time was equal to par value. However, later on, market is determined by prevailing interest rate. If the interest rate becomes greater than the coupon rate, price of the bond becomes lesser than par value because the discount rate for future cash flows increases. So, if the interest rate increases from 7.55% to 8.55%, price of the bond will fall below $100. Calculations show that price of the bond to $88.31 from $100 if the interest rate increases. Similarly, if the interest rate decreases and becomes less than 7.55%, price of the bond will become greater than $100 because now, future cash flows will be discounted at a rate less than 7.55%. So, if the interest rate falls to 6.55%, price of the bond will increase to $115.24 from $100 because coupon payments will be discounted at a lower rate.
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.
Formula for present value calculation of payments in the future is following. “n” in this formula represents the number of periods after which payment will be received.
PV= Payment / (1+ interest rate) ^n
5. Use the Excel Chart function to create pictures of the “raw” cash flows from the Sleeping Beauty bond and present values of the individual cash flows from the Sleeping Beauty bond. Compare the two pictures. Do they look as you expect? Could you have drawn them freehand ahead of time?
When cash flows in dollar terms are plotted against the number of periods on xaxis, it can be seen that the cash flow graph for raw cash flows is a straight line. However, for last year, 2093, raw cash flow graph abruptly becomes vertical because last cash payment includes both coupon payment and principle payment. On the other hand, cash flow graph of these cash payments in future decreases periodically and approaches zero value. This is because as the time period increases; cash flows are discounted over more periods, and hence, their value in present terms decreases. $107.55 in last years has a present value of just $0.007804 because it has been discounted over 100 years. Therefore, for present value cash flows, a downward sloping graph is obtained. If it is assumed that interest rate will remain the same in next 100 years, it is possible to project the cash flows in the future and then discount them at the present value. However, if the interest rate fluctuates, as it happens in real life, present value of these cash payments and the price of the bond also fluctuate.
6. Use the NPV function in Excel to calculate the value of the Sleeping Beauties for each of the interest rates, ranging from 2% to 1000%.
Formula of the present value of cash flows shows that present values of cash flows are inversely proportional to interest rates. As interest rates increase, present values of cash payments decreases because they are now discounted over higher interest rates. So, when cash flows of Sleeping Beauty bond are discounted over 100 years, its value changes depending upon the level of interest rate used. For 2% interest rate, present value of cash flows is $339.20, whereas, present value of cash flows using 100% as interest rate is just $0.76. This explains why price of bonds change rapidly in response to changes in market interest rates.
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?
Although Sleeping Beauty bonds have a maturity period of 100 years, whereas, Napping Beauty bonds have a maturity of 10 years; present value of their coupon and principle payments is same for interest rate of 7.55%. This is because coupon payments are calculated at the same rate at which they are discounted. When interest rate is equal to the coupon rate of a bond, then present value of these interest payments will be equal to par value of the bond regardless of the maturity of the bond. Since, par value of both bonds is $100 each and both the bonds were issued at par, therefore, present values of their cash flows are $100 each at 7.55% interest rate.
Get instant access to this case solution for only $19
Get Instant Access to This Case Solution for Only $19
Standard Price
$25
Save $6 on your purchase
$6
Amount to Pay
$19
Different Requirements? Order a Custom Solution
Calculate the Price
Get More Out of This
Our essay writing services are the best in the world. If you are in search of a professional essay writer, place your order on our website.