CHAPTER 15 Presentation:

For part 2, http://finra-markets.morningstar.com/MarketData/Default.jsp is to be used to determine the bond data...

I want to know how to do this in excel if possible.

Chapter 24 Data Case You are still employed at The Home Depot. Recall the presentation of Chapter 15's plan to increase leverage to the Board of Directors. The idea of changing Home Depot's capital structure stirred some conversations among top executives. The CFO and other top managers in the finance division are all aware that increasing the debt load wil have ramifications in the credit markets. Specifically, they realize that the firm's debt rating could change, which will raise the cost of borrowing as well as possibly lower the value of the existing debt. No one is exactly sure what the impact will be, but they all agree that it deserves investigation Because you prepared the spreadsheet data, you have been summoned to an executive-level meeting and asked to estimate the impact of increasing the debt of the firm. Use the spreadsheet from Chapter 15's Data Case as a starting point. You are to consider four different scenarios: issuing $1 billion, $10 billion, $20 billion, and $30 billion in new debt. In each case, proceeds from the debt will be used to repurchase stock. The CFO believes that the $1 billion level will not affect the firm's credit rating. However, each larger increase in debt will cause the debt to be downgraded one letter grade (e.g., from Baa to Ba). For example, the $10 billion scenario will lower the current debt rating one level, the $20 billion scenario would lower the rating still another level, and so on. Your job is to determine the impact of additional debt on borrowing costs at each debt level. Assume the new debt will be raised by issuing 10-year bonds. 1. Determine the current debt rating for The Home Depot. a. Research the current bond rating at FINRA ( er/). Select the "Corporate" toggle, enter the symbol for Home Depot (HD), and click "Show Results."" b. What is the Moody's bond rating on the Home Depot bond with the maturity closest to 10 years from today? What is the yield on this bond? 2. Because lower bond ratings will lead to higher interest costs, you will need to determine those costs. Go to erL and click "Today's Market." Next, click "US Corporate Bond Spreads." You will see a table ot bond spreads prepared by Reuters and the date the table was prepared right below it. These spreads represent the increased yield a bond must pay over the U.S. Treasury of the same maturity. Choose the 10-year spread for Home Depot's current rating and the three ratings below it. The spreads are in basis points; a basis point is 1/100th of a per estimate the current spread tage point (thus, 50 basis points is 0.5%). we will adjust these old spreads to a. Because these spreads are dated, you will need to create new yield spreads for the various ratings. Use the current difference between the Home Depot bond's yield and the 10-year Treasury as the true spread for the rating. Using Excel, compute the spreads for the other ratings, by adding the difference in spreads from the table to the new true spread for Home Depot's rating. Finally, determine the yield for each rating by adding the new spread to the yield on the 10-year T ury bond b. Compute the required yields on 10-year bonds at each of the new debt l requested 3. What factors cause the bond rating to fall, and the bond yields to increase, as Home Depot in ases its debt levels? Chapter 24 Data Case You are still employed at The Home Depot. Recall the presentation of Chapter 15's plan to increase leverage to the Board of Directors. The idea of changing Home Depot's capital structure stirred some conversations among top executives. The CFO and other top managers in the finance division are all aware that increasing the debt load wil have ramifications in the credit markets. Specifically, they realize that the firm's debt rating could change, which will raise the cost of borrowing as well as possibly lower the value of the existing debt. No one is exactly sure what the impact will be, but they all agree that it deserves investigation Because you prepared the spreadsheet data, you have been summoned to an executive-level meeting and asked to estimate the impact of increasing the debt of the firm. Use the spreadsheet from Chapter 15's Data Case as a starting point. You are to consider four different scenarios: issuing $1 billion, $10 billion, $20 billion, and $30 billion in new debt. In each case, proceeds from the debt will be used to repurchase stock. The CFO believes that the $1 billion level will not affect the firm's credit rating. However, each larger increase in debt will cause the debt to be downgraded one letter grade (e.g., from Baa to Ba). For example, the $10 billion scenario will lower the current debt rating one level, the $20 billion scenario would lower the rating still another level, and so on. Your job is to determine the impact of additional debt on borrowing costs at each debt level. Assume the new debt will be raised by issuing 10-year bonds. 1. Determine the current debt rating for The Home Depot. a. Research the current bond rating at FINRA ( er/). Select the "Corporate" toggle, enter the symbol for Home Depot (HD), and click "Show Results."" b. What is the Moody's bond rating on the Home Depot bond with the maturity closest to 10 years from today? What is the yield on this bond? 2. Because lower bond ratings will lead to higher interest costs, you will need to determine those costs. Go to erL and click "Today's Market." Next, click "US Corporate Bond Spreads." You will see a table ot bond spreads prepared by Reuters and the date the table was prepared right below it. These spreads represent the increased yield a bond must pay over the U.S. Treasury of the same maturity. Choose the 10-year spread for Home Depot's current rating and the three ratings below it. The spreads are in basis points; a basis point is 1/100th of a per estimate the current spread tage point (thus, 50 basis points is 0.5%). we will adjust these old spreads to a. Because these spreads are dated, you will need to create new yield spreads for the various ratings. Use the current difference between the Home Depot bond's yield and the 10-year Treasury as the true spread for the rating. Using Excel, compute the spreads for the other ratings, by adding the difference in spreads from the table to the new true spread for Home Depot's rating. Finally, determine the yield for each rating by adding the new spread to the yield on the 10-year T ury bond b. Compute the required yields on 10-year bonds at each of the new debt l requested 3. What factors cause the bond rating to fall, and the bond yields to increase, as Home Depot in ases its debt levels