Project $2$: Historical Simulation

Complete your work using the excel file $"His{t}_{S}imulatio{n}_P$roject$2"$

and submit $it$ via Project$2$ Dropbox.

Find the attached file named $"His{t}_{S}imulatio{n}_P$roject$2."$ You will use this file

$to$ calculate VaR under the historical simulation approach. The first worksheet

"Historical data" shows historical data for $501$ trading days for three indices,

$S$&$P500$ index, DJIA index, and BofA Merrill $LynchUS$ Corporate Index Total

Return Value. The value $of$ the investment $in$ each index $on$ December $28,$

$2020isas$ follows:

$Goto$ the second worksheet $"500$ Scenarios" and calculate the values

$of$ the three indices $on\frac{12}{29}\frac{?}{2020}$ for $500$ scenarios. For example,

Scenario $1in$ the first row shows the values $of$ the indices $on$

$\frac{12}{29}\frac{?}{2020},$ assuming that their percentage changes between

$\frac{12}{28}\frac{?}{2020}$ and$\frac{12}{29}\frac{?}{2020}$ are the same $as$ they were between

$\frac{01}{02}\frac{?}{2019}$ and$\frac{01}{03}\frac{?}{2019.}$ You will repeat this for Days $1$ through

$500.$

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 $1is$

$7,000\frac{3,863\mathrm{.}62}{3,735\mathrm{.}36}+5,000\frac{31,334\mathrm{.}47}{30,335\mathrm{.}67}+3,000\frac{3,534\mathrm{.}86}{3,554\mathrm{.}400}=$$$15,388\mathrm{.}48315,388\mathrm{.}4831\%of$ the $500$

losses$99\%$ VaR $of$ your portfolio?

What $is$ the one$-$day $99\%$ expected shortfall based $on$ the loss

distribution $in$ Question $3?$