Which Customers Really Pay Late?
A free Excel template for AR payment behavior analysis. Enter your invoices and the payments against them, and it shows each customer’s average days to pay, days late, on-time rate, overdue balance and credit use — with an aging, a monthly trend and an invoice-by-invoice drilldown. Built by experienced finance professionals.
📥 Get your free EasePro Template
- Experienced finance professionals
- Customer by customer, invoice by invoice
- Excel for Microsoft 365 or the web
- No credit card required
One input sheet. Everything else is calculated.
You type into one sheet. The model works out every invoice’s payment timing and status, then builds the customer view, the drilldown, the monthly trend and the dashboard from it.
Cover and instructions
An index linking to every sheet, a check on each one that reads Ok or Error, and a guide to which sheet answers what.
I1 — Input
Company name and the report date; a credit limit for each customer; and up to 250 invoices with customer, invoice date, amount, due date and up to four payments, each with its date and amount.
What it gives you back
O2 — Customer behavior
For each customer: invoiced, received, outstanding and overdue; the aging; invoice counts; average days to pay; average days late; on-time %; oldest overdue invoice; partial-payment rate; days since the last payment; and credit-limit use.
O3 and O4 — Drilldowns
Pick a customer and see every invoice: status, age, days overdue, last payment date, days late and how many payments it took. A second sheet lists every invoice for every customer.
O5 — Monthly trend
By invoice month: invoiced, collected, outstanding by age, overdue, average days to pay, average days late and on-time %.
O1 — Dashboard
Headline figures, an AR aging chart, the top 10 customers by outstanding and by overdue balance, outstanding by invoice age, credit-limit use, and an overview of any customer you select.
Days to pay tells you how long. Days late tells you who to watch.
Every invoice is measured twice: days to pay, from the invoice date to the last payment, and days late, from the due date to the last payment. A customer on 60-day terms will always take longer to pay than one on 30 days. Only days late tells you who is breaking their terms.
Your aging says who owes you. It doesn’t say how they pay.
An AR aging report tells you how old each unpaid invoice is. It doesn’t tell you whether a customer always pays two weeks late, used to pay on time and is slipping, or pays in four installments. That history is what decides when the next invoice will be paid.
So this template works from your payment history, not just what is open today. It measures every paid invoice against its due date, averages it by customer, and puts the customers who break their terms at the top. That number is the expected delay you use when you forecast receipts — covered in how to forecast cash receipts accurately and due dates vs payment behavior.
Six things it does that an aging report can’t.
All of it runs from the one input sheet, measured as at the report date you set.
Days to pay and days late
For every invoice: days from invoice to last payment, and days from due date to last payment. Averages and on-time % use fully paid invoices only, so open invoices don’t distort them.
On-time rate
The share of each customer’s paid invoices settled on or before the due date.
Partial payments
Up to four payments per invoice. The model flags invoices paid in part and shows how often each customer pays that way.
Overdue and oldest overdue
Every unpaid invoice past its due date at the report date is overdue, with the days counted and the oldest one shown for each customer.
Credit-limit use
Outstanding balance against the credit limit you set for each customer, so you can see who is over their limit.
Checks on every sheet
Each sheet reports Ok or Error, including a check for duplicate invoice numbers and that every invoice’s payments plus balance equal its amount.
What to gather before you open the file.
Most of it comes from two exports: your invoice list and your customer payments, ideally covering at least six months.
From your accounting system
- Invoices: number, customer, invoice date, amount and due date — paid and unpaid
- Payments received against each invoice, with dates and amounts
- Up to 250 invoices and four payments per invoice fit on the input sheet
From your team
- A credit limit for each customer, if you set them
- The report date — usually today, or the last month-end
- Which customers have changed terms recently, so you read their history fairly
Clear unapplied payments and credit notes in your accounting system first — otherwise paid invoices look open. Using AR aging to forecast collections covers why.
How to use the AR payment behavior template: four steps.
Then refresh it monthly: add the new invoices and payments, move the report date forward, and look at who moved up the list.
General inputs
On I1_Input: your company name and the report date. Everything is measured as at that date.
Credit limits
Enter a credit limit against each customer, so the model can show credit use. Leave it at zero if you don’t set limits.
Invoices and payments
Paste your invoices — number, customer, invoice date, amount, due date — and up to four payments against each, with dates and amounts.
Read the customer view
On O2, sort by average days late. Start with the customers at the top, then open O3 for any of them to see whether their recent invoices are slower than their older ones.
Is this template right for you? Honestly.
This template is for you if:
- You invoice customers on terms and want to know which of them really pay late.
- You forecast receipts and need each customer’s usual delay, not the due date.
- You want overdue balances, payment history and credit use in one place, customer by customer.
- You can export invoices and payments from your accounting system.
This is not for you if:
- You need the cash each week, not the behavior behind it — use the AR collection forecast template, ideally with the delays this one gives you.
- You run Excel 2021 or earlier, or Google Sheets — the model relies on newer Excel functions they do not have.
- You have more than 250 invoices in the period, or need multi-currency or multi-entity AR.
- You only need this Monday’s pay-or-chase decision. Use the Weekly Cash Decision Calculator instead.
This is a self-serve model. If you would rather have customer payment behavior tracked from your ledger every month and built into your cash forecast, talk to us.
Use it with the rest of your cash flow.
This template tells you how each customer pays. These turn that into a forecast and a plan.
AR payment behavior template questions, answered honestly.
What is AR payment behavior analysis?
What is the difference between days to pay and days late?
Why are the averages based on paid invoices only?
How many invoices and payments does it handle?
Which version of Excel do I need?
How is it different from the AR collection forecast template?
Is it really free? What do you ask for?
What if I want this tracked for my business every month?
Want customer payment behavior tracked from your ledger every month?
Knowing who pays late is the first step. Using it — in a weekly forecast, a chasing routine and credit decisions — is what changes your cash. EasePro’s Cash Flow Management Services build that from your own AR and payment history and run it with your team.
Founders from global advisory firms, supported by an in-house trained team of finance professionals. Big Four-grade depth with our own standards for accuracy and data security.