Client funds in a trust (IOLTA) account must be tracked per client and never overdrawn — commingling or negative balances are serious ethics violations. A running per-client balance from a ledger keeps you compliant.
The example
Deposits and disbursements per client.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Deposits − disb. | |
| 3 | Lee balance | → $4,250 |
The formula
The formula:
How it works
How it works:
- Sum deposits for the client, subtract disbursements for the same client.
- The result is that client’s trust balance — their money, held separately.
- A negative balance means you disbursed more than the client deposited — an ethics violation; flag it with an IF.
- The sum of all client balances must reconcile to the bank account’s actual balance.
Two checks keep a trust account clean. First, no individual client balance may go negative (you can’t spend Client A’s money on Client B). Second, the sum of every client’s computed balance must equal the bank statement balance — a three-way reconciliation. Build both as IF flags on the ledger so a violation lights up immediately. This is illustrative bookkeeping, not legal or accounting advice.
Try it: interactive demo
Client deposits and disbursements.
Variations
Overdraft flag
Never below zero:
Three-way reconcile
Ledger vs bank:
Earned fees to move
Billed against trust:
Pitfalls & errors
Never commingle. Each client’s funds are tracked separately — per-client SUMIFS, not one total.
No negative balances. A client balance below zero is an ethics violation — flag it.
Not legal/accounting advice. Trust rules vary — this is illustrative.
Practice workbook
Frequently asked questions
How do I track a client trust balance in Excel?
How do I catch a trust overdraft?
What is a three-way reconciliation?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class