In: Finance
What is the average monthly return and standard deviation of returns for
(i) S&P 500:
Date | Adj Close |
8/1/2015 | 1972.18 |
9/1/2015 | 1920.03 |
######## | 2079.36 |
######## | 2080.41 |
######## | 2043.94 |
1/1/2016 | 1940.24 |
2/1/2016 | 1932.23 |
3/1/2016 | 2059.74 |
4/1/2016 | 2065.3 |
5/1/2016 | 2096.95 |
6/1/2016 | 2098.86 |
7/1/2016 | 2173.6 |
8/1/2016 | 2170.95 |
9/1/2016 | 2168.27 |
######## | 2126.15 |
######## | 2198.81 |
######## | 2238.83 |
1/1/2017 | 2278.87 |
2/1/2017 | 2363.64 |
3/1/2017 | 2362.72 |
4/1/2017 | 2384.2 |
5/1/2017 | 2411.8 |
6/1/2017 | 2423.41 |
7/1/2017 | 2470.3 |
8/1/2017 | 2471.65 |
9/1/2017 | 2519.36 |
######## | 2575.26 |
######## | 2584.84 |
######## | 2673.61 |
1/1/2018 | 2823.81 |
2/1/2018 | 2713.83 |
3/1/2018 | 2640.87 |
4/1/2018 | 2648.05 |
5/1/2018 | 2705.27 |
6/1/2018 | 2718.37 |
7/1/2018 | 2816.29 |
8/1/2018 | 2901.52 |
9/1/2018 | 2913.98 |
######## | 2711.74 |
######## | 2760.17 |
######## | 2506.85 |
1/1/2019 | 2704.1 |
2/1/2019 | 2784.49 |
3/1/2019 | 2834.4 |
4/1/2019 | 2945.83 |
5/1/2019 | 2752.06 |
6/1/2019 | 2941.76 |
7/1/2019 | 2980.38 |
8/1/2019 | 2926.46 |
(ii) GE:
Date | Adj Close |
8/1/2015 | 22.04931 |
9/1/2015 | 21.37927 |
######## | 24.7352 |
######## | 25.60761 |
######## | 26.64251 |
1/1/2016 | 25.07532 |
2/1/2016 | 25.10978 |
3/1/2016 | 27.61257 |
4/1/2016 | 26.70923 |
5/1/2016 | 26.25756 |
6/1/2016 | 27.3433 |
7/1/2016 | 27.25289 |
8/1/2016 | 27.34041 |
9/1/2016 | 25.92262 |
######## | 25.66629 |
######## | 27.13042 |
######## | 27.8713 |
1/1/2017 | 26.39264 |
2/1/2017 | 26.49039 |
3/1/2017 | 26.69272 |
4/1/2017 | 25.96718 |
5/1/2017 | 24.52506 |
6/1/2017 | 24.19364 |
7/1/2017 | 23.33439 |
8/1/2017 | 22.36858 |
9/1/2017 | 22.03145 |
######## | 18.55219 |
######## | 16.83133 |
######## | 16.05832 |
1/1/2018 | 15.08917 |
2/1/2018 | 13.16687 |
3/1/2018 | 12.68307 |
4/1/2018 | 13.23819 |
5/1/2018 | 13.2476 |
6/1/2018 | 12.80539 |
7/1/2018 | 12.93803 |
8/1/2018 | 12.28306 |
9/1/2018 | 10.71682 |
######## | 9.678614 |
######## | 7.187089 |
######## | 7.254169 |
1/1/2019 | 9.74951 |
2/1/2019 | 10.36903 |
3/1/2019 | 9.969832 |
4/1/2019 | 10.16022 |
5/1/2019 | 9.430923 |
6/1/2019 | 10.4899 |
7/1/2019 | 10.45 |
8/1/2019 | 8.25 |
Return for a particular month = Price of current month / Price of the previous month - 1.
This is in column D for both S&P 500 and GE
(i) S&P 500
Use the excel function Average with inputs as cells D4:D51
Cell D52 =AVERAGE(D4:D51)
0.89% |
Use the function in Cell D53 =STDEV(D4:D51) to calculate the standard deviation of returns
3.54% |
(ii) GE
D105 =AVERAGE(D57:D104)
-1.62% |
D106 =STDEV(D57:D104)
9.11% |
Can you please upvote? Thank You :-)
(i) S&P 500
Screenshot with formulas
(ii) GE
Screenshot with formulas