I am needing help with putting my answers in Excel and using Excel formulas to come up with the answers. Please find attached the work I need input in excel formulas

P6-1 In this advanced problem, let's look at the behavior of ordinary Treasury bonds and inflationindexed bonds or TIPS as described in the opening focus. We will simplify a little by assuming annual interest payments rather than semiannual. Suppose over the next five years, investors expect 3 percent inflation each year. The Treasury issues a five-year ordinary bond that pays $55 interest each year. The Treasury issues a five-year TIPS that pays a coupon rate of 2 percent. With TIPS, the coupon payment is determined by multiplying the coupon rate times the inflationadjusted principal value. Like ordinary bonds, TIPS begin with a par value or principal value of $1,000. However, that principal increases over time as inflation occurs. Assuming that inflation is in fact equal to three percent in each of the next five years, then the cash flows associated with each bond would look like this: Year 0 (cost) Inflation-Indexed Bond (TIPS) Inflation-Adjusted T-Bond Cash Paid Principal - -1,000.00 -1,000.00 1 , 0 0 0 . 0 0 55.00 20.60 1,030.00 55.00 21.22 1,060.90 55.00 21.85 1,092.73 55.00 22.51 1,125.51 1,055.00 1,182.46 1,159.27 Coupon Payment Calculation NA 1,000.00(1.03) 2% 1,030.00(1.03) 2% 1,060.90(1.03) 2% 1,092.73(1.03) 2% 1,125.51(1.03) 2% Notice in the last row of the table the final TIPS payment includes the return of the inflationadjusted principal ($1,159.27) plus the final coupon payment. a. Calculate the yield to maturity of each bond. Why is one higher than the other? Show that the TIPS YTM equals the product of the real interest rate and the inflation rate. b. What is the real return on the T-bond? c. Suppose the real return on the T-bond stays constant, but investors expect four percent inflation rather than three percent. What happens to the required return on the T-bond in nominal terms? d. Imagine that during the first year, the inflation that actually occurred was three percent as expected. However, suppose that by the end of the first year, investors had come to expect four percent inflation for the next four years. Fill out the remaining cash flows for each bond in the table below. Year 0 (cost) Inflation-Indexed Bond (TIPS) Inflation-Adjusted T-Bond Cash Paid Principal - -1,000.00 -1,000.00 1 , 0 0 0 . 0 0 55.00 20.60 1,030.00 Coupon Payment Calculation NA 1,000.00(1.03) 2% e. Now calculate the market price of the Treasury bond as of the end of the first year. Remember to discount the bond's remaining cash flows using the nominal required return that you calculated in part c. Given this new market price, what is the total return offered by the T-bond the first year? f. Next, calculate the market price of the TIPS bond. Remember, at the end of the first year, the YTM on the TIPS will equal the product of one plus the real return (2%) and one plus the inflation rate (4%). What is the total return offered by TIPS the first year? A6-1. a. The YTM of the T-bond is 5.5% and the YTM of the TIPS is 5.06%. (Note that the YTM for the TIPS is the IRR of the cash paid column.) Another way of looking at TIPS yield is: (1.02) (1.03) - 1 = 0.0506. The T-bond offers a higher yield because it does not enjoy protection from inflation risk as the TIPS bond does. An investor who buys a T-bond must receive compensation for bearing this risk, while a TIPS investor does not require compensation for inflation risk. b. The real return on the T-bond is found by solving this equation: (1+0.055) = (1 + 0.03)(1 + x). Solving we find that x = 2.43%. This is approximately equal to the nominal rate, 5.5%, minus the inflation rate, 3%. Notice that the real rate offered by the T-bond is higher than the 2% real rate offered by TIPS. The reason is given in part a. c. The required return on the T-bond if inflation expectations go up is 6.53% which is found by solving for x in this equation: (1 + x) = (1 + 0.04)(1 + 0.0243). d. The missing values are filled in below: Year 0 (cost) Inflation-Indexed Bond (TIPS) Inflation-Adjusted T-Bond Cash Paid Principal - -1,000.00 -1,000.00 1 , 0 Coupon Payment Calculation NA 0 0 . 0 0 55.00 55.00 55.00 55.00 1,055.00 20.60 21.42 22.28 23.17 1,229.05 1,030.00 1,071.20 1,114.05 1,158.61 1,204.95 1,000.00(1.03) 2% 1,030.00(1.04) 2% 1,071.20(1.04) 2% 1,114.05(1.04) 2% 1,158.61(1.04) 2% e. The market price of the Treasury equals $964.74. This is found by discounting four more years of $55 coupons plus the principal at a nominal rate of 6.53%. (Calculator inputs: N = 4, PMT = 55, I = 6.53%, FV = 1,000 and solve for PV = -$964.74). The total return of this bond the first year is $19.74 or 1.974%. Return is (55 + 1,000-964.74)/1,000 = 1.974% f. To calculate the market price of TIPS, you first have to calculate the nominal interest rate used to discount cash flows. Solve for x: (1 + x) = (1.02)(1.04) so x = 0.0608 or 6.08%. Now discount the cash flows over the last four years as determined in part (d) at this rate and you get the price of TIPS, $1,030. In other words, the price of the TIPS bond is currently equal to its inflation-adjusted par value. The total return on TIPS the first year is ($1,030 + $20.60 - $1,000) $50.60 or 5.06%, exactly the YTM calculated in part (a). In this problem, interest rates changed because inflation rose. The increase in inflation did not affect the first-year return on TIPS, but it did affect the first-year return on T-bonds. P6-2 You purchase 1,000 shares of Spears Grinders, Inc. stock for $45 per share. A year later, the stock pays a dividend of $1.25 per share and it sells for $49. A) - Calculate your total dollar return 1,000 x ($1.25 + $4) = $5,250 B) - Calculate your total percentage return ($49 + $1.25 - $45) / $45 = 0.1167 or 11.67% C) - Do the answers to parts (A) and (B) depend on whether you sell the stock after one year of continue to hold it? Answer doesn't depend on if you should sell the stock or hold it P7-1. Calculate the expected return, variance, and standard deviation for the stocks in the table below. Product Demand Probability Stock #1 Stock #2 Stock #3 High Medium Low A7-1. 20% 60% 20% 30% 12% -10% 20% 14% -5% 15% 10% -2% Expected returns are: Stock 1 (11.2%); Stock 2 (11.4%); Stock 3 (8.6%) Variances are: Stock 1 (160.96); Stock 2 (69.9); Stock 3 (31.1) Standard deviations are: Stock 1 (12.7%); Stock 2 (8.4%); Stock 3 (5.6%) P7-2. Calculate the expected return, variance, and standard deviation for each stock listed below. State of the Economy Recession Normal growth Boom A7-2. Probability 15% 65% 20% Stock A Stock B Stock C -20% -10% -5% 18% 13% 10% 20% 40% 28% Stock A: Expected return = 0.15 -0.2 + 0.65 0.18 + 0.2 0.4 = 0.167 Variance = 0.15 (-0.2 - 0.167)2 + 0.65 (0.18 - 0.167)2 + 0.2 (0.4 - 0.167)2 = .02020 + 0.00011 + 0.010858 = .0311 Standard deviation = .1765 or 17.65% Stock B: Expected return = 0.15 -0.1 + 0.65 0.13 + 0.2 0.28 = 0.1255 Variance = 0.15 (-0.1 - 0.1255)2 + 0.65 (0.13 - 0.1255)2 + 0.2 (.28 - 0.1255)2 = 0.00763 + 0.000013 + 0.004774 = 0.0124 Standard deviation = 0.11 Stock C: Expected return = 0.15 -0.05 + 0.65 0.1 + 0.2 0.2 = 0.0975 Variance = 0.15 (-0.05 - 0.0975)2 + 0.65 (0.1 - 0.0975)2 + 0.2 (0.2 - 0.0975)2 = 0.00326+ 0.000004 + 0.002101 = 0.005365 Standard deviation = 0.073