Value at Risk & Expected Shortfall: Historical, Parametric & Stress Tests
Measure market risk the way risk desks do, cell by cell: daily returns and volatility, historical VaR with PERCENTILE.INC at 95 % and 99 %, parametric VaR with NORM.S.INV, √t scaling to 10 days with limit checks and highlighted breaches, expected shortfall with AVERAGEIF, portfolio VaR from a covariance matrix with MMULT, backtesting with exception counts, traffic lights and Kupiec's test, and stress scenarios with a histogram of the P&L – 8 labs on one fictional fund with synthetic data, every cell checked instantly. Educational only, not investment advice.
The first lesson is free — no account needed.
About this course
A value at risk course that measures the market risk of one fictional fund with synthetic data, cell by cell in a spreadsheet, for risk analysts, FRM and CFA candidates, treasury staff and finance students. You turn 60 days of NAV into simple and log returns and annualised volatility, read the 95% and 99% historical VaR from 250 days of P&L with PERCENTILE.INC, compute parametric VaR with NORM.S.INV and compare the two, scale desk VaR to 10 days with the square-root-of-time rule and flag limit breaches, measure expected shortfall with AVERAGEIF, build a three-position covariance matrix for portfolio VaR with MMULT and the diversification benefit, backtest 250 VaR forecasts with exception counts, a traffic light and Kupiec's test, and run stress scenarios with a P&L histogram. Synthetic data and illustrative scenarios, educational only, not investment advice. The first lab is free.
Lessons
- Daily Returns & Volatility: From NAV to σFree
- Historical VaR: PERCENTILE.INC at 95 % and 99 % Pro
- Parametric VaR: NORM.S.INV × σ × Value Pro
- Scaling VaR to 10 Days (√t) & Checking Limits Pro
- Expected Shortfall (CVaR): The Average Loss Beyond VaR Pro
- Portfolio VaR with Correlations: Covariance Matrix & MMULT Pro
- Backtesting VaR: Exceptions, Traffic Light & Kupiec Test Pro
- Stress Scenarios & the Loss Distribution (Histogram) Pro