You sell on installments and you want to know, this morning, who owes you what, who is late and by how many days, and who is due today, without opening each customer's contract and counting payments by hand. This post shows how the free installments tracker does that, with a worked example. The template and its page are in Arabic.
The problem it solves
With installment sales, the money is spread over months and over many customers. Three things go wrong in a notebook or a loose sheet: a payment is written but not matched to the right contract, a part-payment hides the fact that an installment is still not complete, and nobody sees the late ones until the customer stops answering. The sheet keeps one contract per row and one payment per row, and does the matching for you.
What the template gives you
- Settings: today's date, which updates by itself, or a date you type if you want to review a specific day.
- Contracts: one row per contract: code, customer, item, price, down payment, number of installments and first due date. The sheet calculates the monthly installment, the amount paid, the remaining balance, the late amount, days late and the contract status. A drop-down lets you set a collection status for each late customer.
- Payments: every amount a customer pays, with date, contract code and amount. The customer name appears automatically, and a wrong code is flagged.
- Today: an automatic list of every late contract or one due today, most overdue first.
- Summary: total down payments, collected, remaining and late amounts, contracts by status, and late amounts by how long they have been late.
A walkthrough with the sample data
The numbers below are sample data inside the template, not real customers. The monthly installment is (price - down payment) divided by the number of installments.
- Mohamed El-Sayed: a fridge for 36,000 with 6,000 down over 12 installments gives 2,500 a month. He has paid 10,000 and 20,000 remain; status "on schedule".
- Hala Fathy: a washing machine for 12,000 with 3,000 down over 6 installments gives 1,500 a month. She has paid 4,500 and 4,500 remain; she is 38 days late in the example, and the status is "late". The days late depend on the date the example was built, so they will differ when you open it.
- Yasser Mansour: paid 10,000 and nothing remains; status "fully paid".
- Mona Ibrahim: 2,000 a month, paid 4,000, 12,000 remain; status "due today".
- Sameer Abdel Hamid: 1,500 a month, paid 1,500, 13,500 remain, and a long delay of 232 days; he is the first name on the "Today" list.
Two rules are worth knowing. The down payment is recorded in the contract, not in the Payments sheet. And if a customer pays less than the installment, the shortfall stays late and the delay is counted from the oldest installment that is not complete.
Features and why they matter
- Automatic status and days late. You do not compare dates yourself; the sheet does it every time you open it.
- No manual totals. Paid, remaining and late amounts are calculated, so they cannot drift from the payments.
- Errors flagged. A payment with a contract code that does not exist is marked.
- Collection status. You record whether you contacted the customer, got a promise to pay or no answer, so the next person knows.
- Works in Excel 2016 and later, Google Sheets and LibreOffice. It is free, with no sign-up. It holds 200 contracts and 2,000 payments.
Using the online version
The template page opens directly on this worked example, with numbers already in it. You type in the white cells (inputs) right on the page and the grey cells (calculated) update immediately. You can import your own .xlsx file made from this template, export your work to .xlsx, or download the original Excel file. Your data stays in your browser and is not sent to the server. Open the template page to try it, or browse the template list.
Common mistakes, and when a sheet stops being enough
Typical mistakes: recording the down payment as a payment, typing a first due date after the 28th and expecting the 31st (the sheet uses the 28th), and forgetting to update collection status after a call. A sheet also depends on someone copying each invoice and payment into it. When that copying becomes the job, look at Accountwiro, where an invoice and a payment are recorded when they happen and the customer's balance shows in a single statement. For customer balances without installments, see the customer ledger template on the template list. If you are weighing ready-made software, read how to compare business software.
FAQ
How does the sheet decide a customer is late? It counts the installments whose date has passed, compares their value with what the customer paid, and shows the difference as late.
What if the customer pays part of an installment? Payments are applied from oldest to newest, so the missing part stays late, counted from the oldest unfinished installment.
Where do I record the down payment? In the Contracts sheet, in the down payment column.