How to Build an IFRS 9 ECL Model in Excel
An ECL model is not hard arithmetic. It is hard bookkeeping. Most of them fail review not because the loss numbers are wrong, but because nobody can trace how a provision moved between two dates.
The expected credit loss calculation itself is a line of arithmetic. Probability of default times loss given default times exposure at default, discounted. Anyone can write that formula.
What makes an ECL model hard is everything around that line: getting the staging right before you calculate, holding lifetime and 12-month views in parallel, applying a forward-looking overlay without destroying the audit trail, and being able to explain in a committee why the provision moved by the amount it moved. Build for the explanation, not the arithmetic.
The tab structure that works
One workable layout, in dependency order.
INPUTS. Every assumption, nothing else. Segment definitions, PD term structures, LGD by collateral type, EAD parameters and credit conversion factors, discount rates, staging thresholds, macroeconomic scenarios and their weights. If a number can be argued about, it lives here in a blue cell, not buried in a formula three tabs away.
PORTFOLIO. The exposure data as received: facility, segment, origination date, origination PD or grade, current balance, undrawn commitment, collateral, days past due. This tab is a landing zone, so it should contain no formulas at all. Keeping it inert is what lets you swap a month's data without touching the model.
STAGING. The stage 1, 2 and 3 allocation, with each trigger evaluated in its own column so you can see which one fired. Quantitative test, backstop, and any qualitative flags, then the resulting stage. Do this before any ECL is calculated.
PD_TERM. Marginal and cumulative PD by period out to maturity, per segment and scenario. This is the tab that turns a point-in-time PD into the lifetime curve the standard needs.
ECL_12M and ECL_LIFETIME. Both calculated for every exposure, always. Then the stage picks which one flows through. Calculating only the one you think you need is where models become impossible to challenge.
OVERLAY. The forward-looking adjustment, applied as an explicit, separately visible layer. Never blended into the base PD.
RECONCILIATION. The provision walk from opening to closing, decomposed.
CHECKS. Everything that has to tie.
Lifetime is not a multiple of 12-month
The most common structural error is calculating a 12-month ECL and scaling it by some factor for stage 2 exposures.
Lifetime ECL is the sum, across every remaining period, of the marginal probability of default in that period, times LGD, times expected exposure at that point, discounted back at the effective interest rate. Three things vary across those periods and none of them scale linearly: the marginal PD follows a term structure, the exposure amortises, and the discount factor compounds.
For an amortising loan the exposure profile alone can make lifetime ECL a smaller multiple than intuition suggests, because the largest default probabilities in later years apply to a much smaller balance. Scale factors get this backwards on precisely the facilities where the provision matters most.
The scenario weighting most models get wrong
IFRS 9 requires a probability-weighted outcome, not a best estimate. That means running the ECL under each macroeconomic scenario and weighting the results.
Running the model once on probability-weighted inputs is not the same thing and is not compliant. Because the relationship between macro variables and loss is convex, weighting the inputs systematically understates ECL. The gap widens exactly when it matters, in the tail.
Structurally this means your ECL tabs need a scenario dimension from the outset. Retrofitting one into a model built for a single case is a rebuild, so decide early.
Overlays, and keeping them defensible
Post-model adjustments are a fact of life. New risks appear faster than models can be recalibrated. The problem is not having overlays, it is having overlays nobody can dismantle later.
Keep every overlay as a separate, named line with its own rationale, its own quantum, and the population it applies to. When the underlying model is recalibrated, you need to be able to remove the overlay cleanly. An overlay buried inside a PD assumption cannot be removed without unpicking the whole calibration, and tends to survive for years after the risk it addressed has gone.
The reconciliation is the deliverable
The single most useful tab is the provision walk. Opening ECL, then:
- new lending originated in the period
- repayments, maturities and write-offs
- stage transfers, ideally split by direction
- changes in risk parameters within stage
- model and methodology changes
- overlay movements
- closing ECL
If those components do not sum to the movement, something is wrong, and finding out at the reconciliation is far cheaper than finding out in committee. This is also the tab that gets projected on the wall, so it is worth more layout care than the calculation engines behind it.
Checks that must pass
Sum of exposures by stage equals total portfolio. Sum of ECL by stage equals total provision. Every stage 3 exposure has a lifetime calculation. No PD outside 0 to 1. No negative ECL. Scenario weights sum to exactly 1. Provision walk ties opening to closing. Every one of these has caught a real error in a real model.
Our IFRS 9 ECL Model is built on this structure, with lifetime and 12-month ECL calculated in parallel, scenario weighting applied to results rather than inputs, and the provision walk as a standing output.
Get the IFRS 9 ECL Model
The model that puts the principles in this post into practice. Bank-grade build, open formulas, no VBA. Same-day delivery.
SFS Models builds institutional-grade Excel financial models for banking and finance professionals. Browse the catalogue or get the free sample.