Customer Debts Tracker in Excel: Aging, Overdue Amounts and a Collection Plan

Selling on credit raises sales but traps cash. Many small businesses show profit on paper yet struggle for cash because the money sits with customers. Old debts are not collected because nobody sees them sorted by age.

This sheet gives a clear picture: how much people owe you, how much has passed its due date and which customer deserves a call today. You only log invoices and payments; the sheet calculates age, bucket and ranks the top debtors.

Who is it for?

• Businesses that sell on credit
• Accountants and collection staff
• Contractors and service providers whose certificates are paid late
• Workshops and shops that deal in instalments

What is inside the sheet?

• An invoice log with due date, amount and paid
• Aging buckets: current, 1–30, 31–60, 61–90 and over 90
• The top five debtors ranked automatically
• A who-to-call-today list
• Days sales outstanding (DSO) and the oldest debt
• A summary per customer with mobile, balance and overdue

How to use it

1. Enter the report date and the annual credit sales.
2. Log each credit invoice with its date and due date.
3. Log every payment against its invoice.
4. Call the customers on the today list, oldest and largest first.

Formulas used

• Balance = amount − paid
• Days late = report date − due date
• Overdue % = overdue ÷ total receivable
• DSO = total receivable ÷ annual credit sales × 365

Worked example from the sheet itself

Total receivable is 343,150 of which 134,150 is overdue, 39.1%.

• DSO is 104.4 days and the oldest debt is 100 days old.
• The largest debtor owes 78,200, of which 26,200 is overdue.
• Three customers are on today call list.

Debt aging

The older a debt, the lower the chance of collecting it. So receivables are split into age buckets and the oldest ones show in red so you act before they become bad debts. A debt ninety days old calls for a phone call and then a formal letter.

Days sales outstanding

DSO measures how many days on average you wait to be paid for a sale. If your terms are 30 days and your DSO is 104, collection runs far behind the agreement and your working capital is financing your customers.

Collecting without losing the customer

Start with a friendly reminder before the due date, then a call on the first day late, then a written payment plan. If your contract allows a monthly late fee, record it in the settings.

Tips for best results

• Send a monthly statement to every customer.
• Set a credit limit per customer.
• Tie the credit limit to payment history.
• Record each payment the day it arrives.

Frequently asked questions

Does it calculate late fees?

There is an input for a monthly fee if you agreed one; it defaults to zero.

Can I record part payments?

Yes, enter them in the paid column and the invoice reduces automatically.

Does it replace accounting?

No, it is a follow-up and collection tool that complements your accounting system.

Download the sheet

Press the download button at the top of the page to get the Excel file. It includes a full guide sheet, yellow input cells and formulas protected from accidental edits. The sheet is also available in Arabic from the language switcher.

Illustrative figures; late fees depend on what you legally agreed with your customer.