Companion files
A Practitioner’s Guide to Finding the Constraint That Actually Binds
These are the companion files of Financial Regulation by Julian R. Sterling.The book on Amazon →
The four Excel workbooks that go with the book, corrected where they were wrong. Every figure the book prints is reproduced in them by a live formula rather than a typed constant, and each workbook ends with a Checks sheet setting the printed figure beside the computed one: 203 controls in all, every one green. Around them: the same four models with the inputs emptied, thirteen working documents to print, forty questions that mark themselves, and three cases the book names and never takes to a number. Nothing is locked, protected or watermarked.
Free to download. No sign-up, no email address, nothing to fill in.
Twelve workbooks, the printable documents and the read-me. Each group below can also be downloaded on its own. Last revised 24 September 2026.
Reading the paperback or the Kindle edition? The same book is also sold as a DRM-free PDF: searchable, printable, yours on any device. $19.99, instant download.Buy the PDF edition → Free sample
Finished the book? Two or more PDF editions bought together are 15% off. Browse the PDF editions →
The working models behind the books, built out for live deals: the Pro models →
Optional. When a workbook on this page is corrected or a new worked case goes up, readers on the list get one email, plus a short note when a new book comes out. The downloads do not need it.
Join the reader listRun by MailerLite, off this site. How your address is used.
These carry the book’s own figures, so that every number it prints can be traced to a live formula, and each closes on its checks sheet. Download these four148 KB
Chapters 2 to 6
The flat 35 per cent risk weight on residential mortgages is gone. The part of a loan up to 55 per cent of the property value carries 20 per cent; the rest is weighted as an unsecured exposure to the same borrower. Computed band by band, the book weighs 27.7483 per cent. Computed on the average loan-to-value, it weighs 25.2631. That is 248.5 basis points, or 521.9 million of risk-weighted assets, and on this book the shortcut flatters.
Then both ladders line by line — standardised against internal models, with the three non-credit blocks that are a seventh of the total — and the output floor across all six years of its schedule. The year it starts to bind is looked up, not typed: move an internal weight and the year moves with it. Revised: the shortcut does not always flatter. The weight is convex across the 55 per cent threshold and concave above it, and on the four bands above the threshold alone the average loan overstates the weight by 69.5 basis points (33.1922 per cent against 32.4972 band by band).
Download the workbook31 KBChapters 7 to 10
The three capital compartments with their requirements built from Pillar 1 and the Pillar 2 split. Northbank is 40.8 million short of additional tier 1 and 162.3 million long of tier 2, and the surplus cannot fill the shortfall: the hierarchy runs one way only. The shortfall is plugged with common equity, which is then no longer available for the buffer.
So the headroom is computed twice. As the board reads it: 2.9796 per cent. As it exists: 2.8432. Thirteen point six basis points, and the sheet shows exactly where they go. The countercyclical buffer is rebuilt country by country, weighted by own funds requirements and not by exposures, and the trigger carries its four quartile boundaries with their payout factors. Revised: lowering the internal weights cannot make the leverage ratio the binding constraint, as the first edition said. The output floor holds the density at 29.3603 per cent, above the switch of 25.1004; only 35,186.9 million of zero-weighted exposure would bring the two together.
Download the workbook37 KBChapters 11 to 15
The coverage ratio built from the deposit book upward. The securities portfolio is shown before and after encumbrance, so the 51 points between 168.8372 and 117.4934 per cent open and close on a switch: 5,629.8 million of the buffer is already pledged, and a pledged asset is not eligible.
Then stable funding, at 95.7941 per cent and short by 1,956.0 million — with six factor variations on switches and the exposure-by-exposure mortgage reading as a seventh. The band runs from 94.46 to 96.10. None of them reaches a hundred. The last sheet builds the two-cost table: capital per euro against stable funding per euro, whose rank correlation is +0.8628 — the two costs are not inverted, and the finding is that their intensity varies by a factor of 7.4. Revised: all six variations switched on together give 94.7605 per cent, not the most favourable reading; the three that help give 96.5310 and leave 1,601 million missing. The mortgage is not the asset the capital rules treat most gently (the covered bond is); it is the one with the most stable funding per unit of capital.
Download the workbook48 KBChapters 16 to 18
The market confidence charge reads two ways: the combined buffer less 125 basis points, or less the countercyclical buffer. The two requirements sit side by side, 83.7 million apart — which is 52.6 per cent of everything Northbank holds above the requirement. Neither reading is chosen.
The subordinated requirement is written both permitted ways, and they agree to the euro. Beside them sits a cell, marked in red, that deliberately reproduces the double count — adding the buffer to the requirement and deducting it from resources, which are two writings of one rule. It misstates the headroom by 1,486.5 million, and turns 1,315.8 million of headroom into a shortfall of 170.6 million that does not exist. The book’s own reference model made that mistake; the cell is there so you can see it happen. Then the large exposure limit on both bases, and the 227.5 million of headroom that does not exist. Revised: this page called the 1,486.5 million a shortfall; it is the size of the misstatement. The workbook itself was re-verified, with four checks added.
Download the workbook36 KBThe four workbooks with the 233 inputs of their shared Inputs sheet emptied. The checks sheets count what is still missing and test what holds for any inputs.
Chapters 2 to 18
The risk weight engine, the capital stack, the two liquidity ratios, resolution and concentration, with the same formulas and every input emptied: the transitional schedules, the loan-to-value bands, the balance sheet on and off, the funding, the requirements and buffers, the national rates, the liquidity parameters and the resolution terms. The years of the floor schedule, the six switches and the four quartile boundaries stay, because they are the axes of their tables, not data.
The book’s 233 inputs are listed in a text file inside the archive. Type them back in and the book’s figures come back with them: 27.7483 per cent band by band, 2.8432 per cent of headroom as it exists, 1,956,000,000 of stable funding short.
Download the blank set136 KBThirteen pages to print and use. A4, with margins wide enough for US Letter.
Chapters 3 to 18
The seven questions for somebody else’s capital plan, with Northbank’s answers beside a blank column. The loan split band by band, with the sign of the shortcut tested. The two ladders side by side. The output floor calendar, carried into the resolution requirement. The three compartments. The countercyclical buffer country by country. The distribution trigger and the two headrooms. Leverage and the switch. The liquidity buffer after encumbrance. Stable funding and the band of readings. The two costs of each asset. The convention register. The constraint that actually binds, on one page for the board.
Download the PDF32 KBA workbook that marks itself. Answer in the yellow cells; the marking fills in as you go.
All parts of the book
Eight on the risk weight engine, seven on the capital stack, six on liquidity coverage, seven on stable funding and the two costs, six on resolution and concentration, and six on the three cases below. Numbers are marked to a tolerance, multiple choice to the letter, and the score sheet counts your errors part by part: that is your revision list.
Nothing is hidden. The marking sheet holds every answer, every tolerance, and the reason for each answer.
Download the question book17 KBNew material, not a restatement. Three situations the chapters name and never take to a number. Each case is one workbook: the note is the first sheet, the model is the rest, and a checks sheet closes it. Type your own figures in and every finding recomputes.
Chapters 6, 9 and 16 · Case one
Chapter 6 dates the output floor and follows it to the capital ratio, which absorbs it. Carried into every requirement that is a multiple of risk-weighted assets, with the balance sheet held where it stands (ADDED), the floor breaks the one with the least room in the year it first binds: in 2029 the resolution headroom of 159.1 million becomes a shortfall of 262.2 million, 350.1 on the literal reading, and 583.7 million in 2030, while the distribution headroom is still 1.62 per cent. The resources carry a floor of 67.96 per cent of the standardised ladder, and no more.
Chapters 8, 16 and 17 · Case two
The countercyclical buffer is built to be released; the book never releases it. Released in full, it frees 290.1 million for distributions and, on the resolution requirement, 580.2 million on the authority’s reading against 290.1 on the literal one. The gap between the two readings widens from 83.7 to 373.9 million, and they agree only at 1.25 per cent. The other way, a rise of 27 basis points, to 1.2360 per cent, exhausts the resolution headroom. The scenario rates are ADDED.
Chapters 13, 14 and 16 · Case three
The book establishes the stable funding shortfall and stops. Closed with term senior non-preferred debt held as reserves (ADDED), it takes 1,956 million and costs 52,812,000 a year; closed by running off corporate loans to repay wholesale deposits (ADDED), it takes 2,301.2 million and is the cheaper route only if the loans earn less than 2.30 per cent net. Read exposure by exposure the bill is 67.6 million a year; at the most favourable combination, 43.2. The obvious route is the only one that weakens a ratio: leverage, from 5.45 to 5.33 per cent.
Download the three cases38 KB| Blue text | a hardcoded input: you may edit these |
| Yellow fill | an input cell; everything else on the sheet is a formula |
| Black text | a formula: do not overtype these |
| Checks sheet | the printed figure beside the computed one, with a PASS or a FAIL; in the blank set and the cases, each control states its own verdict and the file ends ALL OK |
| ADDED | a figure the book does not give, used in the cases and marked wherever it appears |
There is one Inputs sheet in each file, and it carries the only entered values: the
balance sheet, the funding, the risk weights, the loan-to-value bands, the national buffer rates,
the requirements. Everything else is computed from them. Where the texts permit two readings, both
are shown and neither is chosen — that is the subject of chapter 17.
The workbooks were checked by moving the inputs and confirming that every dependent figure moved with them. Shifting eight points of the mortgage book from the lowest loan-to-value band to the highest moves the portfolio weight, the standardised total, the floor trajectory and the funding factor together. Raising the internal weight on corporate lending moves the binding year of the output floor from 2029 to 2030 through the lookup.
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers; the PDF prints on A4 or US Letter. They use no macros and no add-ins, so nothing needs to be enabled or trusted. If your spreadsheet asks to update links on opening, decline; there are none.
| 24 Sep 2026 | The four workbooks corrected, 203 checks. Chapter 4’s shortcut does not always understate: above the 55 per cent threshold the weight is concave, and on the book’s upper bands the average loan overstates by 69.5 basis points. Chapter 10’s backstop cannot be reached by lowering internal weights while the floor stands: the lowest density is 29.36 per cent, above the switch at 25.10. Chapter 13’s switches all on give 94.76 per cent, not the most favourable reading, which is 96.53. The double count misstates the headroom by 1,486.5 million and produces a shortfall of 170.6 million, not one of 1,486.5. Added: a blank set, thirteen working documents, forty questions that mark themselves, and three cases. |
Also available as a standalone free template: LCR and NSFR calculator.
Financial Regulation develops the complete framework behind these models, with the assumptions, trade-offs and worked examples tied together.
View the book on Amazon Read the related analysisAlso as an ebook on Apple Books · Google Play.
These files accompany 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 workbook stays free either way.
More in regulation and compliance, with free companion files. The full list of titles is on the author page.