Income and work

Average days-to-pay by client: a simple late-payment tracker

Track how long each client takes to pay, average the days per client with a formula and use it to forecast when the next invoice will turn into cash.

Editorial illustration for Average days-to-pay by client: a simple late-payment tracker

Expect each client to pay in line with their own history. Record the invoice date and the date the money arrived for every invoice, subtract to get days, and average the days for each client; then add that average to a new invoice's date to estimate when cash will arrive. It takes four columns and one formula.

What data do I need to record?

  • Invoice number
  • Client name
  • Invoice date
  • Date the final payment arrived, taken from the bank statement
  • Days to pay, calculated as the payment date minus the invoice date

Enter dates as real dates so the sheet can subtract them. With the invoice date in C2 and the payment date in D2, a formula in E2 gives the days: =D2-C2. For part payments, use the date the last part arrived, since the invoice is not truly settled until then.

How do I average the days per client?

Put the list of clients in column G. In H2, use this and fill down. It works in Excel and in Google Sheets:

=AVERAGEIFS($E$2:$E$200,$B$2:$B$200,G2)

Here column B holds the client names and column E the days to pay. If a client has no paid invoice yet, the formula returns an error, so wrap it as =IFERROR(AVERAGEIFS($E$2:$E$200,$B$2:$B$200,G2),"") to leave a blank.

What do the averages look like?

Hypothetical days to pay: Client X took 20, 25 and 33 days. Client Y took 45, 52, 38 and 61 days. Client Z took 7 and 9 days.

ClientInvoicesDays to payAverage (days)
Client X320, 25, 3326
Client Y445, 52, 38, 6149
Client Z27, 98

Check: 78 divided by 3 is 26. 196 divided by 4 is 49. 16 divided by 2 is 8. Notice that the average hides the spread. Client Y ranges from 38 to 61 days, so plan on the upper end if your bills cannot wait.

To see the slowest case for each client, use =MAXIFS($E$2:$E$200,$B$2:$B$200,G2). It works in Google Sheets and in Excel 2019 and later. If your bills cannot wait, the maximum is the more honest planning number.

If one client is paid through a procurement process, ask them when their payment runs happen. A client who pays on a fixed day each month is easier to forecast than one who pays whenever someone remembers. Note that day beside the client name.

How do I forecast the cash date for a new invoice?

Add the client's average to the invoice date. A new invoice to Client Y dated 3 November, plus 49 days, lands on 22 December: 27 days take you to 30 November, and 22 more days reach 22 December. In a sheet, if the invoice date is in C2 and the average in H2, the expected date is =C2+H2.

Use the expected date, not the due date printed on the invoice, when you plan which bills the money will cover.

How many invoices do I need before I can trust the average?

Three is a minimum and five is better. With only one or two, treat the number as a rough guide. Recent invoices matter more than old ones, so look at the last five if a client's behaviour has changed. If you import statements into SpendixAI, you can see the real credit dates without digging through your bank app, which makes the payment date column easy to fill.

A routine that keeps it current

  1. When a payment arrives, enter the date in the sheet that day.
  2. Once a month, refresh the average for each client.
  3. For each open invoice, calculate the expected cash date.
  4. Use those dates in your weekly cash plan, and share the average with the client only if it helps a conversation about terms.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports