L3VLUP
ProSign in
Financial institutionsAdvanced · ~50 minWorking draft · 10 sheets · 1030 formulas

Bank operating and valuation model

Loans, deposits, NIM, credit losses, CET1, residual income. Build a bank from its balance sheet: loans and deposits priced from the policy rate through betas, net interest income and the margin, an expected-loss allowance by stage, risk-weighted assets and the CET1 ratio against its requirement, the distributions the excess allows, and a value per share from residual income on tangible book.

By Surojit Chakraverti, founder of L3VLUP and an investor running a long-short healthcare and technology equities strategy.

On this subject: bank operating and valuation model.Updated 30 October 2026

Who builds it, and for whatThe model a bank analyst builds: in equity research to forecast net interest income and capital a quarter ahead of the bank, in a financial institutions group to advise on a merger, a capital raise or a buyback, and in credit to judge how much loss a bank can absorb. It answers what the bank earns on its balance sheet as rates move, what its loan book will cost in credit losses, how much capital that leaves above what the regulator requires, and what the shares are worth on the return the bank earns over its cost of equity.

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

Inspect the workbook
All checks read zero100%
Every value, every sheet, as the workbook holds it.
ABCDEFGHIJ
1Assumptions
2$m unless stated. FY0A is the opening balance sheet; the forecast runs FY1E to FY5E. Every figure is invented for teaching.
4YearUnitFY0AFY1EFY2EFY3EFY4EFY5ENotes
5Year number#012345FY0A = 0. Drives cost growth and discounting.
7Scenario
8Scenario#11 = base, 2 = upside, 3 = downside. Moves the policy rate, loan growth and credit quality in FY1E to FY5E.
9Policy rate, upside adjustmentpp0.8%Added to the base path in every forecast year when Scenario = 2. Higher rates widen a deposit-funded bank’s margin.
10Policy rate, downside adjustmentpp-1.5%Added when Scenario = 3: rates cut in a slowdown.
11Loan growth, upside adjustmentpp1.0%Added to every segment’s growth when Scenario = 2.
12Loan growth, downside adjustmentpp-3.0%Added when Scenario = 3.
13Credit migration multiplier, upsidex0.8xScales the stage 2 and stage 3 shares of every book when Scenario = 2. The base case is 1.0x.
14Credit migration multiplier, downsidex1.8xApplied when Scenario = 3: more loans deteriorate and more default.
16Policy rate
17Central bank policy rate, base case%4.0%3.8%3.5%3.3%3.3%3.3%The rate every asset yield and deposit rate is priced from.
19Loan book by segment
20Mortgages: gross loans, FY0A$m30,000.0
21Mortgages: loan growth, base case%4.0%4.0%4.5%4.5%4.5%Net of repayments and write-offs.
22Consumer loans and cards: gross loans, FY0A$m4,000.0
23Consumer loans and cards: loan growth, base case%6.0%6.0%6.0%6.0%6.0%Net of repayments and write-offs.
24Business and commercial loans: gross loans, FY0A$m12,000.0
25Business and commercial loans: loan growth, base case%3.0%3.5%4.0%4.0%4.0%Net of repayments and write-offs.
27Loan pricing
28Mortgages: repricing beta to the policy rate%50.0%Share of a policy rate move passed into the book’s yield within the year.
29Mortgages: spread over beta x policy rate%1.9%
30Consumer loans and cards: repricing beta to the policy rate%30.0%Share of a policy rate move passed into the book’s yield within the year.
31Consumer loans and cards: spread over beta x policy rate%8.5%
32Business and commercial loans: repricing beta to the policy rate%100.0%Share of a policy rate move passed into the book’s yield within the year.
33Business and commercial loans: spread over beta x policy rate%2.2%
35Liquid and other assets
36Cash at the central bank, % of deposits%11.0%Earns the policy rate.
37Investment securities, % of deposits%18.0%The liquid asset buffer: high-quality government and covered bonds.
38Securities: repricing beta to the policy rate%60.0%
39Securities: spread over beta x policy rate%0.6%
40Other assets$m1,500.0Held flat: property, equipment, derivatives and receivables.
41Goodwill and intangible assets$m800.0Held flat. Deducted from equity to reach tangible equity and CET1.
43Deposits and wholesale funding
44Current accounts: balance, FY0A$m14,000.0
45Current accounts: growth%1.0%1.0%1.5%1.5%1.5%
46Savings accounts: balance, FY0A$m22,000.0
47Savings accounts: growth%3.5%3.5%3.5%3.5%3.5%
48Term deposits: balance, FY0A$m9,000.0
49Term deposits: growth%5.0%5.0%4.5%4.5%4.5%
50Other liabilities$m1,500.0Held flat.
51Shareholders’ equity, FY0A$m4,100.0Ordinary equity only; no additional tier 1 is modelled.
53Deposit and wholesale pricing
54Current accounts: deposit beta%0.0%Share of the policy rate passed to depositors.
55Current accounts: spread over beta x policy rate%0.0%The rate paid is never below nil.
56Savings accounts: deposit beta%45.0%Share of the policy rate passed to depositors.
57Savings accounts: spread over beta x policy rate%-0.4%The rate paid is never below nil.
58Term deposits: deposit beta%85.0%Share of the policy rate passed to depositors.
59Term deposits: spread over beta x policy rate%0.0%The rate paid is never below nil.
60Wholesale funding: spread over the policy rate%1.2%
62Fees, costs and tax
63Account and payment fees, % of average current accounts%1.2%
64Lending and card fees, % of average consumer and business loans%0.6%
65Wealth, insurance and other income, FY0A$m260.0
66Wealth, insurance and other income, growth%4.0%
67Staff costs, FY0A$m620.0
68Staff cost growth%3.5%
69Technology, premises and other costs, FY0A$m560.0
70Other cost growth%3.0%
71Bank levy and deposit protection, % of opening liabilities%0.0%Four basis points of last year’s closing liabilities.
72Tax rate%25.0%
74Credit quality by stage
75Mortgages: stage 2 share of loans, base%6.0%Significant increase in credit risk since origination: lifetime expected loss.
76Mortgages: stage 3 share of loans, base%0.8%Credit-impaired.
77Mortgages: stage 1 coverage%0.1%Twelve months of expected loss.
78Mortgages: stage 2 coverage%1.0%
79Mortgages: stage 3 coverage%15.0%Close to the loss given default.
80Consumer loans and cards: stage 2 share of loans, base%12.0%Significant increase in credit risk since origination: lifetime expected loss.
81Consumer loans and cards: stage 3 share of loans, base%2.5%Credit-impaired.
82Consumer loans and cards: stage 1 coverage%2.0%Twelve months of expected loss.
83Consumer loans and cards: stage 2 coverage%12.0%
84Consumer loans and cards: stage 3 coverage%70.0%Close to the loss given default.
85Business and commercial loans: stage 2 share of loans, base%10.0%Significant increase in credit risk since origination: lifetime expected loss.
86Business and commercial loans: stage 3 share of loans, base%2.0%Credit-impaired.
87Business and commercial loans: stage 1 coverage%0.4%Twelve months of expected loss.
88Business and commercial loans: stage 2 coverage%3.0%
89Business and commercial loans: stage 3 coverage%40.0%Close to the loss given default.
90Write-offs, % of opening stage 3 loans%35.0%
91Additional cost of risk, every forecast yearbp-A management overlay above the expected-loss charge, in basis points of average loans. Builds the allowance.
93Risk-weighted assets
94Mortgages: risk density%15.0%RWA per $1 of exposure.
95Consumer loans and cards: risk density%75.0%RWA per $1 of exposure.
96Business and commercial loans: risk density%70.0%RWA per $1 of exposure.
97Investment securities: risk density%10.0%
98Other assets: risk density%100.0%
99Operational risk RWA, FY0A$m3,700.0
100Operational risk RWA per $1 of total incomex1.9xA 15% charge on income grossed up at the 8% minimum: 15% / 8%.
101Market risk RWA$m600.0Held flat.
102Other CET1 deductions$m200.0Deferred tax assets, prudent valuation and similar. Held flat.
103Off-balance-sheet exposure, % of gross loans%6.0%Undrawn commitments counted in the leverage exposure.
105Capital requirements and distributions
106Pillar 1 minimum CET1%4.5%
107Pillar 2 requirement met with CET1%1.5%Set by the supervisor for risks Pillar 1 misses.
108Capital conservation buffer%2.5%
109Countercyclical buffer%1.0%
110Systemic risk buffer%0.0%
111Management CET1 target%13.0%Distributions are paid only from capital above this ratio.
112Minimum leverage ratio%3.3%
113Ordinary dividend, % of net income%40.0%Paid only from distributable capital.
114Buybacks, % of the capital left above the target%50.0%
115Buyback price, multiple of opening tangible book per sharex1.1xAn illustrative input: the price the shares are assumed to trade at.
116Shares in issue, FY0Am shares1000
118Valuation
119Risk-free rate%4.2%
120Equity betaβ1.15
121Equity risk premium%5.5%
122Terminal return on tangible equity (ROTE)%12.5%The return the bank earns from FY6E on. Drives the terminal value and the justified multiple.
123Terminal growth%2.5%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

Bank operating and valuation model: the workbook

An Excel workbook with working formulas, no macros and no external links: the same model you can work with on this page.

  1. Browse without an accountPreview every model online, sheet by sheet, with its outputs and checks.
  2. 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.
  3. 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.
  4. L3VLUP Pro$25/monthEverything in Plus, every worked model in the library, and the Pro applications.
The worked model
10 sheets and 1,030 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 net interest income and the margin left blank for you to build, each blank cell marked. The worked model is its answer.
Checking your access Sign in for the starter workbook

What the base case says

Net interest margin, FY1E
2.6%
Cost-to-income ratio, FY1E
58.5%
Cost of risk, FY1E (bp)
47
ROTE, FY1E
14.7%
CET1 ratio, FY1E
13.7%
Leverage ratio, FY1E
4.9%
Value per share, residual income ($)
4.32
Implied price to tangible book
1.3x
Justified P/TBV at the terminal ROTE
1.2x

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 a retail and commercial bank, built from its balance sheet rather than its income statement: loans by segment, deposits by type, liquid assets and wholesale funding, each priced from the policy rate.

It runs from the balance sheet to net interest income and the net interest margin, to fees, costs and an expected-loss impairment charge with a stage view of every book, to net income, ROTE and capital: risk-weighted assets, the CET1 ratio against the requirement and a management target, the leverage ratio, and the dividends and buybacks the excess capital allows. Set Scenario on the Assumptions sheet to 2 (upside) or 3 (downside) to move rates, growth and credit quality together.

A bank’s product is its balance sheet. It earns the spread between what its loans yield and what its deposits cost, and how much of a rate move it keeps depends on its deposit betas: the share of each move it passes on to savers.

Capital, not cash, is the binding constraint. Every loan consumes capital in proportion to its risk density, and the regulator sets the ratio below which distributions stop. So growth, credit losses and payouts all meet on the Capital sheet.

Banks are valued on equity, not enterprise value. The Valuation sheet values residual income on tangible book, shows the justified price to tangible book that a return, a cost of equity and a growth rate imply, and cross-checks both against the distributions the capital plan allows.

Seats: Investment banking, Equity research and hedge funds.

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.

Bank balance sheet: loan book and funding

Loans by segment, deposits by type, wholesale funding

Assumptions, rows 20–25 · Assumptions, rows 36–41 · Assumptions, rows 44–51 · Balance Sheet, rows 6–15 · Balance Sheet, rows 18–22 · Balance Sheet, rows 25–38

Net interest income and the margin

Yields, deposit betas, NII and NIM

Assumptions, row 17 · Assumptions, rows 28–33 · Assumptions, rows 54–60 · Net Interest Income, rows 6–14 · Net Interest Income, rows 17–23 · Net Interest Income, rows 26–32 · Net Interest Income, rows 35–39

Credit losses: expected-loss allowance and cost of risk

Stages, coverage, allowance roll-forward, cost of risk

Assumptions, rows 75–91 · Credit Losses, row 6 · Credit Losses, rows 9–12 · Credit Losses, rows 15–18 · Credit Losses, rows 21–24 · Credit Losses, rows 27–29 · Credit Losses, rows 32–40 · Credit Losses, rows 43–46

Regulatory capital: RWA, CET1 and distributions

Risk densities, CET1 ratio, buffers, leverage, payouts

Assumptions, rows 94–103 · Assumptions, rows 106–116 · Capital, rows 6–28 · Capital, rows 31–40 · Capital, rows 43–50 · Capital, rows 53–61

Residual income valuation and the justified P/TBV

Profit above the cost of equity, on tangible book

Assumptions, rows 119–123 · Valuation, row 6 · Valuation, rows 9–22 · Valuation, rows 25–27 · Valuation, rows 30–38 · Valuation, rows 41–47

Sensitivity tables

Two assumptions at once, without a data table

Sensitivity, rows 5–8 · Sensitivity, rows 11–16 · Sensitivity, rows 19–26 · Sensitivity, rows 29–34

What you should be able to explain

  • Why a bank is modelled from its balance sheet: its revenue is a spread on balances, so loans, deposits and their rates come first and the income statement follows.
  • How deposit betas decide how much of a rate move reaches the net interest margin, and why a bank funded by current accounts gains when rates rise.
  • How an expected-loss allowance works by stage, and why the impairment charge is whatever brings the allowance to the requirement after write-offs, not a number typed in.
  • Why capital, not cash, is the binding constraint: risk-weighted assets, the CET1 ratio against a requirement built from buffers, and distributions only from the excess.
  • Why banks are valued on equity, and why the justified price to tangible book, (ROTE - g) / (cost of equity - g), is a residual income model in one line.

What a reviewer looks for

  • Forecasting net interest income from a growth rate, so a change in rates or in the deposit mix cannot move it.
  • Valuing a bank with a DCF of free cash flow to the firm, when its debt is its raw material and enterprise value has no meaning.
  • Typing the impairment charge as a share of loans rather than building the allowance it must reach, so a downturn barely shows.
  • Paying dividends out of profit without asking whether the capital ratio can afford them.
  • Making wholesale funding the balancing item and charging interest on its closing balance, which makes the workbook circular.

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. Loans and deposits grow at input rates; cash and securities are held as shares of deposits; other assets, intangibles and other liabilities are held flat.
  • Interest is earned and paid on the average of opening and closing balances. Wholesale funding, the balancing item, is charged on the average of its opening balance and its closing balance before the year’s retained capital, so there is no circular reference; the interest saved on one year’s retained profit arrives a year late.
  • Credit losses follow an expected-loss approach of the IFRS 9 kind: stage 1 at twelve months of expected loss, stages 2 and 3 at lifetime loss, each segment’s stage shares scaled by the scenario. The charge brings the allowance to the requirement after write-offs, plus any overlay. Loan growth is net of write-offs.
  • RWA is each exposure times its risk density, operational risk RWA a multiple of the year’s income, market risk RWA flat. CET1 capital is equity less intangibles and other deductions; no additional tier 1 or tier 2 capital is modelled, so tier 1 equals CET1.
  • Distributions come only from capital above the management target: the ordinary dividend at the payout ratio, cut if the excess is smaller, then a share of what remains as buybacks at an assumed multiple of opening tangible book per share.
  • 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 terminal ROTE and growth from FY6E. Tangible book moves only by profit and distributions, so the dividend view equals the residual income view by construction.
  • The capital grid holds deposits, costs and the cost of equity, and rebuilds FY1E at each loan growth and overlay from the model’s own lines.

Build it yourself

The starter workbook

The net interest income build has been cleared: the rates priced from the policy rate, interest income and expense on average balances, net interest income and the margin. Rebuild it from the Assumptions and Balance Sheet sheets, remembering that wholesale funding is charged on its balance before the year’s retained capital. The Checks sheet shows when the capital walk, the valuation and the grids tie again.

Blanks: Net interest income and the margin. 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

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 · 7 min

Equity Research Interview Questions: The Coverage Mindset

Read · Guide · 7 min

Enterprise Value vs. Equity Value, Once and For All

Read · Guide · 11 min

Financial Modelling Best Practices: The Conventions That Make a Model Auditable

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: Cost of Equity, Equity Value, Basis Points (bps), Loss Given Default (LGD), Liquidity, Terminal Growth Rate, Dividend Yield.

Questions about this model

What is net interest margin, and what moves it?

Net interest income over average interest-earning assets. Here the bank earns 2.64% in FY1E: its loans and securities yield more than its deposits and wholesale funding cost. When the policy rate falls, loan yields fall by their repricing betas and deposit rates by their deposit betas; current accounts pay nothing whatever happens, so a bank funded by them loses margin as rates are cut, which is what the base case shows.

How is the impairment charge built?

Each book is split into stage 1 (performing, twelve months of expected loss), stage 2 (deteriorated, lifetime loss) and stage 3 (credit-impaired), each with its own coverage. The allowance must equal that requirement, so the charge is the change in the requirement plus write-offs. In FY1E that is a cost of risk of 47bp; the downside, with 1.8 times as many loans in stages 2 and 3, takes it to 97bp.

Why is the CET1 ratio the number everything else depends on?

Because the regulator sets a requirement (here 9.5% including buffers) below which distributions are restricted, and management holds a target above it (13%). Every loan uses capital in proportion to its risk density, so growth, losses and payouts all meet in one ratio. The bank ends FY1E at 13.7%, paying the ordinary dividend and buying back half of what is left above its target.

Why value a bank on residual income rather than a DCF?

A bank’s debt is its raw material, so free cash flow to the firm and enterprise value mean little. Residual income values the equity directly: tangible book today plus the present value of every year’s profit above the cost of equity. Here that gives $4.32 a share, 1.31 times tangible book; at the 12.5% terminal ROTE the justified multiple is 1.25 times, and the two agree exactly when the bank is in steady state.

Related models

Financial institutions · Advanced

Insurance underwriting and valuation model

Premium, reserves, combined ratio, float, solvency, P/B

Financial institutions · Core

Asset manager AUM and fee economics model

AUM roll-forward, flows, fee rates, margin, P/E and DCF

Credit and restructuring · Advanced

Credit model

Debt capacity, covenants, sweep, maturities, recovery

Questions about this model

Discussions open here soon.

All discussions

Share your result 📊

𝕏in💬🤖

Instagram and TikTok have no desktop share link, so copy the caption and paste it into the app.