a) Calculate the monthly returns (for each month) based on the adjusted close price using the...

 

Transcribed Image Text:

a) Calculate the monthly returns (for each month) based on the adjusted close price using the below formula: Return for month t = (Price at month t-price at month t-1)/ price at month t-1 Important: make sure from the beginning that the firm you chose has at least 5 years of historical data. b) Calculate the average and the standard deviation for the stock return. Use AVERAGE and STDEV.S functions in excel. c) Calculate the annualized average return and standard deviation of the stock return. Date Open 01/11/2018 81.176498 89.199997 01/12/2018 88.473 01/01/2019 73.260002 01/02/2019 04/02/0040 01/03/2019 0410010040 01/08/2019 6410010040 01/09/2019 01/04/2019 01/05/2019 4D510040 06 654500 96.654503 08.00000 98.220001 C 0410010040 06 760000 01/06/2019 88.000504 96.760002 471004 $400000 20000 01/07/2019 96.149002 101.790001 93.585999 01/10/2019 04440040 01/11/2019 0444010040 01/12/2019 81.944 81.944 22 7565 82.7565 90.00FF] 04100/10000 01/03/2020 88.5 87 200000 89 400407 90.22000, 02 75 93.75 05 2015011 01/04/2020 4040000 00 010100 96.648499 High 01/06/2020 122.400002 01/07/2020 137 899506 01/07/2020 137.899506 04/01/10000 01/01/2020 6410010000 100 500000 100.007501 04/09/2000 150.025497 01/08/2020 159.025497 www 88.917 65.349998 86.820503 73.046501 78.337997 22 652 83.653 01 1975 91.1875 07 817001 97.817001 04 806004 94.896004 200600 92.682999 89.942497 91.224407 91.234497 FOT 95.07 100 700000 102.786003 070400 01/05/2020 116.839996 126.272499 120 690002 99.816498 0216408 100 25 123.75 Low 71 01/10/2020 160 200004 174 211006 01/10/2020 160.399994 174.811996 79.328499 00 00650 89.936501 DO COFFEE 88.635002 00 500000 83.599998 00 470 92.472 Kantoo 87.175499 85 46000 84 25000 84.252998 www 86.135498 Close Adj Close Volume 84.508499 2785800000 84.508499 75.098503 75.098503 3096254000 85.936501 85.936501 2680034000 1658728000 1618738000 $10.010 112.819 167 214493 167.214493 137.699997 1000000 $74 75 $50 8640004 174.75 153.649994 DE 01.0016 81.991501 81.991501 20.007400 89.037498 60605000 96.325996 96.325996 1624784000 88.753502 1964288000 60 752502 88.753502 4 CRED 94.681503 On Co 93.338997 of 88.814499 06 305506 86.795502 50.000 88.833 0004000 90.040001 86.75 90.766998 100.435997 00 766008 100 40 90 EEGERS 90.556503 04 1975 94.1875 21 por 81.301498 02.201002 92.391998 600007 94.457497 123.699997 07 400 97.486 122 1125 122.1185 20.027400 6 89.037498 2016644000 C4 C4501000000 94.681503 1494930000 0000000 93.338997 1462976000 2014 88.814499 1595424000 86 705502 1222458000 86.795502 1223458000 0.0000300000 88.833 1407210000 90.040005 co 2201000 1000000000 92.391998 1362992000 100.435997 1693966000 100 435997 1600000000 04 1975 1050000000 94.1875 1850202000 TONTON 97.486 3276182000 139.800003 121.856499 137.940994 137.940994 1756366000 800003 121856499 127.940901 127040004 $750000000 ON 01/09/2020 $74 470004 $77612502 $40 550000 $57400400 $57 40040000000000 01/09/2020 174.479004 177.612503 143.550003 157.436493 157.436493 2317986000 150.040007 151 207405 151 207405 2224522000 150.949997 151.807495 151.807495 2324522000 113 Zowe 123.699997 2492196000 122 6000072402506000 122 1125 1051000000 122.1185 1651688000 500000041 158 220004 158.233994 158.233994 2550040000 $72.549004 $70 64000 172.548004 172.548004 1670332000 01/11/2020 153.087006 168.339996 01/12/2020 159.425003 167.532501 01/12/2020 150 425002 167 522501 041041000 100 5100 104504 163.5 168.194504 147.505997 158.401993 158.401993 1816210000 153.641006 162.846497 162.846497 1551124000 152 641006 162846197 162 946407 1551124000 154.200002 154.300003 160.309998 160.309998 1430578000 154.6465 154.6465 1442140000 01/02/2021 162.117996 171.699997 151.835007 01/03/2021 156.394501 159.100006 144.050003 154.703995 154.703995 1563760000 01/04/2021 155.897003 177.699997 155.777496 173.371002 173.371002 1536792000 01/05/2021 174.236496 174.332504 156.3685 161.153503 161.153503 1503678000 ..... 01/06/2021 162.175003 176.242996 158.610001 172.007996 172.007996 1340220000 01/07/2021 171.730499 188.654007 165.348999 166.379501 166.379501 1674586000 4100100000000000000000000000000 01/08/2021 167.654999 173.628998 158.787994 173.539505 173.539505 1256964000 01/09/2021 174.820007 177.499496 163.699493 164.251999 164.251999 1250554000 01/10/2021 164.4505 173.949997 158.8125 168.621506 168.621506 1273466000 100.0 120 100:02 1900 1 01/11/2021 168.089996 188.107498 164.177505 175.3535 175.3535 1515990000 01/12/2021 177.25 177.994003 165.195007 166.716995 166.716995 1287634000 01/01/2022 167.550003 171.399994 135.352005 149.573502 149.573502 1524654000 1.000000 01/02/2022 150 163.834503 138.332993 153.563004 153.563004 1689604000 PRZECHOD 01/03/2022 152.732498 170.831497 133.572495 162.997498 162.997498 1628486000 1912400 102.comdoo Toc.do/NGO CONO0000 01/04/2022 164.149506 168.394501 121.625 124.281502 124.281502 1465008000 01/05/2022 122.401001 126.220497 101.260002 120.209503 120.209503 2258476000 TO 01/06/2022 122.255997 128.990005 101.43 106.209999 106.209999 1767601100 TO Tike 01/07/2022 106.290001 137.649994 105.849998 134.949997 134.949997 1337852600 www Okaber C VORSTE 01/08/2022 134.960007 146.570007 126.739998 126.769997 126.769997 1170449000 1:300001 10000 E 01/09/2022 126 136.490005 112.059998 www.ce 01/10/2022 113.580002 123 www 01/11/2022 103.989998 104.580002 Pagam www. 113 1210487600 97.660004 102.440002 102.440002 1459311500 www. 85.870003 96.540001 01/12/2022 96.989998 97.230003 81.690002 www 01/01/2023 85.459999 103.489998 aces SOTTONS 01/02/2023 102.529999 114 A FOTOS www. 84 84 1549193300 D3000€ part DE EFT 1.43 103.129997 103.129997 1523798600 92.32 94.230003 94.230003 1364102000 01/03/2023 93.870003 103.489998 88.120003 103.290001 103.290001 1349240300 01/04/2023 102.300003 110.860001 97.709999 105.449997 105.449997 1224083600 01/05/2023 104.949997 122.919998 101.150002 120.580002 120.580002 1432891600 01/06/2023 131.490005 119.93 130.360001 130.360001 1242648800 01/07/2023 130.820007 136.649994 125.919998 133.679993 133.679993 1058754800 122 670002 1058751900 01/08/2023 133.550003 143.630005 126.410004 138.009995 138.009995 1210426200 01/09/2023 139.460007 145.860001 123.040001 127.120003 127.120003 1120271900 01/10/2023 127.279999 134.479996 118.349998 133.089996 133.089996 1224416100 31/10/2023 132.75 133.570007 131.720001 133.089996 133.089996 51512957 96.540001 2035133200 1000 P Monthly Return Average Return Standard Deviation Annual Standard Deviation a) Calculate the monthly returns (for each month) based on the adjusted close price using the below formula: Return for month t = (Price at month t-price at month t-1)/ price at month t-1 Important: make sure from the beginning that the firm you chose has at least 5 years of historical data. b) Calculate the average and the standard deviation for the stock return. Use AVERAGE and STDEV.S functions in excel. c) Calculate the annualized average return and standard deviation of the stock return. Date Open 01/11/2018 81.176498 89.199997 01/12/2018 88.473 01/01/2019 73.260002 01/02/2019 04/02/0040 01/03/2019 0410010040 01/08/2019 6410010040 01/09/2019 01/04/2019 01/05/2019 4D510040 06 654500 96.654503 08.00000 98.220001 C 0410010040 06 760000 01/06/2019 88.000504 96.760002 471004 $400000 20000 01/07/2019 96.149002 101.790001 93.585999 01/10/2019 04440040 01/11/2019 0444010040 01/12/2019 81.944 81.944 22 7565 82.7565 90.00FF] 04100/10000 01/03/2020 88.5 87 200000 89 400407 90.22000, 02 75 93.75 05 2015011 01/04/2020 4040000 00 010100 96.648499 High 01/06/2020 122.400002 01/07/2020 137 899506 01/07/2020 137.899506 04/01/10000 01/01/2020 6410010000 100 500000 100.007501 04/09/2000 150.025497 01/08/2020 159.025497 www 88.917 65.349998 86.820503 73.046501 78.337997 22 652 83.653 01 1975 91.1875 07 817001 97.817001 04 806004 94.896004 200600 92.682999 89.942497 91.224407 91.234497 FOT 95.07 100 700000 102.786003 070400 01/05/2020 116.839996 126.272499 120 690002 99.816498 0216408 100 25 123.75 Low 71 01/10/2020 160 200004 174 211006 01/10/2020 160.399994 174.811996 79.328499 00 00650 89.936501 DO COFFEE 88.635002 00 500000 83.599998 00 470 92.472 Kantoo 87.175499 85 46000 84 25000 84.252998 www 86.135498 Close Adj Close Volume 84.508499 2785800000 84.508499 75.098503 75.098503 3096254000 85.936501 85.936501 2680034000 1658728000 1618738000 $10.010 112.819 167 214493 167.214493 137.699997 1000000 $74 75 $50 8640004 174.75 153.649994 DE 01.0016 81.991501 81.991501 20.007400 89.037498 60605000 96.325996 96.325996 1624784000 88.753502 1964288000 60 752502 88.753502 4 CRED 94.681503 On Co 93.338997 of 88.814499 06 305506 86.795502 50.000 88.833 0004000 90.040001 86.75 90.766998 100.435997 00 766008 100 40 90 EEGERS 90.556503 04 1975 94.1875 21 por 81.301498 02.201002 92.391998 600007 94.457497 123.699997 07 400 97.486 122 1125 122.1185 20.027400 6 89.037498 2016644000 C4 C4501000000 94.681503 1494930000 0000000 93.338997 1462976000 2014 88.814499 1595424000 86 705502 1222458000 86.795502 1223458000 0.0000300000 88.833 1407210000 90.040005 co 2201000 1000000000 92.391998 1362992000 100.435997 1693966000 100 435997 1600000000 04 1975 1050000000 94.1875 1850202000 TONTON 97.486 3276182000 139.800003 121.856499 137.940994 137.940994 1756366000 800003 121856499 127.940901 127040004 $750000000 ON 01/09/2020 $74 470004 $77612502 $40 550000 $57400400 $57 40040000000000 01/09/2020 174.479004 177.612503 143.550003 157.436493 157.436493 2317986000 150.040007 151 207405 151 207405 2224522000 150.949997 151.807495 151.807495 2324522000 113 Zowe 123.699997 2492196000 122 6000072402506000 122 1125 1051000000 122.1185 1651688000 500000041 158 220004 158.233994 158.233994 2550040000 $72.549004 $70 64000 172.548004 172.548004 1670332000 01/11/2020 153.087006 168.339996 01/12/2020 159.425003 167.532501 01/12/2020 150 425002 167 522501 041041000 100 5100 104504 163.5 168.194504 147.505997 158.401993 158.401993 1816210000 153.641006 162.846497 162.846497 1551124000 152 641006 162846197 162 946407 1551124000 154.200002 154.300003 160.309998 160.309998 1430578000 154.6465 154.6465 1442140000 01/02/2021 162.117996 171.699997 151.835007 01/03/2021 156.394501 159.100006 144.050003 154.703995 154.703995 1563760000 01/04/2021 155.897003 177.699997 155.777496 173.371002 173.371002 1536792000 01/05/2021 174.236496 174.332504 156.3685 161.153503 161.153503 1503678000 ..... 01/06/2021 162.175003 176.242996 158.610001 172.007996 172.007996 1340220000 01/07/2021 171.730499 188.654007 165.348999 166.379501 166.379501 1674586000 4100100000000000000000000000000 01/08/2021 167.654999 173.628998 158.787994 173.539505 173.539505 1256964000 01/09/2021 174.820007 177.499496 163.699493 164.251999 164.251999 1250554000 01/10/2021 164.4505 173.949997 158.8125 168.621506 168.621506 1273466000 100.0 120 100:02 1900 1 01/11/2021 168.089996 188.107498 164.177505 175.3535 175.3535 1515990000 01/12/2021 177.25 177.994003 165.195007 166.716995 166.716995 1287634000 01/01/2022 167.550003 171.399994 135.352005 149.573502 149.573502 1524654000 1.000000 01/02/2022 150 163.834503 138.332993 153.563004 153.563004 1689604000 PRZECHOD 01/03/2022 152.732498 170.831497 133.572495 162.997498 162.997498 1628486000 1912400 102.comdoo Toc.do/NGO CONO0000 01/04/2022 164.149506 168.394501 121.625 124.281502 124.281502 1465008000 01/05/2022 122.401001 126.220497 101.260002 120.209503 120.209503 2258476000 TO 01/06/2022 122.255997 128.990005 101.43 106.209999 106.209999 1767601100 TO Tike 01/07/2022 106.290001 137.649994 105.849998 134.949997 134.949997 1337852600 www Okaber C VORSTE 01/08/2022 134.960007 146.570007 126.739998 126.769997 126.769997 1170449000 1:300001 10000 E 01/09/2022 126 136.490005 112.059998 www.ce 01/10/2022 113.580002 123 www 01/11/2022 103.989998 104.580002 Pagam www. 113 1210487600 97.660004 102.440002 102.440002 1459311500 www. 85.870003 96.540001 01/12/2022 96.989998 97.230003 81.690002 www 01/01/2023 85.459999 103.489998 aces SOTTONS 01/02/2023 102.529999 114 A FOTOS www. 84 84 1549193300 D3000€ part DE EFT 1.43 103.129997 103.129997 1523798600 92.32 94.230003 94.230003 1364102000 01/03/2023 93.870003 103.489998 88.120003 103.290001 103.290001 1349240300 01/04/2023 102.300003 110.860001 97.709999 105.449997 105.449997 1224083600 01/05/2023 104.949997 122.919998 101.150002 120.580002 120.580002 1432891600 01/06/2023 131.490005 119.93 130.360001 130.360001 1242648800 01/07/2023 130.820007 136.649994 125.919998 133.679993 133.679993 1058754800 122 670002 1058751900 01/08/2023 133.550003 143.630005 126.410004 138.009995 138.009995 1210426200 01/09/2023 139.460007 145.860001 123.040001 127.120003 127.120003 1120271900 01/10/2023 127.279999 134.479996 118.349998 133.089996 133.089996 1224416100 31/10/2023 132.75 133.570007 131.720001 133.089996 133.089996 51512957 96.540001 2035133200 1000 P Monthly Return Average Return Standard Deviation Annual Standard Deviation

Answer rating: 100% (QA)

a Calculate the monthly returns for each month based on the adjusted close price using the below for... View the full answer