Rental Property Management System in Excel: Units, Rent Roll, Arrears, Lease Expiry, Receipts and Net Yield

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: rental-property-management-system-template.xlsx: a 9-sheet system with example data and real formulas (Read me, Setup, Units, Payments, Expenses, Rent roll, Rent receipt, Report, Dashboard). 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 every report and the dashboard recalculate.

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

1. The business story

Ravina Properties, Pune, tracking from 1 July 2026, as of 17 October 2026 (made-up data). 6 units: four flats in Shivaji Nagar and two shops in Kothrud. Rent is due on the 5th.

October collection is ₹92,500 of ₹1,20,500 due (76.8%). For a single-building list of tenants, the rent collection tracker is simpler; this page adds leases, expenses and yield.

2. Build it sheet by sheet

Step 1. Setup

1. B3 as-of date, B4 tracking start (01-07-2026), B6 rent due day (5), B7 lease warning days (60).
2. B5 current month: =DATE(YEAR(B3),MONTH(B3),1). B8 months tracked =(YEAR(B5)-YEAR(B4))*12+MONTH(B5)-MONTH(B4)+1 (4).
3. Expense types in D3:D6 and payment modes in E3:E6.

Step 2. Units: leases and arrears

1. Row 4: Unit, Property, Tenant, Monthly rent, Deposit, Lease start, Lease end, Months billed, Rent billed, Rent received, Arrears, Lease days left, Lease flag.
2. H5 months billed counts months from the later of lease start and tracking start up to the current month, minus any months after the lease ended. A-202 started in August, so 3.
3. I5 =D5*H5, J5 received =SUMIFS(Payments!$E$5:$E$500,Payments!$B$5:$B$500,A5), K5 arrears =I5-J5.
4. L5 =G5-Setup!$B$3 and M5 =IF(L5<0,"Expired",IF(L5<=Setup!$B$7,"Renew soon","OK")). Arrears above 0 turn red, Renew soon amber.

Step 3. Payments and Expenses

1. Payments: Date paid, Unit, Tenant, Rent month, Amount, Mode, Receipt no, Days late. Rent month is the 1st of the month the rent is for.
2. G5 receipt number ="RR-"&TEXT(ROW()-4,"000") and H5 days late =MAX(0,A5-(D5+Setup!$B$6-1)).
3. Expenses: Date, Unit, Type, Amount, Month. E5 =DATE(YEAR(A5),MONTH(A5),1) so expenses group by month.

Step 4. Rent roll for the month

1. C5 due this month only if the lease covers it: =IF(AND(Units!F5<=EOMONTH(Setup!$B$5,0),Units!G5>=Setup!$B$5),Units!D5,0).
2. D5 paid for that month =SUMIFS(Payments!$E$5:$E$500,Payments!$B$5:$B$500,A5,Payments!$D$5:$D$500,Setup!$B$5), E5 balance.
3. F5 =IF(E5<=0,"Paid",IF(D5>0,"Part paid","Unpaid")) and G5 days overdue after the 5th (12 on 17 October).

Step 5. Rent receipt

1. Type a receipt number in B4 (RR-019). Rows 6 to 11 pull date, unit, tenant, rent month, amount and mode with XLOOKUP.
2. B12 shows the month's remaining balance from the Rent roll (₹9,000 for Amir Sayyed). Print A1:D16.

Step 6. Report and dashboard

1. Report rows 5 to 8: month (=EDATE(Setup!$B$4,0) and so on), rent received by rent month, expenses by month, net income.
2. B11 annual rent roll, B12 net income annualised =D9/Setup!B8*12, B13 property value (example), B14 net yield =B12/B13.
3. Dashboard cards: due this month, collected, collection rate, arrears, leases to renew, net yield. Charts: received vs expenses by month at A8, arrears by unit at A25.

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 LibreOffice with zero errors.

4. Common mistakes

1. Using the payment date as the rent month. September rent paid on 2 October belongs to September. Keep a separate Rent month column.
2. Counting the deposit as income. A deposit is returned at the end of the lease. Keep it in its own column, out of income and yield.
3. Billing rent after a lease ended. Months billed must stop at the lease end, or arrears grow for an empty flat.
4. Ignoring expenses in the return. Gross rent ÷ value looks better than it is. Yield should use rent after society charges, repairs and tax.
5. Renewing late. A lease that lapses leaves the tenant without a valid agreement. Act on Renew soon, not Expired.

5. More real examples

Example 1: PG or hostel rooms. Units become beds; add a Food charge column and bill it with rent in the rent roll.

Example 2: Yearly rent increase. Add an Increase % and Increase month on Units and use =D5*(1+increase) once that month is reached.

Example 3: Shop with maintenance charges. Add a maintenance column billed with rent; the rent roll adds both and the receipt shows them separately.

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 calculate rent arrears in Excel?

Rent billed (rent × months inside the lease) minus everything received for that unit: =I5-J5.

How do I flag leases expiring soon?

Days left = lease end − today. Show Renew soon when it is 60 or less and colour it amber.

What is a rent roll?

A list of every unit with this month's rent due, paid, balance and status. It is the first sheet to open on the 6th of the month.

How do I calculate rental yield?

Net yield = yearly net income ÷ property value. The example annualises 4 months of net income; use a full year when you have it.

Is TDS on rent included?

No. If a tenant deducts TDS, record the gross rent and the TDS amount as two columns and check the rules with your accountant.

7. Quick quiz

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