A free bank stress test template in Excel: PPNR, CET1 and the MDA threshold
Pre-provision net revenue, credit losses, tax, distributions and risk-weighted assets over nine quarters, under a base and an adverse scenario, measured against Pillar 1, P2R and the combined buffer: eight sheets, no macros.
PPNR, pre-provision net revenue, is what a bank earns before credit losses: net interest income plus fees and other income, less operating expenses. In a stress test it is the first line of defense, and CET1 capital is the second. The file runs both for Northfield Bank, a fictional lender starting at a 12.00% CET1 ratio against an MDA threshold of 9.625%. In the base case the ratio never falls below 12.07%. In the adverse case $1,852.5 million of provisions overwhelm $1,322.4 million of PPNR, the ratio bottoms at 8.42% in Q2 2028, 121 basis points below the threshold, and the bank spends seven quarters with its distributions capped.
Get the template
The worked case and an empty copy, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
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 blank file keeps the formulas, the requirement stack and the checks with every input cleared, for your own bank and your own scenarios.
The book behind this model
This file is one worked case from Bank Management. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
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).
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
Eight sheets, in the order the calculation runs. Inputs are blue type on a yellow fill; everything else is a formula.
Inputs
The starting balance sheet and the latest quarter of earnings, the tax rate and payout ratio, the requirement stack (Pillar 1, P2R and its CET1 share, the conservation, countercyclical and systemic buffers, P2G) and the MDA factor for each quartile of the combined buffer.
Requirements
The CET1 stack layer by layer up to the MDA threshold, P2G above it, and the boundaries of the four quartiles of the combined buffer.
Scenarios
Nine quarters of drivers for each scenario: net interest income, fees, trading income, costs, the annualized provision rate, risk-weighted assets and securities losses through OCI.
Base and Adverse
The projection quarter by quarter: PPNR, provisions, tax, net income, the distribution test against the start-of-quarter ratio, CET1 capital, RWA, the CET1 ratio and the distance to the MDA threshold in basis points and in dollars.
Summary
Both scenarios side by side: cumulative PPNR, provisions and net income, the lowest ratio and when it occurs, capital depletion, quarters below the threshold and the CET1 needed to get back to it.
Checks
The capital roll-forward, PPNR and requirement tie-outs, and every figure on this page set against the model.
What the worked case returns
Measure
Base
Adverse
Cumulative PPNR, nine quarters
$2,027.2m
$1,322.4m
Cumulative credit loss provisions
$342.0m
$1,852.5m
Cumulative net income
$1,263.9m
−$575.0m
CET1 ratio, start
12.00%
12.00%
CET1 ratio, lowest
12.07% (Q1 2027)
8.42% (Q2 2028)
CET1 ratio, end of horizon
12.67%
8.85%
Lowest distance to the 9.625% MDA threshold
+245 bp
−121 bp
Quarters below the threshold
0
7
Distributions withheld under the MDA
$0.0m
$7.0m
CET1 needed at the low point to reach the threshold
$0.0m
$465.2m
Read the adverse column from the top. PPNR stays positive in every quarter: the bank never stops earning before losses.
It is the provisions, 1.40 times PPNR over the horizon, and $250 million of securities losses through OCI that take 358 basis points of CET1. Risk-weighted assets are 10% higher at the trough, so the ratio falls faster than the capital.
Below the threshold the MDA bites only when there is a profit to distribute: in Q3 and Q4 2028 the bank earns money, sits in the third quartile of its buffer and may pay out 40% of it instead of the planned 50%. That is the $7.0 million withheld. The real constraint is the $465.2 million of CET1 it would need to stand at the threshold.
How to use it on your own numbers
Enter the bank’s starting CET1 capital, RWA and loans, and the latest quarter of net interest income, fees and costs, on Inputs.
Replace the requirement stack with the bank’s own: Pillar 1, its P2R and the CET1 share of it, and the buffers that apply to it.
Write the scenarios on Scenarios as indices to the latest quarter, with the provision rate annualized. Keep both scenarios on the same nine quarters.
Read Summary: the lowest ratio, the quarter it happens and the distance to the MDA threshold are the three numbers a board asks for.
Keep Checks: the capital roll-forward must read zero after any change.
Questions people ask about it
What is PPNR in banking?
Pre-provision net revenue: net interest income plus non-interest income, less non-interest expenses, before credit loss provisions and tax. It measures how much loss a bank can absorb from earnings before it touches capital. In the worked case it is $220.0 million in the first base quarter.
What is the MDA threshold?
The CET1 ratio below which a bank's distributions are capped at the maximum distributable amount. It is the Pillar 1 minimum plus the part of the Pillar 2 requirement met with CET1 plus the combined buffer requirement: 4.50% + 1.125% + 4.00% = 9.625% here. The model applies the cap to dividends.
What is the MDA buffer?
The distance between a bank's CET1 ratio and its MDA threshold, in basis points or in dollars of CET1. Northfield starts 237.5 basis points above its threshold; under the adverse scenario the distance turns negative in Q3 2027.
Why is P2G not in the MDA threshold?
Pillar 2 guidance is a supervisory expectation, not a requirement: falling below it does not trigger the MDA. The file shows it above the threshold, at 10.625%, as the level a supervisor expects the bank to hold.
Does it use macros or circular references?
No. Distributions in a quarter are tested against the CET1 ratio at the start of that quarter, which the model already knows, so the file recalculates without iterative calculation.
Can I use it for a real bank?
You can reuse the structure. A supervisory stress test has many more lines. The bank 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 Bank Management: the full set adds the four capital readings of one balance sheet, the deposit beta measured two ways, the economic value of equity and the liquidity that lasts 3.9 days. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
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.