Please use excel to solve the attached case. Use a separate worksheet for each answer please

Case week 4 This is a case about interest rate forecasting. On the first page it mentions Wally, who when facing falling sales, sees a memo from his boss. She is concerned (see next page) about the declining operating margin from 14 percent to 10 percent. She has asked Wally for yield curve data showing the stability of interest rates over the past few years, the stability of current prices, and inflation rates. She also wishes to evaluate whether Babes should issue debt. Towards the bottom of the page she states, I think we can currently issue 3-year bonds priced to yield 6.4 percent, 5-year bonds priced to yield 7.3 percent, and 10-year bonds priced to yield 8.4 percent. See the next page, 792. We are just reading through the case at this point. This is the information you need to forecast interest rates. See Table 1. Treasury securities are issued by the government, they are called Treasury securities. There are 3 periods of information for August 1991, August 1992, November 1992. They have varying maturities, meaning they come due in 3 months, 6 months, etc. You notice that yields (returns) rise with maturity, which is what you would expect. If you were tying your money in an investment for a long period of time, your yield would be higher than it would be for a short period of time. Table 2 includes information about corporate bonds, see the section on Default risk premia. Corporate bonds may default, if debt is issued by a firm, the firm may not pay the debt, the risk of nonpayment is a percentage that is added to the return. Riskier bonds like C-rated bonds have higher risk of default, or higher default risk than AAA bonds. Using the data provided in Table 1, construct the yield curves for August 1991, August 1992, and November 1992. Yes, T-bills are risk-free. The yield curve is a graph of interest rates and time to maturity. Draw a graph in Excel. Create a table of values first. In the first column, list the maturities from Table 1. Second column: list the yields for August 1992 Third column: list the yields for November 1992. Insert Line Chart. Highlight maturities 1 - 10 and the second and third columns to get the graph. 2. is descriptive. Evaluate the change in shape of the yield curve using expectations and market segmentation. Look them up in the book and write a paragraph. We use 1-10 just because of ease of comparability, which does not exist for 30 years. We'll do 110, as 3 months and 6 months are of a different duration than the annual maturities for years 1 10. 3. Calculate the one-year forward rates of interest implied by the November 1992 yield curve over the period 1993-2002. We need a starting point. Use 1993 as 3.8% 4.65 = 2 year rate = (3.8+x)/2. According to the expectations theory, the 2 year rate = sum of the previous year's rate and the year before that, or the 2 previous 1 year rates. x = 5.5 % That is your answer for 1994. Next, for 1995, you have a 3-year rate = 5.23 = (3.8+5.5+x)/3 x = 6.39 % Complete the rest by yourselves. 4. Using these 1 year forward rates, calculate the expected annual inflation rate in each of the next 10 years, and use this rate to obtain the average rate of price appreciation over the 1993 to 2002 period. Assume expectations, knominal = kreal + expected inflation premium expected inflation premium = knominal - kreal = 1993-1994 = 5.5-3.8 = 1.7% 5.5 was the first answer we computed, that is the nominal quoted interest rate. The real rate, according to expectations, is the nominal rate for the year before, or 3.8% For 1994-1995, 6.39 -5.5 = 0.89% is the expected inflation premium. knominal = 6.39 - kreal of 5.5 Complete the rest. 5. Examine the information provided in Table 2. Do these data lead you to believe that the annual inflation rate you calculated in Q 4 might be incorrect ? Why or why not ? See Table 2, the second and third columns list maturity and liquidity risk premia which were omitted from the strict expectations theory computation. 6. Using the data provided in Tables 1 and 2 prepare a revised estimate of (a) the one-year forward interest rates implied by the November 1992 yield curve over the 1993-2002 period, and the expected inflation rate in each of these years. Originally, we were using the expectations theory, now we are using liquidity preference. 1993, one year forward rate = 3.8% 1994, 4.65 + 0.2 + 0, take the figure for 2 years from the last column of Table 1, add the maturity risk premium and the liquidity risk premium 3 years, 1995, 5.23 + .3 + .1 = 5.63%. This is 6a. 6b. Find the inflation rates as the difference between the nominal rates (answers) to 6a. We are using the liquidity preference theory, so inflation = sum of maturity and liquidity risk premia 1 year = 0% 2 years = .2 + 0 = .2 3 years, .3 + .1 = .4 7. How would the yield curve for an AAA rated firm, a B-rated firm, and a C-rated firm, differ from the Treasury security yield curve you constructed in # 1 ? You have to draw yield curves - 3 lines for the 3 types of bonds using the same procedure as problem 1. Year 1, nominal rate = T-bill rate + Maturity risk premium + liquidity risk premium + default risk premium for an AAA bond 3.8 + 0 +0 +.9 = 4.7 3 years, 5.23 + .3 + .1 + .9 We are taking the lowest risk free rate on a Treasury security and adjusting it to the rate on an AAA bond. Do the rest of the AAA bonds, then all of the B rated bonds-just use a different column for default risk, and then the C-rated bonds. Also, compare the shape of the yield curve in question 7, and compare the 2 methods used. 9. Can you use the information in the case to estimate Babes-N-Toyland's bond rating ? See the yields on the second page. 3 year bonds priced to yield 6.4% per annum. 5-year bonds priced to yield 7.3 percent per annum, 10 year bonds priced to yield 8.4 percent. Look at the yields on the AAA. B, and C bonds and see if any bond has a maturity and yield similar to these figures. That will be Babes's bond rating. 10. Think through this one. It uses a result from 5080. AAA are the least risky and C the most risky