Question

What is the average monthly return and standard deviation of returns for 08/01/15-08/31/19:

(iii) Chevron:

Date	Adj Close
8/1/2015	67.98351
9/1/2015	67.04664
########	77.24643
########	77.62042
########	77.39832
1/1/2016	74.39566
2/1/2016	71.78875
3/1/2016	83.11975
4/1/2016	89.02702
5/1/2016	87.99893
6/1/2016	92.30142
7/1/2016	90.23226
8/1/2016	88.55934
9/1/2016	91.57452
########	93.20279
########	99.26207
########	105.7736
1/1/2017	100.0671
2/1/2017	101.1006
3/1/2017	97.41397
4/1/2017	96.80608
5/1/2017	93.88468
6/1/2017	95.61873
7/1/2017	100.0729
8/1/2017	98.63402
9/1/2017	108.7711
########	107.2807
########	110.1504
########	116.9746
1/1/2018	117.1241
2/1/2018	104.5755
3/1/2018	107.6171
4/1/2018	118.0636
5/1/2018	117.2992
6/1/2018	120.3496
7/1/2018	120.1973
8/1/2018	112.7629
9/1/2018	117.5152
########	107.2994
########	114.3053
########	105.5726
1/1/2019	111.2593
2/1/2019	116.0435
3/1/2019	120.7387
4/1/2019	117.6805
5/1/2019	111.5936
6/1/2019	123.1738
7/1/2019	121.8573
8/1/2019	116.5222

(iv) Intel:

Date	Adj Close
8/1/2015	25.32108
9/1/2015	26.96275
########	30.29062
########	31.10468
########	31.03551
1/1/2016	27.94547
2/1/2016	26.65721
3/1/2016	29.40016
4/1/2016	27.51891
5/1/2016	28.70946
6/1/2016	30.06661
7/1/2016	31.95494
8/1/2016	32.89911
9/1/2016	34.86641
########	32.2064
########	32.04939
########	33.7531
1/1/2017	34.26493
2/1/2017	33.68795
3/1/2017	33.80661
4/1/2017	33.88159
5/1/2017	33.8441
6/1/2017	31.85807
7/1/2017	33.49157
8/1/2017	33.11388
9/1/2017	36.22591
########	43.27512
########	42.65676
########	44.17273
1/1/2018	46.06749
2/1/2018	47.16798
3/1/2018	50.17598
4/1/2018	49.73279
5/1/2018	53.18191
6/1/2018	48.16903
7/1/2018	46.60894
8/1/2018	46.92871
9/1/2018	46.10273
########	45.70302
########	48.07202
########	46.0397
1/1/2019	46.22609
2/1/2019	51.9553
3/1/2019	53.01519
4/1/2019	50.38911
5/1/2019	43.47838
6/1/2019	47.54896
7/1/2019	50.21099
8/1/2019	47.09205

(v) Tesla:

Date	Adj Close
8/1/2015	249.06
9/1/2015	248.4
########	206.93
########	230.26
########	240.01
1/1/2016	191.2
2/1/2016	191.93
3/1/2016	229.77
4/1/2016	240.76
5/1/2016	223.23
6/1/2016	212.28
7/1/2016	234.79
8/1/2016	212.01
9/1/2016	204.03
########	197.73
########	189.4
########	213.69
1/1/2017	251.93
2/1/2017	249.99
3/1/2017	278.3
4/1/2017	314.07
5/1/2017	341.01
6/1/2017	361.61
7/1/2017	323.47
8/1/2017	355.9
9/1/2017	341.1
########	331.53
########	308.85
########	311.35
1/1/2018	354.31
2/1/2018	343.06
3/1/2018	266.13
4/1/2018	293.9
5/1/2018	284.73
6/1/2018	342.95
7/1/2018	298.14
8/1/2018	301.66
9/1/2018	264.77
########	337.32
########	350.48
########	332.8
1/1/2019	307.02
2/1/2019	319.88
3/1/2019	279.86
4/1/2019	238.69
5/1/2019	185.16
6/1/2019	223.46
7/1/2019	241.61
8/1/2019	225.61

Answer 1

Monthly return for a particular month = Price in that month / Price in the previous month - 1.

This is in Column D.

Use excel function AVERAGE and STDEV with inputs as the monthly returns to calculate the average monthly and standard deviation respectively.

(iii) Chevron:

Cell D53 = AVERAGE(D5:D52)

1.28%

Cell D54 = STDEV(D5:D52)

5.71%

(iv) Intel:

Cell D107 = AVERAGE(D59:D106)

1.50%

Cell D108 = STDEV(D59:D106)

6.38%

(v) Tesla:

Cell D161 = AVERAGE(D113:D160)

0.48%

Cell D162 = STDEV(D113:D160)

11.83%

Can you please upvote? Thank You :-)

(iii) Chevron:

(iii) Chevron: Date Adj Close Monthly return 8/1/2015 67.98351 9/1/2015 67.04664 -1.38% ######## 77.24643 15.21% ######## 77.62042 0.48% ######## 77.39832 -0.29% 1/1/2016 74.39566 -3.88% 2/1/2016 71.78875 -3.50% 3/1/2016 83.11975 15.78% 4/1/2016 89.02702 7.11% 5/1/2016 87.99893 -1.15% 6/1/2016 92.30142 4.89% 7/1/2016 90.23226 -2.24% 8/1/2016 88.55934 -1.85% 9/1/2016 91.57452 3.40% ######## 93.20279 1.78% ######## 99.26207 6.50% ######## 105.7736 6.56% 1/1/2017 100.0671 -5.40% 2/1/2017 101.1006 1.03% 3/1/2017 97.41397 -3.65% 4/1/2017 96.80608 -0.62% 5/1/2017 93.88468 -3.02% 6/1/2017 95.61873 1.85% 7/1/2017 100.0729 4.66% 8/1/2017 98.63402 -1.44% 9/1/2017 108.7711 10.28% ######## 107.2807 -1.37% ######## 110.1504 2.67% ######## 116.9746 6.20% 1/1/2018 117.1241 0.13% 2/1/2018 104.5755 -10.71% 3/1/2018 107.6171 2.91% 4/1/2018 118.0636 9.71% 5/1/2018 117.2992 -0.65% 6/1/2018 120.3496 2.60% 7/1/2018 120.1973 -0.13% 8/1/2018 112.7629 -6.19% 9/1/2018 117.5152 4.21% ######## 107.2994 -8.69% ######## 114.3053 6.53% ######## 105.5726 -7.64% 1/1/2019 111.2593 5.39% 2/1/2019 116.0435 4.30% 3/1/2019 120.7387 4.05% 4/1/2019 117.6805 -2.53% 5/1/2019 111.5936 -5.17% 6/1/2019 123.1738 10.38% 7/1/2019 121.8573 -1.07% 8/1/2019 116.5222 -4.38% Average 1.28% Standard Deviation 5.71%

Screenshot with formulas

Monthly return (iii) Chevron: Date 8/1/2015 9/1/2015 ######## ######## ######## 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 ######## ######## ######## 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 ######## ######## ######## 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 ######## ######## ######## 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019 8/1/2019 Adj Close 67.98351 67.04664 77.24643 77.62042 77.39832 74.39566 71.78875 83.11975 89.02702 87.99893 92.30142 90.23226 88.55934 91.57452 93.20279 99.26207 105.7736 100.0671 101.1006 97.41397 96.80608 93.88468 95.61873 100.0729 98.63402 108.7711 107.2807 110.1504 116.9746 117.1241 104.5755 107.6171 118.0636 117.2992 120.3496 120.1973 112.7629 117.5152 107.2994 114.3053 105.5726 111.2593 116.0435 120.7387 117.6805 111.5936 123.1738 121.8573 116.5222 Average Standard Deviation =C5/C4-1 =C6/C5-1 =C7/C6-1 =C8/C7-1 =C9/C8-1 =C10/09-1 =C11/C10-1 =C12/C11-1 =C13/C12-1 =C14/C13-1 =C15/C14-1 =C16/C15-1 =C17/016-1 =C18/C17-1 =C19/C18-1 =C20/C19-1 =C21/C20-1 =C22/C21-1 =C23/C22-1 =C24/C23-1 =C25/C24-1 =C26/C25-1 =C27/C26-1 =C28/C27-1 =C29/C28-1 =C30/C29-1 =C31/C30-1 =C32/C31-1 =C33/C32-1 =C34/C33-1 =C35/C34-1 =C36/C35-1 =C37/C36-1 =C38/C37-1 =C39/C38-1 =C40/C39-1 =C41/C40-1 =C42/C41-1 =C43/C42-1 =C44/C43-1 =C45/C44-1 =C46/C45-1 =C47/C46-1 =C48/C47-1 =C49/C48-1 =C50/C49-1 =C51/C50-1 =C52/C51-1 =AVERAGE(D5:052) =STDEV(D5:052)

(iv) Intel:

(iv) Intel: Date Adj Close 8/1/2015 25.32108 9/1/2015 26.96275 ######## 30.29062 ######## 31.10468 ######## 31.03551 1/1/2016 27.94547 2/1/2016 26.65721 3/1/2016 29.40016 4/1/2016 27.51891 5/1/2016 28.70946 6/1/2016 30.06661 7/1/2016 31.95494 8/1/2016 32.89911 9/1/2016 34.86641 ######## 32.2064 ######## 32.04939 ######## 33.7531 1/1/2017 34.26493 2/1/2017 33.68795 3/1/2017 33.80661 4/1/2017 33.88159 5/1/2017 33.8441 6/1/2017 31.85807 7/1/2017 33.49157 8/1/2017 33.11388 9/1/2017 36.22591 ######## 43.27512 ######## 42.65676 ######## 44.17273 1/1/2018 46.06749 2/1/2018 47.16798 3/1/2018 50.17598 4/1/2018 49.73279 5/1/2018 53.18191 6/1/2018 48.16903 7/1/2018 46.60894 8/1/2018 46.92871 9/1/2018 46.10273 ######## 45.70302 ######## 48.07202 ######## 46.0397 1/1/2019 46.22609 2/1/2019 51.9553 3/1/2019 53.01519 4/1/2019 50.38911 5/1/2019 43.47838 6/1/2019 47.54896 7/1/2019 50.21099 8/1/2019 47.09205 Average Standard Deviation 6.48% 12.34% 2.69% -0.22% -9.96% -4.61% 10.29% -6.40% 4.33% 4.73% 6.28% 2.95% 5.98% -7.63% -0.49% 5.32% 1.52% -1.68% 0.35% 0.22% -0.11% -5.87% 5.13% -1.13% 9.40% 19.46% -1.43% 3.55% 4.29% 2.39% 6.38% -0.88% 6.94% -9.43% -3.24% 0.69% -1.76% -0.87% 5.18% -4.23% 0.40% 12.39% 2.04% -4.95% -13.71% 9.36% 5.60% -6.21% 1.50% 6.38% 105 106 107 108

Screenshot with formulas

(iv) Intel: Date 8/1/2015 9/1/2015 ######## ######## ######## 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 ######## ######## ######## 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 ######## ######## ######## 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 ######## ######## ######## 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019 8/1/2019 Adj Close 25.32108 26.96275 30.29062 31.10468 31.03551 27.94547 26.65721 29.40016 27.51891 28.70946 30.06661 31.95494 32.89911 34.86641 32.2064 32.04939 33.7531 34.26493 33.68795 33.80661 33.88159 33.8441 31.85807 33.49157 33.11388 36.22591 43.27512 42.65676 44.17273 46.06749 47.16798 50.17598 49.73279 53.18191 48.16903 46.60894 46.92871 46.10273 45.70302 48.07202 46.0397 46.22609 51.9553 53.01519 50.38911 43.47838 47.54896 50.21099 47.09205 Average Standard Deviation =C59/C58-1 =C60/C59-1 =C61/C60-1 =C62/C61-1 =C63/C62-1 =C64/C63-1 =C65/C64-1 =C66/C65-1 =C67/C66-1 =C68/C67-1 =C69/C68-1 =C70/C69-1 =C71/C70-1 =C72/C71-1 =C73/C72-1 =C74/C73-1 =C75/C74-1 =C76/C75-1 =C77/C76-1 =C78/C77-1 =C79/C78-1 =C80/C79-1 =C81/080-1 =C82/C81-1 =C83/C82-1 =C84/C83-1 =C85/C84-1 =C86/C85-1 =C87/C86-1 =C88/C87-1 =C89/C88-1 =C90/C89-1 =C91/C90-1 =C92/C91-1 =C93/C92-1 =C94/C93-1 =C95/C94-1 =C96/C95-1 =C97/C96-1 =C98/C97-1 =C99/C98-1 =C100/C99-1 =C101/C100-1 =C102/C101-1 =C103/C102-1 =C104/C103-1 =C105/C104-1 =C106/C105-1 =AVERAGE(D59:D106) =STDEV(D59:D106) 99 100 101 102 103 104 105 106 107 108

(v) Tesla:

109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 (v) Tesla: Date Adj Close 8/1/2015 249.06 9/1/2015 248.4 ######## 206.93 ######## 230.26 ######## 240.01 1/1/2016 191.2 2/1/2016 191.93 3/1/2016 229.77 4/1/2016 240.76 5/1/2016 223.23 6/1/2016 212.28 7/1/2016 234.79 8/1/2016 212.01 9/1/2016 204.03 ######## 197.73 ######## 189.4 ######## 213.69 1/1/2017 251.93 2/1/2017 249.99 3/1/2017 278.3 4/1/2017 314.07 5/1/2017 341.01 6/1/2017 361.61 7/1/2017 323.47 8/1/2017 355.9 9/1/2017 341.1 ######## 331.53 308.85 ######## 311.35 1/1/2018 354.31 2/1/2018 343.06 3/1/2018 266.13 4/1/2018 293.9 5/1/2018 284.73 6/1/2018 342.95 7/1/2018 298.14 8/1/2018 301.66 9/1/2018 264.77 ######## 337.32 ######## 350.48 ######## 332.8 1/1/2019 307.02 2/1/2019 319.88 3/1/2019 279.86 4/1/2019 238.69 5/1/2019 185.16 6/1/2019 223.46 7/1/2019 241.61 8/1/2019 225.61 Average Standard Deviation -0.26% -16.69% 11.27% 4.23% -20.34% 0.38% 19.72% 4.78% -7.28% -4.91% 10.60% -9.70% -3.76% -3.09% -4.21% 12.82% 17.90% -0.77% 11.32% 12.85% 8.58% 6.04% -10.55% 10.03% -4.16% -2.81% -6.84% 0.81% 13.80% -3.18% -22.42% 10.43% -3.12% 20.45% -13.07% 1.18% -12.23% 27.40% 3.90% -5.04% -7.75% 4.19% -12.51% -14.71% -22.43% 20.68% 8.12% -6.62% 0.48% 11.83% 139 ###### ## #TTT 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 158 161 162 102

Screenshot with formulas

109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 (v) Tesla: Date 8/1/2015 9/1/2015 ######## ######## ######## 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 ######## ######## ######## 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 ######## ######## ######## 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 ######## ######## ######## 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019 8/1/2019 136 137 138 Adj Close 249.06 248.4 206.93 230.26 240.01 191.2 191.93 229.77 240.76 223.23 212.28 234.79 212.01 204.03 197.73 189.4 213.69 251.93 249.99 278.3 314.07 341.01 361.61 323.47 355.9 341.1 331.53 308.85 311.35 354.31 343.06 266.13 293.9 284.73 342.95 298.14 301.66 264.77 337.32 350.48 332.8 307.02 319.88 279.86 238.69 185.16 223.46 241.61 225.61 Average Standard Deviation =C113/C112-1 =C114/C113-1 =C115/C114-1 =C116/C115-1 =C117/C116-1 =C118/C117-1 =C119/C118-1 =C120/C119-1 =C121/C120-1 =C122/C121-1 =C123/C122-1 =C124/C123-1 =C125/C124-1 =C126/C125-1 =C127/C126-1 =C128/C127-1 =C129/C128-1 =C130/C129-1 =C131/C130-1 =C132/C131-1 =C133/C132-1 =C134/C133-1 =C135/C134-1 =C136/C135-1 =C137/C136-1 =C138/C137-1 =C139/C138-1 =C140/C139-1 =C141/C140-1 =C142/C141-1 =C143/C142-1 =C144/C143-1 =C145/C144-1 =C146/C145-1 =C147/C146-1 =C148/C147-1 =C149/C148-1 =C150/C149-1 =C151/C150-1 =C152/C151-1 =C153/C152-1 =C154/C153-1 =C155/C154-1 =C156/C155-1 =C157/C156-1 =C158/C157-1 =C159/C158-1 =C160/C159-1 =AVERAGE(D113:D160) =STDEV(D113:D160) 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 102