Paste your open invoice register into ChatGPT and get back a full aging report in minutes — invoice-level buckets, customer summaries, DSO, and collection priority scoring, all built with formulas you can audit.
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, 61–90 days overdue (unless already High), 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 = Current. - Do NOT age from the invoice date. Age from the due date only. - Do NOT invent invoices, customers, or amounts. - Flag any invoice missing a Due Date rather than guessing. OUTPUT FORMAT 1. INVOICE DETAIL: Customer | Invoice # | Invoice Date | Due Date | Balance | Days Overdue | Bucket | Priority. Sorted by Days Overdue descending. 2. CUSTOMER SUMMARY: Customer | Total AR | Current | 1–30 | 31–60 | 61–90 | 90+. 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.
Replace all bracketed sections with your real data, then paste the prompt into ChatGPT.
My invoice register includes invoices with partial payments already applied. Here are the columns I have (columns: Customer, Invoice #, Due Date, Original Amount, Amount Paid): [Paste your invoices here] Please calculate a Balance Outstanding column as Original Amount minus Amount Paid, then build the aging report using Balance Outstanding — not the original invoice amount — for every calculation and bucket assignment. Do NOT age or size-prioritise based on the original amount. A fully paid invoice should not appear in the aging report at all.
Aging the original invoice amount instead of the remaining balance overstates your overdue exposure.
I want to track DSO period over period, not just for the current report date. Here are my prior DSO calculations: [Paste: Period label, Total AR, Credit Sales, Days in Period, DSO — for each past period you have] Current period data: Report date: [date] Total AR: $[amount] Credit sales: $[amount] over [number] days Please calculate DSO for the current period, then build a trend table showing DSO for every period side by side with the period-over-period change in days. Flag any period where DSO increased by more than [5] days.
A single DSO figure is a snapshot. A trend line is what tells you whether collections are improving or slipping.
| Customer | Invoice # | Terms | Balance | Aged from | Bucket assigned |
|---|---|---|---|---|---|
| Horizon Group | INV-1142 | Net 60 | $42,000 | Inv. Date 01/06 ✗Due: 31/07 | 31–60 days ✗Should be: 1–30 |
| Vertex Ltd | INV-1089 | Net 30 | $18,500 | Inv. Date 15/05 ✗Due: 14/06 | 61–90 days ✗Correct: 61–90 ✓ |
| Bluewave Co | INV-1201 | Net 30 | $7,200 | Inv. Date 20/07Due: 19/08 | Current ✓ |
| NordEx | INV-1055 | Net 30 | $31,000 | Inv. Date 10/04Due: 10/05 | 90+ ✓ |
| Customer | Invoice # | Due Date | Balance | Days Overdue | Bucket | Priority |
|---|---|---|---|---|---|---|
| NordEx | INV-1055 | 10/05/2026 | $31,000 | 97 days | 90+ | 🔴 High |
| Vertex Ltd | INV-1089 | 14/06/2026 | $18,500 | 62 days | 61–90 | 🟡 Medium |
| Horizon Group | INV-1142 | 31/07/2026 | $42,000 | 15 days | 1–30 | 🟡 Medium |
| Bluewave Co | INV-1201 | 19/08/2026 | $7,200 | Current | Current | 🟢 Low |
Total AR: $98,700 1–30 days: $42,000 (Horizon — large balance, Medium) 61–90 days: $18,500 90+ days: $31,000 ← collect immediately DSO = $98,700 ÷ ($540,000 ÷ 180) = 32.9 days ✓
| Customer | Original | Paid | Aged On ✗ | Bucket |
|---|---|---|---|---|
| Horizon Group INV-1142 · Due 31/07 | $42,000 | $28,000 | $42,000 ✗ | 1–30 High |
| NordEx INV-1055 · Due 10/05 | $31,000 | $31,000 ✓ | $31,000 ✗ | 90+ High ✗ |
| Vertex Ltd INV-1089 · Due 14/06 | $18,500 | $5,000 | $18,500 ✗ | 61–90 Med |
| Bluewave Co INV-1201 · Due 19/08 | $7,200 | $0 | $7,200 ✓ | Current Low |
| Customer | Invoice # | Due Date | Original | Paid | Balance | Bucket | Priority |
|---|---|---|---|---|---|---|---|
| Horizon Group | INV-1142 | 31/07/2026 | $42,000 | $28,000 | $14,000 | 1–30 | 🟢 Low |
| NordEx | INV-1055 | 10/05/2026 | $31,000 | $31,000 | $0 | Paid — removed | — |
| Vertex Ltd | INV-1089 | 14/06/2026 | $18,500 | $5,000 | $13,500 | 61–90 | 🟡 Medium |
| Bluewave Co | INV-1201 | 19/08/2026 | $7,200 | $0 | $7,200 | Current | 🟢 Low |
Balance = Original Amount − Amount Paid NordEx fully paid → removed from aging report True AR exposure: $34,700 (not $98,700) Horizon drops from High to Low — $14k balance, 1–30 days ✓
| Period | Total AR | Credit Sales | Days | DSO | Δ vs Prior |
|---|---|---|---|---|---|
| Feb 2026 | $81,400 | $490,000 | 28 | 4.7 days | ? |
| Mar 2026 | $94,200 | $520,000 | 31 | 5.6 days | ? |
| Apr 2026 | $88,700 | $480,000 | 30 | 5.5 days | ? |
| May 2026 | $102,300 | $510,000 | 31 | 6.2 days | ? |
| Jun 2026 | $98,700 | $540,000 | 30 | ? | ? |
| Period | Total AR | DSO | Δ Days | Flag |
|---|---|---|---|---|
| Feb 2026 | $81,400 | 4.7 days | — | — |
| Mar 2026 | $94,200 | 5.6 days | +0.9 | — |
| Apr 2026 | $88,700 | 5.5 days | −0.1 | — |
| May 2026 | $102,300 | 6.2 days | +0.7 | — |
| Jun 2026 | $98,700 | 5.5 days | −0.7 | — |
Jun DSO = $98,700 ÷ ($540,000 ÷ 30) = 5.5 days Period range: 4.7 → 6.2 → 5.5 days No period exceeded the +5-day flag threshold. Collections stable — no escalation needed. ✓
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 ChatGPT your standard payment terms and it will calculate due dates.
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 directly.
A register with 500 paid invoices and 40 open ones forces ChatGPT to sort through noise. Filter to open invoices only. Paid invoices belong in your bank reconciliation, not the AR tracker.
ChatGPT 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.
DSO = Total AR ÷ (Credit Sales ÷ Days in Period). Without a revenue figure and period length, ChatGPT can't compute it. Give both explicitly, and use credit sales rather than total revenue if a meaningful share of sales is cash.