ov VCAr 1. a. Write down a statistical formula for the beta of a stock. B b. The Excel data file Homework4filef19 contains the stock price NFLX (Netflix) and a proxy for the S&P 500 market index. Convert the prices to returns and ignore dividends. Estimate the volatility of the stock returns. ii) Estimate the volatility of the market returns. ii) Estimate the correlation between the market and the stock. Print your Excel sheet using the data and write the command you used on the sheet. iv) Use parts (i)-(iii) to estimate the covariance between the stock and the market. i) Estimate the beta of the stock using the formula you wrote in part (a). ii) Use a search engine, finance website, or information service to find the beta of the c. stock iii) Explain why your calculation of beta in part c. (i) might be different than your result in part c. (ii). (Hint: see Berk and DeMarzo, Chapter 12, Appendix A). d. Estimate the beta of the market using the formula you wrote in part (a). Evaluate. fx A3 D F G A Data from Yaho0 Finance 1 ISPY Adj Close NFLX 3 Date Adj Close 10/1/14 182.6335 56.11 5 11/1/14 187.6508 49.51286 12/1/14 186.1474 48.80143 6 7 1/1/15 181.6289 63.11429 8 2/1/15 191.8373 67.84428 9 3/1/15 187.9852 59.52714 10 4/1/15 190.6813 5/1/15 193.1327 89.15143 6/1/15 188.2938 93.84857 7/1/15 193.4839 11 79.5 12 13 14 114.31 15 8/1/15 183.0605 115.03 16 9/1/15 176.1393 103.26 17 10/1/15 193.0022 108.38 18 11/1/15 192.8174 123.33 19 12/1/15 188.364 114.38 20 1/1/16 180.0512 91.84 21 2/1/16 179.9025 93.41 22 3/1/16 191.0186 102.23 23 4/1/16 192.7606 90.03 24 5/1/16 196.0397 6/1/16 195.7034 102.57 25 91.48 26 7/1/16 203.8958 91.25 27 8/1/16 204.1399 97.45 28 9/1/16 203.1257 10/1/16 200.6124 98.55 29 124.87 30 11/1/16 208.0027 117 31 12/1/16 210.9758 123.8 32 1/1/17 216.0169 2/1/17 224.5045 3/1/17 223.8114 4/1/17 227.0164 140.71 33 142.13 34 147.81 35 152.2 36 5/1/17 230.2202 6/1/17 230.5635 7/1/17 163.07 37 149.41 38 236.45 181.66 homework4answer 22 Home Insert Draw Page Layout Formulas Data Review V X Calibri (Body) A A 11 Paste BIU AV fx A3 A D E F G H 31 12/1/16 210.9758 123.8 32 1/1/17 216.0169 2/1/17 224.5045 140.71 33 142.13 34 3/1/17 223.8114 4/1/17 227.0164 147.81 35 152.2 36 5/1/17 230.2202 6/1/17 230.5635 163.07 37 149.41 38 7/1/17 236.45 8/1/17 237.1399 181.66 39 174.71 9/1/17 240.7235 40 181.35 41 10/1/17 247.6187 11/1/17 255.1874 196.43 42 195.51 43 12/1/17 256.9689 191.96 1/1/18 272.8389 2/1/18 262.9184 270.3 45 291.38 46 3/1/18 254.6916 4/1/18 257.0332 295.35 47 312.46 48 5/1/18 263.2814 351.6 49 6/1/18 263.6118 391.43 50 7/1/18 274.6053 337.45 51 8/1/18 283.3706 9/1/18 283.7709 367.68 52 374.13 53 10/1/18 265.3569 301.78 54 11/1/18 270.2791 12/1/18 245.0504 286.13 55 267.66 56 1/1/19 266.2161 2/1/19 274.8457 3/1/19 278.5934 339.5 57 358.1 58 356.56 59 4/1/19 291.2519 5/1/19 272.6784 370.54 60 343.28 61 6/1/19 290.2415 7/1/19 296.0628 367.32 62 322.99 63 8/1/19 291.1057 9/1/19 295.4058 10/1/19 293.75 64 267.62 65 298.88 284.25 66 67 homework4answer +
