Question

Below 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?

COMPUTE ALL CALCULATIONS IN AN EXCEL SPREADSHEET AND POST IT HERE, THANK YOU

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

Answer 1

(A) Given the portfolio contains equal weights for all the companies

Hence, weight assigned is = 1/12 = 0.83333

Following is attached screenshot of excel calculation

As you can see, Return of portfolio is calculated as sumproduct of weights assigned and actual returns of securities as

where, w_i is the weights assigned and r_i is individual stock return.

(B)

=(1.88%+20.91%-13.87%-8.21%-3.37%+6.13%+4.07%+11.11%+21.74%-11.03%-7.79%+0.02%+29.06%+16.63%+36.36%)/15

= 6.91%.......................................Answer

Alternatively, you can use excel's AVERAGE() function to find the same.

Similarly, for calculating standard deviation of the sample use STDEV.P() function in excel as we have to calculate standard deviation of the whole population mentioned. Following is the screenshot for the same

(C) Correlation and Covariance calculation

For Correlation Calculation use CORREL() function in Excel

For Covariance use COVARIANCE.P() function because We need population covariance

(D) Beta of security #12 Computation

r_i = return of stock #12

r_m = Portfolio/market return (Here Market is assumed to be consisted of the portfolio of 12 stocks mentioned because that comprise of whole population)

Covariance is calulated above = 1.75%

Variance of Market can be Calculated by VAR.P() function on market returns then Beta is Covariance/Variance of market

Therefore, beta₁₂ =0.805

(E) Now, let's collate the information we have calculated

beta₁₂ =0.805

Average Market Return = Average Return of Equally Weighted Portfolio = 6.91% (Calculated in part (B))

Risk free rate = 2.5% (given)

Using CAPM,

r_t = required rate of return

r_f = risk free rate

r_m = market return

Therefore, r_t = 2.5% + 0.805*(6.91% - 2.5%)

= 2.5% + 0.805 * 4.41%

= 2.5% + 3.55%

= 6.05%.............................................Answer

(F)

I would NOT buy company #12 stock if the return rate is 5% because required rate of return for stock # 12 is 6.05% and any retyrn less than that means I would loose money if invested in that stock