Suppose that an FI owns a $10 million portfolio with 4m invested in DJIA, 3m invested in FTSE 100, 1m invested in CAC 40 and 2m invested in Nikkei 225. Suppose that the 250 days ending September 9, 2008, constitute the stress period for the portfolio. Calculate the 97.5% expected shortfall using the overlapping periods method in conjunction with historical simulation and the cascade approach. For the purpose of this problem, assume that DJIA and FTSE have a 10-day liquidity horizon, CAC 40 has a 40-day liquidity horizon, and Nikkei 225 has a 20-day liquidity horizon. For each day during the stressed period, consider the change in a variable over a 10-day period ending on that day. You may want to break down the problem in the following steps.

a. Estimate the 10-day 97.5% expected shortfall when 10-day changes are made to all market variables. We will denote this by ES1.

b. Estimate the ES when 10-day changes are made to all market variables with 20-day and 40-day liquidity horizons. We will denote this by ES2.

c. Estimate the ES when 10-day changes are made only to market variables with 40-day liquidity horizon. We will denote this by ES3.

d. Aggregate the expected shortfalls ES1, ES2 and ES3. We denote this as the liquidity-adjusted ES.

link to data

https://docs.google.com/spreadsheets/d/1_jx06Ripy277yxIndFFyUBXckZtvlwgnijJMGKOf29I/edit?usp=sharing