Bank Statement Analysis for Underwriting in Excel

TL;DR Underwriting from bank statements comes down to a handful of measurements, calculated consistently for each month: true revenue (deposits minus transfers, loan proceeds, refunds and other non-revenue items), number of deposits, average daily balance, the lowest balance, days ending negative, returned items and overdraft or NSF fees, and recurring payments to other lenders. Convert each statement to one row per transaction, check that every statement balances, tag the deposits and debits that matter, and build a one-page monthly summary. The numbers then feed whatever credit policy you use. Consistency matters more than any single figure: the same rules applied to every file, with notes on every judgement call.

Disclaimer: This article describes general analysis techniques for reviewing bank statements. It is not legal, credit or compliance advice, and it doesn't describe any particular lender's credit policy. Lending is regulated, and rules on fair lending, disclosure, privacy and data handling vary by country, state and product. Follow your organization's policies and get qualified advice.

Bank statements are one of the main documents in small business lending, equipment finance, revenue-based financing and many alternative lending products. They're also used by brokers who pre-qualify applicants, by landlords reviewing commercial tenants, and by some lenders for self-employed mortgages. In each case, someone has to read three to twelve months of statements and turn them into a few reliable numbers.

Doing that by eye is slow and inconsistent. Two analysts reading the same statements can come up with different revenue figures because they treated transfers or loan proceeds differently. A spreadsheet-based approach, with clear rules, gives you repeatable numbers you can explain.

This guide covers the core measurements, how to calculate each one in Excel, the judgement calls that come up most often, how to spot statements that need a closer look, and a summary layout that works for most files. If you're on the applicant side, our guide to bank statements for a business loan explains what to prepare.

Start with complete, balanced data

Every measurement below depends on having every transaction. A missing page means missing deposits, which means understated revenue, or missing debits, which hides problems.

Get every statement for every account

Ask for complete statements, all pages, for every business operating account for the review period. Many applicants have more than one account, and money moving between them affects what you see on each.

Convert to one row per transaction

Convert each statement so that every transaction is a row with date, description, signed amount (deposits positive, debits negative) and the running balance if shown. Add Account and Statement columns so you can trace every row back to its source. SheetStatement does this and checks each statement's opening balance plus activity against its closing balance before export, which flags missing pages straight away. The free plan includes 3 pages a month for trying it out; Pro ($19) and Business ($39) monthly plans suit regular review volumes, alongside a $9 Starter plan.

Prove each statement balances

=ROUND(OpeningBalance + SUM(Amount) - ClosingBalance, 2)

Each statement should return 0. Also check that each statement's closing balance equals the next statement's opening balance. A gap means a missing statement. A statement that doesn't balance on its own may be missing a page, or may have been altered; either way, it needs attention before you analyze it. The free balance checker is a quick way to confirm totals.

The core measurements

Calculate these per month and then across the whole period.

1. Total deposits

The simplest figure: the sum of all money in.

=SUMIFS(Tx[Amount], Tx[Amount], ">0", Tx[Month], [@Month])

Useful as a starting point, but rarely the number you should rely on, because it includes things that aren't revenue.

2. True revenue (adjusted deposits)

Start from total deposits and remove items that aren't income from the business's operations. Common exclusions:

• Transfers from the business's own other accounts.
• Transfers from the owner, such as money moved from a personal account.
• Loan or advance proceeds from lenders.
• Refunds and reversals, such as a returned payment to a supplier coming back.
• Returned deposits: a customer check deposited and then returned unpaid.
• One-off non-operating items, such as insurance claims, asset sales or tax refunds, depending on your policy.

Add a Tag column and label each excluded deposit with a reason. Then:

=SUMIFS(Tx[Amount], Tx[Amount], ">0", Tx[Month], [@Month], Tx[Tag], "")

This gives deposits with no exclusion tag. Keeping the reason on each row means anyone reviewing the file can see exactly what was removed and why.

3. Number of deposits

=COUNTIFS(Tx[Amount], ">0", Tx[Month], [@Month], Tx[Tag], "")

The count tells you about the shape of the revenue. A business with 200 card settlement deposits a month is a different risk from one with two large deposits from a single customer, even if the totals are the same.

4. Average daily balance

The average of the end-of-day balance for every calendar day, including weekends and days with no transactions. It shows how much cushion the business typically holds.

You need a daily balance table: one row per calendar day, carrying forward the last known balance on days without transactions. Our guide to calculating average daily balance from a bank statement shows how to build it step by step. Then:

=AVERAGEIFS(Daily[Balance], Daily[Month], [@Month])

5. Lowest balance and negative days

From the same daily table:

=MINIFS(Daily[Balance], Daily[Month], [@Month])

=COUNTIFS(Daily[Balance], "<0", Daily[Month], [@Month])

The lowest balance and the number of days that ended negative show how close to the edge the business runs.

6. Returned items and overdraft or NSF fees

Search descriptions for terms your banks use for returned items, insufficient funds, overdrafts and related fees. The wording varies by bank, so build a keyword list and keep it up to date as you see new statements:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(NSFTerms[Term], [@Description])))>0, "NSF/OD", "")

Then count per month. Always review the matches by eye, because keyword searches catch false positives, like a merchant name that happens to contain one of the terms.

7. Existing debt payments

Recurring debits to other lenders, including loan repayments, merchant cash advance remittances, equipment leases and credit card payments, show existing obligations. Daily or weekly fixed debits to the same payee are a common pattern for some financing products.

Sort debits by payee and look for repeating amounts at regular intervals. Our guide to finding recurring payments in a bank statement has formulas that flag them. Tag each one with the lender name and total per month.

8. Large or unusual items

Flag deposits and debits above a threshold relative to the account's normal activity, for example anything more than a certain percentage of monthly true revenue. These deserve a note: a large customer payment, an asset purchase, a tax payment, an owner draw.

The monthly summary sheet

Put the results on one sheet, one row per month:

The figures are illustrative.

Below the monthly rows, add averages and totals across the period, and a notes section explaining each exclusion and anything unusual. In this example, the May exclusion of 20,000 might be new loan proceeds, which would also explain the higher debt payments that month.

A pivot table can build much of this; see our bank statement pivot table guide.

Businesses with several accounts

When an applicant has more than one operating account, analyze them together but keep each account's figures visible. Money moving between the accounts appears as a deposit on one statement and a debit on the other. If you add both accounts' deposits without removing these, revenue is overstated by the amount of every internal transfer.

A practical method:

1. Convert and balance each account separately.
2. Put all transactions in one table with an Account column.
3. Match internal transfers: same amount, opposite sign, different account, within a day or two. A COUNTIFS on amount and date range across accounts finds most of them; our guide to matching bank transactions in Excel explains the technique.
4. Tag both sides as internal transfers and exclude them from revenue and debit totals.

Average daily balance can be calculated per account and then summed by day for a combined figure, depending on your policy. Note which approach you used.

Self-employed borrowers and personal statements

Some lending decisions for sole proprietors rely on personal statements, either because the business doesn't have a separate account or because the lender looks at both. Personal accounts mix business income with salary, benefits, transfers from family and everyday spending, so the tagging step matters even more. The same rules apply: exclude transfers between the borrower's own accounts, flag large deposits that need an explanation, and keep a note on each judgement call. Our guide to self-employed proof of income from bank statements covers the borrower's side.

Judgement calls and how to handle them

Most disagreements about bank statement analysis come from a few recurring questions. Write your rules down, apply them the same way to every file, and note each case.

Transfers between related businesses. If the owner has two companies and money moves between them, is it revenue? Usually not, unless it's payment for genuine goods or services. Note what you decided and why.

Owner contributions. Money the owner puts into the business isn't revenue from operations. Exclude it, and note that it happened, because it may mean the business needed support that month.

Card processor settlements. Deposits from card processors are usually net of fees and may combine several days' sales. Treat them as revenue. If you need gross sales, ask for processor statements; the bank statement only shows what landed.

Payment platforms. Transfers from payment platforms can be customer revenue or the owner moving their own money around. The description may not tell you which. Ask.

Seasonality. A landscaping company's winter months look very different from its summer months. Look at the same months in previous years if you have them, rather than treating a slow season as a decline.

Returned customer payments. If a customer's payment is deposited and then returned, exclude both or neither, so the revenue figure isn't overstated.

Signs a statement needs a closer look

Statements are occasionally edited before they're submitted. Analysts aren't forensic examiners, but some signs suggest a closer look is warranted:

• The statement doesn't balance. Opening balance plus activity doesn't equal the closing balance, or the running balance column doesn't follow from the transactions.
• Statements don't join. A closing balance doesn't match the next opening balance.
• Inconsistent formatting. Fonts, alignment or spacing that differ from the rest of the document, or from other statements from the same bank.
• Round, regular deposits with vague descriptions that don't fit the business.
• Totals that don't match the summary section on the statement.

The balance checks above catch the most basic problems automatically. For anything beyond that, follow your organization's verification procedures, which may include obtaining statements directly from the bank or through an authorized data connection. Our guide to forensic accounting with bank statements covers deeper analysis techniques.

A worked example

A broker is pre-qualifying a restaurant for equipment financing and has six months of statements for its operating account.

1. Convert and balance. Six statements convert to about 1,100 rows. Five balance. The sixth is out by 2,316.40. The applicant had sent only pages 1 to 3 of 4. The broker requests the missing page, adds it, and the statement balances.
2. Tag exclusions. Two transfers from the owner's personal account (10,000 and 5,000), one loan disbursement (25,000), and a supplier refund (480).
3. True revenue. Total deposits over six months are 512,600. Excluding 40,480 gives true revenue of 472,120, or about 78,690 a month.
4. Deposit count. Mostly daily card settlements plus a few catering checks; between 160 and 190 deposits a month.
5. Average daily balance. Between about 11,000 and 19,000 a month.
6. Negative days and NSF. Three negative days in one month, with one overdraft fee. That's the month before the owner transfers.
7. Debt. A fixed weekly debit to an equipment lessor, and a daily fixed debit to a financing company that started after the loan disbursement.
8. Summary. A one-page monthly summary with notes on each exclusion, the negative days and the new financing.

The broker sends the summary along with the original PDFs. The figures can be traced row by row to the statements.

Making the process repeatable

• Keep a rules sheet in your workbook: exclusion categories, NSF keywords, large-item threshold, and how each judgement call is handled.
• Use the same template for every file. Paste in the converted transactions and the summary updates.
• Keep the PDFs with the workbook. Every figure should be traceable to a page.
• Record who reviewed the file and when.

If you review many files, the time goes into conversion and tagging. Our guide to a bookkeeper's workflow for multiple clients has ideas for organizing high volumes that apply here too.

Handling applicant data carefully

Bank statements contain sensitive personal and business information. Handle them according to your organization's privacy and security policies and the laws that apply to you: limit access, store files securely, and delete them when your retention policy says to. Check how any tool you use processes and stores documents; SheetStatement's approach is on the security page.

Analyze statements faster

Upload statement PDFs to SheetStatement, confirm each statement balances, and download Excel or CSV ready for your underwriting template. The free plan includes 3 pages a month to try it on a real file.

FAQ

What do underwriters look for in bank statements?

Commonly: revenue after excluding transfers and loan proceeds, the number of deposits, average daily balance, lowest balance, negative days, returned items and fees, and existing debt payments. Each lender's credit policy decides how these are used.

How do you calculate true revenue from a bank statement?

Start from total deposits and exclude transfers between the business's own accounts, owner contributions, loan proceeds, refunds and returned deposits. Tag each exclusion with a reason so it can be reviewed.

How is average daily balance calculated?

Average the end-of-day balance for every calendar day in the period, including days with no transactions, carrying the previous balance forward.

How many months of statements are usually reviewed?

It varies by product and lender. Three to twelve months is common. Follow your credit policy.

How can I tell if a statement is complete?

Check that the opening balance plus all transactions equals the closing balance, and that each statement's closing balance equals the next one's opening balance.

Can a spreadsheet detect altered statements?

Balance checks catch some inconsistencies, such as statements that don't add up or don't join. They don't replace verification procedures like obtaining statements directly from the bank.

Is this credit or compliance advice?

No. It's general information about analysis techniques. Follow your organization's policies and applicable law, and get qualified advice.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.