If you can please provide excel equations.

Risk and Return Exercises: On January 2, 2022, you plan to invest $1,000,000 in three stocks to form a portfolio: $300,000 in Stock A, $300,000 in Stock B, and $400,000 in Stock C. To better assess your portfolio's expected return and risk, you decide to rely on the historical data of these three stocks, assuming that past performance will be a good predictor for future performance. You have collected the monthly closing prices information for all three stocks and the market index from December 2019 to December 2021. 1. Create the spreadsheet and input the information given below. Next, calculate returns for each stock and the market index. All three stocks never pay dividends. Compute both mean and standard deviation of returns for all three stocks and the market index. 2. Compute beta coefficients for all three stocks and report their respective regression summary output. Next, calculate your portfolio's annual return and beta. 3. If the risk-free rate, Rs, and the expected return on the market index, E(R), for 2022 will be 2 percent and 10 percent, respectively, what is your portfolio's required rate of return according to the CAPM? Month Dec. 2019 Jan. 2020 Feb. March April May June July Aug. Sept. Oct. Nov. Dec. Jan. 2021 Feb. March April May June July Aug Sept. Oct. Nov. Dec. Stock A 65.00 67.00 67.25 67.75 68.25 67.50 66.50 67.75 69.25 68.75 70.25 71.00 71.50 70.25 69.75 71.50 72.25 73.50 72.25 73.75 74.50 76.25 78.00 77.00 79.00 Stock B 81.00 82.00 81.50 82.25 83.00 83.75 84.25 85.75 86.75 85.00 88.25 87.50 88.00 89.75 90.25 92.00 91.25 93.75 92.75 94.25 95.50 96.25 97.50 97.00 99.25 Stock C 25.00 25.50 26.25 25.75 26.25 27.00 26.75 27.75 28.25 28.00 28.75 28.75 29.75 30.50 30.75 32.25 33.00 33.75 32.75 34.00 34.50 35.00 34.75 34.00 36.75 Market Index 3960.00 3977.52 3994.56 4002.32 4009.36 4014.36 4069.48 4101.04 4145.52 4129.16 4184.68 4193.00 4213.00 4221.52 4199.56 4219.76 4242.12 4251.12 4274.92 4274.48 4291.00 4302.08 3960.00 3977.52 3994.56 1. Compute beta coefficients for all three stocks and report the regression summary output below: Stock A: Stock B: Stock C: 2. Calculation of Portfolio Beta and Portfolio Return Weighted Average Beta Average Monthly Return Weighted Average Return Stock Weight Beta Stock Weight A B Investmen t Amount # # # formula formula # formula # formula # 100% Portfolio Beta formula formula formula formula formula formula A formula B formula # formula 100% Portfolio Monthly Return Portfolio Annual Return formula formula formula 3. If the annual risk-free rate, Rof will be 2 percent in 2022 and the expected return on the market index, E(R.m), will be 10 percent, what is your portfolio's required rate of return according to the CAPM? E(Rp) - R.+ (E(Rm) - R.)Bp = formula Risk and Return Exercises: On January 2, 2022, you plan to invest $1,000,000 in three stocks to form a portfolio: $300,000 in Stock A, $300,000 in Stock B, and $400,000 in Stock C. To better assess your portfolio's expected return and risk, you decide to rely on the historical data of these three stocks, assuming that past performance will be a good predictor for future performance. You have collected the monthly closing prices information for all three stocks and the market index from December 2019 to December 2021. 1. Create the spreadsheet and input the information given below. Next, calculate returns for each stock and the market index. All three stocks never pay dividends. Compute both mean and standard deviation of returns for all three stocks and the market index. 2. Compute beta coefficients for all three stocks and report their respective regression summary output. Next, calculate your portfolio's annual return and beta. 3. If the risk-free rate, Rs, and the expected return on the market index, E(R), for 2022 will be 2 percent and 10 percent, respectively, what is your portfolio's required rate of return according to the CAPM? Month Dec. 2019 Jan. 2020 Feb. March April May June July Aug. Sept. Oct. Nov. Dec. Jan. 2021 Feb. March April May June July Aug Sept. Oct. Nov. Dec. Stock A 65.00 67.00 67.25 67.75 68.25 67.50 66.50 67.75 69.25 68.75 70.25 71.00 71.50 70.25 69.75 71.50 72.25 73.50 72.25 73.75 74.50 76.25 78.00 77.00 79.00 Stock B 81.00 82.00 81.50 82.25 83.00 83.75 84.25 85.75 86.75 85.00 88.25 87.50 88.00 89.75 90.25 92.00 91.25 93.75 92.75 94.25 95.50 96.25 97.50 97.00 99.25 Stock C 25.00 25.50 26.25 25.75 26.25 27.00 26.75 27.75 28.25 28.00 28.75 28.75 29.75 30.50 30.75 32.25 33.00 33.75 32.75 34.00 34.50 35.00 34.75 34.00 36.75 Market Index 3960.00 3977.52 3994.56 4002.32 4009.36 4014.36 4069.48 4101.04 4145.52 4129.16 4184.68 4193.00 4213.00 4221.52 4199.56 4219.76 4242.12 4251.12 4274.92 4274.48 4291.00 4302.08 3960.00 3977.52 3994.56 1. Compute beta coefficients for all three stocks and report the regression summary output below: Stock A: Stock B: Stock C: 2. Calculation of Portfolio Beta and Portfolio Return Weighted Average Beta Average Monthly Return Weighted Average Return Stock Weight Beta Stock Weight A B Investmen t Amount # # # formula formula # formula # formula # 100% Portfolio Beta formula formula formula formula formula formula A formula B formula # formula 100% Portfolio Monthly Return Portfolio Annual Return formula formula formula 3. If the annual risk-free rate, Rof will be 2 percent in 2022 and the expected return on the market index, E(R.m), will be 10 percent, what is your portfolio's required rate of return according to the CAPM? E(Rp) - R.+ (E(Rm) - R.)Bp = formula