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

No, not through a formula. Aging buckets come from the task pane: the Aged Receivables report returns every customer’s balance in Current, 1-30, 31-60, 61-90 and Over 90 columns, and Aged Receivables by Contact gives one customer’s invoices 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.

What columns does the Aged Receivables report return?

Summary view: OrgID and OrgName (auto-added), Contact, Current, 1-30, 31-60, 61-90, Over 90, and Total. Detail view adds Document type (Invoice, Credit note, or Overpayment), Document number, Reference, Document date, and Due date before the bands, with each item’s amount in its band column and repeated in Total. The per-customer Aged Receivables by Contact report instead returns Date, Reference, DueDate, a Status such as "16 days overdue", Currency, Total, Paid, Credited, Due, and Due (Base Currency).

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

Once a write-off clears an invoice’s balance in Xero (for example, a credit note or a void), the invoice drops out of the aged receivables output. The all-customers Aged Receivables report follows your Xero ledger as it is today, so that applies even to an as-of date before the write-off, the same as Xero’s own aged reports. The exact behaviour depends on how your firm books write-offs in Xero.

How does multi-currency Aged Receivables work?

Every foreign-currency item in the Aged Receivables report is converted to your organization’s base currency at Xero’s own exchange rate for the as-of date, the rate Xero prints under its own aged report, not the rate stored on the original invoice. For example, 1,000 EUR at Xero’s 31 August 2026 rate of 0.861418 comes to 1,000 ÷ 0.861418 = 1,160.88 USD. The per-customer Aged Receivables by Contact report shows each invoice in its own currency, plus a Due (Base Currency) column converted to your base currency at Xero’s rate for the report date; add up that column for a customer’s total. For dashboard tiles, XO.BALANCE on the AR account returns the base-currency total.

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. Successive recalculations are served from cache rather than the Xero API, through a short in-workbook cache (about five minutes) backed by a server-side report cache (up to thirty minutes), so a chasing controller running a recalculation every few minutes does not hammer the Xero API. Click Refresh whenever you need live figures.

Xero Aged Receivables Report in Excel: Custom Aging Buckets

Q: Can you customize the aging buckets in Xero's Aged Receivables report?

Yes. In Xero, go to Reporting → All reports and open Aged Receivables Summary or Aged Receivables Detail (in the US, Accounts Receivable Aging Summary or Detail). Under Ageing Periods, choose how many periods to show and the length of each period (for example 30 days), then click Apply. Under Ageing By, choose Due Date or Invoice Date. Click Update, and save the layout as a custom report to reuse it. For buckets Xero can’t do, such as a different rule per customer, use Excel. XO Report’s Aged Receivables report, in Detail view, lists every outstanding invoice with its due date. Add a Days column to the right of the table (your as-of date minus the Due date) and bucket it with one formula, for example =IF([@[Due date]]="","",IFS([@Days]<=0,"Current",[@Days]<=14,"1-14",[@Days]<=45,"15-45",TRUE,"Over 45")). The IF skips the last Total row, which has no due date, so your bucket totals don’t count it twice. The formula column stays in place when you refresh.

Q: Can I see aged receivables for all customers at once?

Yes. The Aged Receivables report covers every customer in one table as of the date you choose. Summary gives one row per customer; Detail gives one row per outstanding invoice, credit note or overpayment. It runs on one organization at a time. For a single customer’s invoice-by-invoice view with Paid and Credited amounts, use Aged Receivables by Contact.

Can you customize the aging buckets in Xero's Aged Receivables report?

Yes. In Xero, go to Reporting → All reports and open Aged Receivables Summary or Aged Receivables Detail (in the US, Accounts Receivable Aging Summary or Detail). Under Ageing Periods, choose how many periods to show and the length of each period (for example 30 days), then click Apply. Under Ageing By, choose Due Date or Invoice Date. Click Update, and save the layout as a custom report to reuse it.

For buckets Xero can't do, such as a different rule per customer, use Excel. XO Report's Aged Receivables report, in Detail view, lists every outstanding invoice with its due date. Add a Days column to the right of the table (your as-of date minus the Due date) and bucket it with one formula, for example =IF([@[Due date]]="","",IFS([@Days]<=0,"Current",[@Days]<=14,"1-14",[@Days]<=45,"15-45",TRUE,"Over 45")). The IF skips the last Total row, which has no due date, so your bucket totals don't count it twice. The formula column stays in place when you refresh. Xero's own guide: Aged Receivables Summary report (Xero Central).

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.

XO Report gives you both views. The Aged Receivables report covers every customer at once as of a chosen date: a Summary with one row per customer, or a Detail view with one row per outstanding invoice, credit note or overpayment. Balances are aged into Current, 1-30, 31-60, 61-90 and Over 90 day bands, the same bands Xero uses by default. Aged Receivables by Contact is the per-customer drill-down: pick one customer and get each outstanding invoice with an aging status (e.g., “16 days overdue”).

Why analysts want it in Excel

Excel is where Aged Receivables becomes a chasing workflow. The standard chasing workbook starts from every customer's aged balance, adds columns for last contact date, next chase action, payment commitment, and dispute status, then drills into one customer's invoices during call rounds. At week-end the same numbers roll up into a working-capital dashboard.

Once the aging data is live, the chasing workbook stops being a snapshot. It becomes an always-current operational view. Custom formula columns are re-applied on refresh, and the underlying aged balances refresh from Xero on demand. Keep any typed chase notes on a separate sheet next to the table. CFOs gain a current DSO trend that no longer waits for the month-end management pack to land.

For accounting firms with credit-control outsourcing engagements, the same aged-receivables 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

Run Xero's Aged Receivables report, export it, paste it into the chasing workbook, and 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.
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.
An export carries one date's exchange rates, so moving the as-of date means re-running and re-exporting. XO Report revalues every foreign-currency item at Xero's own rate for whatever as-of date you set.

The live add-in method

XO Report gives you Aged Receivables through two task pane reports, plus XO.BALANCE for portfolio totals.

Primary: every customer via the Aged Receivables report. Open Tables and Reports, choose Aged Receivables, pick one organization and an as-of date, then choose Summary (one row per customer) or Detail (one row per outstanding item) and whether to age by due date or invoice date. Click Insert. Balances land in Current, 1-30, 31-60, 61-90, Over 90 and Total columns, with foreign-currency items converted to your base currency at Xero's own exchange rate for the as-of date. Figures follow your Xero ledger as it is today, calculated back to the as-of date, just like Xero's own aged reports. If XO Report can't confirm them against Xero, it writes nothing and leaves any existing table untouched.

Drill-down: one customer via Aged Receivables by Contact. 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. You get one row per outstanding invoice: invoice date, reference, due date, an aging Status (e.g., “16 days overdue”), the invoice's Currency, Total, Paid, Credited and Due in that currency, and Due (Base Currency) in your home currency.

Honest note: aging comes from these task pane reports, not from a cell formula. Cell-level XO functions return GL balances (XO.BALANCE) or contact lists (XO.CONTACTS). They do not assign invoices to age buckets 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 aging bands across every customer, insert the Aged Receivables report; for one customer's invoices and days overdue, use Aged Receivables by Contact. There is no cell-level aging function.

XO.BALANCE on the AR GL account returns the aggregate receivables balance as of a date. It is 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; use XO.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, not through a formula. Aging buckets come from the task pane: the Aged Receivables report returns every customer’s balance in Current, 1-30, 31-60, 61-90 and Over 90 columns, and Aged Receivables by Contact gives one customer’s invoices 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.
Can I see aged receivables for all customers at once?: Yes. The Aged Receivables report covers every customer in one table as of the date you choose. Summary gives one row per customer; Detail gives one row per outstanding invoice, credit note or overpayment. It runs on one organization at a time. For a single customer’s invoice-by-invoice view with Paid and Credited amounts, use Aged Receivables by Contact.
What columns does the Aged Receivables report return?: Summary view: OrgID and OrgName (auto-added), Contact, Current, 1-30, 31-60, 61-90, Over 90, and Total. Detail view adds Document type (Invoice, Credit note, or Overpayment), Document number, Reference, Document date, and Due date before the bands, with each item’s amount in its band column and repeated in Total. The per-customer Aged Receivables by Contact report instead returns Date, Reference, DueDate, a Status such as "16 days overdue", Currency, Total, Paid, Credited, Due, and Due (Base Currency).
How are written-off invoices treated in the Aged Receivables report?: Once a write-off clears an invoice’s balance in Xero (for example, a credit note or a void), the invoice drops out of the aged receivables output. The all-customers Aged Receivables report follows your Xero ledger as it is today, so that applies even to an as-of date before the write-off, the same as Xero’s own aged reports. The exact behaviour depends on how your firm books write-offs in Xero.
How does multi-currency Aged Receivables work?: Every foreign-currency item in the Aged Receivables report is converted to your organization’s base currency at Xero’s own exchange rate for the as-of date, the rate Xero prints under its own aged report, not the rate stored on the original invoice. For example, 1,000 EUR at Xero’s 31 August 2026 rate of 0.861418 comes to 1,000 ÷ 0.861418 = 1,160.88 USD. The per-customer Aged Receivables by Contact report shows each invoice in its own currency, plus a Due (Base Currency) column converted to your base currency at Xero’s rate for the report date; add up that column for a customer’s total. For dashboard tiles, XO.BALANCE on the AR account returns the base-currency total.
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. Successive recalculations are served from cache rather than the Xero API, through a short in-workbook cache (about five minutes) backed by a server-side report cache (up to thirty minutes), so a chasing controller running a recalculation every few minutes does not hammer the Xero API. Click Refresh whenever you need live figures.

Xero Aged Receivables in Excel