Free Excel templates

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.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

VaR_ES_and_the_Backtest.xlsx (18 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from Financial Risk Management. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon →

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

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

MeasureValue
Net assets840,000,000
One-day value at risk, 99% confidence7,242,456
Value at risk as a share of net assets0.862%
Twenty-day value at risk (square-root-of-time scaling)32,389,246
Expected shortfall, one day (97.5% confidence)7,278,117
Expected shortfall / value at risk1.005
Diversification benefit, reported correlations0.767 points (11.5%)
Diversification benefit surviving crisis correlations27.6%
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

  1. Open Inputs and overwrite the yellow cells: net assets, the sleeve market values and volatilities, the confidence levels, and the backtest sample.
  2. Read VaR Three Ways for the construction: the variance-covariance grid, portfolio volatility, and value at risk and expected shortfall in sequence.
  3. 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.
  4. 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.
  5. 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. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

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.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Financial Risk Management. The book is on Amazon.

If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.