Problem 7: Go to Yahoo Finance and type the ticker of Tesla (TSLA). Go to historical data and download the data from 03/29/2017 to 03/29/2018 and go to Download Data. Use only the stock price at the ADJ. CLOSE. a. Compute the daily Continuously compounded return: Ln(St) Ln(St-1) This comes from: St = St-1 *eMq*1 Where u is the daily rate of return, hence the return over one day is u * 1. Taking logs and solving for u is: In(St) - In(St-1) b. Compute the average CC daily return over the last year, call this quantity = &t=1 Mt with n being the number of observations. You can use the function in excel =AVERAGE Mt = c. Compute the variance of the CC daily returns over the last year: n 02 = (4 )? . (4) t=1 Do it manually!!!! And the verify your result using the function =VAR.SO d. Compute the daily correlation of returns To do so, you have to reorganize the data. Below is a screenshot of what the data would look like: Log Returns Lagged Log Returns Date 3/29/2017 3/30/2017 3/31/2017 4/3/2017 4/4/2017 4/5/2017 4/6/2017 Adj Close 277.38 277.92 278.3 298.52 303.7 295 298.7 0.19% 0.14% 7.01% 1.72% -2.91% 1.25% 0.19% 0.14% 7.01% 1.72% -2.91% e. To compute the correlation you need to use the Excel Function =CORRELO). The first argument should be the Log Returns and the second argument should be the Lagged Log Returns. Now we need to find the yearly return and the yearly variance. Assume there are 250 days in a year. To compute the yearly return, just multiply * 250. To compute the yearly variance, use the table below. The y axis is the correlation of returns. The x axis is the aggregation. In our case, we have a daily variance and need to compute yearly variance. Since we assumed 250 days in a year, the aggregation in our case is q=250. Compute the variance: 02 * 17.47 * 17.47 Correlation (%) Aggregation Value, q 12 24 2 3 4 6 36 48 125 250 1.21 90 80 70 60 50 40 30 20 1.77 2.42 3 1.07 1.14 1.21 1.3 1.39 1.49 1.6 1.73 1.86 1.1 1.21 1.33 1.46 1.6 1.75 10 0 1.03 1.05 1.08 1.12 1.15 1.2 1.24 1.29 1.35 1.41 1.49 1.58 1.69 1.83 2 2.24 2.58 3.16 4.47 1.05 1.1 1.15 1.21 1.28 1.35 1.43 1.52 1.62 1.73 1.85 1.99 2.13 2.29 2.45 2.61 2.76 2.89 2.97 2 1.43 1.65 1.89 2.12 2.36 2.61 2.88 3.16 3.46 3.8 4.17 4.6 5.09 5.69 6.44 7.45 8.96 12.06 1.41 1.81 2.19 2.55 2.91 3.27 3.65 4.04 4.45 4.9 5.39 5.95 6.59 7.34 8.26 9.44 11.05 13.5 18.29 1.91 2.07 2.25 2.45 2.66 2.9 3.17 3.48 3.84 4.3 4.92 5.91 8.09 1.6 2.14 2.62 3.08 3.53 3.98 4.44 4.93 5.44 6 6.61 7.31 8.1 9.05 10.21 11.7 13.77 16.98 23.32 2.67 3.79 4.75 5.63 6.49 7.35 8.23 9.14 10.12 11.18 12.35 13.67 15.2 17.01 19.26 22.19 26.33 32.96 46.99 -10 -20 -30 -40 -50 -60 -70 -80 -90 3.7 5.32 6.68 7.94 9.15 10.37 11.62 12.92 14.31 15.81 17.47 19.35 21.52 24.11 27.31 31.5 37.43 47.02 67.65 3.53 4.06 4.58 5.12 5.68 6.28 6.93 7.64 8.45 9.38 10.48 11.84 13.59 16.04 19.88 27.61 2.16 2.33 2.53 2.75 3.02 3.37 3.86 4.66 6.47 Problem 7: Go to Yahoo Finance and type the ticker of Tesla (TSLA). Go to historical data and download the data from 03/29/2017 to 03/29/2018 and go to Download Data. Use only the stock price at the ADJ. CLOSE. a. Compute the daily Continuously compounded return: Ln(St) Ln(St-1) This comes from: St = St-1 *eMq*1 Where u is the daily rate of return, hence the return over one day is u * 1. Taking logs and solving for u is: In(St) - In(St-1) b. Compute the average CC daily return over the last year, call this quantity = &t=1 Mt with n being the number of observations. You can use the function in excel =AVERAGE Mt = c. Compute the variance of the CC daily returns over the last year: n 02 = (4 )? . (4) t=1 Do it manually!!!! And the verify your result using the function =VAR.SO d. Compute the daily correlation of returns To do so, you have to reorganize the data. Below is a screenshot of what the data would look like: Log Returns Lagged Log Returns Date 3/29/2017 3/30/2017 3/31/2017 4/3/2017 4/4/2017 4/5/2017 4/6/2017 Adj Close 277.38 277.92 278.3 298.52 303.7 295 298.7 0.19% 0.14% 7.01% 1.72% -2.91% 1.25% 0.19% 0.14% 7.01% 1.72% -2.91% e. To compute the correlation you need to use the Excel Function =CORRELO). The first argument should be the Log Returns and the second argument should be the Lagged Log Returns. Now we need to find the yearly return and the yearly variance. Assume there are 250 days in a year. To compute the yearly return, just multiply * 250. To compute the yearly variance, use the table below. The y axis is the correlation of returns. The x axis is the aggregation. In our case, we have a daily variance and need to compute yearly variance. Since we assumed 250 days in a year, the aggregation in our case is q=250. Compute the variance: 02 * 17.47 * 17.47 Correlation (%) Aggregation Value, q 12 24 2 3 4 6 36 48 125 250 1.21 90 80 70 60 50 40 30 20 1.77 2.42 3 1.07 1.14 1.21 1.3 1.39 1.49 1.6 1.73 1.86 1.1 1.21 1.33 1.46 1.6 1.75 10 0 1.03 1.05 1.08 1.12 1.15 1.2 1.24 1.29 1.35 1.41 1.49 1.58 1.69 1.83 2 2.24 2.58 3.16 4.47 1.05 1.1 1.15 1.21 1.28 1.35 1.43 1.52 1.62 1.73 1.85 1.99 2.13 2.29 2.45 2.61 2.76 2.89 2.97 2 1.43 1.65 1.89 2.12 2.36 2.61 2.88 3.16 3.46 3.8 4.17 4.6 5.09 5.69 6.44 7.45 8.96 12.06 1.41 1.81 2.19 2.55 2.91 3.27 3.65 4.04 4.45 4.9 5.39 5.95 6.59 7.34 8.26 9.44 11.05 13.5 18.29 1.91 2.07 2.25 2.45 2.66 2.9 3.17 3.48 3.84 4.3 4.92 5.91 8.09 1.6 2.14 2.62 3.08 3.53 3.98 4.44 4.93 5.44 6 6.61 7.31 8.1 9.05 10.21 11.7 13.77 16.98 23.32 2.67 3.79 4.75 5.63 6.49 7.35 8.23 9.14 10.12 11.18 12.35 13.67 15.2 17.01 19.26 22.19 26.33 32.96 46.99 -10 -20 -30 -40 -50 -60 -70 -80 -90 3.7 5.32 6.68 7.94 9.15 10.37 11.62 12.92 14.31 15.81 17.47 19.35 21.52 24.11 27.31 31.5 37.43 47.02 67.65 3.53 4.06 4.58 5.12 5.68 6.28 6.93 7.64 8.45 9.38 10.48 11.84 13.59 16.04 19.88 27.61 2.16 2.33 2.53 2.75 3.02 3.37 3.86 4.66 6.47