Free Excel templates

A free LCR and NSFR calculator in Excel, on one bank's balance sheet

High-quality liquid assets with haircuts and encumbrance, thirty-day outflows and the inflow cap, available and required stable funding line by line, and six defensible readings of the funding factors: eleven sheets, no macros.

The file builds both liquidity ratios for Northbank, a fictional €80 billion bank. Its securities portfolio is worth €18.5 billion after haircuts, but €5.6 billion of it is already pledged as margin against secured funding. On the unencumbered €12.9 billion the liquidity coverage ratio is 117.5% against net outflows of €11.0 billion. Forget the pledge and the same arithmetic reads 168.8%: 51 points of headroom that do not exist. The net stable funding ratio is 95.8%, and none of six defensible variations on the factors brings it to 100%.

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.

LCR_NSFR_Calculator.xlsx (47 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 Regulation. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

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: 36 paperbacks by Julian R. Sterling on how to read the documents, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Eleven sheets. Blue on pale yellow is an input; everything else is a formula over the inputs.

Read me
What the workbook does and the three experiments to run first.
Inputs
The bank at 31 December 2026: balance sheet, funding table, securities and haircuts, outflow and inflow rates, funding factors and transitional schedules.
The loan split and The bank
The mortgage book split at the secured threshold and the bank's risk-weighted assets, shared with the capital workbook so both ratios sit on the same balance sheet.
The liquidity buffer
The portfolio after haircuts, the securities pledged against secured funding, and which leave the buffer in which order.
Outflows
Thirty-day outflows on deposits by type, on committed lines and on other contractual flows, inflows and the 75% cap.
The coverage ratio
HQLA after encumbrance over net outflows, and the same ratio if the pledge were forgotten.
Stable funding
Available stable funding by liability and required stable funding by asset and off balance sheet.
Seven readings
Six defensible variations on the factors, each on its own switch, and the mortgage funding factor read the other way.
Two costs
Capital per euro against stable funding per euro for each asset, and how far the two rankings agree.
Checks
Every figure the book prints against the live formula: 50 lines, all pass.

What the worked case returns

MeasureValue
Securities portfolio after haircuts€18.51bn
Securities pledged as margin€5.63bn
HQLA, unencumbered€12.88bn
Net outflows over 30 days€10.97bn
Liquidity coverage ratio117.5%
LCR if the pledge is forgotten168.8%
Available stable funding€44.55bn
Net stable funding ratio95.8%
NSFR range across six readings94.5% to 96.1%

Both coverage figures are arithmetically correct; only one is the ratio. Treasury reports the portfolio after haircuts, and the securities already posted as margin are still on that report. The coverage ratio counts only what can be sold or repoed tomorrow, so the pledge comes off first.

How to use it on your own bank

  1. Replace the funding table and the securities portfolio on Inputs with your own bank.
  2. Enter the encumbrance: secured funding raised against your own inventory and the margin posted on it.
  3. Read The coverage ratio sheet: the gap between the two figures is what encumbrance costs.
  4. Turn the switches on Seven readings one at a time to see how far the NSFR can be argued.
  5. Keep Checks as the pattern for your own reconciliations.

Questions people ask about it

Is this LCR and NSFR calculator 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: every line is a product of an amount and a factor, summed.

Does it follow the EU rules or Basel III?

The factors follow the Basel III framework as transposed in the EU, with the national discretions and conventions shown as switches. It is a teaching model on a fictional bank, simplified where the book says so, not a regulatory reporting tool.

Can I use it for a real bank?

You can reuse the structure. Northbank and every figure in it are fictional, and nothing here is regulatory advice.

The rest of the files

This template is one of the four companion workbooks for Financial Regulation: the others rebuild risk-weighted assets, the capital stack, and resolution and concentration. Free, like this one.

Open the companion files →

Other free templates

This template comes from Financial Regulation. 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.