Blog · Forecast and pipeline · Banking
Build a bank cash flow schedule from loan receipts, deposit maturities and funding payments. Work through maturity buckets, cumulative balances and a deposit-withdrawal scenario, with spreadsheet formulas and reconciliation checks.
A bank cash flow forecasting model shows when expected cash receipts and payments create a funding gap. Assign flows to maturity buckets, roll forward available cash and test the assumptions that could change the lowest balance. A positive ending balance can still conceal a shortage earlier in the forecast.
The Federal Reserve describes liquidity as the capacity to meet cash and collateral obligations without unacceptable losses. A useful schedule therefore asks when funds are available for payment, rather than when income appears in earnings.
The general cash flow forecast method explains the opening-cash rollforward. A bank needs a different set of source rows: loan repayments, deposit withdrawals, securities receipts, funding maturities and commitments that may draw cash.
For an illustrative US bank, export the loan payment schedule, term-deposit maturity register, securities payment schedule, borrowing maturities and operating disbursements at one cutoff. Add separately identified assumptions for new funding and withdrawals from deposits without contractual maturities. Treasury owns those assumptions; finance reconciles opening cash and source totals, while risk reviews the scenarios.
Use cash payment amounts, including principal and interest where relevant. A loan balance is not itself a scheduled receipt. An undrawn borrowing line is potential capacity, not opening cash.
Use the following columns on a sheet named Flows. Keep amounts positive and use direction to distinguish receipts from payments.
| Column | Field | Check before grouping |
|---|---|---|
| A | Unique event ID | No duplicate payment events |
| B | Flow type | Loan receipt, deposit repayment or other defined class |
| C | Expected cash date | Actual date value, not text |
| D | Direction | Exactly In or Out |
| E | Amount (USD) | Positive cash amount |
| F | Legal entity | The entity receiving or paying cash |
| G | Currency | USD for this example |
| H | Basis | Contractual or assumption, with a reference |
Retain the source account reference in the working file so reviewers can investigate exceptions. Define whether the forecast begins before or after the cutoff day's settlements; otherwise the same payment can enter both opening cash and projected flows.
Start with narrow intervals where timing matters. Extend the horizon using the approach in long-term cash flow forecasting, while retaining enough detail to find intervening gaps. A broad bucket can hide a payment due before its offsetting receipt.
Assume opening available cash is $2,000,000. The bank sets an illustrative operating cash floor of $2,000,000; this is an example assumption, not a benchmark. The source rows aggregate as follows, with planned funding receipts clearly separated from contractual items.
| Cash flow | Direction | Days 1-7 (USD) | Days 8-14 (USD) | Days 15-30 (USD) |
|---|---|---|---|---|
| Contractual loan receipts | In | 2,400,000 | 3,000,000 | 4,200,000 |
| Contractual securities receipts | In | 1,000,000 | 1,500,000 | 2,000,000 |
| Planned new funding receipts | In | 500,000 | 0 | 500,000 |
| Contractual term-deposit repayments | Out | 2,700,000 | 3,500,000 | 4,000,000 |
| Contractual wholesale funding repayments | Out | 800,000 | 1,200,000 | 2,000,000 |
| Operating payments | Out | 200,000 | 300,000 | 400,000 |
For this simplified base case, assume no net movement in nonmaturity deposits and no other cash events. A live schedule must include other material flows, including expected loan originations and commitment draws.
Bucket net flow = Cash inflows - Cash outflows
Closing available cash = Opening available cash + Cumulative net flows
Cash needed to reach the floor = MAX(0, Operating floor - Closing available cash)
| Bucket | Inflows (USD) | Outflows (USD) | Net flow (USD) | Closing cash (USD) | Needed to reach floor (USD) |
|---|---|---|---|---|---|
| Days 1-7 | 3,900,000 | 3,700,000 | 200,000 | 2,200,000 | 0 |
| Days 8-14 | 4,500,000 | 5,000,000 | -500,000 | 1,700,000 | 300,000 |
| Days 15-30 | 6,700,000 | 6,400,000 | 300,000 | 2,000,000 | 0 |
| Total flows | 15,100,000 | 15,100,000 | 0 | Not additive | Not additive |
The middle bucket needs $300,000 to restore the floor: $2,000,000 less $1,700,000. Ending cash returns to the starting balance, but that does not eliminate the earlier need. All amounts are whole dollars, with no rounding in this example.
In an Excel summary sheet, put bucket start dates in A2:A4 and inclusive end dates in B2:B4. Store opening cash in J1 and the operating floor in J2. Columns C through G hold inflows, outflows, net flow, closing cash and cash needed.
For C2 and D2, respectively, use these formulas and fill down:
=SUMIFS(Flows!$E$2:$E$1000,Flows!$C$2:$C$1000,">="&A2,Flows!$C$2:$C$1000,"<="&B2,Flows!$D$2:$D$1000,"In",Flows!$F$2:$F$1000,"Example Bank",Flows!$G$2:$G$1000,"USD")
=SUMIFS(Flows!$E$2:$E$1000,Flows!$C$2:$C$1000,">="&A2,Flows!$C$2:$C$1000,"<="&B2,Flows!$D$2:$D$1000,"Out",Flows!$F$2:$F$1000,"Example Bank",Flows!$G$2:$G$1000,"USD")
Microsoft documents SUMIFS as summing amounts that meet multiple criteria. Expand every range together to cover the complete export. Use date-only values here; timestamps need an exclusive upper bound at the following day's start.
In E2, F2 and G2, respectively, enter and fill down:
=C2-D2
=$J$1+SUM($E$2:E2)
=MAX(0,$J$2-F2)
The total check is opening cash plus $15,100,000 less $15,100,000 equals $2,000,000. Independently tie the selected source rows to each export's control totals and show excluded or out-of-horizon rows separately. A zero net-flow total cannot prove that all payments were included.
Covirage is not affiliated with Microsoft.
Now change only one input: apply an additional withdrawal assumption to a nonmaturity-deposit segment in the first bucket. Keep all other flows unchanged.
| Assumption | Base case | Withdrawal scenario |
|---|---|---|
| Deposit segment balance (USD) | 12,000,000 | 12,000,000 |
| Additional withdrawal rate | 0% | 5% |
| Additional first-bucket outflow (USD) | 0 | 600,000 |
The additional outflow is $12,000,000 multiplied by 5%, or $600,000. Subtract it once from first-bucket net flow and carry the lower balance forward.
| Bucket | Base closing cash (USD) | Scenario closing cash (USD) | Scenario need against floor (USD) |
|---|---|---|---|
| Days 1-7 | 2,200,000 | 1,600,000 | 400,000 |
| Days 8-14 | 1,700,000 | 1,100,000 | 900,000 |
| Days 15-30 | 2,000,000 | 1,400,000 | 600,000 |
The maximum gap against the floor is now $900,000. These bucket gaps are snapshots, so do not sum them into a funding requirement. Any proposed funding action needs its own receipt date, availability assumption and later repayment.
The interagency funding and liquidity policy statement emphasizes documented assumptions, cash flow projections and accessible contingency funding. Review the scenario against the bank's approved process; this schedule does not calculate a regulatory liquidity ratio or certify compliance.
Upload a file containing your dated cash flows, opening balance and approved assumptions; Covirage maps the columns, and its tools compute and check bucket totals and cumulative balances. The external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for commercial banking for the supporting industry context.
Bank forecasts center on deposits, loan principal, securities cash flows, wholesale funding and contingent commitments. An operating company's schedule usually centers on customer collections and supplier payments. Loan principal and deposit flows therefore need their own treatment rather than being inferred from profit.
No. Keep their balances separate from contractual maturities and apply documented withdrawal assumptions by deposit segment and scenario. A contractual schedule alone cannot describe when these customers will withdraw funds, and recording the full balance as an expected immediate withdrawal obscures the behavioral assumption.
A negative bucket flow means payments exceed receipts during that interval. Whether cash becomes insufficient depends on opening available cash, earlier flows and the bank's operating floor. Review the cumulative balance and the timing within the bucket before deciding what funding action is needed.
A management schedule identifies timing gaps under its stated assumptions. Regulatory measures have their own scope, definitions and eligibility rules, so this example does not establish compliance. Treasury and risk teams should reconcile it with the bank's approved liquidity reporting and review processes.