A rent collection tracker for a landlord needs one row per flat per month with the rent due, the amount received, the date, the mode of payment and the arrears carried forward. A second tab keeps repairs and society charges so you can see what a property really leaves in your hand. Both tabs need only SUMIFS and a little discipline.
How should the tenant ledger be laid out?
Start a tab called Ledger with these columns and one row for each flat for each month, created at the start of the month.
| Column | Heading | Notes |
|---|---|---|
| A | Month | First date of the month, formatted as mmm-yyyy |
| B | Flat | A short label such as 2B or Ground |
| C | Tenant | Name as it appears on the rent agreement |
| D | Rent due | The agreed monthly rent |
| E | Received | Amount actually received in that month |
| F | Date received | The day the money arrived |
| G | Mode | UPI, NEFT, cheque or cash |
| H | Arrears | Balance still owed up to this month |
Rent due is filled in the day you create the row. Received, date and mode are filled when the money lands, so a blank in column E tells you at a glance who has not paid.
What formula carries arrears forward?
Rather than typing last month's leftover into this month, let the sheet add it up. In H2, fill down:
- =SUMIFS($D$2:$D$500, $B$2:$B$500, B2, $A$2:$A$500, "<="&A2) - SUMIFS($E$2:$E$500, $B$2:$B$500, B2, $A$2:$A$500, "<="&A2)
This adds all rent due for that flat up to and including this month and subtracts everything received up to the same month. A positive result is money still owed; zero means the tenant is square; a negative figure means advance has been paid. Because it recalculates from the whole history, you can correct an old entry and every later balance fixes itself.
What does a month with one late tenant look like?
Say you have three flats, with rents of ₹18,000, ₹22,000 and ₹15,000. The tenant in the third flat pays part in February. Figures are made up.
| Month | Rent due | Received | Arrears after month |
|---|---|---|---|
| January | ₹15,000 | ₹15,000 | ₹0 |
| February | ₹15,000 | ₹10,000 | ₹5,000 |
| March | ₹15,000 | ₹15,000 | ₹5,000 |
| April | ₹15,000 | ₹20,000 | ₹0 |
Notice March: the tenant paid the full month, yet ₹5,000 is still outstanding, because arrears from February have not been cleared. In April the extra ₹5,000 clears it. Without the running formula, many landlords would think March was a clean month.
Where do repairs and society charges go?
Add a second tab called Costs with columns for date, flat, what it was for, amount, who paid (you or the tenant) and a receipt or bill number. A plumber visit of ₹1,800 and a quarterly society charge of ₹6,000 are entered once each.
Then on a Summary tab, collections for a month are =SUMIFS(Ledger!$E$2:$E$500, Ledger!$A$2:$A$500, B1), where B1 holds the month's first date. Costs are =SUMIFS(Costs!$D$2:$D$500, Costs!$A$2:$A$500, ">="&B1, Costs!$A$2:$A$500, "<="&EOMONTH(B1,0)). If a month brings in ₹55,000 against ₹7,800 of costs, the difference is ₹47,200 in hand before anything your CA treats differently.
What about security deposits and receipts?
- Keep deposits on their own small table, never inside the rent columns, because they are refundable and not income for the month.
- Note the date and amount when a tenant gives a deposit and when part of it is adjusted.
- Give the tenant a simple receipt for each payment, mentioning the month it covers, and keep a copy.
- For cash, write the amount received and the date on the same day; cash payments are the ones people dispute later.
Can an app help with the property side?
The sheet shows rent in. For the repairs and bills you pay from your own account, SpendixAI can import the bank or card statement, let you attach a receipt to the transaction and report by category, so plumber and society entries need less retyping.
Monthly routine
- Create the month's rows on day one with rent due filled in.
- Fill received, date and mode as soon as each payment lands.
- Check the Arrears column and message anyone with a balance.
- Enter repairs and charges with bill numbers.
- Save a dated copy of the file at month end.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

