Automate Accounts Receivable Tracking in Excel with Claude AI
Progress1 of 4
1
How the tracker works
2
What Claude needs from you
3
Prompt, FAQ & related
4
Quick quiz
Section 01
An invoice isn't revenue until the cash arrives. This tracker tells you which cash is late.
You know the total AR balance. What you probably don't know — without digging through sub-ledgers — is which invoices are 90+ days overdue, which customer owes the most, and what your collection team should chase first tomorrow morning. That's what this tracker builds in minutes.
Claude takes your open invoice register, calculates aging for every line, assigns each invoice to a bucket, scores collection priority, and produces a customer-level summary with DSO. You update it weekly by pasting in fresh data.
The Pipeline: Invoices → Aging → Collection Priorities
Invoice register
Days overdue
Aging buckets
Priority & DSO
The Four Aging Buckets
Current
Not yet due
Low risk
No action needed
1–30 Days
Recently overdue
Moderate risk
Send reminder
31–60 Days
Significantly late
Elevated risk
Escalate contact
90+ Days
Collection at risk
High write-off risk
Immediate action
The Output: What Claude Produces
A
B
C
D
E
F
G
1
Customer
Invoice
Due Date
Balance
Days Over
Bucket
Priority
2
Acme Ltd
INV-1088
15/03/2025
42,000
118
90+
High
3
Globex Corp
INV-1142
28/05/2025
18,500
44
31–60
Medium
4
Initech
INV-1198
20/06/2025
8,200
21
1–30
Low
5
Soylent Inc
INV-1215
25/07/2025
31,000
—
Current
Low
6
Customer Summary
7
Customer
Total AR
Current
1–30
31–60
61–90
90+
8
Acme Ltd
42,000
0
0
0
0
42,000
Invoice-level detail on top, customer-level summary below. Priority = f(days overdue, balance size). The collection team works top-down: High priority first. Report date: 11/07/2025.
Key insight: The aging report doesn't tell you why a customer is late — but it tells you who is late, how late, and how much is at risk. That's what your collection team needs to prioritise their morning. The DSO number feeds directly into your financial KPI dashboard.
What Claude Needs From You
Claude builds the aging from an invoice register — one row per open invoice. The critical fields are customer name, invoice number, due date, and balance outstanding. Without the due date, Claude can't calculate days overdue. Without the balance, it can't score priority.
Invoice Register Layout
A
B
C
D
E
1
Customer
Invoice #
Invoice Date
Due Date
Balance
2
Acme Ltd
INV-1088
15/02/2025
15/03/2025
42,000
3
Globex Corp
INV-1142
28/04/2025
28/05/2025
18,500
4
Initech
INV-1198
20/05/2025
20/06/2025
8,200
01
Use the due date, not the invoice date, for aging
Aging measures how late a payment is — that's days past due, not days since invoicing. An invoice dated 1 January with Net 60 terms isn't overdue until 2 March. If you only have invoice dates, tell Claude your standard payment terms and it will calculate due dates.
02
Use balance outstanding, not original amount
If a $42,000 invoice has $10,000 already paid, the balance is $32,000. Aging the original $42,000 overstates your overdue exposure. Include a column for partial payments or — better — just provide the remaining balance.
03
Exclude fully paid invoices
A register with 500 paid invoices and 40 open ones forces Claude to sort through noise. Filter to open invoices only before pasting. Paid invoices belong in your bank reconciliation, not the AR tracker.
04
Specify the report date
Claude calculates days overdue as Report Date − Due Date. If you don't specify a report date, the aging is anchored to nothing. State it clearly: Report date: 11/07/2025.
Common mistake: Aging invoices from the invoice date instead of the due date. A Net 60 invoice issued 45 days ago isn't overdue — it's Current. Aging from the invoice date would put it in the 31–60 bucket and trigger a false collection alert.
Pro tip: Add a column for payment terms per customer (Net 30, Net 60, etc.) if they vary. Claude can calculate the due date from the invoice date + terms, saving you from maintaining a separate due-date column. One formula handles all customers.
The Claude Prompt for AR Tracking
Prompt — paste into Claude
ROLE
You are an AR analyst building an accounts receivable aging tracker in Excel.
OBJECTIVE
Take my open invoice register and produce an aging report with invoice-level detail, customer-level summaries, a DSO calculation, and collection priority scoring.
DATA
Report date: [e.g. 11/07/2025]
Date format: DD/MM/YYYY
Credit sales for the period: $[amount] over [number] days
Open invoice register (columns: Customer, Invoice #, Invoice Date, Due Date, Balance Outstanding):
[Paste your open invoices here — one row per invoice, no paid invoices]
AGING RULES
- Current: Due Date is on or after Report Date (not yet due)
- 1–30 days: 1 to 30 days past Due Date
- 31–60 days: 31 to 60 days past Due Date
- 61–90 days: 61 to 90 days past Due Date
- 90+ days: more than 90 days past Due Date
PRIORITY SCORING
- High: 90+ days overdue OR balance > $25,000 and 30+ days overdue
- Medium: 31–60 days overdue, or 1–30 days and balance > $15,000
- Low: Current or 1–30 days with balance ≤ $15,000
CONSTRAINTS
- Calculate Days Overdue as Report Date minus Due Date. Negative values = not yet due = Current.
- Do NOT age from the invoice date. Age from the due date only.
- Do NOT invent invoices, customers, or amounts.
- Do NOT mark Current invoices as overdue.
- Flag any invoice missing a Due Date rather than guessing.
OUTPUT FORMAT
1. INVOICE DETAIL: Columns: Customer | Invoice # | Invoice Date | Due Date | Balance | Days Overdue | Bucket | Priority. Sorted by Days Overdue descending (worst first).
2. CUSTOMER SUMMARY: Columns: Customer | Total AR | Current | 1–30 | 31–60 | 61–90 | 90+. One row per customer, using SUMIFS.
3. DSO: Total AR ÷ (Credit Sales ÷ Days in Period). Show the calculation.
4. AGING SUMMARY: Total AR by bucket across all customers.
Format as tab-separated tables ready to paste into Excel.
AR Aging Is a Leading Indicator, Not a Report
By the time an invoice is 90+ days overdue, the damage is largely done. The real value of an aging tracker is catching the drift: last month you had $20k in the 31–60 bucket. This month it's $45k. That trend — not the absolute number — is the signal to act. It tells you something changed: maybe a big customer switched to slower payment, maybe your invoicing process delayed sends, maybe terms weren't enforced.
The aging data feeds two other dashboards directly. DSO goes into your financial KPI dashboard as an efficiency metric. Total AR by bucket flows into the cash flow dashboard to predict next week's inflows — if 60% of your AR is Current, you can expect most of it within 30 days.
Common AR Tracking Mistakes
A
B
1
Mistake
What Goes Wrong
2
Aging from invoice date instead of due date
Net 60 invoices appear 30 days overdue when they're still Current
3
Including paid invoices in the register
Total AR is overstated; aging buckets include settled amounts
4
Using original amount instead of balance outstanding
Partial payments are ignored; overdue exposure looks worse than reality
5
No report date specified
Days overdue can't be calculated; the entire aging is meaningless
Frequently Asked Questions
What are accounts receivable aging buckets?
Aging buckets group outstanding invoices by how many days past due they are. Standard buckets are Current (not yet due), 1–30, 31–60, 61–90, and 90+ days overdue. Each bucket carries a different collection risk.
How does Claude calculate the aging for each invoice?
Claude subtracts the due date from the report date to get days overdue, then assigns each invoice to the appropriate bucket using IF or IFS logic. Invoices not yet due go into Current. Formulas are written out so you can verify.
Can Claude calculate DSO from the AR data?
Yes. DSO = Total AR ÷ average daily credit sales. You provide the revenue figure and the period length. Claude computes DSO and can track it period over period.
How do I handle partially paid invoices?
Include a Balance Outstanding column reflecting the amount still owed. The aging uses the balance, not the original invoice amount. This ensures the report shows actual exposure.
Does this replace an ERP AR module?
No. This is for teams managing AR in spreadsheets or needing a supplementary view. If your ERP already produces aging reports, this template adds priority scoring and customer-level analysis it may not provide.