AR/AP Ageing Report in Excel (0-30/31-60/61-90/90+)

⬇  Download the free receivables & payables tracker (Excel) — no sign-up

Last updated: 27 June 2026 · Buckets, formulas and a free template included.

An ageing report in Excel groups every unpaid invoice by how long it has been outstanding — usually into 0-30, 31-60, 61-90 and 90+ day buckets — so you instantly see how much money is fresh, slow or seriously stuck. Built with one "days overdue" column and a few SUMIFS formulas, it turns a flat list of invoices into a debtors (or creditors) ageing summary you can act on every week.

Key takeaways

• Ageing buckets sort balances by days overdue: 0-30, 31-60, 61-90 and 90+ are the standard four.
• One helper column — Days overdue = MAX(0, TODAY() − Due Date) — drives the whole report.
• SUMIFS totals the balance that falls in each bucket per party; a pivot table does the same with no formulas.
• The same method works for receivables (debtors) and payables (creditors) — only the data source changes.
• 90+ is your danger zone: old receivables are your highest write-off risk; old payables to Micro/Small suppliers are your Section 43B(h) risk.
• Feeds straight from your receivables tracker or payables tracker.

Fact box. An ageing report (also called an "ageing schedule" or "debtors ageing") is a snapshot, as on a chosen date, of how overdue each unpaid balance is — bucketed by age. It is the single most-used report for chasing collections and spotting bad-debt risk early.

What is an ageing report?

An ageing report lists each party's outstanding balance and splits it by how many days it has been overdue. Instead of one number — "₹14 lakh outstanding" — you see that ₹6 lakh is under 30 days (normal), ₹3 lakh is 31-60, ₹2 lakh is 61-90, and ₹3 lakh is 90+ days old (a real problem).

The same structure works two ways:

• AR ageing (debtors): how long customers have owed you. Drives collections.
• AP ageing (creditors): how long you have owed suppliers. Drives payment planning and MSME compliance.

What are the standard ageing buckets?

The common four buckets count days past the due date, not past the invoice date:

Some businesses add a "Not yet due" column (balance with days overdue = 0) so the total reconciles to your full outstanding figure. Pick buckets that match your credit terms — a business on 45-day terms might use 0-45 / 46-60 / 61-90 / 90+.

Fact box. Buckets are measured from the due date, not the invoice date. An invoice on 30-day terms raised 50 days ago is 20 days overdue (the 0-30 bucket), not 50.

How do I build an ageing report in Excel with SUMIFS?

Start from your invoice list (one row per unpaid invoice) with at least Party, Due Date, Balance. Then:

1. Add a Days Overdue column: =MAX(0, TODAY() − [Due Date]). This stays current every time the file opens.
2. Add a Bucket column so each invoice is labelled: =IF(Days=0,"Not due",IF(Days<=30,"0-30",IF(Days<=60,"31-60",IF(Days<=90,"61-90","90+"))))
3. Build the summary table with parties down the left and the bucket headers across the top.
4. Fill each cell with SUMIFS — total balance for that party in that age band. For the 31-60 cell: =SUMIFS(Balance, Party, $A2, Days, ">30", Days, "<=60")
5. Add a totals row and column with SUM so the grand total matches your full outstanding.

The four bucket formulas, by day range:

Important — include "Not due" to reconcile to total outstanding. These four SUMIFS buckets cover only overdue balances (Days Overdue > 0). Invoices not yet past their due date have Days Overdue = 0 and fall into none of the four bands. Add a separate "Not due" column using the criterion "=0" — or the Bucket label "Not due" from the helper column — so the row totals add up to your full outstanding balance. The FAQ below explains this. Without it, your ageing summary will under-report total exposure by the not-yet-due amount.

Can I use a pivot table instead?

Yes — a pivot table is faster if you don't want formulas. Add the Bucket helper column (step 2 above), then insert a pivot table with Party in Rows, Bucket in Columns, and Balance in Values (set to Sum). Excel builds the entire ageing matrix automatically, and you refresh it with one click when the data changes.

The trade-off: a pivot needs a manual Refresh (the TODAY()-based buckets only recompute when you refresh), whereas SUMIFS recalculates live.

How to build it (step by step)

1. Open your receivables or payables sheet and confirm it has Party, Due Date and Balance columns.
2. Add Days Overdue: =MAX(0, TODAY() − DueDate).
3. Add the Bucket column with the nested IF above.
4. Create the summary block with parties listed once each (use Remove Duplicates on a copy of the Party column).
5. Enter the SUMIFS row for the first party and copy down.
6. Add totals and apply conditional formatting to highlight large 90+ figures in red.
7. Format as a Table (Ctrl+T) so new invoices flow into the report.

A free AR/AP ageing template is available to download, no sign-up — drop in your invoices and the buckets fill themselves.

How Ankeshan helps: Ankeshan builds debtor and creditor ageing straight from your invoices inside Excel — buckets, party totals and 90+ alerts refresh as you bill and pay, with no formulas or pivots to maintain. It's launching soon; join the waitlist.

Frequently asked questions

What is the difference between AR and AP ageing? AR (accounts receivable) ageing shows how long customers have owed you; AP (accounts payable) ageing shows how long you have owed suppliers. The buckets and formulas are identical — only the data source changes.

How do I calculate the age of an invoice in Excel? Use =MAX(0, TODAY() − DueDate) for days overdue past the due date. TODAY() keeps it current, and MAX(0, …) keeps not-yet-due invoices at zero instead of showing negatives.

Should ageing be measured from invoice date or due date? From the due date for overdue ageing — that tells you what is actually late. Some businesses also keep an invoice-date ageing for a pure "how old is the paper" view.

Why does my ageing total not match my outstanding? Usually because "Not yet due" balances are excluded. Add a "Not due" bucket (days overdue = 0) so the buckets sum to your full outstanding figure.

Is the ageing template free? Yes — free to download, no sign-up.

Sources

• Microsoft Excel function reference — SUMIFS, TODAY, IF, PivotTables: support.microsoft.com.
• General accounting practice for debtors/creditors ageing.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: Accounts Receivable Tracker in Excel » · Accounts Payable Tracker in Excel » · Section 43B(h) 45-Day MSME Rule » · Cashflow pillar »