Build an Accounts Receivable Aging Report with Python

The aged receivables report answers the question credit control asks every week and finance asks every month end: who owes us money, and how late is it? It takes every open invoice, works out how far past its due date it is as at a chosen date, sorts the outstanding amounts into buckets — current, 1–30 days, 31–60, 61–90, over 90 — and totals them per customer. The export from the accounting system has everything needed; the work is in getting the as-at date, the buckets, the credit notes and the tie-out right. This page is part of Building Finance and Operations Reports, which covers the shared pipeline.

Prerequisites

pip install pandas openpyxl xlsxwriter

open_items.xlsx is an open-items export: one row per document with the customer, document and due dates, the original amount and the amount paid so far. It includes a partly paid invoice, a fully paid one the export still lists, a credit note, an unapplied payment and an invoice dated after the period end. credit_limits.xlsx has each customer's credit limit.

Step 1 — Work out the outstanding amount and days past due

The balance still owed on each document is its amount less what has been paid. Days past due is measured from the as-at date — the period end — not from today, so the September aging gives the same answer whenever it is run:

import pandas as pd

as_at = pd.Timestamp("2026-09-30")
items = pd.read_excel("open_items.xlsx", parse_dates=["Doc Date", "Due Date"])
items["Outstanding"] = items["Amount"] - items["Paid"]

after = items[items["Doc Date"] > as_at]
items = items[(items["Doc Date"] <= as_at) & (items["Outstanding"] != 0)].copy()
items["Days past due"] = (as_at - items["Due Date"]).dt.days
print("excluded, dated after as-at:", after["Document"].tolist())
print(items[["Document", "Customer", "Due Date", "Outstanding", "Days past due"]].to_string(index=False))

INV-2230 is dated 2 October, after the as-at date, so it was not owed on 30 September and is excluded. INV-2160 has been paid in full; exports often keep such documents until the payment is matched, and they drop out here because nothing is outstanding. A negative days-past-due figure means the invoice is not due yet.

Step 2 — Put each balance in a bucket

pd.cut assigns every document to a bucket from its days past due. The edges are inclusive on the right, which matches how aging is normally defined — exactly 30 days is still "1–30":

edges = [float("-inf"), 0, 30, 60, 90, float("inf")]
buckets = ["Current", "1-30", "31-60", "61-90", "90+"]
items["Bucket"] = pd.cut(items["Days past due"], bins=edges, labels=buckets)
print(items[["Document", "Days past due", "Bucket", "Outstanding"]].to_string(index=False))

The credit note CN-0041 and the unapplied payment PAY-0917 are aged by their own dates like any other document, so they appear as negative amounts in the 1–30 bucket and reduce the customer's balance there. Some teams prefer a separate "Unapplied" column for them; the choice matters less than applying it consistently, because the customer total is the same either way. Bin Values into Ranges with pandas cut explains the edge rules in detail.

Step 3 — Pivot to one row per customer

The report itself is a pivot: customers down the side, buckets across the top, the outstanding amount in the cells. observed=False keeps every bucket as a column even if no customer has anything in it, so the layout is the same every month:

aging = items.pivot_table(index="Customer", columns="Bucket", values="Outstanding", aggfunc="sum",
                          fill_value=0, observed=False)
aging.columns = aging.columns.astype(str)
aging["Total"] = aging.sum(axis=1)
aging["Over 60"] = aging["61-90"] + aging["90+"]
aging = aging.sort_values("Total", ascending=False)
print(aging.to_string())

Sorting by total puts the largest exposures first, which is how credit controllers read the list. The "Over 60" column is a common extra: it is the figure most collection policies act on.

Step 4 — Flag credit limits and tie out to the ledger

Join the credit limits and flag customers whose total exceeds theirs. Then prove the aging accounts for the whole receivables balance: its grand total must equal the outstanding total of every document dated on or before the as-at date, which in turn should match the receivables control account in the trial balance:

limits = pd.read_excel("credit_limits.xlsx").set_index("Customer")["Credit Limit"]
aging["Credit limit"] = limits.reindex(aging.index)
aging["Over limit"] = aging["Total"] > aging["Credit limit"]
print(aging.loc[aging["Over limit"], ["Total", "Credit limit"]])

raw = pd.read_excel("open_items.xlsx", parse_dates=["Doc Date"])
ledger_balance = (raw.loc[raw["Doc Date"] <= as_at, "Amount"] - raw.loc[raw["Doc Date"] <= as_at, "Paid"]).sum()
print(f"aging total {aging['Total'].sum():,.2f}  ledger {ledger_balance:,.2f}")
assert round(aging["Total"].sum() - ledger_balance, 2) == 0

Corex owes 8,700 against a limit of 5,000, most of it more than a month overdue. The aging total of 37,100 matches the independently computed balance. In practice the comparison figure comes from the trial balance — the receivables control account as at the period end — and a difference means documents are missing from the export, or postings bypassed the sales ledger.

Across all customers, most of the balance is not yet due, but 11,800 — almost a third — is more than 30 days past due:

Step 5 — Write the report

Write the customer aging with the buckets in order, a totals row, money formats, and the over-limit customers highlighted. Put the invoice-level detail on a second sheet so any balance can be traced to its documents:

out = f"ar_aging_{as_at:%Y-%m-%d}.xlsx"
cols = buckets + ["Total", "Over 60", "Credit limit"]
with pd.ExcelWriter(out, engine="xlsxwriter") as writer:
    report = aging[cols].reset_index()
    report.to_excel(writer, sheet_name="Aging", index=False, startrow=2)
    wb, ws = writer.book, writer.sheets["Aging"]
    money = wb.add_format({"num_format": "#,##0;(#,##0);-"})
    bold = wb.add_format({"num_format": "#,##0;(#,##0);-", "bold": True, "top": 1})
    ws.write("A1", f"Aged receivables as at {as_at:%d %B %Y}", wb.add_format({"bold": True, "font_size": 14}))
    total_row = len(report) + 3
    ws.write(total_row, 0, "Total", wb.add_format({"bold": True, "top": 1}))
    for c in range(1, len(cols)):
        col = chr(ord("A") + c)
        ws.write_formula(total_row, c, f"=SUM({col}4:{col}{total_row})", bold)
    ws.set_column("A:A", 16)
    ws.set_column(1, len(cols), 11, money)
    ws.conditional_format(3, 0, total_row - 1, 0, {"type": "formula", "criteria": f"=$G4>$I4",
                          "format": wb.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})})
    ws.freeze_panes(3, 1)

    detail = items.sort_values(["Customer", "Due Date"])[["Customer", "Document", "Doc Date", "Due Date", "Outstanding", "Days past due", "Bucket"]]
    detail.to_excel(writer, sheet_name="Detail", index=False)
    writer.sheets["Detail"].set_column("A:B", 14)
    writer.sheets["Detail"].set_column("C:D", 12, wb.add_format({"num_format": "yyyy-mm-dd"}))
    writer.sheets["Detail"].set_column("E:E", 12, money)
    writer.sheets["Detail"].autofilter(0, 0, len(detail), 6)

print(pd.ExcelFile(out).sheet_names, "-", len(report), "customers,", len(detail), "documents")

The totals row uses SUM formulas so the reviewer can see how it adds up — opened in a spreadsheet, it shows 23,650 current and 37,100 in total; the conditional format compares each row's Total (column G) with its credit limit (column I) and shades the customer name. The detail sheet has an autofilter, so filtering on one customer shows exactly the documents behind their row.

Common pitfalls

• Aging from today. Always measure from the as-at date, so reruns match what was reported.
• Including documents after the as-at date. Filter on document date; the export may be run days later.
• Dropping credit notes. Negative items belong in the aging; leaving them out overstates the balance.
• Buckets that disappear. Use observed=False so every bucket is always a column.
• No tie-out. The aging total must equal the receivables control account.
• Due date versus invoice date. Aging by invoice date is a different report; label clearly which one it is.

Performance notes

Aging tens of thousands of open items is instant in pandas. The slow part is usually the export; when the accounting system offers an API or a database view of open items, query it directly rather than waiting for a spreadsheet.

Conclusion

Compute each document's outstanding amount and its days past due as at the period end, excluding documents dated after it. Bucket the balances with pd.cut, pivot them to one row per customer with observed=False so the columns never change, and add totals and an over-60 figure. Flag customers above their credit limit, tie the grand total back to the receivables ledger, and write the aging with a traceable detail sheet.

Frequently asked questions

How do I calculate days past due for an aging report? Subtract the due date from the as-at date — usually the period end — not from today: (as_at - due_date).dt.days. Zero or negative means not yet due. Using today makes the report change every time it is rerun.

How do I put invoices into aging buckets with pandas? Use pd.cut on days past due with edges such as -inf, 0, 30, 60, 90, inf and labels Current, 1–30, 31–60, 61–90 and 90+. The right edges are inclusive, so 30 days falls in 1–30 and 31 in 31–60.

Where do credit notes and unapplied payments go in an aging report? They reduce the customer's balance. Most teams age them by their own date like an invoice, so they sit in a bucket as a negative amount; others show them in a separate Unapplied column. Either way the customer total must equal the ledger balance.

Should an aging report include invoices dated after the period end? No. An aging as at 30 September shows what was owed on that date, so documents dated after it are excluded, even if the export was run later and contains them.

Related

• Up one level: Building Finance and Operations Reports — the pipeline these reports share.
• Build a Bank Reconciliation Report — matching the cash side.
• Calculate Age and Tenure from Dates with pandas — date differences in depth.
• Export pandas Pivot Table to Excel Formatted — styling pivot output.