Rent Ledger for Multiple Properties (Excel)
One workbook for every flat, shop or room you let out, across several buildings. List each unit with its tenant, rent, deposit and lease dates, then write down payments as they come in. The ledger works out how many months of rent have fallen due, what has been paid, the arrears or advance for each tenant, a 12-month paid grid with red gaps, and how many days are left on each lease.
Free · no sign-up · opens in Microsoft Excel, Google Sheets, LibreOffice and WPS Office. Files contain no macros.
Preview
Real view of the file with its sample data (yellow cells are where you type).
| Unit | Property | Tenant | Rent (₹) | Months due | Rent due (₹) | Paid (₹) | Balance (₹) | Status | Deposit (₹) | Lease ends | Days left | Apr-26 | May-26 | Jun-26 | Jul-26 | Aug-26 | Sep-26 |
| GV-101 | Green View Apartments | Rohit Malhotra | 18,000 | 7 | 1,26,000 | 1,26,000 | 0 | Paid up | 36,000 | 30-11-2026 | 28 | 18,000 | 18,000 | 18,000 | 18,000 | 18,000 | 18,000 |
| GV-102 | Green View Apartments | Priya Nair | 16,500 | 5 | 82,500 | 66,000 | 16,500 | Arrears | 33,000 | 30-04-2027 | 179 | 0 | 0 | 16,500 | 16,500 | 16,500 | 16,500 |
| GV-103 | Green View Apartments | (vacant) | 17,000 | 0 | 0 | 0 | 0 | Vacant | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
| MR-S1 | Market Road Shops | Sandhu Electricals | 25,000 | 7 | 1,75,000 | 1,75,000 | 0 | Paid up | 75,000 | 31-03-2028 | 515 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 |
| MR-S2 | Market Road Shops | Kaur Bakery | 22,000 | 7 | 1,76,000 | 1,21,000 | 55,000 | Arrears | 66,000 | 30-09-2027 | 332 | 22,000 | 22,000 | 22,000 | 22,000 | 22,000 | 0 |
What's inside
- Settings: your name, currency symbol, the first month of the ledger year, the "as on" date and your list of properties
- Units sheet for up to 200 units: unit code, property, flat or shop number, tenant, phone, monthly rent, deposit held, rent due day, lease start and end, arrears brought forward
- Payments sheet with 1,000 rows: date paid, unit (drop-down), the month the rent is for, amount, mode and reference; tenant and property fill in by themselves
- Ledger sheet: months due, rent due, paid, balance, status (Arrears, Advance, Paid up), deposit, days left on the lease and a 12-month grid that turns red for unpaid months and green for paid ones
- A ready reminder message for each tenant who is behind, to copy into WhatsApp, SMS or email
- Summary by property: units, occupied, monthly rent roll, rent due so far, collected, arrears, deposits held and money received this month
How to use it
- Open the Settings sheet. Type your name, your currency symbol (₹, $, £, €, AED…), the first month of the ledger year (for example 01-04-2026 for an Indian financial year or 01-01-2026 for a calendar year) and your property names.
- On the Units sheet, replace the sample rows. Give every unit a short unique code such as GV-101, pick its property, and type the tenant, monthly rent, deposit, rent due day (for example 5) and lease dates. Leave the tenant empty for a vacant unit.
- If a tenant already owed rent before the ledger year, type that amount in "Arrears brought forward".
- Every time rent comes in, add a row on the Payments sheet: date, unit code, the month the rent is for (any date in that month) and the amount. A part payment is just a row with a smaller amount.
- Open the Ledger sheet. Each unit shows rent due up to the "as on" date, the amount paid and the balance. Red months in the grid are unpaid or short; green months are paid in full.
- Copy the reminder message of any tenant in arrears and send it. Check the Summary sheet for each building's totals.
- At the end of the year, save a copy for the new year, set the new start month, type each tenant's closing balance as "Arrears brought forward" and clear the Payments sheet.
Tips
- A month counts as due once its rent due day has passed. With a due day of 5, rent for this month is not shown as due until the 5th.
- Advance rent shows as a negative balance with the status "Advance". A tenant who pays three months ahead simply has three rows on the Payments sheet, one per month.
- Leases ending within 60 days turn orange in the "Days left" column and ended leases turn red, so you have time to renew or find a new tenant.
- Dates follow your computer's regional setting (for example 05-04-2026 or 4/5/2026), so the file reads naturally in any country.
- Keep the security deposit out of rent: it is shown separately so it is never counted as income by mistake.
Questions and answers
How is the arrears figure worked out?
Arrears brought forward, plus monthly rent multiplied by the months that have fallen due in the ledger year (from the later of the ledger start and the lease start, up to the "as on" month and not past the lease end), minus all payments recorded for that unit up to the last month of the year.
Can I use it for shops and offices as well as flats?
Yes. A unit can be anything with a monthly rent: a flat, a shop, an office, a room, a parking space or a godown. Use the Property column to group them by building.
What if the rent goes up in the middle of the year?
The ledger multiplies one monthly rent by the months due. When rent changes, type the new rent and add the difference for the earlier months to "Arrears brought forward" (it can be negative), or start the new rent from the next ledger year.
Does it handle GST on commercial rent or TDS?
No. It records rent received. If you charge GST on commercial rent or your tenant deducts tax at source, record the amount actually received and keep the tax working with your accountant.
Does it work in Google Sheets or LibreOffice?
Yes. It uses SUMIFS, EDATE, INDEX and MATCH, which work in Google Sheets and LibreOffice Calc, along with the drop-down lists and colour rules.