Value at Risk & Expected Shortfall: Historical, Parametric & Stress Tests

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn 60 days of fund NAV into simple and log returns, measure the daily volatility and annualise it.
• Apply 250 past daily returns to today's portfolio, read the 95 % and 99 % VaR from the loss tail and count how often the 95 % VaR was exceeded.
• Compute the normal-distribution VaR at 99 % from σ and a z-score and compare it with the historical VaR of the same 250 days.
• Scale each desk's 1-day VaR to 10 days, compare it with the desk limits, set a Breach/Warning/OK status and highlight the breaches.
• Measure how bad the bad days are: historical expected shortfall at 95 % and 99 % with AVERAGEIF, the number of tail days, and the normal ES at 97.5 %.
• Build the covariance matrix of three positions from their volatilities and correlations, compute the portfolio VaR with MMULT and SUMPRODUCT and measure the diversification benefit.
• Compare 250 daily VaR forecasts with the actual P&L, count the exceptions, place the model in a traffic-light zone and run Kupiec's proportion-of-failures test.
• Run four hypothetical stress scenarios on the fund's positions, compare the worst one with the 10-day VaR, bin 250 days of P&L with COUNTIFS and chart the distribution as a histogram.