Historical simulation and var

Question

Problem:  There are two questions in this hands-on assignment.

Instructions: Type your answers clearly below each question, wherever applicable. You may also use this document to provide any additional information, wherever needed. Submit this document along with an Excel sheet to complete the submission.

Question 1:  Historical Simulation and VaR

Measures of risk such as the VaR are often used by trading companies in order to limit the risk that a trader can take on at any given point in time. For a trading company, there is the material risk that a trader, trying to capture high returns, might expose the company to a high level of risk. To limit the occurrence of such scenarios, trading companies often impose a maximum level of risk that each trader can take.

Let's consider the following example. A trader has a risk limit given by a 10-day, 1% $VaR (or $VaR_(t,t+10)^0.01) equal to $100,000. This means that, on each day, her position should respect the following limit

$Position * VaR_(t,t+10)^0.01 ≤ $100,000

Now, let's assume that:

1. The trader only invests in the S&P500 Index;

2. The trader uses the historical simulation method to compute the 1-day VaR;

3. The trader computes the 10-day VaR by simply rescaling the 1-day VaR by √10;

4. The trader, on each day, invests the maximum amount of money possible, that is, she chooses how much money to invest (i.e., her position) such that:

$Position = $100,000/(VaR_(t,t+10)^0.01).

Questions:

1. Download daily prices of the S&P500 Index (the ticker is ^GSPC) from either Yahoo finance or Google finance from January 1, 2000, to December 31, 2013.

2. Starting on July 1, 2008, compute the VaR_(t,t+10)^(0.01) using the historical simulation method and using, on each day, data from the previous 200 days.

3. Compute the daily position of the trader.

4. Compute the daily profit of the trader (simply multiply each daily position by the log-return over the next day).

5. Compute the cumulative profit and losses of the trader from July 1, 2008, to December 31, 2009.

6. Plot the VaR_(t,t+10)^(0.01) over the period from July 1, 2008, to December 31, 2013.

7. Plot the cumulative profit of the trader over the period from July 1, 2008, to December 31, 2013.

8. Comment on the results.

Question 2: Autocorrelation Function

Using the same daily prices of the S&P500 Index from January 1, 2000, to December 31, 2013.

1. Compute the daily log-returns (R_t = ln(P_t/P_(t-1))).

2. Compute the squared log-return on each day (R_t²).

3. Compute the sample autocorrelation function (SACF) of the squared returns from 1 lag to 100 lags, that is ρ ^1...ρ ^100.

4. Plot the SACF.

5. What do you notice? Is it different from the SACF of R_t? What does the SACF of R_t^2 tell us? Comment on the results.

Hint: Look into Excel's "=INDIRECT" function to compute the SACF for multiple lags without having to manually input the formula many times.