Practical guide / Rental Charges & Payment Allocation Workbook 1.0
Know which charge a received payment actually covers.
Connect units and tenancies to charges, receipts and capped allocations. Keep deposit movements and expenses separate from rent so money received is not counted twice.
Your first useful result: Set up the unit and tenancy Follow the first task
Set up the unit and tenancy
Enter distinct Unit and Tenancy IDs, actual start/end dates and a non-sensitive tenant reference.
The linked records and input checks use the same stable IDs and your chosen dates.
Review: Review the labelled results and correct source warnings before acting.
Enter charges and receipts
Record each dated obligation and each actual receipt. A receipt may be partly allocated or spread across charges.
The linked records and input checks use the same stable IDs and your chosen dates.
Review: Review the labelled results and correct source warnings before acting.
Reconcile deposits separately
Allocations cap both receipt and charge totals. Deposits tracks held money separately from rent receipts and expenses.
The linked records and input checks use the same stable IDs and your chosen dates.
Review: Review the labelled results and correct source warnings before acting.
Check the assumptions.
- Save a working copy. Manual entry, with no automatic sync or external connections.
- Supplied tables have fixed capacities. Extend formulas, tables and validation together before using extra rows.
- Use one currency. Blank means unknown; enter 0 when the amount is known to be zero.
- Resolve the highlighted input rows before relying on the summary.
- Uses exact-mode XMATCH. Requires Excel 2021, Excel 2024 or Microsoft 365; older Excel editions are not supported. Native Excel, Google Sheets and Numbers have not been verified.
- A record of entered obligations and cash, not accounting, tax returns, legal documents or automatic late fees.
- Deposits are kept separate from rent and income. A deposit release does not create a rent receipt automatically.
- Supplied rows: 20 units, 60 tenancies, 400 charges, 400 receipts, 800 allocations, 120 deposits and 400 expenses.
- Enter historical charges and partial allocations explicitly. The workbook never invents unpaid monthly charges.
Keep a copy you can return to.
Keep the supplied clean and example files unchanged. Save your own working file with a useful name, such as rental-ledger-2027-home.xlsx, and keep a dated backup before a large edit. A purchased ZIP is the blank source, not a backup of your entries.
If an input or formula is replaced
Undo the edit while it is still available, or return to a known good backup. If you need a fresh clean file, transfer only your records into its marked input cells and follow the setup guide again. Do not paste whole sheets over the supplied formulas, validations or reference lists. Check IDs, units, dates and linked results against the example before relying on the copy.
When the supplied rows are full
20 units · 60 tenancies · 400 charges · 400 receipts · 800 allocations · 120 deposits · 400 expenses. Calculation ranges and validations have fixed capacities. A new row below a table does not prove that every linked result includes it.
For a separate period, project or volume, preserve the old workbook and start a named clean copy. If records must carry forward, record exactly which inputs you transfer, preserve their references and compare the opening and closing figures. Avoid copying historical activity as both an opening amount and a new movement. There is no automatic migration between files.
Inspect the workbook before you choose.
View its real sheets and a fictional calculation example.
Explore Rental Charges & Payment Allocation WorkbookWritten for version 1.0. Examples describe the supplied workflow; they are not customer results.