Rent Collection Tracker in Excel: Monthly Rent Received, Pending Dues and Late Tenants

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: rent-collection-tracker-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Four flats, July to October 2026 (made-up), as of 17-10-2026:

Raja received ₹1,69,500 of ₹1,90,000: a collection of 89.2%. ₹20,500 is pending. Ravina paid half in September and half in October, so her two part payments add up to one full month pending; the amber cells show this at a glance.

2. Build it step by step

Step 1. Tenants and months

1. In A5:L5 type Flat, Tenant, Monthly rent, Due day, Jul-2026 … Oct-2026, Received, Expected, Pending, October status.
2. Type each tenant's rent and due day (yellow). Select D6:D9 > Data Validation > List: 1,5,7,10,15 (drop-down lists).
3. Type what each tenant actually paid under each month. Type 0 if nothing came.

Step 2. Received, expected, pending

1. In I6 type =SUM(E6:H6): ₹48,000.
2. In J6 type =C6*COUNTA($E$5:$H$5): rent × number of months = ₹48,000 (COUNTA).
3. In K6 type =J6-I6: pending. Copy I6:K6 down.

Step 3. This month's status

1. In B3 type =TODAY() (the template has 17-10-2026 typed).
2. In L6 type =IF(H6>=C6,"Paid",IF(DAY($B$3)>D6,"Late","Due")).
3. DAY($B$3) is today's day of the month (17). Zoya's due day is 5, so unpaid on the 17th means Late (nested IF).

Step 4. Totals and summary

1. In C10 type =SUM(C6:C9) and copy right to K10.
2. In O6 type =I10/J10: 89.2%. In O7 =K10: ₹20,500.
3. In O8 type =COUNTIF(L6:L9,"Late"): 2. In O10 type =INDEX(B6:B9,MATCH(MAX(K6:K9),K6:K9,0)): Ravina.

Step 5. Colours

1. Select E6:H9 > Conditional Formatting > New Rule > Use a formula: =E6<$C6, amber. Part and missed payments light up (formula rules).
2. Select L6:L9 > Highlight Cells Rules > Equal To Late, red; Paid, green.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Leaving unpaid months blank. Type 0. A blank is easy to miss when you scan the sheet.
2. Expected typed by hand. Use =C6*COUNTA($E$5:$H$5) so expected grows when you add a month (widen the range).
3. Due day typed as a date. Column D is just the day number (5, 7, 10). DAY(B3) compares day with day.
4. Mixing deposit with rent. Keep the security deposit in a separate column or sheet; it is not rent.
5. Forgetting part payments. Type the actual amount, not "paid". The amber rule and Pending do the rest.

5. More real examples

Example 1: One flat, one tenant. Even with a single tenant, one row per year with 12 month columns gives a clean record for the year-end.

Example 2: Shop or office rent. Add a Maintenance column and include it in Expected: =(C6+maintenance)*COUNTA(…).

Example 3: Rent receipt list. Add a column with the UPI reference for each payment so you can issue receipts.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I track rent payments in Excel?

One row per tenant, one column per month with the amount paid, then SUM for received, rent × months for expected, and expected − received for pending.

How do I show who is late this month?

=IF(H6>=C6,"Paid",IF(DAY(TODAY())>D6,"Late","Due")) compares today's day with the due day.

How do I calculate collection %?

Total received ÷ total expected: =I10/J10.

How do I handle part payments?

Type the actual amount. Pending shows the shortfall and the amber rule marks the month.

Can I add more months?

Insert a column before Received, type the month and widen $E$5:$H$5 in the Expected formula.

7. Quick quiz

Ten questions on this page. Sign in to save your score.