# Installments and collection tracker (free Excel)

> Track installment customers: contracts, down payments, payments, remaining balance, days late, collection status and who is due or late today.

Source: https://softwiro.com/templates/installments-tracker
Language: English
Softwiro, softwiro.com

---

Free Excel template

# Installments and collection tracker

A free Excel sheet for tracking installments and collections in shops. Log each customer's contract and payments. The sheet shows the monthly installment and the remaining balance, who is late and by how many days, who is due today, and the collection status of every late customer.

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

Excel file (.xlsx) · 113 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

| العميل | القسط الشهري | المدفوع | المتبقي | أيام التأخير | الحالة |
| --- | --- | --- | --- | --- | --- |
| محمد السيد | 2,500.00 | 10,000.00 | 20,000.00 |  | منتظم |
| هالة فتحي | 1,500.00 | 4,500.00 | 4,500.00 | 38 | متأخر |
| ياسر منصور | 2,500.00 | 10,000.00 | 0.00 |  | مسدد بالكامل |
| منى إبراهيم | 2,000.00 | 4,000.00 | 12,000.00 | 0 | مستحق اليوم |
| أيمن رضوان | 1,500.00 | 3,600.00 | 2,400.00 | 9 | متأخر |
| سمير عبد الحميد | 1,500.00 | 1,500.00 | 13,500.00 | 232 | متأخر |

Example from the “العقود” tab (Arabic), with the numbers and formulas that are in the file.

## What is inside

- "الإعدادات" (Settings): The date used to work out due and late installments. It updates by itself, and you can replace it with a date you type.
- "العقود" (Contracts): For each contract: price, down payment, number of installments and first installment date. The sheet calculates the monthly installment, amount paid, remaining balance, amount late, days late and contract status, with a list to pick the collection status.
- "الدفعات" (Payments): Record everything customers pay, with the date, contract code and amount. The customer name appears automatically, and you get a warning if you type a code that does not exist.
- "متابعة اليوم" (Today's follow-up): An automatic list of every contract that is late or due today, most overdue first, with the late amount, days late and collection status.
- "الملخص" (Summary): Total down payments, collected, remaining and late amounts, contracts by status, late amounts by how long they are late, and the number of late customers in each collection status.

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

## What makes it different

Open the "متابعة اليوم" (Today's follow-up) sheet each morning and you know whom to call first without going through the contracts one by one. The summary shows how many customers are late and for how long.

## How to use it

1. In the "الإعدادات" (Settings) sheet, today's date appears automatically. You can type another date if you want to review a particular day.
2. In the "العقود" (Contracts) sheet, log each installment contract: code, customer, item, price, down payment, number of installments and first installment date.
3. In the "الدفعات" (Payments) sheet, log each amount a customer pays: date, contract code and amount.
4. The "العقود" (Contracts) sheet automatically calculates the monthly installment, amount paid, remaining balance, amount late, days late and each contract's status.
5. Pick the "حالة التحصيل" (Collection status) for each late customer after you contact them, and write your note.
6. Open the "متابعة اليوم" (Today's follow-up) sheet each morning to see who is due today and who is late, most overdue first.

**Before you start:**

- Monthly installment = (price - down payment) ÷ number of installments, due on the same day of each month as the first installment. If the first installment falls after the 28th, the due day is the 28th.
- Payments are applied in order, oldest installment first. If a customer pays less than an installment, the rest shows as late from the date of the oldest incomplete installment.
- The down payment is recorded in the contract and not in the "الدفعات" (Payments) sheet.
- The sheet holds 200 contracts and 2000 payments, and the "متابعة اليوم" (Today's follow-up) list shows the first 50 contracts.

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

Excel file (.xlsx) · 113 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: instalments and collection (1/3)](https://softwiro.com/uploads/cheatsheets/installments-tracker/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/installments-tracker/en-1.jpg)[![Cheat sheet: instalments and collection (2/3)](https://softwiro.com/uploads/cheatsheets/installments-tracker/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/installments-tracker/en-2.jpg)[![Cheat sheet: instalments and collection (3/3)](https://softwiro.com/uploads/cheatsheets/installments-tracker/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/installments-tracker/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 does the sheet decide a customer is late?

It counts the installments whose due date has passed by today's date and compares their value with what the customer has paid. If the amount paid is less, the difference shows as late, and days late are counted from the due date of the oldest installment that is not fully paid.

### What happens if a customer pays part of an installment?

Payments are applied from the oldest installment to the newest. The unpaid part stays late, days late are counted from the due date of the installment that is not complete, and the customer appears in the "متابعة اليوم" (Today's follow-up) list.

### Where do I record the down payment?

In the "المقدم" (Down payment) column of the "العقود" (Contracts) sheet. It is deducted from the price before the rest is divided by the number of installments, and it is not recorded in the "الدفعات" (Payments) sheet.

### 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)

More free templates for: Shops and stores

## More free templates

- Cash Book Template for Excel
- Invoice Template with Invoice Log (Excel)
- Clothing shop inventory sheet by colour and size
- Customer accounts, credit sales and customer statement sheet
- Cash book and customer accounts sheet
- General shop inventory tracker (stock in/out, reorder, slow movers)
- Supplier purchase orders and payables tracker
- Multi-warehouse and branch inventory sheet with transfers
- Periodic stock count and count-difference reconciliation sheet
- Barcode, batch and expiry inventory sheet
- Grocery store management sheet
- Stationery shop and photocopy management sheet
- Electronics shop sheet: serials, warranty and repairs
- Wholesale clothing trade sheet: traders, stock and collection
- Food wholesale and distribution sheet in Excel: routes, expiry and collection
- Rental portfolio tracker: properties, units, tenants and contracts
- Pharmacy expiry and stock tracking sheet
- Rent collection tracker for properties
- Business expenses and monthly budget sheet
- Lead and deal pipeline tracker
- Employee attendance sheet
- Employee payroll and attendance
- Monthly savings circle (gamaeya) tracker

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