In: Finance
Year | Asset Return | S&P 500 Return |
1 | 12 | 7 |
2 | 12 | 14 |
3 | -1 | -3 |
4 | 14 | 9 |
5 | 9 | 8 |
6 | 15 | 15 |
7 | 20 | 12 |
8 | -10 | -5 |
9 | 7 | 9 |
10 | 12 | 14 |
Find the Beta in Excel
Year | Asset Return(x) | S&P 500(y) | (x-(∑x/n))*(y-(∑y/n)) | (y-(∑y/n))^2 | |
1 | 12 | 7 | -3 | 1 | |
2 | 12 | 14 | 18 | 36 | |
3 | -1 | -3 | 110 | 121 | |
4 | 14 | 9 | 5 | 1 | |
5 | 9 | 8 | 0 | 0 | |
6 | 15 | 15 | 42 | 49 | |
7 | 20 | 12 | 44 | 16 | |
8 | -10 | -5 | 247 | 169 | |
9 | 7 | 9 | -2 | 1 | |
10 | 12 | 14 | 18 | 36 | |
Total | 90 | 80 | 479 | 430 | |
Expected Return=Total Return/total no. of years | |||||
So expected return= | 9 | 8 | |||
(90/10) | (80/10) | ||||
Covariance=((∑(x-(∑x/n))*(y-(∑y/n)))/n) | |||||
So, covariance(x,y)= | 47.90 | ||||
(479/10) | |||||
Variance of market= ((∑(y-(∑y/n))^2)/n) | 43.00 | ||||
(430/10) | |||||
Beta=Covariance(x,y)/Variance of market | 1.11 | ||||
47.9/43 |