We consider two stocks. There is a weekly statistic on the prices of both stocks. Calculate weekly
Question:
- We consider two stocks.
- 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.