500 Days 239 74 0 243 84 242 64 239 37 238 72 235 58 0 238 83 240 08 244 14 246 72 236 08 0 235 45 234 21 241 2 235 6 0 234 3 233 59 242 84 237 41 223 71 0 214 65 209 98 222 11 222 18 219 27 0 219 96 218 51 205 66 200 84 197 36 0 207 3 205 76 212 42 216 52 212 08 0 211 99 220 11 242 68 254 85 253 92 0 251 12 258 87 262 99 263 62 259 67 0 260 53 260 05 261 16 246 53 251 6 0 250 22 246 38 240 5 244 12 246 99 0 244 88 255 7 262 59 266 5 265 28 0 274 39 276 04 271 3 267 48 273 58 0 248 5 251 49 251 92 256 49 0 245 01 258 08 256 9 257 18 238 82 0 238 59 230 04 236 86 233 19 231 28 0 215 49 219 22 225 6 232 96 239 76 0 242 65 245 34 242 19 249 7 251 45 0 253 86 259 32 254 11 261 07 267 43 0 266 44 255 71 264 35 265 28 269 06 0 260 02 262 9 291 26 293 34 290 38 281 38 277 9 271 99 269 79 269 61 0 274 43 276 54 282 48 279 82 0 261 77 257 5 256 24 250 21 241 05 0 256 6 264 61 259 46 274 45 0 260 54 255 9 256 79 258 71 249 83 0 244 4 234 86 224 57 221 31 217 61 0 213 97 207 52 203 93 201 16 0 193 17 184 47 182 9 185 77 188 87 0 180 14 176 89 173 86 166 52 166 35 0 167 98 172 08 168 54 169 15 171 79 0 170 06 161 2 160 61 160 31 161 83 0 164 31 160 19 153 75 160 67 162 55 0 165 08 162 99 180 59 184 31 187 04 0 185 185 9 180 54 186 79 184 51 0 185 06 185 52 192 58 194 77 0 207 46 195 28 193 88 189 19 191 81 0 190 41 192 22 191 15 197 58 183 25 0 180 13 184 13 180 45 183 26 174 48 0 173 44 172 92 182 187 71 193 81 0 197 79 190 9 202 77 205 71 207 63 0 196 88 202 07 200 86 197 37 0 208 31 202 04 214 24 209 25 194 64 0 196 89 207 32 201 29 196 81 194 76 0 189 98 188 27 181 41 173 22 166 66 0 177 9 160 27 144 43 143 89 143 75 0 133 42 127 17 128 78 131 49 0 122 4 123 56 123 22 118 85 119 77 0 113 06 110 34 113 64 108 1 0 123 18 121 82 112 71 109 1 0 123 15 125 35 137 57 137 8 149 87 0 150 23 157 67 156 8 160 95 167 82 0 179 05 173 44 174 04 179 82 182 45 0 194 86 194 7 194 7 180 83 182 92 0 182 86 183 2 169 91 167 87 0 180 19 183 17 186 92 194 42 190 95 0 195 97 190 72 177 59 191 3 197 08 0 207 47 215 31 214 98 227 82 227 54 0 228 52 225 09 224 64 222 415 211 25 0 214 44 207 28 222 04 220 19 219 35 0 204 99 221 72 217 24 216 5 222 96 0 223 07 238 13 240 81 249 44 242 4 0 265 25 268 21 287 81 282 94 276 01 0 275 33 288 59 300 8 308 73 309 07 0 303 35 303 75 302 61 292 13 304 42 0 299 68 289 26 283 7 274 42 0 270 21 277 16 275 61 277 7 284 82 0 288 09 296 07 297 0967 296 4533 289 9133 0 296 6667 302 87 303 9967 306 5633 309 32 0 300 03 286 63 294 3567 283 3333 290 4233 0 288 17 308 6333 307 3967 300 5867 297 2767 0 297 15 280 9 274 82 258 86 268 4333 0 272 2433 271 7067 247 5 245 53 240 5467 0 240 0667 238 3133 237 04 233 07 234 3433 0 250 7633 244 5433 231 7333 233 0667 0 227 2633 224 4733 228 49 232 6633 244 92 0 245 7067 235 07 236 0867 237 0367 0 216 76 213 1 233 220 89 215 7367 0 232 23 239 7067 241 8667 238 8867 238 28 0 234 5167 258 3333 246 79 252 7533 0 253 21 235 91 219 6 209 3867 224 9667 0 221 3 236 4733 236 6033 253 87 241 4567 0 256 53 242 6667 244 6667 266 68 262 37 0 288 55 291 0933 317 54 303 0833 300 98 0 290 2533 292 5033 293 8367 292 14 332 6733 335 0167 336 26 325 7333 342 7167 334 7633 Deliverable 2 Estimating the historical volatility of returns on the underlying stock 1 Using 500 prices (adjusted for dividends), which you downloaded from Bloomberg, calculate daily continuously compounded returns on your stock using the formula 1 rt,t 1 ln(StSt 1) where St is the price on day t 1 Use ln() function in Excel to calculate natural logarithms 2 Use the sample standard deviation function in Excel to calculate historical daily volatility of returns on your stock Since you used daily price data, this is the volatility per day Annualize it

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 22, 2024

500 Days 239.74 0 243.84 242.64 239.37 238.72 235.58 0 238.83 240.08 244.14 246.72 236.08 0 235.45 234.21 241.2 235.6 0 234.3 233.59 242.84 237.41

500 Days
239.74
0
243.84
242.64
239.37
238.72
235.58
0
238.83
240.08
244.14
246.72
236.08
0
235.45
234.21
241.2
235.6
0
234.3
233.59
242.84
237.41
223.71
0
214.65
209.98
222.11
222.18
219.27
0
219.96
218.51
205.66
200.84
197.36
0
207.3
205.76
212.42
216.52
212.08
0
211.99
220.11
242.68
254.85
253.92
0
251.12
258.87
262.99
263.62
259.67
0
260.53
260.05
261.16
246.53
251.6
0
250.22
246.38
240.5
244.12
246.99
0
244.88
255.7
262.59
266.5
265.28
0
274.39
276.04
271.3
267.48
273.58
0
248.5
251.49
251.92
256.49
0
245.01
258.08
256.9
257.18
238.82
0
238.59
230.04
236.86
233.19
231.28
0
215.49
219.22
225.6
232.96
239.76
0
242.65
245.34
242.19
249.7
251.45
0
253.86
259.32
254.11
261.07
267.43
0
266.44
255.71
264.35
265.28
269.06
0
260.02
262.9
291.26
293.34
290.38
281.38
277.9
271.99
269.79
269.61
0
274.43
276.54
282.48
279.82
0
261.77
257.5
256.24
250.21
241.05
0
256.6
264.61
259.46
274.45
0
260.54
255.9
256.79
258.71
249.83
0
244.4
234.86
224.57
221.31
217.61
0
213.97
207.52
203.93
201.16
0
193.17
184.47
182.9
185.77
188.87
0
180.14
176.89
173.86
166.52
166.35
0
167.98
172.08
168.54
169.15
171.79
0
170.06
161.2
160.61
160.31
161.83
0
164.31
160.19
153.75
160.67
162.55
0
165.08
162.99
180.59
184.31
187.04
0
185
185.9
180.54
186.79
184.51
0
185.06
185.52
192.58
194.77
0
207.46
195.28
193.88
189.19
191.81
0
190.41
192.22
191.15
197.58
183.25
0
180.13
184.13
180.45
183.26
174.48
0
173.44
172.92
182
187.71
193.81
0
197.79
190.9
202.77
205.71
207.63
0
196.88
202.07
200.86
197.37
0
208.31
202.04
214.24
209.25
194.64
0
196.89
207.32
201.29
196.81
194.76
0
189.98
188.27
181.41
173.22
166.66
0
177.9
160.27
144.43
143.89
143.75
0
133.42
127.17
128.78
131.49
0
122.4
123.56
123.22
118.85
119.77
0
113.06
110.34
113.64
108.1
0
123.18
121.82
112.71
109.1
0
123.15
125.35
137.57
137.8
149.87
0
150.23
157.67
156.8
160.95
167.82
0
179.05
173.44
174.04
179.82
182.45
0
194.86
194.7
194.7
180.83
182.92
0
182.86
183.2
169.91
167.87
0
180.19
183.17
186.92
194.42
190.95
0
195.97
190.72
177.59
191.3
197.08
0
207.47
215.31
214.98
227.82
227.54
0
228.52
225.09
224.64
222.415
211.25
0
214.44
207.28
222.04
220.19
219.35
0
204.99
221.72
217.24
216.5
222.96
0
223.07
238.13
240.81
249.44
242.4
0
265.25
268.21
287.81
282.94
276.01
0
275.33
288.59
300.8
308.73
309.07
0
303.35
303.75
302.61
292.13
304.42
0
299.68
289.26
283.7
274.42
0
270.21
277.16
275.61
277.7
284.82
0
288.09
296.07
297.0967
296.4533
289.9133
0
296.6667
302.87
303.9967
306.5633
309.32
0
300.03
286.63
294.3567
283.3333
290.4233
0
288.17
308.6333
307.3967
300.5867
297.2767
0
297.15
280.9
274.82
258.86
268.4333
0
272.2433
271.7067
247.5
245.53
240.5467
0
240.0667
238.3133
237.04
233.07
234.3433
0
250.7633
244.5433
231.7333
233.0667
0
227.2633
224.4733
228.49
232.6633
244.92
0
245.7067
235.07
236.0867
237.0367
0
216.76
213.1
233
220.89
215.7367
0
232.23
239.7067
241.8667
238.8867
238.28
0
234.5167
258.3333
246.79
252.7533
0
253.21
235.91
219.6
209.3867
224.9667
0
221.3
236.4733
236.6033
253.87
241.4567
0
256.53
242.6667
244.6667
266.68
262.37
0
288.55
291.0933
317.54
303.0833
300.98
0
290.2533
292.5033
293.8367
292.14
332.6733
335.0167
336.26
325.7333
342.7167
334.7633

Deliverable 2 - Estimating the historical volatility of returns on the underlying stock: 1. Using 500 prices (adjusted for dividends), which you downloaded from Bloomberg, calculate daily continuously compounded returns on your stock using the formula 1 : rt,t+1=ln(StSt+1) where St is the price on day t. 1 Use =ln() function in Excel to calculate natural logarithms. 2. Use the sample standard deviation function in Excel to calculate historical daily volatility of returns on your stock. Since you used daily price data, this is the volatility per day. Annualize it