Does XO Report return per-customer aging buckets through a single formula?

No. There is no single XO function that returns aging buckets — per-customer or portfolio-wide. Aging logic lives in Xero’s report API and is surfaced through the task pane Aged Receivables by Contact wizard (per-customer, per-invoice detail with a Status column reading "X days overdue"). Cell-level XO functions return GL balances (XO.BALANCE) or contact lists (XO.CONTACTS); they do not compute days-overdue or assign invoices to age buckets.

Why does the Aged Receivables by Contact report require a single customer rather than showing the whole portfolio?

The Xero API for this report is per-contact by design — credit-control workflows are conducted one customer at a time (call rounds, dispute follow-up, payment-plan negotiations), so the report shape matches the workflow. For portfolio-level views — total AR balance, DSO, working-capital — use cell-level XO.BALANCE on the AR GL account, which returns the aggregate across all customers. The two surfaces serve different audiences in the same finance team.

What columns does the Aged Receivables by Contact task pane report return?

Per FUNCTIONS.md and TABLES.md: OrgID and OrgName (auto-added), Date (invoice date), Reference (invoice number), DueDate (payment due date), Status (text like "16 days overdue"), Total, Paid, Credited, and Due. The Status column carries the days-overdue text; there are no separate bucket columns (Current / 1-30 / 31-60). For a bucketed view, the chasing workbook adds its own bucket-classification formulas on top of the Status text or DueDate column.

How are written-off invoices treated in the Aged Receivables report?

Written-off invoices are removed from the receivables ledger and therefore disappear from the Aged Receivables by Contact output — the write-off journal moves the balance off AR into a bad-debt expense account. Reversing a write-off restores the invoice. The exact behaviour depends on how your firm books write-offs in Xero (credit note, manual journal, or invoice void).

How does multi-currency Aged Receivables work?

Foreign-currency-denominated invoices appear in the per-contact report with their native-currency totals on the Total / Paid / Credited / Due columns. For aggregate dashboard tiles, XO.BALANCE on the AR account returns the base-currency aggregate (Xero handles the FX conversion at the chosen as-of date), which is the consistent number to feed working-capital metrics across multiple currencies.

Can the Aged Receivables refresh on the schedule my chasing workflow needs?

Yes. Any workbook recalculation triggers XO.BALANCE refreshes for the cell-level portfolio figures; the inserted task pane report refreshes when you click Refresh in the XO Report task pane. Cache TTL is five minutes per organization — successive refreshes inside that window read from cache, so a chasing controller running a refresh every few minutes does not hammer the Xero API.

Xero Aged Receivables in Excel — Per-Customer AR Aging

What it is

Aged Receivables is the report credit controllers open every morning. For each customer, it lists the outstanding invoices, how old they are, what was billed, what was paid, what remains due, and how many days overdue each invoice has become. It is the daily input to chasing calls, payment-plan negotiations, and dispute resolution.

In Xero, the standard Aged Receivables by Contactreport focuses on one customer at a time — you pick the customer, pick an as-of date, and Xero returns the per-invoice detail with an aging status (e.g., “16 days overdue”) on each row. The whole-portfolio aged-trial-balance view is a different report in the Xero UI; XO Report's task pane wizard surfaces the per-contact flavor because that is what credit-control workflows actually use.

Why analysts want it in Excel

Excel is where Aged Receivables becomes a chasing workflow. The standard chasing workbook layers the per-customer invoice detail with columns for last contact date, next chase action, payment commitment, and dispute status. The credit controller works one customer at a time during call rounds, then rolls aggregate AR up into a working-capital dashboard at week-end.

Once the aging data is live, the chasing workbook stops being a snapshot — it becomes an always-current operational view. Annotation columns added once survive every refresh; the underlying invoice list refreshes from Xero on demand. CFOs gain a real-time DSO trend that no longer waits for the month-end management pack to land.

For accounting firms with credit-control outsourcing engagements, the same per-customer template scales across every client: identical workflow columns, identical escalation thresholds, refreshed against each client's Xero data. One template, many customers, no export ceremony.

Why the manual method breaks

Generate the Aged Receivables by Contact report per customer in Xero, export each one, paste into the chasing workbook, re-apply formatting and chase-status annotations — repeat the full cycle every chase round.
New invoices issued mid-week are invisible in the workbook until the next export; payments received do not clear the chase queue.
Per-customer exports lose context fast — annotation columns added in the workbook do not survive a re-export because Xero produces a fresh sheet every time.
Portfolio-level DSO calculations rely on month-end aggregate exports being correct; mid-month DSO is unavailable without a fresh export and re-running the formula.
Multi-currency AR requires per-currency exports and manual FX conversion in the workbook; foreign-currency invoices keep aging in their own currency.

The live add-in method

XO Report exposes Aged Receivables two ways. Be aware that neither surface invents an aging function that does not exist — aging logic lives inside Xero's report API.

Primary: per-customer aging via the Aged Receivables by Contact wizard. Open Tables and Reports, choose Aged Receivables by Contact, pick a customer from the searchable dropdown, pick an as-of date, optionally restrict the invoice date range, and click Insert. The report lands on a new sheet with one row per outstanding invoice for that customer — columns for invoice date, reference, due date, an aging Status (e.g., “16 days overdue”), Total, Paid, Credited, and Due.

Honest note:there is no single XO function that returns per-customer or portfolio-wide aging buckets. Aging logic lives inside Xero's API. Cell-level XO functions return GL balances (XO.BALANCE) or contact lists (XO.CONTACTS) — they do not aggregate aging or compute days-overdue status.

Complementary: portfolio AR totals via XO.BALANCE. For dashboard tiles showing total Accounts Receivable across the customer book — and for DSO calculations — use XO.BALANCE on the AR GL account. Combine with trailing-twelve-month revenue from another XO.BALANCE call on the revenue account to compute days sales outstanding.

# Total Accounts Receivable balance as of date in E1
# (account 1100 is the typical AR code — check your chart)
=XO.BALANCE($A$2, "1100", $E$1, $E$1)

# To discover the AR account code, spill the chart of accounts:
=XO.COA($A$2)
# Then filter the spilled table for Class="ASSET" and Type="CURRENT"
# and look for an account whose Name contains "Receivable".

# DSO calculation: AR balance / (revenue / days)
# IMPORTANT: P&L date ranges are capped at 365 days by the Xero API,
# so the TTM revenue window must use exactly E1 - 364 to E1.
# Revenue should be the total of all revenue accounts — if your chart
# has multiple revenue accounts, sum them or use the inserted P&L
# report's Total Revenue row instead of a single XO.BALANCE call.
AR Balance:    =XO.BALANCE($A$2, "1100", $E$1, $E$1)
Revenue (TTM): =XO.BALANCE($A$2, "4000", $E$1 - 364, $E$1)
DSO:           =B2 / (B3 / 365)

For per-customer invoice aging — which invoices are overdue and by how many days for a specific customer — use the task pane Aged Receivables by Contact wizard. There is no cell-level aging function; the aging logic lives inside Xero's report API.

XO.BALANCE on the AR GL account returns the aggregate receivables balance as of a date — useful for a single dashboard tile or a DSO calculation, not for per-customer breakdowns. The account code is typically "1100" in default charts but varies; useXO.COA to confirm.

For DSO, combine total AR with trailing-twelve-month revenue (capped at the 365-day Xero limit). If your chart has multiple revenue accounts, sum them — a single XO.BALANCE on one revenue account understates DSO if you have more than one revenue stream. The cleanest production pattern is to insert the P&L via the task pane wizard once a period, then reference its Total Revenue cell in the DSO formula.

Related docs

Frequently asked questions

Does XO Report return per-customer aging buckets through a single formula?: No. There is no single XO function that returns aging buckets — per-customer or portfolio-wide. Aging logic lives in Xero’s report API and is surfaced through the task pane Aged Receivables by Contact wizard (per-customer, per-invoice detail with a Status column reading "X days overdue"). Cell-level XO functions return GL balances (XO.BALANCE) or contact lists (XO.CONTACTS); they do not compute days-overdue or assign invoices to age buckets.
Why does the Aged Receivables by Contact report require a single customer rather than showing the whole portfolio?: The Xero API for this report is per-contact by design — credit-control workflows are conducted one customer at a time (call rounds, dispute follow-up, payment-plan negotiations), so the report shape matches the workflow. For portfolio-level views — total AR balance, DSO, working-capital — use cell-level XO.BALANCE on the AR GL account, which returns the aggregate across all customers. The two surfaces serve different audiences in the same finance team.
What columns does the Aged Receivables by Contact task pane report return?: Per FUNCTIONS.md and TABLES.md: OrgID and OrgName (auto-added), Date (invoice date), Reference (invoice number), DueDate (payment due date), Status (text like "16 days overdue"), Total, Paid, Credited, and Due. The Status column carries the days-overdue text; there are no separate bucket columns (Current / 1-30 / 31-60). For a bucketed view, the chasing workbook adds its own bucket-classification formulas on top of the Status text or DueDate column.
How are written-off invoices treated in the Aged Receivables report?: Written-off invoices are removed from the receivables ledger and therefore disappear from the Aged Receivables by Contact output — the write-off journal moves the balance off AR into a bad-debt expense account. Reversing a write-off restores the invoice. The exact behaviour depends on how your firm books write-offs in Xero (credit note, manual journal, or invoice void).
How does multi-currency Aged Receivables work?: Foreign-currency-denominated invoices appear in the per-contact report with their native-currency totals on the Total / Paid / Credited / Due columns. For aggregate dashboard tiles, XO.BALANCE on the AR account returns the base-currency aggregate (Xero handles the FX conversion at the chosen as-of date), which is the consistent number to feed working-capital metrics across multiple currencies.
Can the Aged Receivables refresh on the schedule my chasing workflow needs?: Yes. Any workbook recalculation triggers XO.BALANCE refreshes for the cell-level portfolio figures; the inserted task pane report refreshes when you click Refresh in the XO Report task pane. Cache TTL is five minutes per organization — successive refreshes inside that window read from cache, so a chasing controller running a refresh every few minutes does not hammer the Xero API.

Xero Aged Receivables in Excel