Softwiro
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.

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

Example from the “المستأجرون” tab (Arabic), with the numbers and formulas that are in the file.
كود الوحدةالمستأجرالإيجار الشهريالرصيد المستحقالأشهر المتأخرةالحالة
U01أحمد سالم5,000.000.000مسدد
U02مها حسن6,000.000.000مسدد
U03خالد عمر4,500.009,000.002متأخر
U04سارة يوسف7,000.007,000.001متأخر
U05طارق مجدي12,000.0024,000.002متأخر
U06منى حسن5,500.00-5,500.000دفع مقدمًا
U076,500.00شاغرة
U08هشام لطفي4,000.004,000.001متأخر

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.

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)Cheat sheet: rent collection (2/3)Cheat sheet: rent collection (3/3)
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 sheetIn 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 · Ask for a demo

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, 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

All the templates in this group: Compound and owners' association sheets: dues, maintenance and property

More free templates for: Real estate

More free templates

All templates · Free tools