Loan Portfolio Modelling
Executive Summary
Key Takeaways
- ✓ The loan book should be segmented by product type, risk grade, or business line, each with its own origination, repayment, yield, and expected loss assumptions, rather than modelled as a single blended balance.
- ✓ Loan balances should be rolled forward period over period as opening balance plus new originations less scheduled and unscheduled repayments less write-offs, at the segment level, not just in aggregate.
- ✓ Segment-level loan balances feed directly into both the interest income build (as the asset-side volumes) and credit loss provisioning (as the base against which segment loss rates are applied).
- ✓ Risk-grade or product-type segmentation should be granular enough to support a meaningfully differentiated expected loss assumption per segment, since a single blended loss rate defeats the purpose of segmenting at all.
- ✓ Loan growth assumptions should be checked against the loan-to-deposit ratio and available regulatory capital, since a bank cannot fund unlimited loan growth from deposits or carry it without adequate capital support.
Objective¶
This guide covers how to segment and model a bank's loan portfolio, within the Banking Financial Modelling pillar, feeding directly into Interest Income Modelling and Credit Loss Provisions.
Segmentation¶
The loan book should be segmented by product type (mortgages, commercial loans, consumer loans, credit cards), risk grade, or business line, with each segment carrying its own origination, repayment, yield, and expected loss assumption. A single blended loan balance obscures the mix and risk profile that actually drives both interest income and credit losses.
| Segment Dimension | Typical Use |
|---|---|
| Product type | Mortgages, commercial/corporate loans, consumer loans, credit cards |
| Risk grade | Internal credit rating bands, used to differentiate loss assumptions |
| Business line | Retail, commercial, corporate, or specialized lending units |
Roll-Forward Mechanics¶
Each segment's balance should be rolled forward explicitly, period over period:
Closing Balance = Opening Balance
+ New Originations
− Scheduled Repayments (amortization)
− Unscheduled/Early Repayments (prepayments)
− Write-Offs
Building this roll-forward at the segment level, rather than only in aggregate, is what allows the model to show where growth, repayment, and credit losses are actually occurring within the book, rather than presenting a single net change that hides offsetting movements.
Feeding the Rest of the Model¶
Segment-level average balances feed directly into the interest income build as the asset-side volumes multiplied by segment yield (see Interest Income Modelling), and into credit loss provisioning as the base against which segment-specific loss rates are applied (see Credit Loss Provisions). Write-offs reduce the gross loan balance and are drawn down against the allowance for credit losses built up through prior provisions, rather than hitting the income statement directly in the period the write-off occurs.
Growth Constraints¶
Loan growth assumptions should be checked against two constraints the model should make explicit rather than assume away: funding capacity, since loans are predominantly funded by deposits and wholesale funding (see the loan-to-deposit ratio), and regulatory capital, since risk-weighted loan growth consumes available capital headroom — see Capital Adequacy Models.
Common Construction Pitfalls¶
- Modelling the loan book as a single blended balance, collapsing the product-type and risk-grade detail needed for meaningful expected loss assumptions.
- Rolling forward only an aggregate net change in loan balance, rather than explicitly separating origination, repayment, and write-off drivers at the segment level.
- Forecasting loan growth without checking it against funding capacity or regulatory capital constraints.
- Charging write-offs directly to the income statement rather than drawing them down against the allowance for credit losses.
Continue Reading¶
Prerequisites¶
- Banking Financial Modelling — the parent pillar
Related Technical Guides¶
Related Glossary¶
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
How should a bank's loan portfolio be modelled?
Segmented by product type (mortgages, commercial loans, consumer loans), risk grade, or business line, each with its own origination, repayment, yield, and expected loss assumption, rather than as a single blended loan balance.
How is a loan segment balance rolled forward period over period?
Opening Balance + New Originations − Scheduled Repayments − Unscheduled/Early Repayments − Write-Offs = Closing Balance, applied at the segment level rather than only in aggregate.
How does the loan portfolio module connect to the rest of the model?
Segment-level balances feed directly into the interest income build as the asset-side volumes (see Interest Income Modelling) and into credit loss provisioning as the base against which segment-specific loss rates are applied (see Credit Loss Provisions).
Why does segmentation granularity matter for expected loss?
Because a single blended loss rate applied to the whole loan book defeats the purpose of segmenting in the first place — segmentation is only useful if it is granular enough to support a meaningfully differentiated expected loss assumption for each segment's actual risk profile.
What constrains how much a bank's loan book can grow?
Loan growth is constrained by two things a model should check the forecast against — funding capacity (the loan-to-deposit ratio, since loans are predominantly funded by deposits and wholesale funding) and regulatory capital (since risk-weighted loan growth consumes capital headroom).
How should write-offs be modelled?
As a distinct reduction to the segment's gross loan balance, drawn down against the allowance for credit losses built up through prior provisions, not as a direct income statement charge in the period the write-off occurs — see Allowance for Credit Losses.
Related Articles
Banking Financial Modelling
Banking financial modelling is structurally distinct from a standard corporate model: it is built balance-sheet-first, with earnings derived from asset and liability volumes and spreads rather than a top-line revenue forecast, and it must represent loan portfolio and deposit dynamics, credit loss provisioning, and a set of bank-specific KPIs that a generic corporate model has no equivalent for. This page is the hub for the Knowledge Centre's banking modelling content: how the bank business model translates into a model's architecture, how the three financial statements are structured for a bank, how interest income and the net interest margin bridge are built, and how loan portfolios, deposits, and credit loss provisions should be modelled.
Interest Income Modelling
Interest income modelling is the core mechanic of a bank financial model: interest income and expense are derived from forecast asset and liability volumes and their associated yields and costs, not from a standalone revenue assumption. This guide covers how to structure that build at a segment-by-segment level, how net interest income and net interest margin are calculated from it, and how to construct the net interest margin bridge that separates a period's margin change into volume, rate, and mix effects — the single most useful diagnostic output in a bank model.
Credit Loss Provisions
Credit loss provisioning is the income statement charge that builds up the allowance for credit losses held against a bank's loan portfolio. Provisions should be derived from portfolio-segment loss-rate assumptions applied to segmented loan balances — not a single blended provisioning rate applied to the total book — since default risk varies substantially by product type and risk grade. This guide covers how to structure that segment-level provisioning build and how it connects to the allowance roll-forward on the balance sheet.
Deposit Modelling
Deposit modelling is the liability-side counterpart to loan portfolio modelling: deposits should be segmented by product type — transactional, savings, and term — each carrying its own volume, cost, and behavioural assumptions. Behavioural modelling matters more on the deposit side than almost anywhere else in a bank model, since a deposit's contractual maturity (or lack of one, for transactional accounts) frequently does not match its actual behavioural stickiness, and that gap is central to both funding and liquidity risk management.
Non-Performing Loan Ratio
The non-performing loan (NPL) ratio measures non-performing loans — those in significant default or unlikely to be repaid in full without recourse to collateral — as a percentage of a bank's total loan book. It is the core asset-quality indicator, and should be read alongside the provision coverage ratio, since a rising NPL ratio without a corresponding increase in provisioning coverage signals building, unrecognized credit risk.
Allowance for Credit Losses
The allowance for credit losses is a contra-asset account on a bank's balance sheet, representing the reserve held against expected credit losses on the loan portfolio. It is built up through periodic provision charges against the income statement and drawn down as specific loans are written off, following the same roll-forward discipline a corporate model applies to a bad debt reserve, but at a scale and centrality that makes it one of the most closely scrutinized figures on a bank's balance sheet.
Loan-to-Deposit Ratio
The loan-to-deposit ratio compares total loans to total deposits, giving a core indicator of how much of a bank's lending is funded from its deposit base versus wholesale or other funding sources. A ratio above 100% means the bank is lending more than it holds in deposits, funding the difference through wholesale markets — a funding structure that carries more refinancing and liquidity risk than deposit-funded lending.