How to Reconcile Past-Due Rent in Excel | Jolene.pro
Your past-due balance fell by $8,000. Before you tell the owner you collected $8,000, check the tenant ledger. A payment and a credit both lower the balance. Only one brings in cash.
Pull two aging reports and the activity between them
If you work in Excel, start with last month’s and this month’s past-due balances. Add the tenant ledger for the period between those two dates. Keep the tenant account ID, transaction date, transaction type, amount, and charge reference where available.
If you use property management software, start with two dated delinquency or aging reports and the tenant ledger detail for the same properties. Use the available spreadsheet exports. The aging reports show what was past due on each date. The ledger shows the payments, credits, write-offs, and reversals posted in between.
Separate collections from adjustments
- Match tenant accounts across both reports. Include accounts that disappeared from this month’s report. Their balance may have been paid, credited, or written off.
- Check what each transaction was applied to. Count a payment toward past-due rent only if it was applied to a past-due rent charge. Keep unapplied payments and payments against current charges separate.
- Account for new overdue charges. A charge can become past due during the month. Include it before deciding whether the opening and closing balances tie out.
In a sample portfolio, past-due rent fell from $20,000 to $12,000. No new rent charges became overdue in the period. The ledger shows $5,300 in payments applied to past-due rent and $2,700 in credits. The owner update can now show both figures separately.
What to ask Jolene.pro
Add the two aging reports and the tenant ledger, then ask:
Explain the change in past-due rent by tenant account. Separate payments, credits, write-offs, reversals, and newly overdue charges. Show the source rows and flag anything that doesn’t tie out.
Use the results to report collections and update your follow-up list. If a payment is still unapplied, check the ledger before deciding whether the tenant needs a reminder.