Three-Way Trust Account Reconciliation Excel Template (Law Firm IOLTA & Property Management Audit Sheet)
01_Three_Way_Reconciliation — Interactive Spreadsheet
| # | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | THREE-WAY TRUST ACCOUNT RECONCILIATION MASTER WORKPAPER | ||||||||
| 2 | Legal IOLTA & Real Estate Property Management Fiduciary Audit | Entity: Sterling & Hayes Legal Group LLP | Cutoff: March 31, 2026 | Audit: ZERO VARIANCE | ||||||||
| 3 | |||||||||
| 4 | RECORD 1: ADJUSTED BANK | RECORD 2: CHECKBOOK REGISTER | RECORD 3: CLIENT SUBLEDGERS | TRI-RECONCILIATION STATUS | |||||
| 5 | $1,482,650.00 | $1,482,650.00 | $1,482,650.00 | BALANCED ($0.00) | |||||
| 6 | |||||||||
| 7 | RECORD 1: BANK STATEMENT RECONCILIATION (ADJUSTED BANK BALANCE) | ||||||||
| 8 | Line # | Reconciliation Component / Line Item | Reference / Account # | Source Schedule | Item Amount ($) | Adjusted Bank Balance ($) | Audit Status | Tickmark | Audit & Corroboration Notes |
| 9 | 1.1 | Bank Statement Ending Balance as of Cutoff | Chase IOLTA Acct ...8842 | Bank Statement | - | VERIFIED | TB | Corroborated directly to JPMorgan Chase monthly bank statement cutoff. | |
| 10 | 1.2 | ADD: Deposits in Transit (Uncleared Wire / Check Receipts) | Schedule B Register | Schedule B (Deposits) | $68,500.00 | - | IN TRANSIT | DT | Verified against subsequent bank clearing within 2-3 business days. |
| 11 | 1.3 | LESS: Outstanding Disbursements & Checks (Issued / Uncleared) | Schedule A Register | Schedule A (Checks) | -$100,695.20 | - | OUTSTANDING | OC | Traced to detailed uncleared disbursements register; zero stale items > 90d. |
| 12 | 1.4 | OTHER: Bank Adjustments / Recording Errors Investigated | Dispute Log Ref #0 | N/A - Zero Errors | $0.00 | - | NONE | OK | Bank confirmed zero erroneous service charges or improper debits posted. |
| 13 | ADJUSTED BANK BALANCE (RECORD 1) | $1,482,650.00 | $1,482,650.00 | RECORD 1 TIED | TB | Adjusted bank balance ready for tri-reconciliation comparison. | |||
| 14 | |||||||||
| 15 | RECORD 2: TRUST CHECKBOOK REGISTER & GENERAL LEDGER BALANCE | ||||||||
| 16 | Line # | Reconciliation Component / Line Item | Reference / Account # | Source Schedule | Item Amount ($) | Adjusted Bank Balance ($) | Audit Status | Tickmark | Audit & Corroboration Notes |
| 17 | 2.1 | Ending Balance per Trust Cash Journal / Checkbook Register | GL Account #1010-00 | Trust Cash Ledger | - | VERIFIED | GL | Agrees to internal general ledger cash disbursements journal ending balance. | |
| 18 | 2.2 | LESS: Unposted Bank Service Fees (To be reimbursed by Operating) | Due to/from Operating | Bank Memo | $0.00 | - | COMPLIANT | OK | No client funds utilized for bank fees. Zero unauthorized debits. |
| 19 | 2.3 | ADD: Firm Operating Reimbursements Deposited in Transit | Operating Transfer #441 | Deposit Slip | $0.00 | - | COMPLIANT | OK | Firm maintains separate operating reserve to prevent any trust account shortfall. |
| 20 | 2.4 | LESS: Accrued IOLTA Interest Swept to State Bar Foundation | IOLTA Trust Sweep | State Bar Notice | $0.00 | - | COMPLIANT | OK | Interest remitted directly to State Legal Services Trust Fund per Bar Rule 1.15. |
| 21 | ADJUSTED CHECKBOOK / GL BALANCE (RECORD 2) | $1,482,650.00 | $1,482,650.00 | RECORD 2 TIED | GL | Trust general ledger register balance validated and reconciled. | |||
| 22 | |||||||||
| 23 | RECORD 3: MASTER CLIENT TRUST SUBLEDGER BALANCE (INDIVIDUAL CLIENT LEDGERS) | ||||||||
| 24 | Line # | Reconciliation Component / Line Item | Reference / Account # | Source Schedule | Item Amount ($) | Adjusted Bank Balance ($) | Audit Status | Tickmark | Audit & Corroboration Notes |
| 25 | 3.1 | Total Active Client & Matter Escrow Accounts Reconciled | Client Master List | Client Subledger Register | 20 | - | 20 MATTERS | CL | Count of active individual client subledgers holding trust funds. |
| 26 | 3.2 | Sum of All Individual Client Trust Subledger Balances | All Matters Sum | Client Subledger Register | $1,482,650.00 | - | BALANCED | CL | Direct summation formula across all 20 active client and matter ledgers. |
| 27 | 3.3 | Individual Client Ledgers with Negative Balances (Bar Rule Violation Check) | Zero Overdraft Audit | Compliance Matrix | 0 | - | 0 OVERDRAFTS | OK | Crucial legal fiduciary rule: Zero negative client balances permitted. |
| 28 | TOTAL CLIENT TRUST LIABILITIES (RECORD 3) | $1,482,650.00 | $1,482,650.00 | RECORD 3 TIED | CL | Client subledger totals tie out exactly to trust cash liabilities. | |||
| 29 | |||||||||
| 30 | THREE-WAY TRI-RECONCILIATION & AUDIT VARIANCE DETERMINATION MATRIX | ||||||||
| 31 | Test # | Tri-Reconciliation Comparison Pair | Primary Record Balance | Secondary Record Balance | Audit Variance ($) | Allowable Tolerance ($) | Audit Determination | Tickmark | Mandatory Regulatory Compliance Finding |
| 32 | TEST 1 | Record 1 (Adjusted Bank) vs. Record 2 (Trust Checkbook Register) | $1,482,650.00 | $1,482,650.00 | $0.00 | $0.00 | ZERO VARIANCE | OK | Adjusted bank balance equals general ledger checkbook register perfectly. |
| 33 | TEST 2 | Record 2 (Trust Checkbook Register) vs. Record 3 (Client Subledgers) | $1,482,650.00 | $1,482,650.00 | $0.00 | $0.00 | ZERO VARIANCE | OK | Trust GL register matches the exact penny sum of all client matter liabilities. |
| 34 | TEST 3 | Record 1 (Adjusted Bank) vs. Record 3 (Client Subledgers) | $1,482,650.00 | $1,482,650.00 | $0.00 | $0.00 | ZERO VARIANCE | OK | Bank funds held in trust cover 100.0% of client fiduciary trust obligations. |
| 35 | AUDIT VERDICT: PASSED - THREE-WAY RECONCILIATION 100% BALANCED ($0.00 VARIANCE, ZERO NEGATIVE CLIENT BALANCES) | ||||||||
| 36 | |||||||||
| 37 | PREPARED BY (LEAD TRUST ACCOUNTANT / CONTROLLER): | REVIEWED & CERTIFIED BY (MANAGING PARTNER / DESIGNATED BROKER): | |||||||
| 38 | Marcus Sterling, CPA — Senior Trust Accounting Manager | Date: April 2, 2026 | Eleanor Hayes, Esq. — Managing Partner, Sterling & Hayes LLP | Date: April 3, 2026 | |||||||
| 39 | MANDATORY FIDUCIARY COMPLIANCE CERTIFICATION: I hereby attest that this monthly three-way reconciliation has been performed in strict accordance with State Bar Rules of Professional Conduct (ABA Model Rule 1.15) and State Real Estate Commission trust accounting statutes. The bank balance, internal general ledger register, and sum of client trust subledgers reconcile to $0.00 difference, with zero commingling of operating funds and zero negative client balances. | ||||||||
Free Instant Download
No email signup • Unlocked formulas
Built-In Excel Formulas & Calculation Logic
Unlocked native Microsoft Excel functions pre-programmed inside this workbook:
=Ending_Bank_Statement + Deposits_In_Transit - Outstanding_Disbursements + Bank_Adjustments Calculates true external available cash as of the statement cutoff date by adding client funds deposited prior to cutoff that have not yet posted, and deducting authorized checks written to clients or third parties that have not yet cleared.
=G10 + H10 - I10 Rolls forward each client matter or tenant reserve individually by taking the beginning trust balance, adding current period retainers and settlement deposits, and deducting earned fee transfers and authorized matter disbursements.
=COUNTIF('02_Client_Trust_Subledgers'!J10:J29, "<0") Scans all active client and matter balances to verify that no individual ledger holds a negative balance. Under ABA Model Rule 1.15, a negative balance indicates client commingling or conversion of another client's funds.
=C32 - D32 Calculates the exact cent difference between Record 1 (Adjusted Bank), Record 2 (Trust Checkbook Register), and Record 3 (Sum of Client Subledgers). Must display $0.00 across all pairwise comparisons.
=IF(AND(ABS(E32)<0.005, ABS(E33)<0.005, ABS(E34)<0.005, E27=0), "PASSED", "AUDIT EXCEPTION") Evaluates all three pairwise variance calculations and the zero-overdraft audit count simultaneously, granting compliance certification only when all balances tie out to $0.00 and zero client overdrafts exist.
Full Workbook Print Layout Preview (.XLSX)
Overview & Industry Standard Alignment
Managing trust funds is the single highest-liability operational obligation for law firms, title companies, and real estate property management agencies. Unlike standard corporate operating accounts where minor timing differences or ledger variances can be adjusted during quarterly reviews, client trust funds (IOLTA) represent fiduciary property held in safekeeping. State Bar Associations (such as the State Bar of California, The Florida Bar, and the New York State Unified Court System) and State Real Estate Licensing Boards enforce strict rules of professional conduct requiring complete separation of client money from operating funds and a formal monthly 'three-way reconciliation'. Standard accounting software and generic spreadsheets often fail to protect practitioners during random bar audits or state licensing examinations because they only perform a two-way reconciliation (bank statement vs. general ledger cash book). A two-way reconciliation is blind to internal matter-level errors: if an attorney accidentally disburses $10,000 for Client A using funds deposited by Client B, a two-way bank reconciliation will balance perfectly, yet the attorney has committed a severe disciplinary violation (fiduciary commingling and unauthorized conversion). Only a true three-way reconciliation—which matches the adjusted bank balance, the checkbook register balance, and the exact sum of every individual client subledger—exposes these lethal errors before an auditor arrives. This template delivers an institution-grade, audit-tested framework engineered to eliminate manual calculation errors. Featuring dedicated schedules for outstanding checks and deposits in transit, automatic aging analysis for stale checks (>90 days), an active client ledger rollforward across 20 distinct matters, and an executive tri-reconciliation matrix with supervisory sign-off blocks, it provides the precise documentary evidence demanded by forensic auditors and regulatory investigators.
Practical Operational Use Cases:
Law Firm Monthly IOLTA Trust Account Close & Compliance Filing
Enables legal administrators, billing managers, and managing partners to reconcile complex multi-matter retainer accounts, personal injury settlement distributions, and litigation cost advances at each month-end close.
Property Management Tenant Security Deposit & Owner Escrow Audits
Allows licensed real estate brokers and property management firms to tie out pooled tenant security deposit escrow bank accounts against individual tenant reserve ledgers and owner operating accounts in compliance with Real Estate Commission mandates.
Title Company & Real Estate Settlement Closing Escrow Reconciliations
Provides title agents, escrow officers, and closing attorneys with an exact-penny audit trail reconciling high-volume daily closing wire inflows and lender payoffs across multiple escrow files.
CPA Fiduciary Review & State Bar Disciplinary Audit Defense
Equips CPAs, forensic accountants, and legal compliance consultants with an exhaustive audit workpaper complete with standardized tickmark legends (TB, GL, CL, OC, DT, OK) to defend firms during regulatory compliance inquiries.
Designed For:
What’s Inside the Workbook (Tab-by-Tab Breakdown)
Worksheet 1
The master executive workpaper featuring high-level liquidity KPI cards, Record 1 (Adjusted Bank Balance schedule with transit links), Record 2 (Trust Checkbook Register / General Ledger schedule), Record 3 (Sum of Client Subledgers summary with negative balance detection), the Tri-Reconciliation Pairwise Variance Matrix, and supervisory signature blocks.
Worksheet 2
Comprehensive individual client and matter subledger rollforward tracking 20 pre-populated real-world legal and escrow matters. Automatically computes ending trust balances (=Beginning + Receipts - Disbursements), compares against minimum retainer thresholds, and alerts to any overdrafts.
Worksheet 3
Dual supporting registers for reconciling items. Schedule A details outstanding uncleared checks with automated day-aging calculations and stale date (>90 days) risk flags. Schedule B tracks deposits in transit, payment remitter details, and subsequent bank clearing verification.
Worksheet 4
Authoritative fiduciary compliance reference manual detailing statutory definitions under ABA Model Rule 1.15, the top 5 disciplinary red flags (negative ledgers, commingling, stale checks, bank fee deductions, deposit delays), and standard CPA audit tickmark explanations.
Practitioner Tips & Best Practices
-
✓
-
✓
-
✓
-
✓
Frequently Asked Questions
What is the difference between a two-way and a three-way trust reconciliation?
A two-way reconciliation only compares the bank statement ending balance (adjusted for outstanding checks and deposits in transit) against the firm's internal checkbook or general ledger cash register. A three-way reconciliation adds a mandatory third dimension: comparing both of those figures against the sum of every individual client's trust subledger. A firm can have a balanced two-way reconciliation while having severe client ledger discrepancies (such as one client having a negative balance subsidized by another client). The three-way reconciliation guarantees that the funds in the bank equal the obligations owed to each specific client to the exact penny.
Why is an individual client ledger never permitted to have a negative balance?
Under ABA Model Rule 1.15 and state bar regulations across all 50 US jurisdictions, an attorney or escrow agent holds client funds strictly as a fiduciary. Because all client trust funds are held in a single pooled bank account, a negative balance on Client A's ledger means that Client A's expenses were paid using funds deposited by Client B or Client C. This constitutes unauthorized commingling and conversion of client property, which is grounds for emergency license suspension, formal ethics charges, or disbarment.
How often must a law firm perform a three-way IOLTA reconciliation?
Most state bar rules (including California Rule 1.15, Florida Bar Rule 5-1.2, and New York Rule 1.15) explicitly mandate that three-way trust account reconciliations be performed at least monthly. Additionally, firms are required to maintain monthly reconciliation workpapers, bank statements, canceled checks, and client ledgers for a minimum statutory period (typically 5 years in most states, and up to 7 years in others) for random audit inspection.
Can this template be used for real estate property management trust accounts?
Yes. Real estate licensing commissions (such as the California DRE, Florida DBPR, and Texas Real Estate Commission TREC) impose identical fiduciary trust accounting requirements on property managers holding tenant security deposits and owner reserve funds. In the template, simply treat each tenant unit or property owner reserve as an individual client subledger on sheet '02_Client_Trust_Subledgers'.
What should I do if a bank service fee is accidentally deducted from the trust account?
If a bank deducts a monthly maintenance fee, wire charge, or check printing fee from the IOLTA account, immediately deposit firm operating funds into the trust account to reimburse the exact amount of the fee. Document the reimbursement on the reconciliation workpaper and contact the bank to ensure future service fees are linked to your operating account or waived under the bank's IOLTA agreement.
How does the template handle stale outstanding checks older than 90 days?
On sheet '03_Outstanding_Disbursements' (Schedule A), the template calculates the number of days each check has remained uncleared. Any check outstanding for more than 90 days is automatically highlighted with a 'STALE (>90D) - INVESTIGATE' alert in column H. This prompts the accounting team to contact the payee, confirm receipt, stop payment if lost, or initiate statutory unclaimed property (escheatment) reporting if the payee cannot be located.