1. Find the attached file named Hist_Simulation. You will use this file to calculate VaR under the historical simulation approach. The first worksheet data shows historical data for 501 trading days for three indices, S&P 500 index, DJIA index, and BofA Merrill Lynch Total Bond Return Index. The value of the investment in each index on May 20, 2020 is as follows:

Index Portfolio Value (000s)

S&P 4,000

DJIA 3,000

BofA Merrill Lynch 3,000

Total 10,000

a. Go to the second worksheet 500 Scenarios and calculate the values of the three indices on 05/21/2020 for 500 scenarios. For example, Scenario 1 in the first row shows the values of the indices on 05/21/2020, assuming that their percentage changes between 05/20/2020 and 05/21/2020 are the same as they were between 05/23/2018 and 05/24/2018. You will repeat this for Days 1 through 500.

b. Once you calculate the values of the indices for 500 scenarios, you can calculate the value of your portfolio. For example, the value of the portfolio under Scenario 1 is

4,000 2,964.61/ 2,971.61 + 3,000 24,517.79 /24,575.9 + 3,000 1,299.99 /1,300.520 = 9,982.262

Therefore, a loss from the portfolio under Scenario 1 is (10,000 9.982.262) 1,000 = $17,738. Please calculate the values of the indices/portfolio and losses for 500 scenarios..

c. Copy the portfolio losses to the third worksheet Ranked Losses_VaR and ES. Since the losses in the second worksheet include equations, you should copy the values only (copy the values of losses, go to the third worksheet, right-click on a cell, and click Paste Special and Values). Rank the losses and find the fifth worst loss (1% of the 500 losses). What is the one-day 99% VaR of your portfolio?

d. What is the one-day 99% expected shortfall based on the loss distribution in Question 3?

AutoSave OCH * 2 Hist Simulation HW2 (2) - Excel Search A Nguyen, Lion T NL ca File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments X Calibri = = - as Number Conditional Formatting Insert W 4 LIS B 1 u A A = $ 2 Format as Table SK Delete Ideas Sensitivity Paste % -23 FFT A 28 Cell Styles Format Clipboard Font Alignment Number 5 Styles celo Editing Ideas Sensitivity B6 > f F G 1 1 M N 0 P Q R S T . B D 1 Scenario S&P 500 DJIA BofA 2 1 2961.61 21517.79 1299.99 3 4 F Portfolio Value ('000s) 9982.262 H Loss ('000s) 17.738 Portfolio value as of 05/20/2020 Portfolio S&P 500 DJIA Investments 4000 3000 Bofa 3000 5 6 This worksheet calculates the value of the portfolio on 05/21/2020 for the 500 scenarios. Culurrin shows the loss betweeri 05/20/2020 and 05/21/2020. 7 Day 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Historical Data Date S&P 500 DIA BofA 485 4/29/2020 2939.51001 24633.85928 1268.690 486 4/30/2020 2912.429932 24345.7207 1272.770 187 5/1/2020 2830.709961 23723.68915 1269.220 488 5/4/2020 2842.73999 23749.75977 1267.260 489 5/5/2020 2868,430941 23883.08984 1274.820 490 5/6/2020 2948.419922 23664.64063 1276.310 491 5/7/2020 2881.189941 23875.89063 1279.230 192 5/8/2020 2929.8000:19 24331.32031 1281.570 493 5/11/2020 2930.189941 24221.99023 1281.510 494 5/12/2020 2870.120117 23764.7793 1287.560 495 5/13/2020 2820 23247.9707 1281.540 496 5/14/2020 2852.5 23625.33984 1273.060 197 5/15/2020 2863.699951 23685.41992 1272.210 498 5/18/2020 2953.909912 24597.36914 1286.820 499 5/19/2020 2922.939941 24206.85938 1290.770 500 5/20/2020 2971.610107 24575.90029 1300.520 Historical data Sheet2 Sheet1 500 Scenarios Ranked Losses_VaR and ES + Display Settings M - + 100 Type here to search O e 00) 3:15 PM 5/29/2020 = AutoSave OCH * 2 Hist Simulation HW2 (2) - Excel Search A Nguyen, Lion T NL ca File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments X Calibri = = - as Number Conditional Formatting Insert W 4 LIS B 1 u A A = $ 2 Format as Table SK Delete Ideas Sensitivity Paste % -23 FFT A 28 Cell Styles Format Clipboard Font Alignment Number 5 Styles celo Editing Ideas Sensitivity B6 > f F G 1 1 M N 0 P Q R S T . B D 1 Scenario S&P 500 DJIA BofA 2 1 2961.61 21517.79 1299.99 3 4 F Portfolio Value ('000s) 9982.262 H Loss ('000s) 17.738 Portfolio value as of 05/20/2020 Portfolio S&P 500 DJIA Investments 4000 3000 Bofa 3000 5 6 This worksheet calculates the value of the portfolio on 05/21/2020 for the 500 scenarios. Culurrin shows the loss betweeri 05/20/2020 and 05/21/2020. 7 Day 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Historical Data Date S&P 500 DIA BofA 485 4/29/2020 2939.51001 24633.85928 1268.690 486 4/30/2020 2912.429932 24345.7207 1272.770 187 5/1/2020 2830.709961 23723.68915 1269.220 488 5/4/2020 2842.73999 23749.75977 1267.260 489 5/5/2020 2868,430941 23883.08984 1274.820 490 5/6/2020 2948.419922 23664.64063 1276.310 491 5/7/2020 2881.189941 23875.89063 1279.230 192 5/8/2020 2929.8000:19 24331.32031 1281.570 493 5/11/2020 2930.189941 24221.99023 1281.510 494 5/12/2020 2870.120117 23764.7793 1287.560 495 5/13/2020 2820 23247.9707 1281.540 496 5/14/2020 2852.5 23625.33984 1273.060 197 5/15/2020 2863.699951 23685.41992 1272.210 498 5/18/2020 2953.909912 24597.36914 1286.820 499 5/19/2020 2922.939941 24206.85938 1290.770 500 5/20/2020 2971.610107 24575.90029 1300.520 Historical data Sheet2 Sheet1 500 Scenarios Ranked Losses_VaR and ES + Display Settings M - + 100 Type here to search O e 00) 3:15 PM 5/29/2020 =