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
- 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.
- 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.
- Leave the tenant cell empty for a vacant unit. Its status then shows "vacant" (شاغرة).
- In the "الدفعات" (Payments) sheet, log every payment you receive: date, unit code, amount and payment method.
- The "Tenants" sheet automatically calculates months due, amount due, amount paid, balance, months late, last payment and each tenant's status.
- 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.
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.
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. |
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
- 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)





