A B C D E F G H K L Covariance and Correlation Estimating covariance and correlation, which are used in capital asset pricing models. Problem 1: What is the covariance between large company stocks and risk-free Treasury Bills? Another measure of how they move together is the 10 correlation. The closer the number is to 1 (100%), the greater the two variables track each other. 11 12 13 Finance Concepts: 14 Covariance is a statistical calculation that tells us how closely two variables move together. 15 Covarince(x,y) = cov(x,y) = 1(()(Xi - Xbar)(Yi - Ybar)) Where n is the number of returns, Xi and Yi are individual returns and Xbar and Ybar are 16 the average of the X and Y returns respectively. 17 18 Time Series Table of Historical Total Returns: 19 Large Co Treasury Consumer 20 Year Stock Bills Price Index 21 1990 -0.0313 0.0785 0.0610 22 1991 0.3053 0.0571 0.0306 23 1992 0.0762 0.0357 0.0289 24 1993 0.1007 0.0308 0.0275 25 1994 0.0127 0.0415 0.0268 26 1995 0.3780 0.0564 0.0253 27 1996 0.2274 0.0512 0.0332 28 1997 0.3343 0.0522 0.0170 29 1998 0.2813 0.0506 0.0161 30 1999 0.2103 0.0485 0.0269 31 32 33 Steps: 1. To calculate the covariance between Large Comapany Stocks and Treasury Bills for the 1990-1999 34 period, we need to find the average returns 35 36 Calulate: 37 Average Return Large Stocks 38 39 Average Return T-Bills 40 41 42 Use the average function: =average(E21:E30) for large stks for example 43 The average return on T-Bills was 5.03% for the years 1990-1999 44 45 2. The next step is to find the difference between the individual returns and the average returns 46 for each year. Subtract the average return from the actual return for each year for both the large company stocks and the T-Bills. 49 50 In F53 enter: = D53-SGS37. In G53 enter =E53-SGS39. Copy. In 153 we want to multiply the differences. Enter: =F53*G53. Copy.A B C D E F G H I J K L 49 In F53 enter: = D53-SGS37. In G53 enter =E53-SG$39. Copy. 50 51 In 153 we want to multiply the differences. Enter: =F53*G53. Copy. Large T-Bills Diff Large Large Co Treasury minus minus Times Diff T. Year Stocks Bills average average 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 1.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(()(Xi - Xbar)(Yi - Ybar)) We have found (Xi - Xbar)(Yi - Ybar) in column I. 68 Next, sum the column of the multiplication: Enter =sum(153:162) => Then, we divide by the number of years (observations), which is 10. Enter =169/9 => The result is that the covariance between large stock and T-Bills (risk-free rate) is 0.0001074 76 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. 79 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 81 measures this tendency. Standard Deviation is the square root of the variance. 83 A. Covarance of large stocks and T-Bills => from J72 84 85 B. We need to find the standard deviations by using the formula: =stdev.s(range) 86 standard deviations of Large Co stock = => standard deviations of T-Bills 88 89 C. Multiply the two standard deviations=> #H86*H87 90 92 Correlation between large stocks and T-Bills is = => 93 94 =H83 /H89 (Cov / (stdevStk * stdevT-B))A B C D E F G H J K 94 95 What meaning does this have? We know that the closer the correlation is to 100% (or 1), the 96 more the two variables track each other. In this case we see that the correlation 97 between large stock returns and Treasury returns is only 5.76%, which is very, very slight. 98 This has portfolio diversification and risk reduction implications. 99 Test Your Skills: 00 The returns for the large stocks and the CPI have been copied to the table below. 101 We can find their covariance and correlation using Excel formulas.. 102 03 To find their covariance enter: =Cover(D109:D118, E109:E118) ==> 04 05 To find their correlation enter: =(Covar(D109:D118,E109:E1 18))/(stdev.s(D109:D118)"(stdev.s(E109:E1 18))) 106 107 Large Co Consumer 108 Year Stocks Price Index 109 1990 -0.0313 0.0610 110 1991 0.3053 0.0306 111 1992 0.0762 0.0289 112 993 0.1017 0.0275 113 199 0.0127 0.0268 114 1995 0.3780 0.0253 115 99 0.2274 0.0332 116 1997 0.3343 0.0170 117 1998 0.2813 0.0161 118 1999 0.2103 0.0269 119 120 121 122 123 124