|
|
|
|
|
|
|
|
|
|
|
Finance Concepts: |
|
|
|
|
|
|
|
|
|
Covariance is a statistical calculation that tells us how closely two variables move together. |
|
|
Covarince(x,y) = cov(x,y) = 1/n((∑(Xi - Xbar)(Yi - Ybar)) |
|
|
|
|
|
Where n is the number of returns, Xi and Yi are individual returns and Xbar and Ybar are the average of the X and Y returns respectively. |
|
|
|
|
|
|
|
|
|
|
|
Time Series Table of Historical Total Returns: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Year |
|
Large Co Stocks |
|
Treasury Bills |
|
Consumer Price Index |
|
|
|
1990 |
|
-0.0313 |
|
0.0785 |
|
0.0610 |
|
|
|
1991 |
|
0.3053 |
|
0.0571 |
|
0.0306 |
|
|
|
1992 |
|
0.0762 |
|
0.0357 |
|
0.0289 |
|
|
|
1993 |
|
0.1007 |
|
0.0308 |
|
0.0275 |
|
|
|
1994 |
|
0.0127 |
|
0.0415 |
|
0.0268 |
|
|
|
1995 |
|
0.3780 |
|
0.0564 |
|
0.0253 |
|
|
|
1996 |
|
0.2274 |
|
0.0512 |
|
0.0332 |
|
|
|
1997 |
|
0.3343 |
|
0.0522 |
|
0.0170 |
|
|
|
1998 |
|
0.2813 |
|
0.0506 |
|
0.0161 |
|
|
|
1999 |
|
0.2103 |
|
0.0485 |
|
0.0269 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Steps: |
|
|
|
|
|
|
|
|
|
1. |
To calculate the covariance between Large Comapany Stocks and Treasury Bills for the 1990-1999 period, we need to find the average returns. |
|
|
|
|
|
|
|
|
|
|
|
|
Calulate: |
|
|
|
|
|
|
|
|
|
|
Average Return Large Stocks |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Average Return T-Bills |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Use the average function: =average(E21:E30) for large stks for example |
|
|
|
The average return on T-Bills was 5.03% for the years 1990-1999. |
|
|
|
|
|
|
|
|
|
|
|
|
|
2. |
The next step is to find the difference between the individual returns and the average returns |
|
|
for each year. Subtract the average return from the actual return for each year for both |
|
|
the large company stocks and the T-Bills. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
In F53 enter: = D53-$G$37. In G53 enter =E53-$G$39. Copy. |
|
|
|
|
In I53 we want to multiply the differences. Enter: =F53*G53. Copy. |
|
|
|
|
|
|
|
|
|
|
|
|
|
Year |
Large Co Stocks |
Treasury Bills |
Large minus average |
T-Bills minus average |
|
Diff Large Times Diff T-bill |
|
|
|
1990 |
-0.0313 |
0.0785 |
|
|
|
|
|
|
|
1991 |
0.3053 |
0.0574 |
|
|
|
|
|
|
|
1992 |
0.0762 |
0.0357 |
|
|
|
|
|
|
|
1993 |
0.1017 |
0.0308 |
|
|
|
|
|
|
|
1994 |
0.0127 |
0.0415 |
|
|
|
|
|
|
|
1995 |
0.3780 |
0.0564 |
|
|
|
|
|
|
|
1996 |
0.2274 |
0.0512 |
|
|
|
|
|
|
|
1997 |
0.3343 |
0.0522 |
|
|
|
|
|
|
|
1998 |
0.2813 |
0.0506 |
|
|
|
|
|
|
|
1999 |
0.2103 |
0.0485 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3. |
The formula: |
|
|
|
|
|
|
|
|
|
Covarince(x,y) = cov(x,y) = 1/n((∑(Xi - Xbar)(Yi - Ybar)) |
|
|
|
We have found (Xi - Xbar)(Yi - Ybar) in column I. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Next, sum the column of the multiplication: Enter =sum(I53:I62) => |
|
|
|
|
|
|
|
|
|
|
|
|
|
Then, we divide by the number of years (observations), which is 10. |
|
|
|
|
|
|
|
Enter =J69/9 => |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The result is that the covariance between large stock and T-Bills (risk-free rate) is 0.0001074 |
|
|
For practical purposes, the covariance is zero. Note that for historical returns we divide by n (not n-1). |
|
|
|
|
|
|
|
|
|
|
|
4. |
We can find the correlation between large stocks and T-Bills. |
|
|
|
|
The Formula: Correlation coefficient = cov(x,y) / (sx*sy) |
|
|
|
|
where sx and sy are the standard deviations of the Large stocks (x) and T-Bills (y). |
|
|
Correlation is the tendency of two variables to move together, and the correlation coefficient |
|
|
measures this tendency. Standard Deviation is the square root of the variance. |
|
|
|
|
|
|
|
|
|
|
|
|
A. Covarance of large stocks and T-Bills => |
|
from J72 |
|
|
|
|
|
|
|
|
|
|
|
|
|
B. We need to find the standard deviations by using the formula: =stdevp(range) |
|
|
standard deviations of Large Co stock ==> |
|
|
|
|
|
|
standard deviations of T-Bills ===> |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
C. Multiply the two standard deviations=> |
|
=H86*H87 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Correlation between large stocks and T-Bills is ==> |
|
|
|
|
|
|
=H83 / H89 (Cov / (stdevStk * stdevT-B)) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
What meaning does this have? We know that the closer the correlation is to 100% (or 1), the |
|
|
more the two variables track each other. In this case we see that the correlation |
|
|
between large stock returns and Treasury returns is only 5.76%, which is very, very slight. |
| |
|
This has portfolio diversification and risk reduction implications. |
|
|
| Test Your Skills: |
|
|
|
|
|
|
|
|
|
The returns for the large stocks and the CPI have been copied to the table below. |
|
|
We can find their covariance and correlation using Excel formulas.. |
|
|
|
|
|
|
|
|
|
|
|
|
|
To find their covariance enter: =Covar(D109:D118, E109:E118) ==> |
|
|
|
|
|
|
|
|
|
|
|
|
|
To find their correlation enter: =(Covar(D109:D118,E109:E118))/(stdevp(D109:D118)*(stdevp(E109:E118))) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Year |
Large Co Stocks |
Consumer Price Index |
|
==> |
|
|
|
|
|
1990 |
-0.0313 |
0.0610 |
|
|
|
|
|
|
|
1991 |
0.3053 |
0.0306 |
|
|
|
|
|
|
|
1992 |
0.0762 |
0.0289 |
|
|
|
|
|
|
|
1993 |
0.1017 |
0.0275 |
|
|
|
|
|
|
|
1994 |
0.0127 |
0.0268 |
|
|
|
|
|
|
|
1995 |
0.3780 |
0.0253 |
|
|
|
|
|
|
|
1996 |
0.2274 |
0.0332 |
|
|
|
|
|
|
|
1997 |
0.3343 |
0.0170 |
|
|
|
|
|
|
|
1998 |
0.2813 |
0.0161 |
|
|
|
|
|
|
|
1999 |
0.2103 |
0.0269 |
|
|
|
|
|