Consumer lending model: loan vintages
Vintages, loss curves, allowance, advance rates, ROE, P/TBV. Build a non-bank lender from its loan vintages: each year’s originations repaid, prepaid or charged off on curves by years on book, the matrix of charge-offs and balances by origination year and forecast year, an allowance for the lifetime loss still to come and the provision that builds it, yield and fees less the cost of securitisation and warehouse funding at their advance rates, the cost of acquiring and servicing each loan, tangible equity against a covenant, new shares when the facilities will not fund the book, and a value per share from residual income and price to tangible book.
By Surojit Chakraverti, founder of L3VLUP and an investor running a long-short healthcare and technology equities strategy.
On this subject: consumer lending model: loan vintages.Updated 12 November 2026
Who builds it, and for whatThe model an equity research analyst keeps for a listed consumer lender, that a specialty finance banker rebuilds before a securitisation, a warehouse renewal or a sale, and that a private equity or credit fund builds before buying a loan book or the platform that makes one. The analyst uses it to say what the lending earns once the loans that go bad are paid for; the funder reads the same vintages to set an advance rate, and the buyer to see how much equity the book would need if losses rose and the facilities were re-margined in the same year.
Every sheet below, in your browser, with its outputs and checks
Starter workbook free with an account. Worked model: one a month with Plus, every one with Pro. Downloads
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Assumptions | |||||||||
| 2 | $m unless stated. FY0A is the opening balance sheet; the forecast runs FY1E to FY5E. All figures are illustrative. | |||||||||
| 4 | Year | Unit | FY0A | FY1E | FY2E | FY3E | FY4E | FY5E | Notes | |
| 5 | Year number | # | 0 | 1 | 2 | 3 | 4 | 5 | FY0A = 0. Drives cost growth and discounting. | |
| 7 | Scenario | |||||||||
| 8 | Scenario | # | 1 | 1 = base, 2 = credit downturn, 3 = funding squeeze. Selects a row of each case on the Scenarios sheet, from FY1E. | ||||||
| 10 | Originations | |||||||||
| 11 | Originations, FY-2 | $m | 1,500.0 | Principal lent two years before the opening balance sheet. Still partly on the book. | ||||||
| 12 | Originations, FY-1 | $m | 1,700.0 | |||||||
| 13 | Originations, FY0A | $m | 1,900.0 | |||||||
| 14 | Originations, base case | $m | 2,100.0 | 2,300.0 | 2,500.0 | 2,700.0 | 2,900.0 | Principal lent in the year, spread evenly through it. The scenario scales it. | ||
| 15 | Average loan size | $ per loan | 9000 | Turns originations into a number of loans for the acquisition cost. | ||||||
| 17 | How a vintage ends | |||||||||
| 18 | Share of principal prepaid | % | 22.0% | Repaid early, by refinancing or from the borrower’s own cash. | ||||||
| 19 | Lifetime loss rate | % | 10.0% | Principal charged off over a vintage’s life, net of recoveries, as a share of the principal originated. The rest is repaid on schedule. | ||||||
| 21 | Timing by years on book | |||||||||
| 22 | Scheduled repayments: share in the year of origination | % | 12.0% | A loan made mid-year pays about half a year of instalments in its first year. The four shares sum to 100%. | ||||||
| 23 | Scheduled repayments: share in one year on | % | 30.0% | |||||||
| 24 | Scheduled repayments: share in two years on | % | 36.0% | |||||||
| 25 | Scheduled repayments: share in three years on | % | 22.0% | |||||||
| 26 | Prepayments: share in the year of origination | % | 15.0% | Most prepayment comes in the first full year on book. The four shares sum to 100%. | ||||||
| 27 | Prepayments: share in one year on | % | 45.0% | |||||||
| 28 | Prepayments: share in two years on | % | 30.0% | |||||||
| 29 | Prepayments: share in three years on | % | 10.0% | |||||||
| 30 | Charge-offs: share in the year of origination | % | 10.0% | The loss curve: losses peak one to two years after origination. The four shares sum to 100%. | ||||||
| 31 | Charge-offs: share in one year on | % | 45.0% | |||||||
| 32 | Charge-offs: share in two years on | % | 33.0% | |||||||
| 33 | Charge-offs: share in three years on | % | 12.0% | |||||||
| 35 | Yield and fees | |||||||||
| 36 | Portfolio yield | % | 23.0% | Interest income / average receivables, after interest reversed on loans that stop paying. | ||||||
| 37 | Fees and other income, % of average receivables | % | 1.5% | Late fees and commissions on optional products. | ||||||
| 39 | Acquisition, servicing and overhead | |||||||||
| 40 | Acquisition cost per loan, FY0A | $ per loan | 320 | Marketing, broker fees and credit checks per loan originated. Expensed when the loan is made. | ||||||
| 41 | Servicing and collections, % of average receivables | % | 3.2% | |||||||
| 42 | Technology and overhead, FY0A | $m | 95.0 | |||||||
| 43 | Cost inflation | % | 3.0% | Annual, from FY1E, on the acquisition cost per loan and overhead. | ||||||
| 44 | Tax rate | % | 25.0% | On profit before tax. No credit for a loss. | ||||||
| 46 | Funding | |||||||||
| 47 | Share of receivables in term securitisations | % | 65.0% | Seasoned loans sold into securitisation trusts. The rest is pledged to the warehouse. | ||||||
| 48 | Advance rate, securitisations | % | 90.0% | Notes sold to investors / receivables in the trusts. The scenario changes it. | ||||||
| 49 | Advance rate, warehouse | % | 82.0% | What the warehouse banks lend / receivables pledged. The scenario changes it. | ||||||
| 50 | Benchmark rate | % | 4.0% | |||||||
| 51 | Spread on securitisation notes | % | 1.5% | Over the benchmark, averaged across the classes of notes sold. | ||||||
| 52 | Spread on the warehouse | % | 2.8% | Over the benchmark, including fees on the commitment. | ||||||
| 53 | Minimum cash | $m | 100.0 | Held at this level; any surplus repays the warehouse. | ||||||
| 54 | Minimum undrawn warehouse capacity after a dividend | $m | 50.0 | |||||||
| 56 | Equity, covenant and dividends | |||||||||
| 57 | Shareholders’ equity, FY0A | $m | 400.0 | |||||||
| 58 | Goodwill | $m | 80.0 | Held flat. Deducted from equity to reach tangible equity. | ||||||
| 59 | Covenant: minimum tangible equity / receivables | % | 9.0% | Tested by the warehouse banks at each year end. | ||||||
| 60 | Management target: tangible equity / receivables | % | 10.5% | Dividends are paid only from tangible equity above this ratio. | ||||||
| 61 | Dividend, % of net income | % | 50.0% | Nothing in a loss year. | ||||||
| 62 | Shares in issue, FY0A | m shares | 50.0 | |||||||
| 63 | Price of new shares, x opening tangible book per share | x | 1.0x | Paid for shares issued when the facilities will not fund the book. A lender raising equity in a funding squeeze rarely gets more than book. | ||||||
| 65 | Valuation | |||||||||
| 66 | Risk-free rate | % | 4.2% | |||||||
| 67 | Equity beta | β | 1.40 | |||||||
| 68 | Equity risk premium | % | 5.5% | |||||||
| 69 | Sustainable return on tangible equity (ROTE) | % | 16.0% | The return earned from FY6E on. Drives the terminal value and the justified multiple. | ||||||
| 70 | Terminal growth | % | 3.0% | Growth in tangible book after FY5E. Must be below the cost of equity. | ||||||
Select a cell to see its row, its schedule and the type of cell. Font colours: blue for inputs, black for formulas, green for links to other sheets. Scroll sideways to see every period.
Download
Consumer lending model: loan vintages: the workbook
An Excel workbook with working formulas, no macros and no external links: the same model you can work with on this page.
- Browse without an accountPreview every model online, sheet by sheet, with its outputs and checks.
- Free accountWork with every model online: change the inputs, switch the case, see every formula. And every starter workbook, with its key schedules left blank to build.
- L3VLUP Plus$4.99/monthEverything in Free, and one worked model each billing month as an Excel file: every schedule built, formulas live, checks at zero, yours to keep.
- L3VLUP Pro$25/monthEverything in Plus, every worked model in the library, and the Pro applications.
- The worked model
- 12 sheets and 862 live formulas, all 6 schedules built and linked, every check reading zero. Change an input in Excel and the whole model moves.
- The starter
- The same inputs and layout with the loan vintages: run-off and the cumulative loss curve left blank for you to build, each blank cell marked. The worked model is its answer.
What the base case says
- Value per share ($)
- 13.78
- Equity raised, FY1E to FY5E
- $0.0m
- Value transferred to new shareholders, per share ($)
- 0.00
- Implied price to tangible book
- 2.2x
- Justified P/TBV at the sustainable ROTE
- 1.5x
- Return on equity, FY1E
- 19.0%
- Net income, FY1E
- $79.9m
- Receivables, FY1E
- $3,152.5m
- Net charge-off rate, FY1E
- 6.0%
- Provision for credit losses, FY1E
- $210.0m
- Allowance, % of receivables, FY1E
- 9.4%
- Net interest margin after charge-offs, FY1E
- 12.1%
- Cost of funds, FY1E
- 5.9%
- Tangible equity / receivables, lowest year
- 11.4%
- Undrawn warehouse capacity, lowest year
- $151.3m
Read from the workbook as served, every input at its default. Periods: FY0A, FY1E, FY2E, FY3E, FY4E, FY5E. The figures are invented and move with whatever you type in.
What this model is
A five-year model (FY1E to FY5E, from an FY0A opening balance sheet) of an invented non-bank lender with $2.8bn of unsecured instalment loans to consumers, funded by securitisations and a bank warehouse rather than deposits.
It is built on vintages. Each year’s lending is followed down its own row: repaid on schedule, prepaid or charged off on a loss curve by years on book. The vintage matrix gives charge-offs, each vintage’s cumulative loss and the balance still outstanding, and receivables are the sum of the vintages.
The allowance holds the lifetime loss still to come on every loan on the book, so the provision is charge-offs plus the change in the allowance: the whole expected loss of a loan is charged in the year it is made, and a growing book provides more than it charges off.
The facilities lend against receivables at an advance rate and the lender funds the rest. The Funding sheet shows the notes, the warehouse borrowing base, what is drawn and what is undrawn; the Capital sheet tests tangible equity to receivables against a covenant, pays dividends only from what is above a target and issues shares when the facilities will not fund the book.
Three scenarios (base, a credit downturn and a funding squeeze) and two board-style sensitivity tables: FY1E return on equity by loss rate and yield, and value per share by cost of equity and sustainable return. The shares are valued on residual income and price to tangible book, as lenders are.
Seats: Equity research and hedge funds, Investment banking, Private equity.
How the schedules connect
Every row in the workbook is tagged with the schedule it belongs to; the inspector above shows the tag when you click a row. These are the schedules this model is made of and where each one lives.
Loan vintages: run-off and the cumulative loss curve
Originations by year, charge-offs by years on book
Assumptions, rows 11–15 · Assumptions, rows 18–19 · Assumptions, rows 22–33 · Vintages, rows 6–20 · Vintages, rows 23–25 · Vintages, rows 28–38 · Vintages, rows 41–48 · Vintages, rows 51–60 · Vintages, rows 63–72
Allowance for expected credit losses on a lifetime basis
Loss still to come, reserve build, the provision
Allowance, rows 6–8 · Allowance, rows 11–13 · Allowance, rows 16–21 · Allowance, rows 24–26 · Allowance, rows 29–31
Warehouse and securitisation funding at an advance rate
Notes, borrowing base, equity the lender funds itself
Assumptions, rows 47–54 · Funding, rows 6–13 · Funding, rows 16–24 · Funding, rows 27–33
Operating costs and margins
Cost of sales, opex, EBITDA
Assumptions, rows 40–44 · Income Statement, rows 19–24
Residual income valuation and the justified P/TBV
Profit above the cost of equity, on tangible book
Assumptions, rows 66–70 · Valuation, row 6 · Valuation, rows 9–19 · Valuation, rows 22–23 · Valuation, rows 26–33 · Valuation, rows 36–44
Sensitivity tables
Two assumptions at once, without a data table
Sensitivity, rows 5–8 · Sensitivity, rows 11–19 · Sensitivity, rows 22–27 · Sensitivity, rows 31–38
What you should be able to explain
- Why a lender is forecast vintage by vintage: losses arrive a year or two after the lending that caused them, so a book that is growing fast looks cleaner than it is.
- How a cumulative loss curve by years on book turns into the year’s charge-offs, and what it means when one vintage’s curve runs above the last.
- Why the provision is not the charge-offs: a lifetime allowance charges a loan’s whole expected loss in the year it is made, so growth costs profit before it earns any.
- What an advance rate is, and why a funder cutting it is a call on the lender’s equity rather than a higher interest bill.
- Why net interest margin means little until charge-offs are taken off it, and how acquisition cost per loan follows originations while servicing follows the book.
- Why a lender is valued on equity, and how a return on tangible equity above the cost of equity becomes a price above tangible book.
What a reviewer looks for
- One loss rate applied to the whole book, so a year of fast growth lowers the loss rate and a year of slow growth raises it with no change in credit quality.
- The provision typed as the year’s charge-offs, which leaves out the allowance a growing book has to build.
- Debt sized as a share of assets with no advance rate, so the model never asks how much equity the receivables need.
- A warehouse that is the balancing item charged on its closing balance, which makes the workbook circular.
- A warehouse that lends whatever the book needs, so a cut in the advance rate is simply borrowed at the warehouse rate and never reaches the shareholders.
- Dividends paid out of profit without testing the covenant or the liquidity they leave behind.
- A DCF of free cash flow to the firm for a business whose debt is its raw material.
Conventions this workbook uses
Stated on the cover sheet too. A model is only as trustworthy as the decisions it tells you it made.
- Annual periods. FY0A is the opening balance sheet; income and flows run FY1E to FY5E. Every scenario changes FY1E onwards only.
- Originations fall evenly through the year. Years on book count from the year of origination (the first, about half a year on average) to three years on, by which time every loan has been repaid, prepaid or charged off.
- Charge-offs = principal originated x lifetime loss rate x the share of losses at that age x the selected scenario’s loss factor for the year. Charge-offs are net of recoveries. A vintage’s balance runs off on the base curve in every scenario: charge-offs above the curve take the place of scheduled repayments in the same year.
- The allowance is the loss still to come on every vintage on the book at the base curve, times the outlook factor for the year end. The provision is charge-offs plus the change in the allowance. When losses follow the curve it equals the lifetime loss rate times the year’s originations.
- Interest income and fees are earned on average receivables. Acquisition cost is charged per loan originated in the year it is made and is not deferred; servicing follows average receivables.
- Securitisation notes are a share of receivables times their advance rate, and the warehouse borrowing base is the rest times its advance rate. A change in an advance rate applies to all the receivables at once, as if every facility were re-margined in the year.
- The warehouse drawn is the balancing item: net receivables, minimum cash and goodwill, less the notes and equity. It is never drawn above its borrowing base less the minimum undrawn capacity: beyond that, after the year’s retained earnings, the lender issues shares. Notes are charged on the average of their opening and closing balance; the warehouse on the average of its opening balance and its closing balance before the year’s retained earnings, capped at the borrowing base less the minimum, so there is no circular reference.
- Dividends are the payout ratio on positive net income, cut to the tangible equity above the management target and to the undrawn warehouse capacity above its minimum, so a year that raises equity pays none. New shares are priced at a multiple of opening tangible book value per share. Earnings per share are on the average shares in issue; dividends and tangible book per share on the shares at the year end, when the dividend is paid.
- Valuation date is the end of FY0A, with year-end discounting at the cost of equity. Residual income is net income less the cost of opening tangible equity; the terminal value assumes the sustainable ROTE and growth from FY6E. Tangible book moves only by profit, dividends and equity raised, so the residual income value of the equity equals the value of its net distributions (dividends less equity raised) by construction. Value per share is what one share held today receives: its dividends and FY5E tangible book per share at the justified multiple. A raise below what new shares are worth passes value to their buyers.
What this model does not cover
Where its answer stops, so you know what to add before relying on it.
- Deposits, risk-weighted assets and regulatory capital. This is a non-bank lender; the bank model covers those.
- Monthly vintages and delinquency roll rates. Years on book stand for months on book, and a loan goes from performing to charged off with no 30, 60 and 90 day buckets between.
- Each securitisation as its own trust: the revolving period, note classes, triggers and excess spread. Notes are a share of receivables at one advance rate, re-margined every year.
- Credit cards and other revolving credit, where the balance is redrawn and a vintage is an account, not a loan.
- Interest rate hedging, deferred origination costs and fees, and deferred tax on the allowance.
- Tax losses carried forward. A loss year earns no tax credit, then or later, so a downturn costs a little more than it would after tax relief.
- How new shares are sold: a rights issue, a placing and their fees. Equity is raised at the year end at a set multiple of tangible book.
- A real company. OneMain Holdings and Enova International are candidates for a case built from their reports; this model uses invented figures.
Build it yourself
The starter workbook
The Vintages sheet has been cleared: what becomes of $1 originated by years on book, originations in the selected scenario, the matrix of charge-offs by vintage with each vintage’s cumulative loss curve, receivables by vintage and the receivables roll-forward. Rebuild it from the Assumptions and Scenarios sheets, one vintage to a row, then switch to the credit downturn and read the loss curves steepen from one vintage to the next.
Blanks: Loan vintages: run-off and the cumulative loss curve. Download it beside the worked model.
The path around this model
Understand it, drill it, read the build, then apply it to a real company.
Build · Lab · ~10 min
Covenant Headroom
Compute the three maintenance ratios from a certificate, find the binding test and its EBITDA cushion, and say whether a given downside breaches.
Build · Lab · ~10 min
Beta Refresher
Estimate a beta, see how far one regression lands from the truth, and know when to use raw, adjusted, unlevered or bottom-up.
Read · Guide · 11 min
Credit and Covenant Modelling: What a Lender Actually Tests
Read · Guide · 10 min
Scenario and Sensitivity Architecture: Building a Model That Can Be Stress-Tested
Read · Guide · 7 min
Equity Research Interview Questions: The Coverage Mindset
Apply · Skill
Company Profile
Build a banker-grade one-pager on any company, fast.
Apply · Skill
Model Audit
Find the errors in a financial model before someone senior does.
Vocabulary: Charge-Off, Advance Rate, Securitisation, Borrowing Base, Covenant, Credit Spread, Cost of Equity, Basis Points (bps), Equity Value.
Questions about this model
What is a loan vintage, and why build a lender on them?
A vintage is one year’s lending followed as a group for the rest of its life. Each $1 lent here ends one of three ways: 10% is charged off, 22% is prepaid and the rest is repaid on schedule, each on a timing curve by years on book. In FY1E the lender originates $2,100.0 million and the vintages on the book charge off $180.6 million, a net charge-off rate of 6.0% on average receivables of $2,993.9 million. Receivables of $3,152.5 million at the year end are the sum of what each vintage still has outstanding. A single loss rate on the whole book could not show that losses peak a year or two after the lending that caused them.
Why is the provision larger than the charge-offs?
Because the allowance holds the lifetime loss still to come on every loan on the book, so the whole expected loss of a loan is charged in the year it is made. In FY1E the provision is $210.0 million, 10.0% of the year’s originations, against charge-offs of $180.6 million; the other $29.4 million builds the allowance to $294.9 million, 9.4% of receivables. A lender that stops growing provides only what it charges off, and one that shrinks releases reserves.
What happens in the credit downturn?
Charge-offs run at up to 170% of the curve, the outlook in the allowance worsens and originations are cut. The FY0A vintage ends with a cumulative loss of 14.6% of its principal where the curve said 10.0%. In FY1E charge-offs are $248.4 million and the allowance is built up by $121.0 million, so the provision is $369.4 million and the lender loses $59.4 million. Tangible equity falls to 9.06% of receivables against a covenant of 9.00%, headroom of $1.7 million; no dividend is paid for two years, and the value per share falls from $13.78 to $8.32.
What does the funding squeeze do?
It cuts both advance rates by eight points and widens spreads by up to 2.5 points. The receivables the facilities will not fund rise from 12.8% to 20.8% of the book in FY1E, $655.7 million. The warehouse lends no more than its borrowing base of $816.5 million and the lender keeps $50.0 million of it undrawn, so it issues $142.9 million of new shares at tangible book, 22.3 million shares at $6.40, and pays no dividend that year. The cost of funds rises from 5.9% to 7.9%, FY1E net income falls from $79.9 million to $48.0 million, and the value per share is $10.03. Cut the advance rates alone and a share is worth $12.00 rather than $13.78: the equity as a whole is worth a little more, but the new shares are sold at book when each is worth more than twice that, and $2.10 a share passes to their buyers. Widen the spreads alone and it is worth $11.44.
How is a consumer lender valued?
On its equity, because its debt is its raw material. Residual income on tangible book, net income less a cost of equity of 11.9% on opening tangible equity, gives $13.78 a share, 2.15 times tangible book of $6.40. The justified multiple at a 16.0% sustainable return is 1.46 times; the forecast is worth more because it earns a return on tangible equity of 23.5% in FY1E. The dividends the plan pays, discounted at the same rate, give the same value. Where the plan needs new equity, the two part company: the equity as a whole is valued on its dividends less what new shareholders pay in, and a share held today on its own dividends, so shares sold below their worth cost the holders of the old ones.
Related models
Financial institutions · Advanced
Bank operating and valuation model
Loans, deposits, NIM, credit losses, CET1, residual income
Credit and restructuring · Advanced
Credit model
Debt capacity, covenants, sweep, maturities, recovery
Financial institutions · Advanced
Insurance underwriting and valuation model
Premium, reserves, combined ratio, float, solvency, P/B
Questions about this model
Discussions open here soon.