On January 2, 2022, you plan to invest $1,000,000 in three stocks to form a portfolio:...

Transcribed Image Text:

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, R. and the expected return on the market index, E(Rm), for 2022 will be 4 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 65.00 66.00 66.25 67.75 68.25 69.00 70.00 72.25 73.50 72.75 73.45 74.50 75.00 74.05 73.75 74.05 75.25 76.50 75.75 76.35 77.50 76.25 77.00 78.00 76.00 Stock 81.00 82.00 82.50 83.25 83.00 83.75 84.25 85.75 86.75 85.00 88.25 87.50 88.00 87.00 88.25 92.00 91.25 94.00 92.75 92.00 95.50 96.25 95.50 96.00 95.00 Stock C 26.00 26.50 27.25 28.00 28.55 29.00 29.25 30.00 29.25 28.00 29.75 30.25 31.00 29.50 29.25 30.25 31.00 31.75 32.75 33.00 34.50 35.00 34.00 33.00 34.00 Market Index 3960.00 3973.52 3990.56 4002:32 4006.32 4014.36 4069.48 4101.04 4145.52 4129.16 4184.68 4193.00 4213.00 4201.52 4199.56 4211.76 4242.12 4263.12 4274.92 4282.48 4291.00 4302.08 4296.00 4293.00 4300.04 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, R. and the expected return on the market index, E(Rm), for 2022 will be 4 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 65.00 66.00 66.25 67.75 68.25 69.00 70.00 72.25 73.50 72.75 73.45 74.50 75.00 74.05 73.75 74.05 75.25 76.50 75.75 76.35 77.50 76.25 77.00 78.00 76.00 Stock 81.00 82.00 82.50 83.25 83.00 83.75 84.25 85.75 86.75 85.00 88.25 87.50 88.00 87.00 88.25 92.00 91.25 94.00 92.75 92.00 95.50 96.25 95.50 96.00 95.00 Stock C 26.00 26.50 27.25 28.00 28.55 29.00 29.25 30.00 29.25 28.00 29.75 30.25 31.00 29.50 29.25 30.25 31.00 31.75 32.75 33.00 34.50 35.00 34.00 33.00 34.00 Market Index 3960.00 3973.52 3990.56 4002:32 4006.32 4014.36 4069.48 4101.04 4145.52 4129.16 4184.68 4193.00 4213.00 4201.52 4199.56 4211.76 4242.12 4263.12 4274.92 4282.48 4291.00 4302.08 4296.00 4293.00 4300.04

Answer rating: 100% (QA)

To calculate the returns mean and standard deviation of returns for the stocks and market index as well as the beta coefficients and portfolio return we need to organize the data in a spreadsheet and ... View the full answer