please do all steps

Finance

x B D G H K What is the covariance between large company stocks and risk-frce Treasury Bills? Another measure of how they move together is the Orrelation. The closer the number is to 1 (100%), the greater the two variables track cach other. Finance Concepts: Covariance is a statistical calculation that tells us how closely two variables move together Coverincek,Y)= Cov(x,y) = 1/(xi Xbar) Yi-Yus)) Wheren is the number of returns, Xi and Yi arc individual retums and Xbar and Ybar are the average of the X and Y retums respectively Time Series Table of Historical Total Returns: Y car 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 Steps: 1. To calculate the covariance between Large Company Stocks and Treasury Bills for the 1990-1999 period, we need to find the average retums. Calulate: Average Return Large Stocks Average Retum T-Bills Use the average function: average(E21:E30) for large stks for example The average retum on T-Bills was 5.03% for the years 1990-1999, B 2. The next step is to find the difference between the individual returns and the average returns for each year. Subtract the average retum from the actual retum for each year for both the large company stocks and the T-Hills In F53 ente: 053-$G$37. In G53 enter -553-$G$39. Copy. In 153 we want to multiply the differences. Enter:=F53*G53. Copy. Large Diff Lage minus T-Bills minus average Times Diff T-bilt average Year 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 Large Co Stocks -0.0313 0.3053 0,0762 0.1017 0.0127 0.3780 0.2274 0.3343 0.2813 0.2103 Treasury Bills 0.0785 0.0574 0.0357 0.0308 0.0415 0.0564 0.0512 0.0522 0.0506 0.0485 3. The formula: Covarince(x,y) cov(x,y) - 1(((XIXbar) - Ybar)) We have found (Xi - Xbar) Yi Ybar) in column 1. Next, sum the column of the multiplication: Enter-sum(153:162) => 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 retums we divide by n (not n-1). K Pasta B Format W30 x fx A B D E G H 75 Por practical purposes, the cover is mo. Note that for historical returns we divide by n (not n-1) 76 77 We can find the correlation between Large stocks and T-Bills. 78 The Formula lo COD.)/( ww 79 what ex and by me the standard deviations of the Large stocks (*) and T-Bills Cy) 80 Correlation is the tendency of two variables to move together, and the correlation coefficient 81 measures this tendency. Standard Devision is the pure root of the Vic. 82 A. Covenance of large stocks and T-Bills from 372 84 85 B. We need to find the standard deviations by using the formula atdev.(range) 86 standard deviations of Large Co stock 87 standard deviations of T-Bills BB B9 C. Multiply the two standard deviations =H86*H87 90 91 92 Correlation between large stocks and T-Bills is 93 =H83/H89 (Cov/(stdevStk. stdevT-BY) 94 95 What meaning does this have? We know that the closer the correlation is to 100% for 1) the 96 more the two variables truck each other. In this case we see the the correlation 97 between large stock returns and Treasury retums is only 5.76%, which is very, very slight. 98 This has portfolio diversification and risk reduction implications 99 Test Your Skills 100 The retums for the large stocks and the CPI have been copied to the table below. 101 We can find their covariance and condition using Excel formulas 102 103 To find their covariance mter: Cova(109:D118, E109E118) 104 105 To find their comestion enter CoveD109D118, 109-E118)(stder D109-D1187*(stdes(E109:5118 106 107 108 109 110 111 Year 1990 1991 1992 1993 1994 1995 112 Large Co Consumer Stocks Price Index 0.0610 0.3053 0.0306 0.0289 0.1017 0.0275 0.0121 0.0068 0.3780 0.0253 2274 03143 0.020 0 2813 0.0161 0 210 0.0162 113 114 115 116 117 118 119 120 121 122 1976 1997 1998 1999 Sid den SOVO Ready x B D G H K What is the covariance between large company stocks and risk-frce Treasury Bills? Another measure of how they move together is the Orrelation. The closer the number is to 1 (100%), the greater the two variables track cach other. Finance Concepts: Covariance is a statistical calculation that tells us how closely two variables move together Coverincek,Y)= Cov(x,y) = 1/(xi Xbar) Yi-Yus)) Wheren is the number of returns, Xi and Yi arc individual retums and Xbar and Ybar are the average of the X and Y retums respectively Time Series Table of Historical Total Returns: Y car 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 Steps: 1. To calculate the covariance between Large Company Stocks and Treasury Bills for the 1990-1999 period, we need to find the average retums. Calulate: Average Return Large Stocks Average Retum T-Bills Use the average function: average(E21:E30) for large stks for example The average retum on T-Bills was 5.03% for the years 1990-1999, B 2. The next step is to find the difference between the individual returns and the average returns for each year. Subtract the average retum from the actual retum for each year for both the large company stocks and the T-Hills In F53 ente: 053-$G$37. In G53 enter -553-$G$39. Copy. In 153 we want to multiply the differences. Enter:=F53*G53. Copy. Large Diff Lage minus T-Bills minus average Times Diff T-bilt average Year 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 Large Co Stocks -0.0313 0.3053 0,0762 0.1017 0.0127 0.3780 0.2274 0.3343 0.2813 0.2103 Treasury Bills 0.0785 0.0574 0.0357 0.0308 0.0415 0.0564 0.0512 0.0522 0.0506 0.0485 3. The formula: Covarince(x,y) cov(x,y) - 1(((XIXbar) - Ybar)) We have found (Xi - Xbar) Yi Ybar) in column 1. Next, sum the column of the multiplication: Enter-sum(153:162) => 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 retums we divide by n (not n-1). K Pasta B Format W30 x fx A B D E G H 75 Por practical purposes, the cover is mo. Note that for historical returns we divide by n (not n-1) 76 77 We can find the correlation between Large stocks and T-Bills. 78 The Formula lo COD.)/( ww 79 what ex and by me the standard deviations of the Large stocks (*) and T-Bills Cy) 80 Correlation is the tendency of two variables to move together, and the correlation coefficient 81 measures this tendency. Standard Devision is the pure root of the Vic. 82 A. Covenance of large stocks and T-Bills from 372 84 85 B. We need to find the standard deviations by using the formula atdev.(range) 86 standard deviations of Large Co stock 87 standard deviations of T-Bills BB B9 C. Multiply the two standard deviations =H86*H87 90 91 92 Correlation between large stocks and T-Bills is 93 =H83/H89 (Cov/(stdevStk. stdevT-BY) 94 95 What meaning does this have? We know that the closer the correlation is to 100% for 1) the 96 more the two variables truck each other. In this case we see the the correlation 97 between large stock returns and Treasury retums is only 5.76%, which is very, very slight. 98 This has portfolio diversification and risk reduction implications 99 Test Your Skills 100 The retums for the large stocks and the CPI have been copied to the table below. 101 We can find their covariance and condition using Excel formulas 102 103 To find their covariance mter: Cova(109:D118, E109E118) 104 105 To find their comestion enter CoveD109D118, 109-E118)(stder D109-D1187*(stdes(E109:5118 106 107 108 109 110 111 Year 1990 1991 1992 1993 1994 1995 112 Large Co Consumer Stocks Price Index 0.0610 0.3053 0.0306 0.0289 0.1017 0.0275 0.0121 0.0068 0.3780 0.0253 2274 03143 0.020 0 2813 0.0161 0 210 0.0162 113 114 115 116 117 118 119 120 121 122 1976 1997 1998 1999 Sid den SOVO Ready