You own or manage rented units and you want to know, without a calculator, what each tenant owes, how many months behind they are, when they last paid, and whose lease ends soon. The free rent collection tracker answers those four questions from two things you type: the tenants and the payments. The template and its page are in Arabic.
The problem it solves
Rent is simple for one unit and messy for ten. You have to count how many monthly due dates have passed since each lease began, multiply by the rent, subtract everything the tenant has paid, and then repeat it every month for everyone. The mistakes come from a forgotten payment, a tenant who paid in advance, a vacant unit mixed into the totals, or a lease that ended without anyone noticing. The sheet does this count for every tenant each time you open it.
What the template gives you
- Settings: today's date (you can type =TODAY() so it updates itself) and how many days before a lease ends you want to be warned.
- Tenants: per unit: code, description, tenant, phone, monthly rent, lease start and end, and due day (1 to 28, so it fits every month). The sheet fills in months due, amount due, paid, balance, months late, last payment, collection rate, status and a lease-ending alert.
- Payments: each payment with date, unit code, amount and payment method; the tenant name appears by itself.
- Follow-up: an automatic list of late tenants, largest balance first, with phone, months late, last payment and the lease alert.
- Summary: rented and vacant units, occupancy rate, due to date, collected, arrears, collection rate and the number of leases ending soon.
A walkthrough with the sample data
These are sample units and names built into the template, not real people. The Tenants sheet shows eight units:
- U01, Ahmed Salem, rent 5,000: balance 0, status "paid".
- U02, Maha Hassan, rent 6,000: balance 0, "paid".
- U03, Khaled Omar, rent 4,500: balance 9,000, which is 2 months late.
- U04, Sara Youssef, rent 7,000: balance 7,000, 1 month late.
- U05, Tarek Magdy, rent 12,000: balance 24,000, 2 months late. He is first on the follow-up list because his balance is the largest.
- U06, Mona Hassan, rent 5,500: balance -5,500, status "paid ahead".
- U07, rent 6,500: no tenant, so it is "vacant" and not counted as late.
- U08, Hisham Lotfy, rent 4,000: balance 4,000, 1 month late.
Adding the late balances (9,000 + 7,000 + 24,000 + 4,000) gives 44,000 in arrears for this example. Note how months late are calculated: the balance divided by the monthly rent, rounded up. Mona's negative balance is an advance payment and is not counted as arrears. Seven of the eight units are rented in this example.
Features and why they matter
- Balance and months late per tenant, calculated from the lease and the payments, so they cannot disagree.
- Vacant units handled: a unit counts as rented only if a tenant name is written for it.
- Lease-ending alert so a renewal conversation happens before the end date, not after.
- A follow-up list instead of scanning the whole table.
- Works in Excel 2016 and later, Google Sheets and LibreOffice; free, with no sign-up. It holds 200 units and 2,000 payments, and the follow-up list shows the first 50 late tenants.
Using the online version
The template page opens directly on this worked example. Type in the white (input) cells right on the page and the grey (calculated) cells update immediately. You can import your own .xlsx made from this template, export your work to .xlsx, or download the original Excel file. Your data stays in your browser and is not sent to the server. Open the template page or the template list.
Common mistakes, and when a sheet stops being enough
Typical mistakes: leaving the old tenant's name when they move out (clear the name and lease dates and keep their payments), choosing a due day above 28, and recording a payment against the wrong unit code. A sheet also needs someone to copy every payment into it. If that is becoming the job, Accountwiro records each invoice and payment when it happens and shows the customer's balance in one statement. To weigh ready-made options first, read how to compare business software.
FAQ
How are months late calculated? The sheet counts the monthly due dates from the lease start to today or the lease end, multiplies by the rent, subtracts payments, and divides the balance by the rent, rounding up.
What if a tenant pays in advance? The balance becomes negative, the status shows "paid ahead", and it is not counted as arrears.
How do I mark a unit vacant? Leave the tenant cell empty. The unit then shows "vacant" and counts toward the vacancy figures.