Your aging report flags it. We work it. You approve every message Zindo sends.  Send the 10 largest →
AR aging guide

How to use AI to build a better AR aging report

And what to do with the accounts it flags

AI tools like ChatGPT and Copilot in Excel can turn a raw open-invoice export into a clean AR aging report in minutes: days past due, aging buckets, totals by customer and a short written summary. The bigger win is adding a few columns most aging reports leave out, such as last contact date, dispute status and a "stopped working" flag, so the report shows which old balances nobody is chasing. A report shows you the problem. Someone still has to act on the 90+ day accounts it turns up.

I've spent years looking at aging reports from the other side, after the accounts in the far right column were already given up on. Here's how I'd build one with AI today, and what I'd do with what it shows.

Step 1: Start with a clean export

AI is only as good as the file you give it. Export open invoices from your accounting system (QuickBooks, Xero, NetSuite, Sage Intacct, Business Central or whatever you run) with one row per open invoice and these columns:

  • Customer name
  • Invoice number
  • Invoice date
  • Due date
  • Open amount
  • Last contact date (from your CRM, email or collections notes)
  • Dispute status (none, open, resolved)
  • Customer state or country

Pick one as-of date, usually the period end, and use it on every row. Tie the total open amount back to your AR control account before you do anything else. If the totals don't match, fix that first.

A note on data. Check your company's AI policy before you upload customer data to any tool. Many teams replace customer names with an ID for the AI step and map them back afterward.

Step 2: Let AI write the formulas

You don't need to remember Excel syntax. Describe your columns and ask for the formula. Here is an example prompt that works in ChatGPT or Copilot in Excel:

I have an Excel table named AR with columns Customer, Invoice, InvoiceDate, DueDate, OpenAmount, LastContact, DisputeStatus and State. The as-of date is in cell B1. Add columns for Days Past Due (zero if not yet due), Aging Bucket (Current, 1-30, 31-60, 61-90, 91-120, Over 120), and Days Since Last Contact. Give me the formulas and explain each one.

You'll get something close to this (example formulas, test them on your own file):

  • Days Past Due: =MAX(0, $B$1 - [@DueDate])
  • Aging Bucket: =IFS([@DaysPastDue]=0,"Current",[@DaysPastDue]<=30,"1-30",[@DaysPastDue]<=60,"31-60",[@DaysPastDue]<=90,"61-90",[@DaysPastDue]<=120,"91-120",TRUE,"Over 120")
  • Days Since Last Contact: =IF([@LastContact]="", "No contact logged", $B$1 - [@LastContact])

Split the old balances into 91-120 and Over 120 instead of one "90+" bucket. That's where the story usually is.

Step 3: Add the flags most aging reports leave out

A standard aging report tells you how old a balance is. It doesn't tell you whether anyone is working it. Add these columns and your report becomes a worklist.

Stopped working flag. Mark any account that is past 90 days and hasn't had a logged contact in 60 days or more. Example formula: =IF(AND([@DaysPastDue]>90, OR([@LastContact]="", $B$1-[@LastContact]>60)), "Stopped", ""). Adjust the thresholds to your own terms. The point is to see the balances that went quiet on your side, not just the customer's.

Dispute status. A disputed invoice and an ignored invoice need different handling. Keep disputes in their own column so they don't get chased like a simple late payment, and so they don't hide in the 90+ bucket forever.

Customer concentration. Ask for each customer's open balance as a percent of total AR, and each customer's 90+ balance as a percent of total 90+. If two or three customers make up most of your oldest bucket, that's a different conversation than a long tail of small accounts.

In-statute check. Every state and country sets a time limit for collecting a business debt, and the clock usually runs from the due date or the last payment or acknowledgment. Add a column with the limit for each customer's state, and flag anything getting close. See our B2B statute of limitations by state page for a starting point, and confirm the rule for your contracts.

Days sales outstanding (DSO). Ask AI to calculate DSO for the period and compare it to last quarter. DSO tells you the trend. The flags above tell you which accounts are driving it. More on that in how to reduce DSO without hiring more AR staff.

Step 4: Turn it into a dashboard and a summary

Once the columns are in place, ask for the views your CFO or owner actually reads. Example prompts:

  • "Build a PivotTable of open amount by customer and aging bucket, sorted by Over 120 balance, largest first."
  • "Make a stacked column chart of total AR by aging bucket for the last six month-ends."
  • "List every account flagged Stopped, with balance, days past due, days since last contact and dispute status."
  • "Write a five-sentence summary of our AR position for the weekly cash meeting. Use only the numbers in the table and say which accounts changed buckets since last week."

Copilot in Excel, ChatGPT's data analysis, Power BI and most modern AR platforms can all do this. Whichever you use, check the totals against your ledger. AI can misread a column or round a number, and it doesn't know which customer just promised a check.

Step 5: Decide what happens to each flagged account

This is the step most reports skip. An aging report that gets prettier every month but has the same names in the Over 120 column isn't helping. Sort your flagged accounts into groups:

Keep working it in-house when

  • The customer is still buying from you.
  • They paid recently or made a partial payment.
  • A dispute is actively moving toward a fix.
  • Sales has a live reason to handle the relationship.

Hand it off when

  • It's flagged Stopped: nobody has worked it in 60+ days.
  • Reminders and calls have gone unanswered.
  • It was reserved, written off or returned by a prior agency.
  • It's still inside the statute of limitations.

A third group is accounts that are outside the statute, were settled or released, or were discharged in bankruptcy. Those come off the worklist. For more on keeping customers you want to keep, see a controller's guide to stopped accounts, and for the write-off decision, should you write off or recover receivables over 120 days old?

What to do with the accounts your report flags

The accounts in the "hand it off" column are what we call abandoned receivables. They're still on the report, or they were written off last year, but nobody is actively working them. That's usually not a judgment call. Your team is busy with current invoices, and the old ones fall to the bottom.

If you've stopped working it, we'll work it.

Zindo recovers abandoned B2B receivables with an escalating sequence of demand letters on attorney letterhead. Here's how it works:

  • You approve every message. Nothing reaches your customer that you haven't cleared. Customers you want to keep stay with your team.
  • Debtors pay you directly. Funds never sit with Zindo.
  • 25% after collection. No retainer and nothing upfront. If we don't collect, you don't pay. See what contingency receivables recovery costs.
  • $100,000 minimum in B2B receivables you've stopped working, per company. No per-claim minimum.
  • Light to start. The same six fields your AI report already has: debtor name, invoice amount, invoice and due date, last activity, any dispute or payment history, and the contact on file. No data room or systems access.
  • Recovery Dashboard. Every account, step and payment, so the results show up in your next aging report too.

What the results look like

  • More than 50% recovered on in-statute accounts.*
  • $12M+ recovered for a single client.
  • Files worked since 2018, in 18 countries.
  • One anonymized company placed $211,816 in stopped accounts. Zindo recovered $125,000, or 59%.

More in our case studies.

, counting only accounts within the applicable statute of limitations. Prior results do not guarantee a similar outcome.

The Dare

You have two choices with the accounts your report flags. Leave them where they are, or send one file and let us show you what it's worth. You pay nothing unless we collect.

or call (830) 483-8443.

Related answers

Straight answers

AI and AR aging questions

Yes. Upload an open-invoice export (or paste anonymized rows) and ask it to calculate days past due from a set as-of date, group invoices into aging buckets and total them by customer. It can also write the Excel formulas so you can rebuild the report yourself each month. Check the totals against your ledger before you use it.

Yes. Copilot works on the data in your open workbook. Ask it to add a days-past-due column, assign aging buckets, build a PivotTable by customer and summarize the result in plain English. It doesn't pull live data from your ERP, so start from a current export.

At minimum: customer, invoice number, invoice date, due date, open amount, days past due and aging bucket. To make it useful for collections, add last contact date, dispute status, customer state or country, a stopped-working flag and each customer's percent of total AR.

Follow your company's AI and data policy first. Many teams use the business or enterprise version of a tool, or replace customer names with an ID before uploading and map them back afterward.

Age by due date to see what is actually overdue, which is what collections needs. Some teams age by invoice date for reserve work. Pick one, label it on the report and use it every period.

Decide, account by account. Keep working the ones where the customer is still buying, paying something or resolving a real dispute. For the ones nobody has worked in months and that are still inside the statute of limitations, hand them to someone who will. Waiting rarely makes an old invoice easier to collect.

A simple rule: more than 90 days past due and no logged contact in 60 days or more. Tune the numbers to your terms. If your report flags it and nobody on your team owns it, it's stopped.

No. Your AR tools and your aging report show you what's owed. Zindo works the accounts your team has stopped working, and the results flow back into the same report.

25% of what's collected, billed only after collection. Nothing upfront and no retainer. Zindo works with companies that have at least $100,000 in B2B receivables they've stopped working.

Find out what's still worth chasing first.

Send your aging report and we'll score up to 50 past-due B2B accounts in two business days. Free, no obligation, and no customer is contacted.

We work with companies that have at least $100,000 in B2B receivables they've stopped working.

If you want us to work it

After you have the Score

If you want us to work it, you choose which accounts we work. When you place accounts, Zindo retains attorneys, at our expense, to send an escalating sequence of letters on your behalf. You approve every letter before it goes out. Zindo is not a law firm and does not give legal advice. Your customer pays you directly.

We're paid 25% of the amount actually collected, invoiced only after collection. No recovery, no fee. Custom rates are available for larger portfolios.

Minimum to place accounts: $100,000