Attached is a spreadsheet that has the annual return measured for 12 different stock investments The spreadsheet shows the average return and standard deviation of the return for the past 15 years Use this spreadsheet and spreadsheet commands to do the following Compute the return for each year on a portfolio that contains an equal investment in all 12 securities Compute the 15 year average return and standard deviation of return for the portfolio that consists of all 12 securities with equally weighted investment Compute the correlation and covariance between the return on company 12 and the return on the equally weighted portfolio Hint There is a spreadsheet command that does this calculation Compute the beta of Company 12 using the information you have collected Now using the beta you created for Company 12, compute the required rate of return using the Capital Asset Pricing Model (CAPM), assuming that the average market return is the return of your equally weighted portfolio and the risk free rate of return is 2 5 If you were told analysts estimate that Company 12 will have a 5 rate of return next year, would you buy the stock Why or why not Comp 1 Comp 2 Comp 3 Comp 4 Comp 5 Comp 6 Comp 7 Comp 8 Return Return Return Return Return Return Return Return 2012 3 60 10 04 1 38 5 25 3 50 0 14 5 33 2 55 2011 54 44 23 22 0 55 15 35 0 22 22 32 23 55 23 00 2010 29 30 18 92 44 54 22 24 17 66 11 87 1 93 5 68 2009 37 57 11 88 6 00 13 93 16 09 6 23 15 42 55 35 2008 11 00 11 64 9 39 4 00 2 80 12 18 3 33 3 33 2007 7 11 13 59 0 52 26 35 6 06 23 92 22 90 4 23 2006 20 91 18 92 44 54 2 24 17 66 11 87 1 93 5 68 2005 16 02 11 88 6 00 13 93 16 09 6 23 15 42 55 35 2004 55 35 23 14 43 33 23 33 0 33 1 08 1 44 38 53 2003 11 56 23 00 38 30 3 53 5 07 6 58 5 12 13 43 2002 11 52 39 67 28 46 20 72 6 22 8 25 22 70 2 60 2001 0 23 1 48 51 99 7 35 16 54 1 83 32 25 47 38 2000 3 10 13 56 7 33 11 03 17 69 44 92 0 93 3 72 1999 3 43 7 16 47 74 2 39 4 27 31 57 19 44 3 90 1998 31 48 45 52 53 49 29 15 58 33 67 99 25 12 0 44 N A Comp 9 Comp 10 Comp 11 Comp 12 Return Return Return Return 14 18 14 76 3 35 0 10 36 36 42 15 9 90 0 10 39 86 6 04 5 36 9 57 5 78 9 63 13 75 33 93 4 18 4 76 7 85 5 33 46 36 59 17 6 02 37 79 39 86 6 04 5 36 9 57 5 78 9 63 13 75 33 93 35 44 9 40 15 05 49 56 12 18 24 68 7 69 37 39 32 87 13 16 34 55 20 56 11 10 2 96 51 00 14 48 9 20 4 87 298 67 6 04 12 12 53 37 19 46 62 66 26 83 50 67 40 62 6 72

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 09, 2024

Attached is a spreadsheet that has the annual return measured for 12 different stock investments. The spreadsheet shows the average return and standard deviation of

Attached is a spreadsheet that has the annual return measured for 12 different stock investments. The spreadsheet shows the average return and standard deviation of the return for the past 15 years. Use this spreadsheet and spreadsheet commands to do the following:

Compute the return for each year on a portfolio that contains an equal investment in all 12 securities.
Compute the 15-year average return and standard deviation of return for the portfolio that consists of all 12 securities with equally weighted investment.
Compute the correlation and covariance between the return on company #12 and the return on the equally-weighted portfolio. Hint: There is a spreadsheet command that does this calculation.
Compute the beta of Company #12 using the information you have collected.
Now using the beta you created for Company #12, compute the required rate of return using the Capital Asset Pricing Model (CAPM), assuming that the average market return is the return of your equally-weighted portfolio and the risk-free rate of return is 2.5%.
If you were told analysts estimate that Company #12 will have a 5% rate of return next year, would you buy the stock? Why or why not?

Comp. #1 Comp. #2 Comp. #3 Comp. #4 Comp. #5 Comp. #6 Comp. #7 Comp. #8 Return Return Return Return Return Return Return Return 2012 3.60% -10.04% -1.38% 5.25% -3.50% 0.14% 5.33% -2.55% 2011 54.44% 23.22% 0.55% 15.35% 0.22% 22.32% 23.55% 23.00% 2010 -29.30% -18.92% -44.54% -22.24% -17.66% 11.87% -1.93% -5.68% 2009 -37.57% -11.88% -6.00% -13.93% -16.09% 6.23% -15.42% -55.35% 2008 -11.00% -11.64% -9.39% -4.00% -2.80% 12.18% 3.33% -3.33% 2007 7.11% 13.59% 0.52% 26.35% -6.06% 23.92% 22.90% 4.23% 2006 20.91% 18.92% -44.54% 2.24% -17.66% 11.87% 1.93% -5.68% 2005 16.02% 11.88% -6.00% -13.93% 16.09% 6.23% 15.42% 55.35% 2004 55.35% 23.14% 43.33% 23.33% 0.33% -1.08% -1.44% 38.53% 2003 -11.56% 23.00% -38.30% -3.53% 5.07% -6.58% -5.12% -13.43% 2002 11.52% 39.67% -28.46% -20.72% -6.22% -8.25% 22.70% -2.60% 2001 -0.23% -1.48% -51.99% 7.35% 16.54% 1.83% 32.25% 47.38% 2000 3.10% 13.56% -7.33% -11.03% 17.69% 44.92% 0.93% -3.72% 1999 -3.43% -7.16% 47.74% 2.39% 4.27% 31.57% 19.44% -3.90% 1998 31.48% 45.52% 53.49% 29.15% 58.33% 67.99% 25.12% 0.44% N/A Comp. #9 Comp. #10 Comp. #11 Comp. #12 Return Return Return Return 14.18% 14.76% -3.35% 0.10% 36.36% 42.15% 9.90% -0.10% -39.86% 6.04% 5.36% -9.57% -5.78% 9.63% 13.75% 33.93% 4.18% -4.76% -7.85% -5.33% -46.36% 59.17% 6.02% -37.79% 39.86% 6.04% 5.36% 9.57% -5.78% -9.63% 13.75% 33.93% 35.44% 9.40% -15.05% 49.56% -12.18% -24.68% -7.69% -37.39% -32.87% -13.16% -34.55% -20.56% 11.10% 2.96% -51.00% -14.48% -9.20% -4.87% 298.67% 6.04% 12.12% 53.37% -19.46% 62.66% 26.83% 50.67% 40.62% 6.72%