Simple Trust Accounting Ledger for STR Management Companies

This is a simple template designed specifically for short-term rental management companies to track all trust account activity. Built as a true running balance ledger, it captures every deposit, withdrawal, and payment flowing through your trust account in real time — giving you a complete, auditable record of funds held on behalf of property owners and guests.

To get started, make a copy of this Google Sheet: File → Make a copy

What This Template Includes

This trust account tracker provides a chronological record of every transaction across six fields:

1. ‍Date: The date each transaction was recorded.
2. ‍Reference #: A unique transaction identifier that also indicates the transaction type.
3. ‍Payee/Description: The vendor, guest, or description associated with the transaction.
4. ‍Property Name: The property the transaction is linked to.
5. ‍Credit: Any funds received into the trust account, including guest rent and security deposits. Enter as a positive number.
6. Debit: Any funds paid out of the trust account, including owner distributions, management fees, cleaning fees, utilities, and security deposit refunds.
7. ‍Balance: Automatically calculated running balance after each transaction.

Customization Guidelines

Step 1: Enter Account Details

Fill in the following fields at the top of the sheet:

• Bank Name: Your bank's name where the trust account is held.
• Account Number: Your trust account number for reconciliation purposes.
• Starting Balance: The opening balance of the trust account at the start of the reporting month. This is entered in cell G5 and is the anchor for all balance calculations.
• Ending Balance: Auto-populates from the last row of the Balance column. Do not edit manually.

Step 2: Understand the Reference # System

The Reference # column uses a structured prefix system to identify the type of each transaction at a glance. The prefixes used in this template are:

• DEP-[RES#]R: Guest rent payment received for a reservation.
• DEP-[RES#]S: Security deposit collected from a guest at check-in.
• REF-[RES#]: Security deposit returned to a guest after checkout.
• CLN-[RES#]: Cleaning or turnover fee paid to a cleaning vendor.
• EFT-UTIL: Electronic funds transfer for a utility payment (electric, water, wifi, etc.).
• TXFR-[MON]: Owner distribution transfer for a prior month's performance.
• MGMT-FEE: Monthly management fee deducted per property.

These prefixes are customizable. You are free to create your own reference system to match your company's existing naming conventions or property management software. What matters most is that you apply your chosen system consistently across every entry, every month. A consistent reference structure is what makes the ledger filterable, auditable, and easy to hand off to an accountant or reviewer.

Step 3: Enter Transactions

Add each transaction as a new row in chronological order. Keep the following in mind:

• Always enter deposits in the Credit column and payments in the Debit column. Never use negative numbers, both columns stay positive.
• Each row should have a date, reference number, description, and property name before the Balance column will reflect correctly.
• Transactions on the same date can be entered in any order, but grouping by date keeps the ledger clean and auditable.
• Do not skip rows or leave blank rows between transactions, as this can break the running balance formula.

Step 4: Understand the Balance Formula

The Balance column is pre-formulated and should not be manually edited. The formula works by checking both the Deposit Credit (+) and Withdrawal Payment (-) columns on each row and automatically adding or subtracting from the prior balance depending on which column has a value. Both columns always remain positive numbers — the formula handles the math direction for you.

There are two formulas used in this column:

1. Cell I9 references the Starting Balance in G5: =G5+IF(G9<>"",G9,0)-IF(H9<>"",H9,0).
2. Cell I10 and all rows below reference the prior row's balance: =I9+IF(G10<>"",G10,0)-IF(H10<>"",H10,0).

Important Details:

• Each formula reads as: take the prior balance, add anything in the Deposit column, and subtract anything in the Withdrawal column.
• If a cell is empty it is treated as zero, so partial rows do not break the calculation.
• When adding new rows, highlight the Balance cell in the last populated row, copy it, then paste it into the new row. Do not drag from I9 as it will pull the wrong reference. Never type a balance amount manually, any hardcoded number will break the running chain for every row below it and produce incorrect balances for the month.

Step 5: Reconcile at Month End

At the end of each reporting month, confirm the following before closing out the ledger:

• The Ending Balance shown at the top of the sheet matches the actual closing balance on your bank statement.
• All security deposits collected during the month have a corresponding refund entry or remain reflected in the running balance as funds still held.
• All owner distributions and management fees for the period have been recorded as withdrawals.
• The Starting Balance for the next month's tracker matches the Ending Balance of this one.

Summary & Strategic Use

This STR-specific Trust Account Balance Tracker provides a complete, transaction-level record of every dollar flowing through your trust account each month. By implementing this standardized ledger, you gain:

1. A Real-Time View of Trust Account Activity: Moves beyond end-of-month summaries to show every deposit, withdrawal, and payment in chronological order so you always know exactly what is in the account and why.
2. Owner Distribution Transparency: Each owner distribution is recorded as a line item by property, making it easy to confirm that every owner was paid correctly and that no funds were distributed in excess of what was collected.
3. Audit-Ready Compliance Documentation: A clean, structured ledger that is ready for review by owners, accountants, state regulators, or legal counsel at any time, particularly important in states where trust account recordkeeping is a licensing requirement.
4. Foundation for Broader Financial Reporting: This tracker feeds directly into your monthly balance sheet by providing the exact cash balance held in trust, your security deposit liability, and your owner distributions payable, making your full financial close faster and more accurate.
5. Professionalism and Owner Trust: Providing a detailed, itemized account of every transaction in your trust account positions your company as a financially disciplined and transparent operator, a meaningful differentiator when retaining and acquiring owners.

Questions or Need Help?

If you need support or want help automating this process, email: info@topkey.io.