See spread sheet below that has the annual return measured for 12 different stoc
ID: 2640696 • Letter: S
Question
See spread sheet below 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:
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%
N/A
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%
N/A
Explanation / Answer
Portfolio Return = Sum of all the stocks with (Weight of each stock in the portfolio * Return of the each stock)
Part (A)
Portfolio is to be constructed with equal weights of each stock. Therefore return of the portfolio = ( Sum of the annual return of the stocks ) * 1/12.
Year Portfolio Return
2012 1.88%
2011 20.91%
2010 -13.87%
2009 -8.21%
2008 -3.37%
2007 6.13%
2006 4.07%
2005 11.11%
2004 21.74%
2003 -11.03%
2002 -7.79%
2001 0.02%
2000 29.06%
1999 16.63%
1998 36.36%
Part (B)
15 year average return = Sum of the annual return each year of the portfolio / No.of years
= Using the data of part (A) / 15
= 103.65% / 15
= 6.91%
Variance = Sum of (Weights of each stock * (Square of (Average Return of each stock - Average return of the portfolio)))
Standard Deviation = Square root of Variance
Using the spreadsheet Standard Deviation of the Portfolio = 62.68 %
Part (C)
Correlation between return of Company # 12 and equally weighted portfolio (using the spreadsheet) = 41.94%
Covariance between the return of the Company # 12 and equally weighted portfolio (using the spreadsheet) = 1.75%
Part (D)
Beta of Comapny # 12 = Covariance of comp. # 12 with the portfolio / Variance of Portfolio
= 0.0233
Part (E)
Required Rate of Return using Capital Asset Pricing Model of Comp. #12 stock = Risk free return + beta * (Market return - risk free return)
Risk free return = 2.5%
Market Return = Return of the Portfolio (as per question)
Beta of Comp.# 12 = .0233
Required return = 2.5% + .0233 (6.91%-2.5%)
= 2.602%
Part (F)
If the analyst estimates that return of Company #12 return is 5% next year, we will suggest the company to buy the stock because the required rate of return calculated under CAPM model is 2.603 which is below the expected rarte of return, hence it suggests that the price of the stock would have been underpriced and it will be beneficial for the company to buy the stock and wait for capital appreciation.