A free value at risk and expected shortfall model in Excel, with the backtest
Parametric VaR built from sleeve volatilities and a correlation matrix one step at a time, expected shortfall beside it, a reported-versus-crisis correlation switch, and the exception count that grades a 99 per cent measure: five sheets, one fictional fund, no macros.
The file follows a 840,000,000 fixed-income fund split across four sleeves: investment-grade credit, high yield, leveraged loans and structured credit. Their volatilities and a reported correlation matrix combine into a one-day 99% value at risk of 7,242,456, 0.862% of net assets, scaled by the square root of time to 32,389,246 over twenty days. Diversification is worth 0.767 of a point of volatility, 11.5% of the undiversified figure, and only 27.6% of that benefit survives when the correlations are switched to their crisis values. Every one of those numbers is a formula you can move.
Download the template
One Excel file, no macros, nothing locked. No account, no email address.
Five sheets, in the order the calculation runs. Yellow cells are inputs; everything else is a formula.
Read me
What the workbook does, the result worth sitting with, and what changed in this revision.
Inputs
Net assets, trading days in a year, the VaR and expected-shortfall confidence levels, the backtest sample size and exceptions observed, the reported-versus-crisis correlation switch, and the four sleeves with their market value, weight and volatility.
VaR Three Ways
The reported and crisis correlation matrices side by side, the variance-covariance grid built sleeve by sleeve, portfolio volatility, the one-day and twenty-day value at risk, expected shortfall and its multiplier, the horizon scaling table, and the diversification benefit computed under both matrices at once.
The Backtest
Exceptions expected against exceptions observed, the zone the count falls in, and the green, amber and red thresholds drawn by the binomial distribution rather than typed in, at both 500 and 250 observations.
Checks
Every figure the book prints set against the live cell that computes it, with a pass or fail verdict: 18 checks, all passing.
What the worked case shows
Measure
Value
Net assets
840,000,000
One-day value at risk, 99% confidence
7,242,456
Value at risk as a share of net assets
0.862%
Twenty-day value at risk (square-root-of-time scaling)
Exceptions observed, 500 days (green zone ends at 8)
6, green
Read the last three lines together. Diversification is worth 11.5% of undiversified volatility on the reported correlation matrix, but three-quarters of that benefit disappears once the correlations are set to their crisis values: only 27.6% of it survives, because the sleeves that looked separate stop behaving that way exactly when it matters. Meanwhile the backtest, at six exceptions against five expected on 500 trading days, sits inside the green zone, which the Backtest sheet now draws with the binomial rather than a typed threshold: a correct model shows eight or fewer exceptions with 93.3% probability, not the nine the first edition of this workbook used.
How to use it on your own portfolio
Open Inputs and overwrite the yellow cells: net assets, the sleeve market values and volatilities, the confidence levels, and the backtest sample.
Read VaR Three Ways for the construction: the variance-covariance grid, portfolio volatility, and value at risk and expected shortfall in sequence.
Throw the correlation switch on Inputs (cell B11: 1 for reported, 2 for crisis) and watch value at risk move on the VaR Three Ways sheet.
Enter your own exception count on Inputs and read the zone on The Backtest: green, amber or red, drawn from the binomial at your actual sample size.
Keep the Checks sheet: once you change inputs it stops matching the book, which is expected. Use its structure for your own tie-outs.
Questions people ask about it
Is this VaR and expected shortfall model really free?
Yes. It is a companion file to a book. There is no sign-up, no email address and no paid version.
Does it use macros or circular references?
No macros and no circular reference: value at risk, expected shortfall and the backtest zones each read only from the sleeve volatilities, the correlation matrix and the inputs above them. The file recalculates in Excel, LibreOffice or Google Sheets without iterative calculation.
Why does expected shortfall barely change the answer?
On a normal distribution, expected shortfall at 97.5% divided by value at risk at 99% is 1.005: switching measures changes almost nothing. What changes the answer is the shape of the tail, which a parametric model assumes away, not the statistic computed on it.
Can I use it for a real portfolio?
You can reuse the structure: the sleeve volatilities, the correlation switch and the backtest zones. The fund, the sleeves and every figure in it are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for Financial Risk Management: the full set adds
the four-numbers overview, the stress book and its reverse, and the liquidation engine with the transfer to
investors who stay. Free, like this one.