# Rent collection tracker for properties (free Excel)

> Rent tracker: units, tenants, lease dates, payments log, balance and months late, lease-ending alerts, follow-up list and expected vs collected summary.

Source: https://softwiro.com/templates/rent-collection
Language: English
Softwiro, softwiro.com

---

Free Excel template

# Rent collection tracker for properties

A free Excel sheet for collecting rent on your properties. You enter your units and tenants with their monthly rent and lease dates, then log payments. For each tenant the sheet calculates what is due, what has been paid, the balance, the number of months late and the last payment. It alerts you to leases ending soon and gives you a list of tenants to follow up.

[Download the free sheet (with a worked example)](https://softwiro.com/downloads/templates/softwiro-rent-collection.xlsx) [Blank copy, no example](https://softwiro.com/downloads/templates/softwiro-rent-collection-blank.xlsx)

Excel file (.xlsx) · 146 KB · 7 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

## Try the sheet here, on the example

The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.

## Example from the sheet

| كود الوحدة | المستأجر | الإيجار الشهري | الرصيد المستحق | الأشهر المتأخرة | الحالة |
| --- | --- | --- | --- | --- | --- |
| U01 | أحمد سالم | 5,000.00 | 0.00 | 0 | مسدد |
| U02 | مها حسن | 6,000.00 | 0.00 | 0 | مسدد |
| U03 | خالد عمر | 4,500.00 | 9,000.00 | 2 | متأخر |
| U04 | سارة يوسف | 7,000.00 | 7,000.00 | 1 | متأخر |
| U05 | طارق مجدي | 12,000.00 | 24,000.00 | 2 | متأخر |
| U06 | منى حسن | 5,500.00 | -5,500.00 | 0 | دفع مقدمًا |
| U07 |  | 6,500.00 |  |  | شاغرة |
| U08 | هشام لطفي | 4,000.00 | 4,000.00 | 1 | متأخر |

Example from the “المستأجرون” tab (Arabic), with the numbers and formulas that are in the file.

## What is inside

- "الإعدادات" (Settings): Today's date and how many days before a lease ends the alert starts (60 days in the example).
- "المستأجرون" (Tenants): For each unit: tenant, monthly rent, lease start and end, and due day. The sheet then shows months due, amount due, amount paid, balance, months late, last payment, collection rate, status and a lease-ending alert.
- "الدفعات" (Payments): Each payment with its date, unit code, amount and payment method. The tenant's name appears automatically.
- "للمتابعة" (Follow-up): An automatic list of late tenants, largest balance first, with their phone number, months late, last payment and lease alert.
- "الملخص" (Summary): Rented and vacant units and the occupancy rate; amount due to date, amount paid, arrears and collection rate; and the number of leases ending soon.

The file opens on a start tab (“ابدأ هنا”, Start here) that explains how to use it.

## What makes it different

Open the "للمتابعة" (Follow-up) sheet and you see who is late, by how many months and when they last paid. You do not have to add up payments and subtract them from rent for each tenant by hand. The summary shows total arrears and how many units are vacant.

## How to use it

1. In the "الإعدادات" (Settings) sheet, enter today's date (or type =TODAY() so it updates by itself) and the number of days before lease end that the alert should start.
2. In the "المستأجرون" (Tenants) sheet, enter for each unit: code, description, tenant name, phone, monthly rent, lease start, lease end and the due day of the month.
3. Leave the tenant cell empty for a vacant unit. Its status then shows "vacant" (شاغرة).
4. In the "الدفعات" (Payments) sheet, log every payment you receive: date, unit code, amount and payment method.
5. The "Tenants" sheet automatically calculates months due, amount due, amount paid, balance, months late, last payment and each tenant's status.
6. Open the "للمتابعة" (Follow-up) sheet to see late tenants sorted by largest balance, and check the "الملخص" (Summary) sheet for occupancy, amount due and amount collected.

**Before you start:**

- Months due is the number of monthly due dates from the lease start up to today, or up to the lease end if that is earlier. Amount due = months due x monthly rent.
- Balance due = amount due - amount paid. A negative balance means the tenant paid in advance, and their status shows "paid in advance" (دفع مقدمًا).
- Months late = balance due divided by monthly rent, rounded up.
- Enter the due day as a number from 1 to 28 so that it works for every month.
- A unit counts as rented when a tenant name is entered. When a tenant leaves, clear their name and lease dates, and leave their payments in the Payments sheet as they are.
- The sheet holds up to 200 units and 2,000 payments, and the Follow-up list shows the first 50 late tenants.

[Download the free sheet (with a worked example)](https://softwiro.com/downloads/templates/softwiro-rent-collection.xlsx) [Blank copy, no example](https://softwiro.com/downloads/templates/softwiro-rent-collection-blank.xlsx)

Excel file (.xlsx) · 146 KB · 7 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

## Free cheat sheet for this field

Terms, abbreviations, tables and diagrams over several pages, in Arabic and English. Download it as a PDF or images, including a phone-size version.

[![Cheat sheet: rent collection (1/3)](https://softwiro.com/uploads/cheatsheets/rent-collection/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/rent-collection/en-1.jpg)[![Cheat sheet: rent collection (2/3)](https://softwiro.com/uploads/cheatsheets/rent-collection/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/rent-collection/en-2.jpg)[![Cheat sheet: rent collection (3/3)](https://softwiro.com/uploads/cheatsheets/rent-collection/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/rent-collection/en-3.jpg)

0 downloads · 3 pages

## Still running this on Excel?

This sheet needs someone to copy every invoice and payment into it. In Accountwiro an invoice and a payment are recorded the moment they happen, and the customer's balance shows in a single statement of account.

| In this sheet | In Accountwiro |
| --- | --- |
| You copy every invoice and payment into the sheet by hand. | It shows the customer's invoices, payments and outstanding balance in a single statement of account. |
| Invoices are typed or copied by hand. | It creates an invoice by scanning a barcode or searching by product name, calculates discounts and taxes, and sends it to the customer over WhatsApp. |
| Stock shortages are discovered late. | It updates quantities after every sale and alerts you when a product drops below the required level. |
| Anyone who opens the sheet sees every number. | Permissions per user: the sales employee issues invoices, and the warehouse employee manages stock without access to financial data. |

[See Accountwiro](https://softwiro.com/systems/budoor.html) · [Ask for a demo](https://softwiro.com/contact)

## When your work grows: a custom system instead of the sheet

A sheet is a great start. When more than one person works on your data, when it grows, or when you need alerts and reports of your own, we build you a custom system at competitive prices. This is what changes:

| In the sheet | In a custom system |
| --- | --- |
| One file that one person edits at a time, and copies travel by WhatsApp. | A program with a login for each user and permissions you set; everyone sees the same data live. |
| You type and check every number by hand, and one wrong cell breaks the result. | Screens built around your steps: each person records only their part, and the calculations run on their own. |
| Limited size; the file slows down as the data grows. | Holds years of data without slowing down, with instant search and filters. |
| Fixed columns and reports, as the sheet was designed. | Reports, alerts, printing and WhatsApp built around how you work. |
| Backups and security are your own responsibility. | A secured server with automatic backups and a log of who changed what and when. |

### Extra features in the app

- A login for each user with permissions, and a log of who changed what and when
- Automatic reminders and alerts by WhatsApp and email
- Photos, files and documents kept with each record
- Instant search and filters, with reports exported to PDF and Excel
- Works from a phone anywhere, with automatic backups

[Ask for your custom system](https://softwiro.com/contact)

## Frequently asked questions

### How are months late calculated?

The sheet counts the monthly due dates from the lease start up to today, or up to the lease end if that is earlier. It multiplies that count by the monthly rent, then subtracts what the tenant has paid. Months late is that balance divided by the monthly rent, rounded up.

### What happens if a tenant pays in advance?

Their balance becomes negative and their status shows "paid in advance" (دفع مقدمًا). It is not counted in arrears. When the next due date arrives, it is deducted from that payment.

### How do I mark a unit as vacant?

Leave the tenant cell empty. The unit's status shows "vacant" (شاغرة), and it counts toward the occupancy rate and the rent of vacant units. In Settings, type =TODAY() so that today's date updates by itself.

### Is the template free?

Yes, with no sign-up and no email address. Use it, edit it and share it as you like.

### Does it work in Google Sheets and on mobile?

The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.

### What is the difference between the white and the gray cells?

You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.

### Can I use the sheet from an AI assistant?

Yes. Add our MCP server (URL [https://softwiro.com/api/mcp](https://softwiro.com/api/mcp), no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: [https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html](https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html)

All the templates in this group: [Compound and owners' association sheets: dues, maintenance and property](https://softwiro.com/templates/for-compound)

More free templates for: Real estate

## More free templates

- Real estate leads and units tracker
- Rental portfolio tracker: properties, units, tenants and contracts
- Serviced apartments and short-stay bookings sheet
- Business expenses and monthly budget sheet
- Lead and deal pipeline tracker
- Employee attendance sheet
- Employee payroll and attendance
- General shop inventory tracker (stock in/out, reorder, slow movers)
- Monthly savings circle (gamaeya) tracker
- Clinic appointments tracker (Excel)
- Recipe costing for restaurants and cafes (Excel)

[All templates](https://softwiro.com/templates) · [Free tools](https://softwiro.com/tools)
