Bank Financial Model in Excel: FIG Modeling Guide (2026)

Point a standard DCF at a bank and you will produce a number that means nothing. Enterprise value assumes debt is financing; for a bank, deposits and borrowings are raw material — the thing it buys in order to sell loans. Subtracting net debt from a bank's enterprise value is like subtracting inventory from a retailer's.

That is why a bank financial model in Excel is built in the opposite direction from a corporate model: the balance sheet drives the income statement, regulatory capital caps growth, and the dividend is a plug rather than an assumption. This guide builds the whole thing — the earning-asset schedule, net interest income, the CECL allowance roll-forward, the CET1 constraint, and a dividend discount valuation — with formulas you can paste into a live workbook.

Why is a bank financial model different from a standard three-statement model?

In a corporate model, revenue drives the income statement and the balance sheet follows through working capital. In a bank model that reverses: you forecast balances first — loans, securities, deposits — then apply yields and costs to average balances to produce revenue. Capital ratios then constrain how fast those balances can grow.

The practical consequences are large enough that almost none of your corporate modeling muscle memory transfers cleanly.

Modeling element	Corporate model	Bank model	Why the difference
Revenue driver	Volume × price	Average earning assets × yield	Interest is a function of balances outstanding
Debt	Financing, sits below EBIT	Operating input — deposits and borrowings	Funding is the cost of goods sold
Cash	Plug from the cash flow statement	A yielding asset with a 0% risk weight	Reserves at the Fed earn interest
Growth constraint	Capex and working capital	CET1 ratio versus risk-weighted assets	Regulators cap leverage, not lenders
Dividend	Policy assumption	Plug that holds CET1 at target	Excess capital must be returned or deployed
Valuation	DCF on unlevered FCF, EV/EBITDA	DDM, excess returns, P/TBV and P/E	Enterprise value is not meaningful
Key margin	EBITDA margin	Net interest margin and efficiency ratio	Spread business, not a markup business

ℹ️ Note: The same logic applies to insurers, specialty lenders, and BDCs. Anything whose balance sheet is the business gets modeled balance-sheet-first. Only the regulatory layer changes.

The build order that actually works

1. Balance sheet schedule — loans by category, securities, cash, deposits by type, wholesale funding
2. Rate deck — policy rate path, curve assumptions, asset yields, deposit betas
3. Net interest income — average balances × rates
4. Provision — CECL allowance roll-forward and net charge-offs
5. Non-interest income and expense — fees, salaries, the efficiency ratio target
6. Capital — RWA, CET1 roll-forward, dividend and buyback capacity
7. Valuation — DDM or excess returns off the capital-constrained dividend stream

Each step feeds the next in one direction, which is what keeps a bank model out of circularity. Compare that with the three-statement financial model in Excel, where the interest-on-average-debt loop forces you to either iterate or hardcode.

How do you forecast net interest income in Excel?

Net interest income equals interest income minus interest expense, and both are calculated on average balances, not period-end balances. Multiply the average of opening and closing balance for each asset class by its yield, do the same for each funding source and its cost of funds, then subtract. Net interest margin is NII divided by average earning assets.

Set up the average-balance grid

Lay out one row per asset and liability category, with beginning balance, ending balance, average balance, rate, and income in adjacent columns. The averaging convention matters more than analysts expect — using ending balances in a fast-growing loan book overstates interest income by roughly half a year of growth.

=LET(
  bal_beg,  D12,
  bal_end,  E12,
  avg_bal,  (bal_beg + bal_end) / 2,
  yield_pct, F12,
  avg_bal * yield_pct
)

For a monthly or quarterly model, use the average of daily-average balances where you have them; banks disclose average balance sheets in their 10-Q, which is the single most useful page in the filing for calibration.

Model deposit betas, not deposit rates

The most consequential assumption in any bank model is the deposit beta — the share of a policy rate move that passes through to what the bank pays depositors. A bank with 25% beta on checking accounts and 70% on CDs behaves completely differently in a cutting cycle than one funded by brokered deposits.

=LET(
  rate_prior,   G20,
  policy_now,   Rates_Policy_Current,
  policy_prior, Rates_Policy_Prior,
  beta,         Assumptions_Beta,
  floor_rate,   0,
  MAX(floor_rate, rate_prior + beta * (policy_now - policy_prior))
)

Build betas by deposit category, and use asymmetric betas if your history supports it: deposit costs almost always rise faster than they fall. A 60% up-beta with a 40% down-beta is a common calibration for commercial deposits.

Funding source	Typical up-beta	Typical down-beta	Modeling note
Non-interest-bearing checking	0%	0%	Value comes from the float, not the rate
Interest checking and savings	15–35%	10–25%	Stickiest rate-paying category
Money market accounts	45–65%	40–60%	Rate-sensitive, moves with competition
Retail CDs	70–90%	65–85%	Reprices on maturity, so lags by term
Brokered deposits and FHLB	95–100%	95–100%	Effectively indexed to market

💡 Pro Tip: Track the non-interest-bearing deposit mix as an explicit output. When that mix falls, the whole funding cost curve shifts even if every individual beta assumption stays put. A model that only forecasts blended cost of deposits will miss the entire story.

Roll the balances forward

Each earning-asset category needs a simple roll-forward: beginning balance, originations, repayments and maturities, charge-offs, ending balance. The mechanics mirror a loan amortization schedule in Excel, scaled to a portfolio.

=LET(
  beg,        D30,
  originations, E30,
  runoff_rate,  F30,
  chargeoffs,   G30,
  runoff,     beg * runoff_rate,
  beg + originations - runoff - chargeoffs
)

Runoff rates are the honest way to model a loan book. A 20% annual runoff on a $10B portfolio means you must originate $2B just to stand still — which immediately disciplines growth assumptions that would otherwise look free.

graph LR
    A["Policy rate path"] --> B["Asset yields via loan betas"]
    A --> C["Deposit costs via deposit betas"]
    D["Loan and securities roll-forward"] --> E["Average earning assets"]
    F["Deposit and borrowing roll-forward"] --> G["Average funding balances"]
    B --> H["Interest income"]
    E --> H
    C --> I["Interest expense"]
    G --> I
    H --> J["Net interest income"]
    I --> J
    J --> K["Net interest margin equals NII over average earning assets"]

How do you model CECL provisions and the allowance for credit losses?

The provision expense is not an assumption — it is a derived plug from the allowance roll-forward. Set a target allowance as a coverage ratio on the loan book, then solve for the provision that gets you there after net charge-offs: Provision = Ending allowance − Beginning allowance + Net charge-offs.

The allowance roll-forward

Under ASC 326 (CECL), the allowance reflects lifetime expected losses on the loan portfolio, which means it is sized off balances and outlook, not off incurred losses. That makes the coverage ratio the natural driver. The same standard applies to trade receivables at a non-bank, but the driver is different — there the allowance is derived from an AR aging report in Excel and its roll rates rather than from a coverage ratio on a loan book.

=LET(
  allow_beg,     D40,
  loans_end,     E40,
  coverage,      Assumptions_Coverage_Ratio,
  ncos,          F40,
  allow_end,     loans_end * coverage,
  provision,     allow_end - allow_beg + ncos,
  provision
)

Two disciplines separate a credible provision forecast from a placeholder:

1. Charge-offs come first. Model net charge-offs as a loss rate on average loans by category — C&I, CRE, residential, consumer — because loss rates differ by an order of magnitude across them.
2. Coverage moves with the cycle. Holding coverage flat through a recession scenario is the single most common shortcut in bank models and it silently removes the credit cycle from your forecast.

⚠️ Warning: A growth scenario in CECL is provision-negative in year one. Because the allowance is lifetime, originating a new loan requires you to book the expected loss immediately, before earning the spread. If your model shows loan growth boosting EPS on day one, your provision logic is wrong.

Loss rates by category

Portfolio	Through-cycle NCO rate	Stress NCO rate	Coverage ratio range
Residential mortgage	0.05–0.15%	0.5–1.0%	0.3–0.8%
Commercial real estate	0.10–0.35%	1.5–3.0%	1.0–2.0%
Commercial and industrial	0.25–0.50%	1.5–2.5%	1.2–2.0%
Credit card	3.0–4.0%	7.0–10.0%	6.0–10.0%
Auto and consumer	0.5–1.5%	2.0–3.5%	1.5–3.0%

Use disclosed history from the bank's 10-K credit quality tables to anchor these, then scenario them. The technique is the same one covered in our sensitivity analysis in Excel guide: switch the loss-rate block by scenario index rather than overwriting cells.

How do you forecast non-interest income and expense?

Fee income should be driven by the activity that generates it — deposit accounts for service charges, AUM for wealth fees, origination volume for mortgage banking — never as a flat growth rate on the prior year. Expenses are best anchored to a target efficiency ratio, then checked against headcount and compensation build-up.

Fee income drivers

=LET(
  aum_avg,        (D50 + E50) / 2,
  fee_rate_bps,   Assumptions_Wealth_Fee_bps,
  service_charges, Accounts_Avg * Fee_Per_Account * 12,
  card_income,    Card_Spend * Interchange_Rate,
  aum_avg * fee_rate_bps / 10000 + service_charges + card_income
)

The efficiency ratio check

Efficiency ratio equals non-interest expense divided by revenue (NII plus fee income). Lower is better; US regional banks typically run 55–65%, the most efficient run below 50%.

=LET(
  nie,     Expense_Total,
  revenue, NII_Total + Fee_Income_Total,
  IFERROR(nie / revenue, NA())
)

Build expenses bottom-up, then display the implied efficiency ratio next to a target. If your bottom-up build implies 48% for a bank that has never printed below 58%, the assumption error is in your build, not in the bank.

How does regulatory capital constrain a bank model?

Capital is the governor on the whole model. Risk-weighted assets grow with the balance sheet, CET1 capital grows with retained earnings, and the CET1 ratio must stay above the bank's regulatory minimum plus its management buffer. The dividend — and any buyback — is whatever capital is left over after holding that ratio.

Calculating risk-weighted assets

RWA is a weighted sum: each asset class multiplied by its prescribed risk weight, plus add-ons for operational and market risk. SUMPRODUCT handles it in one cell.

=SUMPRODUCT(Balances_Range, RiskWeights_Range) + Operational_Risk_RWA

Standardized-approach risk weights worth memorizing: cash and reserves at the Fed 0%, US Treasuries 0%, agency MBS 20%, first-lien residential mortgages 50%, standard corporate and CRE loans 100%, and high-volatility CRE 150%. This is why a bank shifting from securities into C&I lending burns capital fast even with a flat balance sheet.

The CET1 roll-forward and dividend plug

=LET(
  cet1_beg,    D60,
  net_income,  E60,
  aoci_change, F60,
  rwa_end,     G60,
  target_ratio, Assumptions_CET1_Target,
  buyback,     H60,
  capital_before_div, cet1_beg + net_income + aoci_change - buyback,
  required_cet1,      rwa_end * target_ratio,
  MAX(0, capital_before_div - required_cet1)
)

That last line is the dividend. Wrapping it in MAX(0, ...) matters: if the bank is below target, the answer is not a negative dividend, it is a suspended dividend and — in a real stress case — a capital raise.

graph TD
    A["Loan and securities growth"] --> B["Risk-weighted assets"]
    C["Net interest income"] --> D["Pre-provision net revenue"]
    E["Fee income"] --> D
    F["Non-interest expense"] --> D
    D --> G["Provision expense"]
    G --> H["Net income"]
    H --> I["CET1 capital roll-forward"]
    B --> J["Required CET1 at target ratio"]
    I --> K{"Capital above requirement?"}
    J --> K
    K -->|"Yes"| L["Excess capital funds dividends and buybacks"]
    K -->|"No"| M["Cut distributions, shrink RWA, or raise equity"]
    L --> N["Dividend stream feeds the DDM valuation"]

💡 Pro Tip: Model the management buffer separately from the regulatory minimum. A bank with a 7.0% regulatory floor that runs to a 10.5% internal target has 350bps of policy, not regulation, in its dividend. Making that an input cell is what lets you answer "what if they run leaner?" without rebuilding anything.

How do you value a bank in Excel?

Value a bank on equity cash flows, not enterprise cash flows. The two standard approaches are the dividend discount model, which discounts the capital-constrained dividend stream at cost of equity, and the excess returns (residual income) model, which starts from book value and adds the present value of returns above cost of equity.

Dividend discount model

The DDM works for banks precisely because the dividend is not an arbitrary policy — it is the excess capital the model just calculated. Discount it at cost of equity from CAPM.

=LET(
  div_stream,  Dividends_Forecast,
  periods,     SEQUENCE(COUNT(Dividends_Forecast)),
  ke,          Cost_of_Equity,
  g,           Terminal_Growth,
  pv_explicit, SUM(div_stream / (1 + ke) ^ periods),
  terminal_val, INDEX(div_stream, COUNT(div_stream)) * (1 + g) / (ke - g),
  pv_terminal, terminal_val / (1 + ke) ^ COUNT(div_stream),
  pv_explicit + pv_terminal
)

Cost of equity comes from the standard CAPM build — see our beta calculation in Excel guide for the regression mechanics, and the dividend discount model in Excel walkthrough for multi-stage variants.

Excess returns model

The excess returns model is often more stable than a DDM because it puts most of the value in today's book value rather than in a terminal assumption.

=LET(
  bv_beg,   Book_Value_Beginning,
  roe,      ROE_Forecast,
  ke,       Cost_of_Equity,
  periods,  SEQUENCE(COUNT(ROE_Forecast)),
  excess,   (roe - ke) * Book_Value_Beginning_Series,
  bv_beg + SUM(excess / (1 + ke) ^ periods)
)

The intuition is worth stating plainly: a bank earning exactly its cost of equity is worth exactly book value. Every point of ROE above Ke creates value; every point below destroys it. That is why P/TBV and ROTCE plot on a near-straight line across the sector.

Method	What it discounts	Best used when	Main weakness
Dividend discount model	Capital-constrained dividends	Mature, well-capitalized banks	Very sensitive to terminal growth
Excess returns / residual income	ROE above cost of equity	Banks near or below book value	Requires clean tangible book value
P/TBV vs ROTCE regression	Market-implied relationship	Relative valuation across peers	Inherits the market's mood
P/E on normalized EPS	Through-cycle earnings	Stable-credit franchises	Breaks down at cycle turns
Sum of the parts	Segment-level economics	Diversified banks with fee businesses	Allocating capital by segment is subjective

For the peer-set mechanics, the approach in comparable company analysis in Excel applies directly — just swap EV/EBITDA for P/TBV and P/E, since enterprise multiples are meaningless here.

⚠️ Warning: Use tangible book value. Goodwill from past acquisitions is not loss-absorbing capital, regulators exclude it from CET1, and the market prices banks on ROTCE. A P/BV that includes goodwill will make a serial acquirer look permanently cheap.

What checks should a bank model have?

Every bank model needs a small block of always-visible integrity tests, in the same spirit as our financial model audit checklist.

1. Balance sheet ties — assets minus liabilities minus equity equals zero in every period
2. Loan roll-forward ties — beginning plus originations minus runoff minus charge-offs equals ending
3. Allowance roll-forward ties — beginning plus provision minus net charge-offs equals ending
4. CET1 never negative and never below the regulatory floor without a flagged capital action
5. Dividend payout ratio is plausible — a plug producing a 140% payout signals over-capitalization or an RWA error
6. NIM is within a sane band — anything outside roughly 2.0–5.0% for a US commercial bank needs an explanation

=LET(
  bs_check,    ABS(Assets_Total - Liabilities_Total - Equity_Total) < 0.001,
  loan_check,  ABS(Loans_Roll_End - Loans_BS_End) < 0.001,
  cet1_check,  CET1_Ratio >= Regulatory_Minimum,
  nim_check,   AND(NIM > 0.015, NIM < 0.055),
  IF(AND(bs_check, loan_check, cet1_check, nim_check),
     "All checks pass",
     "CHECK FAILED - review flags")
)

Put this cell in the top-left of every sheet with conditional formatting. A model that tells you the instant it breaks is worth more than one that is merely correct today.

Frequently Asked Questions

Why can't you use a DCF to value a bank?

Because a DCF values the enterprise, and for a bank debt is an operating input rather than financing. Deposits fund loans the way inventory funds a retailer's sales, so subtracting net debt to get equity value double-counts. There is also no meaningful unlevered free cash flow: capital expenditure is immaterial and working capital is the balance sheet. Value banks on equity cash flows instead — DDM or excess returns.

What is a good net interest margin for a bank?

US commercial banks typically run NIM between 2.5% and 4.0%, with community banks at the higher end and large custody or trust banks materially lower. NIM depends far more on funding mix than on asset yield: a bank with 30% non-interest-bearing deposits will out-earn a wholesale-funded peer through any rate cycle. Compare a bank to its own history and its funding-mix peers, not to the sector average.

How do you model deposit beta in Excel?

Regress historical changes in the bank's deposit cost against changes in the policy rate, by deposit category, using SLOPE on the change series. Apply the resulting beta as a coefficient on your forecast rate path: new rate = prior rate + beta × change in policy rate. Use separate up-betas and down-betas, since deposit costs consistently rise faster than they fall.

Is the dividend an assumption or an output in a bank model?

It should be an output. Once you forecast risk-weighted assets and a target CET1 ratio, the dividend is whatever capital exceeds the requirement after net income and buybacks. Modeling it as a fixed payout ratio hides the capital constraint and produces forecasts where a bank distributes capital it does not have — the exact failure mode that stress tests exist to catch.

What is the difference between CET1 and tangible common equity?

CET1 is the regulatory measure: common equity less goodwill, most intangibles, and certain deferred tax assets, subject to Basel III deductions, measured against risk-weighted assets. Tangible common equity is the accounting analogue — common equity less goodwill and intangibles — measured against tangible assets. They move together but rarely match, and analysts use CET1 for capital adequacy and TCE for valuation multiples.

Putting it together

A bank model is not harder than a corporate model; it is inverted. Forecast balances, apply rates to averages, derive the provision from the allowance, let capital decide the dividend, and value the equity directly. Once that skeleton is in place, scenarios become a rate-path change and a loss-rate switch rather than a rebuild.

The tedium is in the wiring — dozens of average-balance formulas, roll-forwards that must tie, and check cells across every sheet. That is precisely the work Dezzmond is built to take off your plate, generating the schedules and integrity checks from a plain-language description so you spend your time on the deposit beta assumption that actually decides the answer.

Start with one quarter of a real bank's 10-Q average balance sheet and rebuild its reported NII from the ground up. If you can tie to within a few basis points of the disclosed margin, the forecast is the easy part.