Trust Account Excel Workbooks

It is highly recommended that you use software that tracks your transactions and performs reconciliations of your trust accounting. There are many legal applications available that provide the reports necessary to reconcile your trust accounts, and some applications that are programmed to perform a three-way trust account reconciliation. Generally, that level of automation may be preferable to these workbooks, regardless of the size of the practice. The Law Practice Management program would only recommend these workbooks for smaller firms that cannot use such software for some reason, have a lower volume of trust account activity, and have some experience using automated Excel spreadsheets.

The three workbooks are templates. First, save the templates as a Read-Only file and do not make any changes to the template. When you are ready to create your Trust Account Client Ledger, Trust Account Journal, and 3-Way Reconciliation workbooks for each new year, you can simply open the appropriate template file, save another copy for the new year, and make changes to the copy only. By not modifying your templates, you can start new blank copies from the template each year. These instructions, as well as specific instructions about the functions of the workbooks, are also included on the first tab of the trust account templates’ worksheet titled “Instructions.”  (You can also setup Excel to save as Excel Templates.)

ASB recommends that you review the pertinent Rules of Professional Conduct as well as the Handbook for Trust Accounting found here. Note that Rule 1.15(e)(9) requires quarterly reconciliation, but the comments recommend monthly reconciliation. When reconciling your trust account, it is best to do a Three-Way Reconciliation, which balances three things: your internal books, your trust account bank statement, and the client ledger balances. See the following instructions on how to conduct a Three-Way Reconciliation.

Monthly reconciliation of trust account.

  1. Open and review your bank statement every month.
  2. If there was a bank charge that you were not aware of until you received the bank statement, add this to your general ledger and adjust as necessary. You will also need to add this to your administrative ledger and adjust as necessary. There is a place on the reconciliation for bank charges.
  3. Reconcile the bank statement to the general ledger, checking off all checks and deposits that have cleared. Subtract checks and add deposits that have not cleared AS OF the ending date of the bank statement (example – bank statement ends on 1/31/05 and check #555 was written on 2/1/05. You would not include this transaction on this month’s reconciliation). You may use the back of the bank statement to complete this part of the reconciliation.
  4. Add the total of your client ledgers, including the administrative ledger together. Again, use only transactions up to the ending date of the bank statement. Write this number on the 3-way reconciliation worksheet.
  5. Take the balance on the general ledger, again, as of the ending date of the bank statement. Write this amount on the 3-way reconciliation worksheet.
  6. All three figures should match on the 3-way reconciliation worksheet, the reconciled bank statement, total of client ledgers and general ledger. If they match, congratulations – your trust account is reconciled. If they do not match, you must review your records to find the problem.
  7. If the client ledger total or general ledger total does not match, the problem is generally that you did not log a transaction on one of the ledgers. Review and compare each client ledger to the general ledger.
  8. If the reconciled bank statement does not match, you may have missed a transaction that has not yet cleared. This could be from a previous month. Be sure to check last month’s reconciliation for check numbers or deposits that did not clear. They may not have cleared again.
  9. Do not close out client ledgers until all transactions on the ledger have cleared.