A B E F G H M Problem 1: What is the covariance between large company stocks and risk-free Treasury Bills? Another measure of how they move together the correlation. The closer the number is to 1 (100%), the greater the two variables track each other. Finance Concepts: Covariance is a statistical calculation that tells us how closely two variables move together. Covariacety)=cov(x,y) 1760 XXi -XbarX(Yi - Ybar) Where n is the number of returns, Xi and Yi are individual retums and Xbar and Ybar are the average of the X and Y returns respectively Time Series Table of Historical Total Returns: Year 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 Large Co Stocks -0.0313 0.3053 0.0762 0.1007 0.0127 0.3780 0.2274 0.3343 0.2813 0.2103 Treasury Bills 0.0785 0.0571 0.0357 0.0308 0.0415 0.0564 0.0512 0.0522 0.0506 0.0485 Consumer Price Index 0.0610 0.0306 0.0289 0.0275 0.0268 0.0253 0.0332 0.0170 0.0161 0.0269 Step 1. Lo calculate the covanance between Large Comapany Stocks and Treasury Bills for the 1990- 1999 penod, we need to find the average returns BIH Step: 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-SGS37. In G53 enter E53-SG$39. Copy In 153 we want to multiply the differences. Enter: =F53"G53. Copy Large minus average T-Bills minus averare Diff Large Times Dif T-bill Year 1990 1991 1992 1993 1994 1995 1990 1997 1998 1999 Large Co Stock 0.0313 0.3053 0.0762 0.1017 0.0127 03780 02274 03143 0.PRIN 02 103 Treasury Bills 0.0785 0.0574 0.0357 0.0308 0.0415 0.0564 0.0512 0.0522 0.0506 0,048:2 3. The formula: Covarice(x) = cy(x,y) - 1.XXI - Xharky - Ybar)) We have found (Xi - Xbar)(Yi - Ybar) in column L 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 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: stdev.s(range) standard deviations of Large Co stock => standard deviations of T-Bills C. Multiply the two standard deviations-> =H86H87 Correlation between large stocks and T-Bills is => -H83 /H89 (Cov! (stdev Stk - stdev T-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 A B E F G H M Problem 1: What is the covariance between large company stocks and risk-free Treasury Bills? Another measure of how they move together the correlation. The closer the number is to 1 (100%), the greater the two variables track each other. Finance Concepts: Covariance is a statistical calculation that tells us how closely two variables move together. Covariacety)=cov(x,y) 1760 XXi -XbarX(Yi - Ybar) Where n is the number of returns, Xi and Yi are individual retums and Xbar and Ybar are the average of the X and Y returns respectively Time Series Table of Historical Total Returns: Year 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 Large Co Stocks -0.0313 0.3053 0.0762 0.1007 0.0127 0.3780 0.2274 0.3343 0.2813 0.2103 Treasury Bills 0.0785 0.0571 0.0357 0.0308 0.0415 0.0564 0.0512 0.0522 0.0506 0.0485 Consumer Price Index 0.0610 0.0306 0.0289 0.0275 0.0268 0.0253 0.0332 0.0170 0.0161 0.0269 Step 1. Lo calculate the covanance between Large Comapany Stocks and Treasury Bills for the 1990- 1999 penod, we need to find the average returns BIH Step: 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-SGS37. In G53 enter E53-SG$39. Copy In 153 we want to multiply the differences. Enter: =F53"G53. Copy Large minus average T-Bills minus averare Diff Large Times Dif T-bill Year 1990 1991 1992 1993 1994 1995 1990 1997 1998 1999 Large Co Stock 0.0313 0.3053 0.0762 0.1017 0.0127 03780 02274 03143 0.PRIN 02 103 Treasury Bills 0.0785 0.0574 0.0357 0.0308 0.0415 0.0564 0.0512 0.0522 0.0506 0,048:2 3. The formula: Covarice(x) = cy(x,y) - 1.XXI - Xharky - Ybar)) We have found (Xi - Xbar)(Yi - Ybar) in column L 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 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: stdev.s(range) standard deviations of Large Co stock => standard deviations of T-Bills C. Multiply the two standard deviations-> =H86H87 Correlation between large stocks and T-Bills is => -H83 /H89 (Cov! (stdev Stk - stdev T-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