How to use it
- Export the trust bank account's transactions, all dates up to the reconciliation date, with the Name column. In QuickBooks: Reports → Transaction Detail by Account → Export to Excel. In Xero: Account Transactions → Export → Excel.
- Drop the file above, check the reconciliation date, and type the ending balance from the bank statement.
- Tick or add the outstanding checks and deposits in transit.
- When all three agree and no client is below zero, download the report and keep it with the month's records.
Why three figures, not two
A normal bank reconciliation only proves the books match the bank. A trust account also has to prove that the money belongs to the right people: the bank and the books can agree to the cent while one client's balance is negative and another client's money is paying for it. Adding the client ledgers as the third figure catches that.
Questions
What is a three-way reconciliation?
On one date, three figures for a trust (client money) account must agree: the bank statement balance, adjusted for deposits in transit and outstanding checks; the trust account balance in your books; and the total of every client's ledger balance. Lawyers (IOLTA and client accounts), real estate brokers, property managers and escrow agents are usually required to do it every month.
Which report do I export?
The trust bank account's transactions with the client's name on each line. In QuickBooks: Reports → Transaction Detail by Account (or the account's register), filtered to the trust account, dates All, with the Name column shown → Export to Excel. In Xero: Account Transactions for the trust account, with the Contact column. Any CSV or Excel file with a date, an amount and a name column works.
Where do the client balances come from?
From the Name column: every entry is added to the client it names, so deposits for a client increase their balance and payments for them reduce it. Entries with no name are listed apart, since they're in the books but on no client's ledger.
What are outstanding items?
Deposits entered in the books but not on the bank statement yet (deposits in transit) and checks written but not cashed yet (outstanding checks). If your export has QuickBooks's Clr column, the lines not marked cleared are ticked for you; you can also type them in.
Why does a client below zero matter?
A negative client balance means more was paid out for that client than was held for them, so another client's money covered it. Trust account rules treat this as a serious problem even when the totals agree, which is why it's flagged on its own.
Is my file uploaded?
No. The ledger is read and reconciled in your browser, and the Excel report is built there too. Nothing about your clients leaves your computer.
This tool does the arithmetic and flags problems. The rules for trust accounts differ by state and country; check with your bar, regulator or accountant what your monthly record must include.