---
source: https://carbone.io/examples/accounts-receivable-aging-report/
title: "AR Aging Report in Excel from Your ERP Data | Carbone"
description: "Generate an AR aging report in Excel from your ERP data with the Carbone API: open invoices by customer, aging buckets, DSO and a GL tie-out, as live formulas."
generated_at: "2026-11-17"
---

# Accounts receivable aging report

Open invoices by customer and aging bucket, reconciled to the GL

[![Accounts receivable aging report: XLSX generated from an XLSX template and JSON data](/examples/accounts-receivable-aging-report/thumb-result.webp)](/examples/accounts-receivable-aging-report/result.xlsx "Download the generated document")

[Download template](/examples/accounts-receivable-aging-report/template.xlsx)

[Sample data](/examples/accounts-receivable-aging-report/data.json)

[Download the generated XLSX](/examples/accounts-receivable-aging-report/result.xlsx)

- **Template:** XLSX
- **Export as:** XLSX, PDF, ODS, CSV, HTML [+19 more](/documentation/developer/http-api/generate-reports.md#output-file-type)
- **Carbone:** 5.18+

Features

-   [Nested loop](/documentation/design/repetitions/with-arrays.md)
-   [Formula](/documentation/design/template-formats/xlsx.md)
-   [Subtotal](/documentation/design/computation/aggregation.md)
-   [Conditional formatting](/documentation/design/template-formats/xlsx.md)
-   [Data bars](/documentation/design/template-formats/xlsx.md)
-   [Chart](/documentation/design/advanced-features/charts.md)
-   [Aggregation](/documentation/design/computation/aggregation.md)
-   [Date](/documentation/design/formatters/date.md)
-   [Number format](/documentation/design/formatters/number.md)

## Generate AR aging reports from your ERP

Credit and collections teams run the accounts receivable aging every week and at month-end, one report per business unit, from the AR subledger of the ERP. Collectors use it to decide whom to call first, sales owners follow their accounts, and the controller checks that the total agrees with the GL control account before the close. The ERP sends the open items to the Carbone API as JSON and gets back a native Excel workbook. Days past due, buckets, subtotals and KPIs are Excel formulas, so the file recalculates when a collector changes the as-of date or an amount.

In this example, Vantrell Industries ages the 28 open items of its Fluid Handling division as of October 31, 2026. The company and its seven customers are fictional.

In this example, you will:

-   Group the open items by customer with nested loops
-   Write the dates and the amounts as native Excel values
-   Age each item by due date with Excel formulas
-   Compute the customer subtotals, the grand total and the % of total of each bucket
-   Color the buckets from green to red and chart their distribution
-   Compute DSO and reconcile the total with the GL

## AR aging report structure

| Block | Content |
| --- | --- |
| Header | Company, as-of date, business unit, ledger, aging basis, currency |
| KPIs | Total receivables, past due, over 90 days, DSO, average days delinquent, credit sales, GL balance and difference |
| Customer | Name, account, payment terms, collector, then its open invoices and credit memos |
| Open item | Document number, type, date, due date, days past due, open balance, amount in its bucket |
| Customer total | Item count, credit limit, open balance and buckets |
| Totals | Total receivables and the % of total of each bucket: current, 1-30, 31-60, 61-90 and over 90 days past due |

The JSON holds the raw ledger data only. An excerpt:

```json
{
  "company": "Vantrell Industries, Inc.",
  "asOf": "2026-10-31",
  "currency": "USD",
  "glAccount": "1210 Trade receivables",
  "glBalance": 3670517.3,
  "creditSales": { "from": "2026-08-01", "amount": 8642310 },
  "customers": [
    {
      "id": "C-10655",
      "name": "Kilvane Mechanical Contractors",
      "terms": "Net 30",
      "creditLimit": 250000,
      "collector": "D. Okafor",
      "items": [
        { "type": "Invoice", "number": "INV-116958", "date": "2026-05-27", "dueDate": "2026-06-26", "openAmount": 71880 },
        { "type": "Invoice", "number": "INV-118902", "date": "2026-10-15", "dueDate": "2026-11-14", "openAmount": 33410 }
      ]
    }
  ]
}
```

## How to build an AR aging report in Excel

### 1\. Write the header and the KPIs

```text
A1: {d.company}{o.preReleaseFeatureIn=5018000}
A3: {d.asOf:diffD('2000-01-01','days'):mul(-1):add(36526):formatN}
A4: {d.businessUnit} · {d.ledger} · Aged by due date · Amounts in {d.currency}
```

-   `{o.preReleaseFeatureIn=5018000}` prints nothing. It makes the [formulas](/documentation/design/template-formats/xlsx.md#formulas), the [conditional formatting](/documentation/design/template-formats/xlsx.md#conditional-formatting) and the [chart](/documentation/design/template-formats/xlsx.md#charts) follow the generated rows.
-   Excel stores a date as a number of days. [:diffD](/documentation/design/formatters/date.md#diffd-todate-unit-patternfromdate-patterntodate) counts the days from January 1, 2000, and [:add(36526)](/documentation/design/formatters/number.md#add-value) adds the Excel number of that date. With [:formatN](/documentation/design/template-formats/xlsx.md#numbers-and-dates) last, the cell is a real date, displayed by the cell format `"As of "mmmm d, yyyy`. The same tag writes the invoice dates and the due dates.
-   The KPIs are formulas on the total row of the template: `=G22` for total receivables, `=G22-H22` for past due, `=L22` for over 90 days. Carbone moves these references with the total row.
-   DSO is `=IF(B11=0,0,A7/B11*($A$3-B12+1))`: total receivables divided by the credit sales of the period, times the days in the period. Average days delinquent uses the past due amount instead of the total.
-   `=ROUND(A7-E11,2)` compares the aging with the balance of the GL control account. The cell format prints "No difference with the aging" when they agree.

### 2\. Group the open items by customer

```text
A16: {d.customers[i].name}
B16: {d.customers[i].id} · {d.customers[i].terms} · Collector {d.customers[i].collector}
A17: {d.customers[i].items[i].number}    G17: {d.customers[i].items[i].openAmount:formatN}
A18: {d.customers[i].items[i+1].number}
A19: Total {d.customers[i].name}
A21: {d.customers[i+1].name}
```

The [nested loops](/documentation/design/template-formats/xlsx.md#loops-nested-loops) repeat rows 16 to 20 for each customer, and row 17 for each open item. Rows 18 and 21 end the loops and are removed. Row 20 is the space between two customers.

### 3\. Age each item by due date

-   E17, days past due: `=$A$3-D17`, the as-of date minus the due date. A negative number means the item is not due yet.
-   H17 to L17 put the open balance in one bucket, bounds included: `=IF($E17<=0,$G17,0)` for Current, `=IF(AND($E17>=1,$E17<=30),$G17,0)` for 1-30, then 31-60, 61-90, and `=IF($E17>90,$G17,0)` for Over 90.
-   The bucket cells use the format `#,##0.00_);(#,##0.00);`: the credit memo shows in parentheses, and the empty buckets show nothing.

### 4\. Compute the subtotals and the totals

-   B19 counts the items of the customer with [:aggCount](/documentation/design/computation/aggregation.md#aggcount-partitionby): `{d.customers[i].items[].openAmount:aggCount:formatN}`.
-   The number of rows changes for each customer, so the subtotal reads this count: `=SUBTOTAL(9,OFFSET(G$1,ROW()-1-$B19,0,$B19))` sums the rows above the customer total.
-   The total row sums the whole table with `=SUBTOTAL(9,G16:G21)`. Carbone extends the range to all the generated rows, and SUBTOTAL skips the customer subtotals.
-   Row 23 divides each bucket by the total: `=IF($G$22=0,0,H22/$G$22)`. G23 adds the five percentages, a check that prints 100.0%.

### 5\. Color the risk and chart the buckets

The [conditional formatting](/documentation/design/template-formats/xlsx.md#conditional-formatting) rules are defined on rows 17 and 19, and Carbone extends them to every customer:

-   Each bucket of row 17 gets a fill when it is not zero, from muted green for Current to red for Over 90, so each item lights up in its bucket.
-   E17 turns red above 90 days and gray for the items not due yet.
-   On the customer total, `=AND($F19>0,$G19>$F19)` highlights the credit limit when the balance exceeds it, and solid data bars compare the balances of the customers.

The [chart](/documentation/design/template-formats/xlsx.md#charts) sits above the table, next to the KPIs. Its categories are the bucket labels `H15:L15` and its values the total row `H22:L22`, which Carbone moves below the generated rows.

### 6\. Set up the page

Freeze the panes under the column labels in row 15, and repeat rows 14 and 15 on each printed page (Page Layout > Print Titles). The [header and footer](/documentation/design/template-formats/xlsx.md#page-layout) print `{d.company}`, the title and `Page &P of &N`, in landscape, fitted to the page width.

## Related Examples

- [Excel transaction report](/examples/transaction-report/index.md): XLSX template to XLSX.
- [Quarterly financial dashboard](/examples/s-financial-dashboard/index.md): XLSX template to XLSX.
- [Checking account statement](/examples/bank-statement-expert/index.md): DOCX template to PDF.

## Related topics

- [Budget vs actual report](/examples/budget-vs-actual-report/index.md)
- [Fixed asset register](/examples/fixed-asset-register/index.md)
- [Bank reconciliation report](/examples/bank-reconciliation-report/index.md)
- [13-week cash flow forecast](/examples/cash-flow-forecast-13-week/index.md)
- [Sales commission statement](/examples/sales-commission-statement/index.md)
- [Excel transaction report](/examples/transaction-report/index.md)
