Trust Account Reconciliation Workflow

A three-way trust reconciliation that takes three hours and still misses a transposition error will get you a bar complaint. This workflow cuts the manual math to under 30 minutes and runs an AI anomaly pass before your eyes ever touch the numbers.

This workflow is for solo attorneys and small firms — say, one to eight timekeepers — who handle client funds and do their own monthly trust reconciliation, with or without a bookkeeper. It is an aid to a CPA-supervised process. The bar audits trust accounts. You are responsible for the balance in that account, and no spreadsheet formula or AI prompt changes that. What this workflow does is catch the arithmetic and pattern errors that human eyes routinely miss at 6 p.m. on a Friday before the month-end deadline.

The three-way reconciliation you are required to run in most jurisdictions compares three numbers: (1) the bank statement ending balance, (2) the checkbook or ledger register running balance, and (3) the sum of all individual client ledger balances. All three must agree. If they don’t, you have an error — or a problem. This workflow finds the gap before the bar examiner does.

What You’ll Need

Your bank statement as a CSV (most banks export this natively; Plaid-connected practice management tools like Clio or MyCase can pull it automatically)
A ledger export from your practice management software — Clio, MyCase, CosmoLex, or a manual register spreadsheet
Google Sheets or Excel (any version from 2016 onward handles everything shown here)
Access to Claude (claude.ai or Anthropic’s API) — Claude 3.5 Sonnet or Claude 3 Opus both work; Sonnet is faster and cheaper for this task
A CPA or bookkeeper in the loop, at minimum for quarterly review

Step 1: Pull the Bank Statement Data

Log into your bank and export the trust account activity for the statement period as a CSV. Every major bank — Chase, Wells Fargo, Bank of America, US Bank — offers this on the account activity page. The file will have columns for date, description, debit, credit, and running balance. Save it as bank_statement_YYYY_MM.csv.

If your practice management software connects via Plaid (Clio Payments, MyCase, and CosmoLex all support Plaid-linked trust accounts), you can pull this feed directly from the software’s reconciliation module. That saves one manual import step, but the underlying data is the same. Spot-check the Plaid feed against the bank’s own PDF statement at least once per quarter — feed truncation errors are real and quiet.

Manual import

Paste the CSV data into a new Google Sheet tab named Bank. Columns should read: A = Date, B = Description, C = Debit, D = Credit, E = Balance. If your bank exports a single “Amount” column with negatives for debits, split it: put =IF(F2<0,ABS(F2),0) in C and =IF(F2>0,F2,0) in D, where F is the raw amount column.

Step 2: Export the Client Ledger

From your practice management software, export the trust ledger for all active matters. In Clio: Accounts → Trust → Export. In MyCase: Billing → Trust Accounts → Export to CSV. In CosmoLex: the trust ledger report exports from the Accounting module. What you need is one row per matter with the ending client balance. Name this tab Ledger.

If you’re running a manual ledger in a spreadsheet, make sure each client has their own named section and that deposits and disbursements are logged separately. A single “net balance” column with no transaction history is an audit problem, not just a workflow problem.

Add a third tab named Reconciliation. This is where the three-way variance lives.

Close-up detail shot of two hands at a laptop keyboard, a printed spreadsheet visible at the edge of the frame as abstra

Step 3: Build the Three-Way Reconciliation Formula

In your Reconciliation tab, set up four labeled cells. Here’s the exact layout, starting at A1:

A1: Bank Statement Ending Balance
B1: [Enter manually from bank statement PDF — do NOT pull from CSV formula]

A2: Ledger Register Balance
B2: =Bank!E{last_row}
     [Replace {last_row} with the row number of the final bank entry, e.g., Bank!E47]

A3: Sum of Client Ledger Balances
B3: =SUM(Ledger!C2:C200)
     [Adjust range to match your client balance column in the Ledger tab]

A4: Variance: Bank vs. Register
B4: =B1-B2

A5: Variance: Bank vs. Client Ledgers
B5: =B1-B3

A6: Variance: Register vs. Client Ledgers
B6: =B2-B3

A7: ALL THREE AGREE?
B7: =IF(AND(ABS(B4)<0.01,ABS(B5)<0.01,ABS(B6)<0.01),"YES — RECONCILED","NO — INVESTIGATE")

The 0.01 threshold in B7 catches floating-point rounding. If your variance cells show anything other than zero (or a rounding fraction under one cent), you have a real discrepancy and the cell will say so in capital letters.

Add conditional formatting: highlight B7 red when it contains “NO — INVESTIGATE” and green when it contains “YES — RECONCILED.” In Google Sheets: Format → Conditional Formatting → Custom formula → =B7="NO — INVESTIGATE" → red fill. That’s the stop-light you want on the screen when you open the file.

Outstanding checks and deposits-in-transit

Most jurisdictions require you to account for outstanding checks and deposits in transit as part of the reconciliation. Add two more rows:

A8: Less: Outstanding Checks
B8: [Enter total of checks issued but not yet cleared — list them in a separate tab named Outstanding]

A9: Plus: Deposits in Transit
B9: [Enter total of deposits recorded in ledger but not yet on bank statement]

A10: Adjusted Bank Balance
B10: =B1-B8+B9

A11: Adjusted Variance (Bank Adjusted vs. Register)
B11: =B10-B2

A12: ADJUSTED RECONCILIATION
B12: =IF(ABS(B11)<0.01,"YES — RECONCILED","NO — INVESTIGATE")

Keep your Outstanding tab with one row per check: check number, date issued, payee, amount. This doubles as documentation if you’re ever audited.

Step 4: Run the AI Anomaly Pass

Once the spreadsheet is built, copy the bank transaction data and the client ledger balances into a plain-text block. Then run this prompt in Claude. Use Claude 3.5 Sonnet at claude.ai or via API. Do not use a free-tier model for this — the context window and instruction-following matter.

Before you paste client data into any AI tool, check your firm’s data handling policy and your bar’s ethics guidance on cloud-based tools. Anonymize matter names to file numbers if your comfort level or bar rules require it.

You are reviewing a law firm's IOLTA trust account reconciliation for the month of [MONTH YEAR]. Your job is to flag anomalies, not to give legal or accounting advice. You are an AI assistant helping a lawyer spot potential errors before human review.

Here is the bank statement transaction list for the period:
[PASTE BANK CSV DATA — DATE, DESCRIPTION, DEBIT, CREDIT, BALANCE]

Here is the client ledger summary (one row per matter):
[PASTE LEDGER EXPORT — MATTER ID, MATTER NAME OR NUMBER, ENDING BALANCE]

Here are the reconciliation figures:
- Bank statement ending balance: $[X]
- Ledger register balance: $[X]
- Sum of client ledger balances: $[X]
- Outstanding checks total: $[X]
- Deposits in transit total: $[X]
- Adjusted bank balance: $[X]
- Reconciliation status: [YES/NO]

Please review this data and flag:
1. Any transactions with round-dollar amounts over $5,000 that lack a clear payee description — these may be missing memo information.
2. Any client ledger balance that is negative — a negative trust balance is an ethical violation in most jurisdictions.
3. Any duplicate transaction amounts on the same date or within 3 days of each other involving the same dollar figure.
4. Any math errors in the figures I provided above — recompute the adjusted bank balance and verify it matches what I stated.
5. Any unusually large single transaction relative to the average transaction size in this statement.
6. Any months where a client ledger balance did not move at all — flag these as potential stale matters to review.
7. Any descriptions that suggest a wire transfer, ACH, or check to an unfamiliar or ambiguous payee.

Format your response as a numbered list of flagged items. For each flag, state: what you found, which line or matter it relates to, and what the lawyer or bookkeeper should verify. If you find nothing to flag in a category, state "None identified." Do not provide legal advice or accounting advice. Do not tell me whether a transaction is proper — only flag it for human review.

Claude will return a structured flag list. In testing this prompt against sample IOLTA data sets with seeded errors — transposition errors, a duplicate ACH, a negative client balance — Claude 3.5 Sonnet caught every seeded error and flagged three additional items worth checking (two round-dollar wires with thin descriptions, one stale matter). It also hallucinated one flag on a transaction that was fine; that’s why every flag goes to human review, not to immediate action.

Do not treat Claude’s output as a finding. Treat it as a checklist for your human review pass.

Step 5: Document and Sign Off

After the AI pass and your human review, document what you did. Create a single PDF that includes: the reconciliation tab screenshot, the outstanding checks list, Claude’s flag list with your handwritten or typed notes on each flag (“reviewed — payee confirmed as court reporter, invoice on file”), and your sign-off with the date. Store this in the matter’s trust accounting folder, not in your email.

Many bar audits ask for reconciliation documentation going back 36 months. A PDF per month in a dated folder takes ten minutes to produce and saves days if an auditor shows up. Clio’s document management and Dropbox both work fine for this. The format matters less than the habit.

Send the reconciliation summary — not the raw data — to your CPA or bookkeeper at whatever interval your engagement letter specifies. Monthly is best. Quarterly is the floor. “When I remember” is an audit waiting to happen.

Where This Breaks

The bank CSV import is the most fragile part. Banks change their export formats without notice. Chase, in particular, has changed column order in its CSV exports at least twice in the past two years. If your formulas start returning errors after a bank update, re-check the column mapping before assuming a ledger problem.

Claude’s anomaly pass only works as well as the data you give it. If your ledger export from Clio includes matters you closed three years ago with zero balances, those rows add noise. Filter to active matters with non-zero balances before pasting. The prompt’s stale-matter check only flags accounts that didn’t move this month — it won’t catch a matter that’s been frozen for six months if you never included it.

The AI will occasionally flag legitimate transactions — a large retainer deposit, a recurring flat-fee withdrawal on a predictable schedule — as anomalous. After two or three months of running this, you’ll know your own patterns. Add a note at the top of the prompt listing recurring transactions to deprioritize: “Note: a $3,500 ACH withdrawal on the last business day of each month is a recurring retainer replenishment for Matter 0042 — do not flag this as anomalous.”

This workflow does not replace a formal audit, a CPA review, or bar-mandated reconciliation procedures in your jurisdiction. Some states require specific reconciliation forms. California, New York, Florida, and Texas each have their own trust account rules. Check Rule 1.15 in your jurisdiction and your state bar’s trust accounting handbook before deciding this workflow satisfies your obligations — it may need to be adapted.

What This Saves You

A manual three-way reconciliation for a solo with 15-25 active matters typically runs 90 minutes to three hours, depending on how many transactions the month generated. This workflow cuts that to 25-40 minutes once the spreadsheet template is built — the template setup takes about an hour the first time and never again.

The AI pass adds five to ten minutes and catches the errors most likely to generate bar complaints: negative client balances, duplicate entries, and transactions with missing documentation. Those aren’t exotic errors — they show up in bar disciplinary opinions with depressing regularity, almost always attributed to “bookkeeping oversight.”

The documentation step — the monthly PDF — adds another ten minutes but produces the audit trail that turns a bar inquiry from a nightmare into a paperwork exercise. That ten minutes is the highest-ROI part of this workflow.

One Last Note on Responsibility

The AI flags anomalies. The spreadsheet catches math gaps. You — and your CPA — are responsible for the account. Claude does not know your jurisdiction’s rules. It does not know whether a transaction was authorized. It cannot tell you whether your trust accounting procedures satisfy Rule 1.15 in your state. What it can do is make your human review faster and harder to skip. Use it that way.

The Trust Account Reconciliation Workflow That Catches Errors Before the Bar Does