Build an Aged Creditors Report

The aged creditors report — accounts payable ageing — answers the questions a finance team asks about what it owes: how much, to whom, and how late. It lists every open item with each supplier, sorts the amounts into columns by how far past their due date they are, and totals by supplier and overall. It is used to plan payments, to spot suppliers who will soon be chasing, to find credit notes that have been sitting unused, and at month end to prove that the payables ledger agrees with the balance sheet. This page builds it from an open-items export with pandas: ages every invoice, credit note and unallocated payment by due date, keeps credits as negative amounts in the right columns, marks disputed items, summarises by supplier with the overdue share, reconciles the total to the payables control account and writes a formatted workbook with overdue amounts highlighted. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The input is the open payables listing at 30 September 2026 — every document not yet fully settled — with supplier, document type (invoice, credit note or payment on account), reference, document and due dates, amount and a status column where disputes are marked. The payables control account in the general ledger shows 52,100.70 at the same date.

Step 1 — Age every open item

import pandas as pd

REPORT_DATE = pd.Timestamp("2026-09-30")
items = pd.read_excel("ap_open_items.xlsx")
items["Days overdue"] = (REPORT_DATE - items["Due date"]).dt.days
BUCKETS = ["Current", "1-30", "31-60", "61-90", "90+"]
items["Bucket"] = pd.cut(items["Days overdue"], bins=[-10_000, 0, 30, 60, 90, 10_000], labels=BUCKETS)
items.loc[items["Type"].isin(["CRN", "PAY"]), "Bucket"] = "Current"
print(items[["Supplier", "Type", "Reference", "Due date", "Days overdue", "Bucket", "Amount"]].to_string(index=False))

Age is measured from the due date, because the question is how late a payment is; some teams age from the document date instead, which shows how old the debt is regardless of terms — pick one and say which in the report heading. pd.cut with explicit edges sorts the days into buckets, and the bins are right-closed, so an invoice due today (0 days) is still Current while one due yesterday is in 1–30. Credit notes and unallocated payments are amounts the company can set against what it owes, so they are kept as negatives and placed in Current: ageing them would make it look as if credits were overdue.

Step 2 — Summarise by supplier

by_supplier = items.pivot_table(index="Supplier", columns="Bucket", values="Amount", aggfunc="sum",
                                fill_value=0, observed=False)[BUCKETS]
by_supplier["Total"] = by_supplier.sum(axis=1)
by_supplier["Overdue"] = by_supplier[BUCKETS[1:]].sum(axis=1)
by_supplier["Overdue %"] = (by_supplier["Overdue"] / by_supplier["Total"]).where(by_supplier["Total"] > 0)
disputed = items[items["Status"].eq("disputed")].groupby("Supplier")["Amount"].sum()
by_supplier["In dispute"] = disputed.reindex(by_supplier.index, fill_value=0)
by_supplier = by_supplier.sort_values("Overdue", ascending=False)
totals = by_supplier[BUCKETS + ["Total", "Overdue", "In dispute"]].sum()
print(by_supplier.round(2).to_string())
print(totals.round(2).to_string())

A pivot with the buckets as columns gives the classic layout, one row per supplier; observed=False keeps every bucket as a column even when no supplier has an item in it, so the report always has the same shape. Sorting by overdue amount puts the suppliers most likely to be on the phone at the top. The In dispute column separates amounts that are overdue on purpose — the Brightline invoice under query — from those that are simply unpaid; a payment run should pay the second and resolve the first. Corex's unallocated payment sits as a negative in Current while their invoice ages in 61–90: the payment has been made but not matched to the invoice, which is a housekeeping job for the payables team, and the report makes it visible — it also pushes Corex's overdue share above 100%, a figure that only makes sense once the payment is allocated. Evergreen tops the list with 12,400 overdue by sixteen days.

Step 3 — Reconcile to the control account

The total of the ageing must equal the payables balance in the general ledger. Any difference means an item is missing from one or the other:

CONTROL_ACCOUNT = 52_100.70
difference = round(totals["Total"] - CONTROL_ACCOUNT, 2)
print(f"ageing total {totals['Total']:,.2f} | control account {CONTROL_ACCOUNT:,.2f} | difference {difference:,.2f}")

The ageing totals 52,200.70; the control account shows 52,100.70. The difference of exactly 100.00 is the kind a month-end review exists to find. The usual causes are a document posted directly to the control account without a supplier — a manual journal correcting a supplier balance, say — a cut-off difference between when the listing and the ledger were run, or a supplier item posted to the wrong account. A round figure points to a manual journal, so the next step is to list journals to the control account in the period. The reconciliation line goes on the report whether or not it is zero, because a reviewer will want to see it, and a non-zero difference is a finding to resolve before the figures are signed off. The ledger side of this is covered in Build a Trial Balance from a General Ledger.

Step 4 — Write the report

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

path = "aged_creditors_2026-09-30.xlsx"
with pd.ExcelWriter(path, engine="openpyxl", date_format="dd mmm yyyy", datetime_format="dd mmm yyyy") as xw:
    by_supplier.to_excel(xw, sheet_name="Summary", startrow=2)
    items.sort_values(["Supplier", "Due date"]).to_excel(xw, sheet_name="Open items", index=False)
wb = load_workbook(path)
ws = wb["Summary"]
ws["A1"] = f"Aged creditors at {REPORT_DATE:%d %B %Y} — aged by due date"
ws["A1"].font = Font(bold=True, size=13)
last = ws.max_row
ws.cell(last + 1, 1, "Total").font = Font(bold=True)
for col in range(2, 2 + len(BUCKETS) + 2):
    letter = ws.cell(3, col).column_letter
    ws.cell(last + 1, col, f"=SUM({letter}4:{letter}{last})").font = Font(bold=True)
ws.cell(last + 3, 1, "Control account"), ws.cell(last + 3, 2 + len(BUCKETS), CONTROL_ACCOUNT)
ws.cell(last + 4, 1, "Difference"), ws.cell(last + 4, 2 + len(BUCKETS), f"={ws.cell(last + 1, 2 + len(BUCKETS)).coordinate}-{ws.cell(last + 3, 2 + len(BUCKETS)).coordinate}")
late = PatternFill("solid", fgColor="FCE4D6")
for row in ws.iter_rows(min_row=4, max_row=last + 4, min_col=2, max_col=ws.max_column):
    for cell in row:
        cell.number_format = "0.0%" if ws.cell(3, cell.column).value == "Overdue %" else "#,##0.00;[Red]-#,##0.00;-"
        if ws.cell(3, cell.column).value in ("61-90", "90+") and isinstance(cell.value, (int, float)) and cell.value > 0:
            cell.fill = late
ws.column_dimensions["A"].width = 24
wb.save(path)
print(wb.sheetnames)

The summary sheet has a title stating the date and the ageing basis, the supplier rows, a total row with SUM formulas — so anyone who adjusts a figure sees the total follow — and the control account and difference beneath. Amounts more than 60 days overdue are shaded, negatives show in red and zeros as dashes. The Open items sheet lists every document behind the figures, sorted by supplier and due date, so each number can be traced. The receivables equivalent, with its dunning follow-up, is in Build an Accounts Receivable Aging Report.

Reading the report

A good aged creditors report is read top-down. Start at the reconciliation: if the difference is not zero, nothing below can be relied on. Then look at the old buckets. Amounts over 90 days are rarely just slow payment — here the Fjord IT invoice from May is probably a missing approval, a goods-received problem or an invoice nobody can match, and each such line deserves a named owner and a date. Amounts in dispute should have a matching query log. Then check the negatives: credit notes and payments on account that have sat unallocated for weeks are money the company is entitled to use, and they often signal duplicate payments or invoices that were paid without being matched. Finally, the current column is the cash requirement for the coming weeks, which is what the next payment run will draw on.

Common pitfalls

• Ageing credits. Keep credit notes and payments on account as negatives in Current.
• Mixing ageing bases. Age by due date or document date, and say which.
• Missing buckets. Use a categorical with observed=False so every column always appears.
• No reconciliation. The ageing total must match the control account.
• Disputes hidden among overdue items. Show them in their own column.

Conclusion

An aged creditors report ages each open payable by its due date into current, 1–30, 31–60, 61–90 and 90+ day buckets with pd.cut, keeps credit notes and unallocated payments as negatives in Current, pivots by supplier with total, overdue and in-dispute columns, and reconciles its total to the payables control account. Written to Excel with formula totals, highlighted old balances and the open items behind them, it supports payment planning, supplier conversations and month-end close.

Frequently asked questions

How do I age accounts payable in pandas? Subtract each item's due date from the report date to get days overdue, then use pd.cut with bucket edges such as 0, 30, 60 and 90 days to assign an ageing bucket.

Where do credit notes go in an aged creditors report? Keep them as negative amounts, usually in the current column, so they reduce what is owed without appearing overdue.

Should payables be aged by invoice date or due date? Due date shows how late payments are and is the usual choice for payment planning; invoice date shows the age of the debt. State which one the report uses.

How do I check the aged creditors report is complete? Reconcile its total to the accounts payable control account in the general ledger; any difference means items are missing from one side.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Supplier Payment Run Proposal — paying what the report shows is due.
• Detect Possible Duplicate Invoices — checking the open items first.
• Build an Accounts Receivable Aging Report — the receivables side.