How to Build a Bank Fee Analysis Spreadsheet in Excel

Last updated July 2026.

A bank fee analysis spreadsheet has one job: put the volume and the unit price of every billed service next to the price you agreed to pay, month by month. Build it that way and billing drift becomes obvious in an afternoon. Build it around monthly totals instead, which is what most first attempts do, and you will see that fees went up without ever being able to say which service moved or by how much. The difference is entirely in the grain of the data.

Start from the account analysis statement, not the bank statement

These are two different documents and people mix them up constantly. The bank statement lists the transactions that moved through the account. The account analysis statement lists the services the bank performed and what it charged for each one, along with the earnings credit your balances generated to offset those charges. Only the second document carries unit pricing, so only the second document supports an analysis. If you have never seen one, ask your relationship manager for the account analysis statements for the last twelve months. Every US bank produces them monthly for commercial accounts.

The column layout that works

One row per service per account per month. That is the grain, and getting it right at the start saves rebuilding later. The columns:

• Statement month as a real date, set to the first of the month, so it sorts and pivots properly.
• Bank and account number, because a group with several relationships needs both to be comparable.
• Service description exactly as the bank prints it. Do not tidy it. You need the original text to trace a line back to the statement when someone challenges it.
• Bank service code and AFP code in separate columns. Some banks print both, some print only their own code.
• Standard service name, which is your own mapping column. This is the one you group by.
• Volume, unit price, and total charge as three separate numbers.
• Contracted unit price, pulled from your pricing schedule.

Keep the balance and settlement figures on a second sheet at one row per account per month: average ledger balance, average deposit float, average collected balance, earnings credit rate, earnings credit allowance, total charges, and net analyzed fee. Those are summary figures and they do not belong at service line grain.

Getting the data in without retyping it

This is where the project usually dies. A single corporate account can carry dozens of billed services, and twelve months across several accounts and several banks runs to thousands of lines. Nobody types that.

Ask the bank whether they can deliver the account analysis electronically. In the US the standard is the EDI 822 account analysis transaction set, offered by more than a hundred banks, and BSB, TWIST, and camt.086 do similar jobs internationally. If you can get it, take it.

The catch is that electronic delivery is forward looking. A bank can switch the format on for future periods but generally cannot reissue closed months in a format they were not produced in. So the history you actually want to analyze, the twelve or twenty four months that would show you a price drift, is almost always sitting in PDFs. Converting those PDFs is the practical answer: run them through a bank fee analysis converter and each service line comes back as a row with description, code, volume, unit price, and charge already separated. Turn on the electronic feed at the same time and you end up with a continuous data set rather than a gap where the interesting history should be. The same approach works for any financial document that only exists as a printed page, which is why teams that do this once tend to keep a PDF to spreadsheet workflow around for the rest of the finance stack.

The four tests worth running

Once the sheet exists, four checks find almost everything worth finding.

Price versus contract. Compare unit price to contracted unit price on every row. Filter to non zero differences. Repricing events are the usual cause: a schedule gets renegotiated, most services get updated, a handful quietly do not.

Recalculate the charge. Multiply volume by unit price and compare to the total charge the bank billed. This sounds redundant and it is not. Rounding conventions, minimum charges, and tiered pricing that was not applied correctly all show up here.

Services that should not be there. Pivot by standard service name and look for lines still billing on accounts or products that were closed or migrated. This is the single most common finding, and it persists because a closed product does not generate a complaint, it just keeps generating a small monthly charge.

Check the earnings credit. The standard calculation is the collected balance times one minus the reserve requirement, times the earnings credit rate, times days in the period over 365. Written out: EC = CB x (1 - RR) x ECR x (D/365). Reserve requirements were reduced to zero percent in March 2020, so the reserve term should no longer be cutting your credit. Legacy pricing setups have been known to carry the old deduction forward, and it is a one minute check.

How do I compare fees between two banks?

Only at the service line level, and only after mapping. Headline totals are useless for comparison because two banks process different volumes for you. What you want is unit price for the same service at a similar volume tier.

The obstacle is naming. Two banks will bill an identical service under two different descriptions, and even where both use AFP Service Codes, the six character standard the Association for Financial Professionals has maintained since 1986, application is inconsistent. Some statements carry only a proprietary code. So the mapping column in your sheet is the real work, and it is manual the first time. Map once, and every subsequent month drops into the same structure.

Budget for maintenance too. Banks revise pricing and reclassify services on a regular cycle, so a mapping that was correct last January will quietly stop matching. Reviewing new or renamed service descriptions each month takes a few minutes and prevents the sheet from silently going wrong.

What a first review usually turns up

Rarely anything dramatic. The recurring findings are ordinary and still worth money: services billing after the underlying product closed, a unit price that drifted off schedule at a repricing, duplicate charges where a service is billed at both the account and the group level, volume tiers that were never reset as your volumes grew, and an earnings credit rate that stopped tracking the market while everything else moved. None of these appear in a total. They appear when volume times unit price is compared to the contract, line by line.

The second use of the sheet is negotiation. Walking into a pricing conversation with twelve months of your own volumes by service changes the discussion, because you can ask for a specific unit price at a specific volume rather than asking for a discount in general. Banks respond very differently to the two requests.

Keep the analysis close to the ledger

The analyzed fee settles either as a direct debit against the account or as a separate invoice, and either way it has to be posted. Once the statement is in rows you can allocate charges to the entities or cost centers that generated them instead of dropping the whole amount into a single bank charges account, which makes next year's budget conversation a lot more concrete. If you are rebuilding the transaction side of the same accounts at the same time, the bank statement analyzer handles deposits and withdrawals, and transaction categorization codes those lines before they reach the general ledger.