1. We consider two stocks.
2. There is a weekly statistic on the prices of both stocks. Calculate weekly rates of return, average rates of return, standard deviations, covariance, and/or correlation coefficients for stock returns.

Table 1.

Week	Price of stock 1	Price of stock 2
1	25	155
2	26	153
3	30	147
4	29	149
5	27	150
6	31	145
7	33	141
8	35	139
9	37	130
10	34	140
11	39	136
12	41	131
13	38	135
14	39	140
15	41	142
16	40	145
17	45	150
18	49	155
19	51	160
20	50	165

  

• You can organize your calculation like this (the first time do not use the functions of Excel (covariance and correlation).
•  Table 2.

№	Price of the 1st stock	Price of the 2nd stock	Rate of return of the 1st stock (RR1)	Rate of return of the 2nd stock (RR2)
1	X	X
2	X	X	X	X
3	X	X	X	X
4	X	X	X	X
5	X	X	X	X
6	X	X	X	X
7	X	X	X	X
8	X	X	X	X
9	X	X	X	X
The average rate of return (ARR)			ARR1	ARR2

Correlation coefficient calculation    

Table 3.

(RR1i – ARR1)* (RR2i – ARR2)	(RR1i – ARR1)2	(RR2i – ARR2)2

 

Investment portfolio construction

 

Table 4.

 

				Risk (portfolio standard deviation)
Portfolio	Weight for the 1st security	Weight for the 2nd security	Portfolio rate of return	correlation coefficient = your calculation	correlation coefficient = 1	correlation coefficient = 0	correlation coefficient = - 0.2
1	0.05	0.95
2	0.10	0.90
3	0.15	0.85
4	0.20	0.80
5	0.25	0.75
6	0.30	0.70
7	0.35	0.65
8	0.40	0.60
9	0.45	0.55
10	0.50	0.50
11	0.55	0.45
12	0.60	0.40
13	0.65	0.35
14	0.70	0.30
15	0.75	0.25
16	0.80	0.20
17	0.85	0.15
18	0.90	0.10
19	0.95	0.05
20	1.00	0.00

  

Use the results of Table 4 to draw curves with different correlation coefficients.

  

Draw capital allocation line for correlation coefficient equal 0.