2. (Spreadsheet Problem) Use the adjusted closing prices for the ETFs SPY (which tracks the S&P 500) and VB (the Vanguard Small-Cap Index Fund ETF) in the spreadsheet "Assignment 7 Problem 2 Data 2021" on D2L Suppose that your portfolio consists of $250.000 invested in SPY and $100,000 in VB index. I suggest doing all the calculations in thousands of dollars (ie, with investments of $250 and $100). The 251 closing prices yield 250 daily returns. Determine the following: (2) Compute the one-day VaR and Eggs as shown in your chapter 13 slides (see also Section 13.1. but follow the procedure in you PP slides for computing ES). Note that VaRos corresponds to the 0.02>250 = 5th worst loss Paste the sorted 5 worst losses, along with the date that they occurred into your solutions. (b) Compute the one-day VaRon using the weighting of observations procedure in Section 133. (You should also know how to compute ES, but I will not require it for this assignment) Note that the weights do not change the returns, only the probability of each return Sort the losses, together with the weights and date, and determine in the cumulative weight hits or exceeds 2% For the weighting scheme, let 1-0.995. Double check that the 250 weights (corresponding to the 250 returns) add to one. Paste the sorted 10 worst losses, along with the date that they occurred and the corresponding weights, into your solutions. (c) (3 points extra credit - solution must be precisely correct) Compute the one-day VaRoss using the volatility-scaling procedure in your chapter 13 PP slides and in Section 13.3 of the text. Use the EWMA procedure as in (10.8) in chapter 10 (the same as you did for in Assignment problem 1). Assume a starting daily variance of 0.022 for the SPY returns and 0.042 for the VB returns. These starting values are the predicted variances for the 3/31/20 returns (i.e., the return computed from the 3/30/20 close of trading to 3/31/20 close). Let i=0.94 for both volatility estimates. The last calculated variance will be based on the Friday, 3/26/2021 return and will give the daily variance estimate for "tomorrow." which we assume is Monday, 3/29/2021 (we are pretending today is Friday, 3/26/2021). To check your work, your first two variance estimates (the first one is the starting value) should be SPY VB 3/31/2020 0.0004 0.0016 4/1/2020 0.00038933 0.00151202 Note that the 4/1/2020 variance estim is a weighted average of the 3/31/2020 squared return and the 3/31/2020 variance estimate Each day d return is scaled by the ratio of the 3/29/2021 volatility estimate (the square root of tomorrow's" variance estimate) to the day-d volatility estimate. Once you have the scaled returns you can repeat the procedure that you used in (a) to find the VaRes. Paste the sorted 10 worst volatility-adjusted losses, along with the date that they occurred into your solutions