theyre all related to one another..'please help
Cell Styles 154 A XV fx o A B D E F G H K Estimating covariance and condation, which are used in capital asset pricing models. 7 8 9 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 12 Finance Conopis: Covariance is a statistical calculation that tells us how closely two variables move together Covincexy) (5.91 XXXlYba Where is the number of retums, Xi and Yi se individual refums and Xbar and Ybar are the wenge of the X and Y retums respectively, Time Series Table of Historical Total Returns 16 17 Treasury 20 21 0.0785 g= = = = = = = = = = 23 24 25 26 27 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 02274 09343 0.2813 2103 0.0351 0.0105 00115 0.0564 0.0512 0.0522 0,0506 0.0485 Consumer Price Index 0.0610 0.0306 0.0289 0.0273 0.0268 0.0253 0.0332 0.0170 0.0161 00260 31 32 32 Srps 1. To all the bow Large Company Stocks and Trowary Bills for the 1990-1999 portowe to find the wong 34 35 35 37 33 A Run Large Sider Average T-Bills OO 47 CON con Ready + cannot conduct illegal activities Public Control of real bronery 100% MacB Formulas Data Home | Conditional Formatting Insert Page Layout A. Font Alignment % Format as Table Cells Editi Paste Number Cell Styles 154 X fx 0 A B 1 K 42 43 D E F G H Use the average function averageE21:30) for large stks for sample The average retum on T-Bills was 5.03% for the years 1990-1999, 2. The next step is to find the difference between the individual retums and the average retums for each year Subtract the average return from the actual retum for each year for both the large company stocks and the T-Bills. 45 46 17 48 49 50 51 In F53 mnter DS3-SG$37. In G53 enter-ES3-SGS39. Copy In 153 we want to multiply the differences. Enter: F53"G53. Copy Diff Large 52 53 Yew 1990 1991 Times Diff T-bill 20.0100.000 0.0000000 55 57 58 1992 1993 1994 1995 1996 1997 1998 Large Co Treasury Stocks Bills -0.0313 0.0785 0.3053 0.0574 0.0762 0.0357 0.1017 0.0308 0.0127 0.0415 0.3780 0.0564 0.2274 0.0512 0.3343 0.0522 0.2813 0.0506 0.2103 0.0485 Large minus average -0.22 0.01 -0.11 -0.09 -0.18 0.19 0.04 0.14 0.09 0.02 T-Bills minus average 0.0283 0.0072 -0.0146 -0.0195 -0.0088 0.0062 0.0009 0.0020 0.0003 -0.0018 30 2 1999 3. The formula Covariace() (y) Inox - Xbar)YI - Ybar)) We have found X - XwXYi - You) in column 54 35 36 37 18 19 " 1 2 3 4 Nost, sms the course of the multiplication: Entersum(153:162) Then, we divide by the number of years foservations), which is 10 Enter 69:9 The role is that the vacance between lage stock and T-Bills risk-frcetate) is 0.0001074 Ferpretical purposes, the D. Note that for historical returns we divide by n (not n-DL 6 7 " 9 an 4. We find the condition between large cocks and T-Bills The Fole Carnation coefident CY) (Y) Where the dard deviation of the large stocks and T-Bills In the limither the left Std der COV Cart Ready cannot conduct illegal activities 10099 Public Control of real onery Font Alignment Number Cell Styles fx 0 K F H G E A B D 4. We can find the correlation between large stocks and T-Bills. The Formula Correlation coeffident - cov)/(2*sy) where sex 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 masures this tendency. Standard Deviation is the square root of the variance. A. Covenance 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-> =H86*H87 Conciation between large stocks and T-Bills is =H83 / H89 (Cov/ (stdevStkstdevt-B)) What meaning does this have? We know that the closer the correlation is to 100% (or 1), the more the two variables track cach other. In this case we see that the correlation between large stock retums and Treasury retums is only 5.76%, which is very, very slight This has portfolio diversification and risk reduction implications Test Your Ski: The retums 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-1118) To find their continenter ( CD109-D118 E109-E118)(stdev.DI09:D118) sidev.s(E109:118) Year 1990 Large Co Consumer Stocks Price Index -0.0313 0.0610 0.1053 0.0306 0.0762 0.020 D1011 0.0275 0.0127 Sid de coy con Ready cannot conduct illegal activities + 10096 Public Control of real bronetty Conditional Formatting A.. % Format as Table Q Editing Cells Paste Font Number Alignment Cell Styles 154 XV fx o B D E F G H 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. 100 101 102 103 104 105 To find their covariance enterCova (D109:D118, E109E118) - To find their correlation enter. (Cove(D109:D118,E109:E118)(stdev.s(D109.D118)*(stdev.s(E109:E118))) 106 107 108 109 110 111 112 113 114 115 116 Year 1990 1991 1992 1993 1994 Large Co Consumer Stocks Price Index -0.0313 0.0610 0.3053 0.0306 0.0762 0.0289 0.1017 0.0275 0.0127 0.0268 0.3780 0.0253 0.2274 0.0332 0.3343 0.2813 0.0161 0.2103 0.0269 1995 1996 1997 1998 1999 00170 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 + Std de CON corr + Ready cannot conduct illegal activities + T0096 Public Control of real nronertv Mar Cell Styles 154 A XV fx o A B D E F G H K Estimating covariance and condation, which are used in capital asset pricing models. 7 8 9 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 12 Finance Conopis: Covariance is a statistical calculation that tells us how closely two variables move together Covincexy) (5.91 XXXlYba Where is the number of retums, Xi and Yi se individual refums and Xbar and Ybar are the wenge of the X and Y retums respectively, Time Series Table of Historical Total Returns 16 17 Treasury 20 21 0.0785 g= = = = = = = = = = 23 24 25 26 27 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 02274 09343 0.2813 2103 0.0351 0.0105 00115 0.0564 0.0512 0.0522 0,0506 0.0485 Consumer Price Index 0.0610 0.0306 0.0289 0.0273 0.0268 0.0253 0.0332 0.0170 0.0161 00260 31 32 32 Srps 1. To all the bow Large Company Stocks and Trowary Bills for the 1990-1999 portowe to find the wong 34 35 35 37 33 A Run Large Sider Average T-Bills OO 47 CON con Ready + cannot conduct illegal activities Public Control of real bronery 100% MacB Formulas Data Home | Conditional Formatting Insert Page Layout A. Font Alignment % Format as Table Cells Editi Paste Number Cell Styles 154 X fx 0 A B 1 K 42 43 D E F G H Use the average function averageE21:30) for large stks for sample The average retum on T-Bills was 5.03% for the years 1990-1999, 2. The next step is to find the difference between the individual retums and the average retums for each year Subtract the average return from the actual retum for each year for both the large company stocks and the T-Bills. 45 46 17 48 49 50 51 In F53 mnter DS3-SG$37. In G53 enter-ES3-SGS39. Copy In 153 we want to multiply the differences. Enter: F53"G53. Copy Diff Large 52 53 Yew 1990 1991 Times Diff T-bill 20.0100.000 0.0000000 55 57 58 1992 1993 1994 1995 1996 1997 1998 Large Co Treasury Stocks Bills -0.0313 0.0785 0.3053 0.0574 0.0762 0.0357 0.1017 0.0308 0.0127 0.0415 0.3780 0.0564 0.2274 0.0512 0.3343 0.0522 0.2813 0.0506 0.2103 0.0485 Large minus average -0.22 0.01 -0.11 -0.09 -0.18 0.19 0.04 0.14 0.09 0.02 T-Bills minus average 0.0283 0.0072 -0.0146 -0.0195 -0.0088 0.0062 0.0009 0.0020 0.0003 -0.0018 30 2 1999 3. The formula Covariace() (y) Inox - Xbar)YI - Ybar)) We have found X - XwXYi - You) in column 54 35 36 37 18 19 " 1 2 3 4 Nost, sms the course of the multiplication: Entersum(153:162) Then, we divide by the number of years foservations), which is 10 Enter 69:9 The role is that the vacance between lage stock and T-Bills risk-frcetate) is 0.0001074 Ferpretical purposes, the D. Note that for historical returns we divide by n (not n-DL 6 7 " 9 an 4. We find the condition between large cocks and T-Bills The Fole Carnation coefident CY) (Y) Where the dard deviation of the large stocks and T-Bills In the limither the left Std der COV Cart Ready cannot conduct illegal activities 10099 Public Control of real onery Font Alignment Number Cell Styles fx 0 K F H G E A B D 4. We can find the correlation between large stocks and T-Bills. The Formula Correlation coeffident - cov)/(2*sy) where sex 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 masures this tendency. Standard Deviation is the square root of the variance. A. Covenance 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-> =H86*H87 Conciation between large stocks and T-Bills is =H83 / H89 (Cov/ (stdevStkstdevt-B)) What meaning does this have? We know that the closer the correlation is to 100% (or 1), the more the two variables track cach other. In this case we see that the correlation between large stock retums and Treasury retums is only 5.76%, which is very, very slight This has portfolio diversification and risk reduction implications Test Your Ski: The retums 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-1118) To find their continenter ( CD109-D118 E109-E118)(stdev.DI09:D118) sidev.s(E109:118) Year 1990 Large Co Consumer Stocks Price Index -0.0313 0.0610 0.1053 0.0306 0.0762 0.020 D1011 0.0275 0.0127 Sid de coy con Ready cannot conduct illegal activities + 10096 Public Control of real bronetty Conditional Formatting A.. % Format as Table Q Editing Cells Paste Font Number Alignment Cell Styles 154 XV fx o B D E F G H 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. 100 101 102 103 104 105 To find their covariance enterCova (D109:D118, E109E118) - To find their correlation enter. (Cove(D109:D118,E109:E118)(stdev.s(D109.D118)*(stdev.s(E109:E118))) 106 107 108 109 110 111 112 113 114 115 116 Year 1990 1991 1992 1993 1994 Large Co Consumer Stocks Price Index -0.0313 0.0610 0.3053 0.0306 0.0762 0.0289 0.1017 0.0275 0.0127 0.0268 0.3780 0.0253 0.2274 0.0332 0.3343 0.2813 0.0161 0.2103 0.0269 1995 1996 1997 1998 1999 00170 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 + Std de CON corr + Ready cannot conduct illegal activities + T0096 Public Control of real nronertv Mar
