Finance & Accounting

Accounts Receivable Aging

Every unpaid invoice sorted into an age bucket, so the conversation is about the four that are late

✓ No account, no email · ✓ Excel 2016+ and Google Sheets · ✓ Free for commercial use

A receivables ledger tells you how much you are owed. An aging report tells you which part of it you are about to have a problem with, and those are different numbers. This template derives the bucket from the due date rather than asking anyone to assign one, so the summary cannot drift from the ledger. A fully paid invoice labels itself Paid and drops out on its own; there is nothing to archive.

What the worksheet looks like

The actual columns, with sample rows

Invoices sheet: outstanding, days overdue and bucket are all derived.
A	B	C	D	E	F	G	H	I
1	Customer	Invoice	Due	Amount	Paid	Outstanding	Days	Bucket
2	Offshore Freights	INV-0001	2025-12-03	2,400.00	0.00	2,400.00	101	90+
3	Rencool	INV-0014	2026-02-20	1,150.00	1,150.00	0.00	22	Paid
4	Stem Archive	INV-0021	2026-03-08	780.00	300.00	480.00	6	1-30

The formulas that do the work

Why each one is written the way it is — shown in Excel table syntax; the file you download uses plain-range equivalents so it works in Google Sheets too

=IF(A5="","",MAX(0,$H$2-D5)) Days overdue against the ageing date in H2, which defaults to today. The MAX floors it at zero so an invoice not yet due is not reported as negative days.
=IF(G5<=0,"Paid",IF(H5=0,"Current",IF(H5<=30,"1-30",IF(H5<=60,"31-60",IF(H5<=90,"61-90","90+"))))) The bucket, derived rather than typed. Nesting the paid test first means a settled invoice leaves the buckets without being deleted.
=SUMIFS(Invoices!$G$5:$G$204,Invoices!$I$5:$I$204,$A5) Bucket total, matched against the derived label — so the summary and the ledger cannot disagree.
=SUMIFS(Invoices!$G$5:$G$204,Invoices!$A$5:$A$204,$A5,Invoices!$H$5:$H$204,">60") What one customer owes beyond 60 days. The comparison is passed as text, which is how SUMIFS takes an operator.

86,000 owed, and the number that matters is 12,400

A worked example with real numbers

A ledger shows 86,000 outstanding across 34 invoices, which sounds alarming and is not actionable. The aging summary splits it: 61,300 current, 12,300 in the 1-30 bucket, and 12,400 past 60 days across four invoices from two customers. The first two buckets need nothing. The last one is a morning of phone calls, and it is the only part at real risk.

What is in the workbook

Every tab, and what it holds

Every tab in the workbook and what it is for.
Tab	Contents
README	What to fill in and what is calculated, with the colour key.
Invoices	The ledger: 200 rows, with outstanding, days overdue and bucket derived per row.
Aging	Totals and invoice counts per bucket, each bucket's share, and the over-60 figure.
By customer	Outstanding and over-60 per customer. Type each customer once.

Features and related templates

What is included, and what to look at next

What it does

Buckets derived from the due date, never typed
Paid invoices drop out on their own
Ageing date you can set to any reporting date
Totals per bucket and per customer
Excel 2016+ and Google Sheets

Related templates

Need it adapted?

Built around your own data and column names
Connected to your source system
Delivered within 24 hours

Get your Excel job done

Questions about this template

The ones that come up most

Can I age against a date other than today?

Yes. H2 on the Invoices sheet holds the ageing date and defaults to =TODAY(). Type a fixed date over it to reproduce a month-end report exactly as it stood.

What about partial payments?

Put what has been received in the Paid column. Outstanding is amount minus paid, and the invoice is bucketed on the remaining balance, not the original.

Why are my buckets empty?

The Aging tab matches the labels exactly as the Bucket formula writes them — Current, 1-30, 31-60, 61-90, 90+. If you rename a bucket on the summary, change it in the formula too.