Problem $5\mathrm{.}5$

Use the historical simulation method described in class $($Ch$.22\mathrm{.}2$ in Hull, page $496)$ to compute the daily $95\%$ Confidence Level Value$-$at$-$Risk of a portfolio consisting of $1000$ shares of the SPY ETF at the close of the market on $17-$Mar$-2025.$ Denote Pn $L_t=V_{t1}-V_t$ the portfolio PnL $($Price$-$and$-$Loss$)$ for day $t.$

At the market close on $17-$Mar$-2025$ we do not know yet the PnL for the next day, and we would like to estimate its probability distribution, in order to compute the VaR.

Use the daily market data over the previous year $(17-$Mar$-2024$ to $17-$Mar$-2025)$ as inputs to the computation.

You can organize the computation using Excel, by modifying the spreadsheet used in class.