How to build an AR aging report in Excel with Copilot, Claude or ChatGPT
An accounts receivable aging report sorts every open customer invoice by how many days it is past its due date at a fixed cut-off, then totals the balances by customer and by bucket: current, 1-30 days, 31-60 days, 61-90 days and over 90 days. Copilot in Excel, Claude for Excel and ChatGPT data analysis can all build that report from an open-items export, write the formulas and summarize the result, but each of them can also age from the wrong date, misplace a boundary invoice or quietly use today's date instead of the month-end cut-off.
The reliable workflow is to give the AI a clean open-items table, a fixed as-of date and an explicit bucket rule, then check its totals against a result you already know. The worked example below uses ten illustrative invoices at October 31, 2026, with the expected answer computed independently: $63,730.00 of open receivables, of which $46,480.00 (72.9%) is past due and $23,750.00 (37.3%) is more than 60 days past due.
··········
WHAT AN AR AGING REPORT DOES IN THE MONTH-END CLOSE.
Controllers use the aging report for three separate jobs, and the AI prompts should reflect which one is in play. The first is collections: the credit team needs a customer-level list of overdue balances, sorted by age and amount, to decide who to call first. The second is the allowance for credit losses: under ASC 326 and under the IFRS 9 simplified approach for trade receivables, many companies estimate the allowance by applying loss rates to aging buckets, so the bucket totals feed a journal entry. The third is reporting: days past due and the share of balances over 60 or 90 days appear in board packs, lender compliance reports and audit requests.
The accounting background on why receivables are aged and how aging analysis supports customer credit decisions is covered in Accounts receivable aging: how it works and how to analyze customer debt. The rest of this piece concentrates on the mechanics of producing the report with AI tools and proving that the numbers are right.
Two definitions decide the result before any formula is written. Aging can run from the invoice date or from the due date; most US credit teams report days past due, measured from the due date, while some allowance models age from the invoice date. The as-of date must be the reporting cut-off, typically the last day of the month, and it must be a fixed value in the workbook. A report that uses TODAY() changes every time the file is opened, which makes it impossible to tie back to the general ledger balance at month-end.
··········
COPILOT IN EXCEL, CLAUDE FOR EXCEL AND CHATGPT: DOCUMENTED REQUIREMENTS.
The three tools reach the same task by different routes. Copilot and Claude work inside the open workbook and can write formulas into cells, while ChatGPT data analysis reads an uploaded file and runs Python on it in a separate environment, returning tables, charts or a downloadable file. The requirements below are taken from each vendor's official documentation as checked on November 2, 2026.
........
Tool | Access documented by the vendor | Where it works | Limits and notes |
Copilot in Excel | Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Copilot subscription, or a Copilot Chat-eligible business or enterprise plan | Inside the workbook | Save as .xlsx; calculation options set to Automatic for editing |
Claude for Excel | Claude Pro, Max, Team and Enterprise plans | Add-in for Excel on the web, Windows (Microsoft 365) and Mac | No data tables, macros or VBA; not supported on Excel 2016/2019 perpetual, iPad or Android |
ChatGPT data analysis | File uploads documented for Plus and Enterprise users; Free users limited to 3 files per day | Uploaded .xls, .xlsx or .csv copy, Python in a sandbox | Spreadsheets up to about 50MB; up to 10 files per conversation |
........
Microsoft documents that Copilot in Excel can perform calculations and apply formulas across multiple sheets, insert and modify cell values and ranges, apply sorting, filtering and conditional formatting, and build PivotTables and charts. Its support pages describe edit, plan and chat modes, with the editing mode intended for multi-step tasks such as reshaping data and merging sheets. Microsoft also notes that Copilot in Excel on Windows or Mac will not work on SharePoint files from sites that require check-out, although Excel for the web still handles them.
Anthropic documents that Claude for Excel answers questions about the workbook with cell-level citations, keeps formula relationships intact when it changes values, can sort, filter, edit pivot tables and apply conditional formatting, and warns before overwriting existing data. Its own documentation states that the add-in is not recommended for audit-critical calculations without verification or for final deliverables without human review.
OpenAI documents that ChatGPT data analysis uses pandas to analyze uploaded data and that the user can open the analysis view or copy the generated code. That makes ChatGPT a useful second check on an aging report built in Excel, because the Python logic can be read line by line. For teams already using these tools in the close, the reconciliation side of the same process is set out in Month-end close with Copilot in Excel, Claude and ChatGPT: checklist and bank reconciliation.
··········
PREPARING THE OPEN-ITEMS EXPORT FOR AI AGING.
The input should be an open-items export from the accounting system, one row per open invoice, with plain-language headers and no subtotals, blank rows or merged cells. OpenAI's documentation makes the same recommendation for uploaded spreadsheets: descriptive headers, one record per row, and no multiple tables on a sheet. Copilot and Claude also read structured ranges more reliably when the data sits in a single block, and formatting the range as an Excel table makes formulas easier to audit.
The minimum columns are invoice number, customer, invoice date, due date and open amount. Open amount means the balance still unpaid after partial payments and credit memos, which is the figure that must reconcile to the accounts receivable control account. Unapplied cash and unapplied credit memos should be listed separately, because an AI asked to age them alongside invoices will often treat a negative line as a normal balance and net it against the wrong bucket.
The illustrative dataset below holds ten open invoices for four customers at an as-of date of October 31, 2026. It is invented for this example and includes two deliberate edge cases: one invoice exactly 30 days past due, and one invoice due the day after the cut-off.
........
Invoice | Customer | Invoice date | Due date | Open amount ($) |
INV-1041 | Harbor Supply Co. | 2026-06-12 | 2026-07-12 | 8,400.00 |
INV-1052 | Harbor Supply Co. | 2026-09-20 | 2026-10-20 | 5,150.00 |
INV-1063 | Harbor Supply Co. | 2026-10-15 | 2026-11-14 | 3,900.00 |
INV-1038 | Pinecrest Clinics | 2026-07-28 | 2026-08-27 | 12,600.00 |
INV-1057 | Pinecrest Clinics | 2026-10-02 | 2026-11-01 | 7,250.00 |
INV-1044 | Redline Logistics | 2026-08-10 | 2026-09-09 | 9,800.00 |
INV-1049 | Redline Logistics | 2026-09-01 | 2026-10-01 | 4,300.00 |
INV-1060 | Redline Logistics | 2026-10-09 | 2026-11-08 | 6,100.00 |
INV-1035 | Summit Dental Group | 2026-05-30 | 2026-06-29 | 2,750.00 |
INV-1055 | Summit Dental Group | 2026-09-25 | 2026-10-25 | 3,480.00 |
........
··········
PROMPTS FOR AN AR AGING REPORT IN COPILOT AND CLAUDE.
The prompt should state the as-of date, the date to age from, the exact bucket boundaries and the label text. Bucket labels such as 1-30 typed into a cell can be converted by Excel into a date, so labels with the word "days" are safer. A prompt for Copilot in Excel or Claude for Excel, with the data in columns A to E and the as-of date in cell J1, can read as follows.
The table in A1:E11 lists open customer invoices. Cell J1 holds the as-of date 10/31/2026.
1. Add column F "Days past due" = as-of date in J1 minus the due date in column D. Use $J$1, never TODAY().
2. Add column G "Bucket" with these rules: 0 or fewer days = "Current"; 1 to 30 = "1-30 days";
31 to 60 = "31-60 days"; 61 to 90 = "61-90 days"; more than 90 = "Over 90 days".
3. On a new sheet "Aging", build a summary with one row per customer and one column per bucket,
using SUMIFS on the open amounts, plus a Total column and a Total row.
4. Do not change any values in columns A to E. Show me the formulas you used.The formulas the AI should produce are ordinary Excel functions, and they are short enough to check by eye. For row 2 of the invoice table, they look like this.
F2: =$J$1-D2
G2: =IFS(F2<=0,"Current",F2<=30,"1-30 days",F2<=60,"31-60 days",F2<=90,"61-90 days",TRUE,"Over 90 days")
Summary cell for customer in A2 and bucket label in B1 on the Aging sheet:
=SUMIFS(Invoices!$E$2:$E$11,Invoices!$B$2:$B$11,$A2,Invoices!$G$2:$G$11,B$1)For ChatGPT, the same rules go into the prompt together with an instruction to show the code and to return the summary as a table and a downloadable .xlsx file. The value of the ChatGPT pass lies in reading the pandas code: the date subtraction, the bin edges and whether the as-of date was hard-coded as requested.
··········
THE EXPECTED AGING RESULT AT OCTOBER 31, 2026.
The correct result below was computed independently of any AI tool, so it can be used as the answer key for the example. Days past due equal October 31, 2026 minus the due date. INV-1049, due October 1, is exactly 30 days past due and belongs in 1-30 days. INV-1057, due November 1, shows minus one day and is current. INV-1041 (111 days) and INV-1035 (124 days) are over 90 days.
........
Customer | Current | 1-30 days | 31-60 days | 61-90 days | Over 90 days | Total |
Harbor Supply Co. | 3,900.00 | 5,150.00 | 0.00 | 0.00 | 8,400.00 | 17,450.00 |
Pinecrest Clinics | 7,250.00 | 0.00 | 0.00 | 12,600.00 | 0.00 | 19,850.00 |
Redline Logistics | 6,100.00 | 4,300.00 | 9,800.00 | 0.00 | 0.00 | 20,200.00 |
Summit Dental Group | 0.00 | 3,480.00 | 0.00 | 0.00 | 2,750.00 | 6,230.00 |
Total | 17,250.00 | 12,930.00 | 9,800.00 | 12,600.00 | 11,150.00 | 63,730.00 |
Share of total | 27.1% | 20.3% | 15.4% | 19.8% | 17.5% | 100.0% |
........
The grand total of $63,730.00 must equal the sum of the open amounts in the source data and, in a real close, the accounts receivable control account balance in the trial balance at the same date. Past-due balances total $46,480.00, or 72.9% of the ledger, and balances more than 60 days past due total $23,750.00, or 37.3%. Pinecrest Clinics carries the single largest problem item, a $12,600.00 invoice 65 days past due, while Harbor Supply Co. has the oldest material balance, $8,400.00 at 111 days.
If the same invoices were aged from the invoice date instead of the due date, the buckets would shift: INV-1049 would be 60 days old instead of 30 days past due, and INV-1057 would fall into 1-30 days instead of current. Both versions are arithmetically correct; they answer different questions, and the report heading must say which basis was used.
··········
FROM AGING BUCKETS TO THE ALLOWANCE FOR CREDIT LOSSES.
Many companies apply historical loss rates, adjusted for current conditions and reasonable forecasts, to the aging buckets to estimate the allowance. ASC 326 permits loss-rate methods built on aging schedules for trade receivables, and IFRS 9 allows a provision matrix under the simplified approach, which always measures lifetime expected credit losses for trade receivables without a significant financing component. The measurement rules themselves are explained in How expected credit losses are measured under IFRS 9 (ECL) and ASC 326 (CECL).
The loss rates below are illustrative only and would come from the company's own collection history, adjusted for forward-looking information. They are not benchmarks.
........
Bucket | Balance ($) | Illustrative loss rate | Allowance ($) |
Current | 17,250.00 | 0.5% | 86.25 |
1-30 days | 12,930.00 | 2.0% | 258.60 |
31-60 days | 9,800.00 | 6.0% | 588.00 |
61-90 days | 12,600.00 | 15.0% | 1,890.00 |
Over 90 days | 11,150.00 | 40.0% | 4,460.00 |
Total | 63,730.00 | 7,282.85 |
........
If the allowance account carries an existing credit balance of $4,000.00 before the month-end adjustment, the entry increases it by $3,282.85. The debit side is labelled credit loss expense under ASC 326; under IFRS the same amount is an impairment loss on trade receivables in profit or loss.
........
Account | Debit ($) | Credit ($) |
Credit loss expense | 3,282.85 | |
Allowance for credit losses | 3,282.85 | |
Total | 3,282.85 | 3,282.85 |
........
Specific reserves sit outside the matrix. If the credit team knows that a customer has filed for bankruptcy, that customer's balance is usually assessed individually and removed from the pooled calculation, otherwise it is double-counted. The presentation side, including how the allowance appears on the balance sheet, is covered in Allowance for doubtful accounts: IFRS and US GAAP treatment, expected credit losses, provision matrices and balance-sheet presentation.
··········
COMMON AI ERRORS IN AR AGING AND HOW TO CATCH THEM.
The most frequent error is the date basis. An AI asked to "age the invoices" without instructions will pick invoice date or due date on its own, and the choice is not always stated in the answer. The second is the use of TODAY() or the current system date in place of the cut-off, which produces a report that looks right on the day it is built and drifts afterwards. The third is boundary handling: a rule written as "less than 30" instead of "30 or fewer" moves INV-1049 from 1-30 days into 31-60 days and changes two bucket totals by $4,300.00 each.
Other problems show up in messy exports. Dates stored as text are aged as zero or produce errors; credit memos with negative amounts get netted against the wrong customer; and customer names with trailing spaces or slightly different spellings split one account into two rows of the summary. Excel itself may also convert a typed bucket label into a date, which breaks every SUMIFS that refers to it.
Four checks catch these errors. The summary grand total must equal the sum of the source open amounts and the AR control account at the cut-off date. Each customer total in the summary must equal that customer's open items in the source. A count of invoices per bucket should be compared with a quick manual sort of the days-past-due column, with particular attention to invoices exactly on 30, 60 and 90 days. The formulas should reference a fixed as-of cell, which Claude's cell citations, Copilot's formula display or ChatGPT's code view all make visible.
··········
CONTROLS AND DATA PRIVACY FOR AI-BUILT AGING REPORTS.
The aging report and any allowance adjustment that follows from it should remain reviewed and approved by a person with authority over credit and close. The AI can build and refresh the schedule, but the decision to write off an invoice, to reserve a specific customer or to change loss rates belongs to the controller, and the evidence trail should show who approved it. Claude for Excel's documentation also warns against prompt injection in workbooks from external sources and asks users to work only with trusted spreadsheets, a relevant point when customer remittance files or third-party collection reports are pasted into the same workbook.
Data handling differs between the tools. Anthropic states that inputs and outputs of Claude for Excel are deleted on the backend within 30 days, except in documented cases, that chat history is stored locally in the browser, and that add-in activity is not included in Enterprise audit logs, although Enterprise organizations with the Compliance API enabled have it covered. OpenAI states that it does not use content submitted to its business offerings to improve its models, while content submitted by individual users may be used to improve model performance, and that data analysis sandboxes are destroyed within 13 hours of a conversation becoming inactive. Microsoft directs work and school users to its Copilot data, privacy and security documentation, and notes that enterprise admins must enable Anthropic as a Microsoft sub-processor before users can work with Claude models in Copilot. Customer names and balances are commercial data, so the company's AI policy should say which of these tools may receive them.
··········
AR AGING WITH AI IN EXCEL: WHAT THE EXAMPLE PROVES.
The example shows that an AI-built aging report is only as reliable as the three definitions given in the prompt: the as-of date, the date to age from and the bucket boundaries. With those fixed, the result for the ten illustrative invoices is unambiguous: $63,730.00 in total, $17,250.00 current, $12,930.00 at 1-30 days, $9,800.00 at 31-60 days, $12,600.00 at 61-90 days and $11,150.00 over 90 days, and an illustrative allowance of $7,282.85 requiring a $3,282.85 month-end entry.
What the example cannot settle is the judgement around it. Loss rates, specific reserves, the treatment of disputed invoices and the decision to write off old balances depend on each company's collection history, customer contracts and credit policy, and they remain management decisions with documented approval. Copilot and Claude are well suited to building and refreshing the schedule inside the workbook, ChatGPT's code view is a practical independent recalculation, and the tie-out to the general ledger remains the control that proves the report is complete.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org
