# Cash Flow Analysis Template for Lenders - Author: TrueBankFlow (https://www.truebankflow.com) - Published: 2026-04-12 - Updated: 2026-04-12 - Category: Analysis software and reports - Canonical: https://www.truebankflow.com/blog/cash-flow-analysis-template-for-lenders > A cash flow analysis template for lenders: monthly deposit rows, exclusions, averages, NSF counts and a notes column, with how to fill each section. A cash flow analysis template for lenders is a worksheet with one row per statement month, columns for total deposits, excluded items and qualifying deposits, a block for averages and trend, a count of NSF and overdraft events, and a notes column. Filled in carefully, it shows how the income figure was reached and lets a second reviewer check it. ## What goes in a cash flow analysis template for lenders? A lender's cash flow template needs five blocks: a header that names the borrower, accounts and period; monthly deposit rows; exclusion columns; a results block with averages and trend; and an events and notes area. Each block answers one question a reviewer will ask about the income figure. ### The filled example The table below is an example of the monthly block, completed for a fictional borrower with one business checking account and a six-month lookback. All names and figures are made up. | Month | Total deposits | Transfers in (excluded) | One-time items (excluded) | Qualifying deposits | NSF / OD events | Note ref | | --- | --- | --- | --- | --- | --- | --- | | 2026-03 | 14,250.00 | 2,000.00 | 0.00 | 12,250.00 | 0 | | | 2026-04 | 13,980.00 | 0.00 | 0.00 | 13,980.00 | 1 | N1 | | 2026-05 | 19,600.00 | 0.00 | 6,500.00 | 13,100.00 | 0 | N2 | | 2026-06 | 12,875.00 | 1,500.00 | 0.00 | 11,375.00 | 0 | | | 2026-07 | 14,410.00 | 0.00 | 0.00 | 14,410.00 | 0 | | | 2026-08 | 15,020.00 | 0.00 | 0.00 | 15,020.00 | 2 | N3 | | **Total** | **90,135.00** | **3,500.00** | **6,500.00** | **80,135.00** | **3** | | | **Monthly average** | **15,022.50** | | | **13,355.83** | | | ### The header block Above the monthly rows, record the borrower or business name, each account by its last four digits, the statement period covered, the loan program and its required lookback, and who filled in the sheet on which date. A header that names two accounts while the monthly rows cover one is the first thing a reviewer should catch. ### The results and notes blocks Below the rows sit the averages, the trend figure and the event count, then the notes keyed to the "Note ref" column. The sections that follow explain how to fill each one. ## Monthly deposit rows in the cash flow template Monthly deposit rows hold one statement period each, in date order, with the total of every credit on that statement. Use the statement's own period rather than calendar months when the bank cycle ends mid-month, and label the row by the statement end date so it matches the document. ### Fill rules for each row - **Total deposits** is every credit on the statement, before any judgment. It should match the deposit total printed in the statement summary, if the bank prints one. - **One row per statement, per account.** If the borrower has two accounts, either keep a separate block for each or add an account column. Mixing them in one row hides which account a deposit came from. - **No blank months.** A missing statement gets a row that says "not provided", not a zero. A zero looks like a month with no income and drags the average down; a missing row makes the average cover fewer months than the program asks for. ### Checking the row before moving on Before you trust a row, confirm the statement's opening balance plus credits minus debits equals its closing balance. A row built from a statement that does not reconcile may be missing a page. When more than one account is in the file, [combining bank statements for a mortgage file](/blog/combined-bank-statement-for-mortgage) covers how to keep the accounts apart while still producing one income figure. ## Exclusion columns for transfers and one-time deposits Exclusion columns hold the amounts removed from total deposits before the qualifying figure is computed, split by reason. Keeping transfers and one-time items in separate columns shows a reviewer why money came out, and keeps the qualifying column as plain arithmetic: total minus exclusions. ### Transfers in A transfer in is money the borrower moved from another of their own accounts. It is not new income, so counting it would count the same dollars twice if the other account is also in the file. In the example, March and June each show one transfer from the borrower's savings. The amount goes in the transfers column, and the savings statement should show the matching withdrawal. ### One-time items A one-time item is a real credit that will not repeat: an equipment sale, an insurance payout, a tax refund, a loan disbursement. In the example, May carries a 6,500.00 equipment sale, explained in note N2. Programs treat these differently, so record the item and its reason, and let the program rule decide whether it stays out. ### Recurring deposits that look one-time Some deposits look irregular but repeat on a longer cycle, such as a quarterly distribution or an annual contract payment. Before you exclude one, check whether the same source appears at a steady interval. [How recurring income detection groups deposits by source](/blog/recurring-income-detection) explains the pattern to look for. ## Averages and trend figures in the lender worksheet A lender worksheet needs two averages and one trend figure. The total average shows what came in; the qualifying average shows what counts. The trend compares later months with earlier ones, so a reviewer sees at a glance whether the business is growing, flat or shrinking across the lookback. ### Two averages, side by side In the example, total deposits average 15,022.50 a month and qualifying deposits average 13,355.83. The gap, about 1,667 a month, is the effect of the exclusions. A wide gap is not wrong, but it should be explained by the notes. A qualifying average that equals the total average on a file with transfers is a sign that the exclusion columns were skipped. Divide by the number of months the program requires, not by the number of rows you happen to have. If the program asks for 12 months and you have 11, the template should say the file is short, not quietly average 11. ### A simple trend figure One way to show direction is to compare the average qualifying deposits of the second half of the lookback with the first half. In the example: - March to May qualifying average: (12,250.00 + 13,980.00 + 13,100.00) / 3 = 13,110.00 - June to August qualifying average: (11,375.00 + 14,410.00 + 15,020.00) / 3 = 13,601.67 - Change: about 3.8% higher in the later half That is one illustrative method, not a program rule. Some lenders compare the last three months with the full average instead. Whichever method you use, write it in the sheet so the next reviewer applies the same one. ## How should the template count NSF and overdraft events? The template should count NSF and overdraft events per month, in their own column, and show the total for the lookback. Count each event once, on the date the bank charged it. A fee the bank later refunded still counts as an event, but the note should say it was refunded. ### What counts as one event An NSF event is an item the bank returned or charged a non-sufficient-funds fee for. An overdraft event is an item the bank paid while the balance went negative, usually with a fee. One day can carry several. If three checks bounce on one day, record three events and note that they share a date, since some lenders count per item and others per day. ### Why per month matters A total of three events reads differently when all three fall in one month than when they are spread across six. In the example, August carries two of the three, which is the most recent month. Note N3 should say what happened, because a cluster at the end of the lookback is the pattern an underwriter will ask about first. ### Fees outside the events column Monthly maintenance fees and wire fees are not NSF or overdraft events. Leave them out of this column. They belong in the expense picture, if your template tracks one, not in the event count. ## The notes column in a cash flow worksheet The notes column in a cash flow worksheet explains every judgment that changed a number. Each note names the date, the amount, what was decided and why, and what document supports it. A reviewer who reads only the notes should be able to rebuild every exclusion. ### Example notes These notes go with the example table above. They are illustrations. - **N1 (2026-04):** One NSF fee on 2026-04-17, 35.00, for a returned vendor payment. Fee refunded by the bank on 2026-04-19. Event still counted. - **N2 (2026-05):** 6,500.00 deposit on 2026-05-09 from the sale of a used delivery van. Bill of sale in file. Excluded as one-time. - **N3 (2026-08):** Two overdraft events, 2026-08-28 and 2026-08-29, during a week with a late customer payment. Letter of explanation requested. ### What makes a note useful A good note is specific enough that someone else would reach the same number. "Excluded odd deposit" is not a note. "Excluded 6,500.00 equipment sale, bill of sale in file" is. Notes are also where a reviewer records a decision to keep something in, such as a large deposit that turned out to be a regular customer paying two invoices at once. ## When does a spreadsheet template stop working for a lending team? A spreadsheet template stops working when the volume of files, the number of accounts per file or the number of people sharing the sheet outgrows careful manual entry. The arithmetic does not change; what breaks is the time to fill it and the chance of a copied formula or a skipped row. ### Signs the template has outgrown the desk - **Typing takes longer than judging.** Analysts spend most of a file keying in deposit totals rather than deciding what counts. - **Copies drift.** Each analyst has a slightly different version, with different averaging rules. - **Multi-account files are common.** Matching transfers between three or four accounts by eye is slow and easy to miss. - **Reviewers cannot trace.** A qualifying figure is in the sheet, but finding the statement line behind it means opening the PDFs again. ### What to do next When those signs appear, it is time to compare tools against the template rather than replace it blindly. [Cash flow analysis software for lenders, compared](/blog/cash-flow-analysis-software-for-lenders) sets out the types of tool, and [running a bank statement analysis pilot](/blog/bank-statement-analysis-pilot) explains how to score one against files your team has already worked by hand. A completed template is the answer key for that test. TrueBankFlow produces the same blocks this template holds: monthly totals, qualifying and total averages, NSF and overdraft events, and the exclusions and notes the reviewer added, with each total linked to its statement lines. If you want to compare it against your own sheet, you can [request a spot in early access](/get-started).