Skip to main content

Overview: Generate the A/R and A/P Aging Reports

ExtendInsights FRE | Generate A/R and A/P aging reports and build your own with XAVI.AGING

The A/R Aging and A/P Aging templates show open receivables by customer and open payables by vendor. Amounts are grouped into aging buckets: Current, 1–30, 31–60, 61–90, 90+ days, plus a Total.

  • Credit memos, unapplied payments, and vendor credits are included and shown as negative amounts.

  • Each amount is a XAVI.AGING formula you can edit.

  • Subtotals and the grand total are standard Excel totals over the rows.


Generate an aging report

  1. Open the ExtendInsights Financial Reporting task pane in Excel, then select Quick Start.

  2. Select Reports.

  3. Choose A/R Aging or A/P Aging.

  4. Enter the period date.

  5. Select Generate.


How to Use It

Both reports use the same filter block at the top of the sheet. Every formula reads from these cells, so changing one updates the whole report. Use the XAVI task pane's filter selector to fill them in.

Filter

What it does

Default

As of Date

The report includes only transactions dated on or before this date. The report title updates automatically.

Dependent on the date entered prior to generating the report.

Age as of

The date used to count how many days each balance has been outstanding. "today" ages balances as of the current date.

Format YYYY-MM-DD.

Today

Aging based on

How days are counted. With"dueDate", days are counted from the due date (days past due).

The other common option is the transaction date.

Due date

Aging buckets

A comma-separated list that sets the column headers.

Current, 1–30, 31–60, 61–90, 90+

Dimension filters, custom segments, Fields

Subsidiary, department, location, class, accounting book, custom segments, and Fields apply to the report.

None

Changing a value updates every amount on the sheet.


What the Report Includes

  • Entity rows: One row for each customer or vendor with an open balance.

  • Group subtotals: Related entities are grouped under a parent or category, with a "Total – [Group]" line under each group.

  • Unassigned balances: "No Customer/Project" (A/R) or "No Vendor" (A/P) collects open balances that aren't tied to a specific entity.

  • Grand Total: Sums every bucket across all entities. Each balance is counted once, so subtotals aren't double-counted.

Subtotals and the Grand Total are standard Excel formulas, so they recalculate instantly without calling NetSuite again.


Things to know

  • No drill-down. XAVI.AGING does not support drill-down.

  • Point-in-time aging (e.g., month-end close): Set Age As Of to the same date as As of Date. If As of Date is in the past and Age As Of is "today," balances will look older than they really were on that date.

  • Loading cells: Cells may briefly show #BUSY! or #CALC! while data loads from NetSuite. This is normal.

  • New customers or vendors: A new entity with an open balance won't appear until you re-sync the list. To add one by hand, insert a row, enter the Entity ID in column A, and copy the formulas from the row above. Make sure the row falls inside the group subtotal.

  • Currency and subsidiaries. Amounts are in the subsidiary’s base currency. A customer or vendor active in more than one subsidiary needs one formula per subsidiary.
    ➡️ Full syntax and every example: XAVI Formula Reference.


Build your own aging report with XAVI.AGING

=XAVI.AGING(type, entity, bucket, [asOfDate], [ageAsOf], [agingBasis],[subsidiary], [department], [location], [class], [accountingBook],[customSegment],[fields]) returns the open A/R or A/P amount for one customer or vendor in one bucket.

Use the standard buckets or your own ranges, such as "0-15" or "121+", up to 10 custom buckets that do not overlap.

  • Total open A/R for a customer: =XAVI.AGING("AR", 1234, "total")

  • A/P 31–60 days for a vendor: =XAVI.AGING("AP", 567, "31-60")

➡️ Full syntax and every example: XAVI Formula Reference. These examples are taken from that article.


Notes

Pre-built reports are examples, not permanent templates for day-to-day reporting.

📖 Review Working with Pre-Built Reports for guidance about fiscal period compatibility and customization.


Related Articles


Need Help?

Did this answer your question?