Free Excel template

Free Excel accounts receivable aging report template

For owners, bookkeepers and credit controllers who invoice customers on terms and need to see who owes what and for how long. Enter each open invoice with its date, amount and payments received; the template works out the due date, how many days it is past due as of the date you choose, sorts the balance into Current, 1-30, 31-60, 61-90 and Over 90 day buckets, and totals each bucket.

cellwise-accounts-receivable-aging-template.xlsxExcel workbook · 6.1 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	F	G	H	I
1	Accounts receivable aging report
2	Aging as of	Oct 31, 2026
3	Default terms (days)	30
4	Customer	Invoice #	Invoice date	Due date	Amount	Paid	Balance	Days past due	Aging bucket
5	Alder Creek Landscaping	INV-2291	Jun 18, 2026	46221	3450	0	3450	105	Over 90 days
6	Bayview Property Mgmt	INV-2304	Jul 9, 2026	46242	1280	500	780	84	61-90 days
7	Cobalt Fitness	INV-2318	Jul 30, 2026	46263	2960	0	2960	63	61-90 days
8	Alder Creek Landscaping	INV-2325	Aug 12, 2026	46276	1875	0	1875	50	31-60 days
9	Delta Print Co	INV-2331	Aug 21, 2026	46285	640	640	0	0	Paid
10	Evergreen Cafe	INV-2340	Sep 3, 2026	46298	920	0	920	28	1-30 days
11	Bayview Property Mgmt	INV-2347	Sep 10, 2026	46305	2150	0	2150	21	1-30 days
12	Fairway Auto Body	INV-2352	Sep 15, 2026	46310	4300	2000	2300	16	1-30 days
13	Cobalt Fitness	INV-2360	Sep 24, 2026	46319	1190	0	1190	7	1-30 days
14	Granite Peak Builders	INV-2366	Sep 29, 2026	46324	6800	0	6800	2	1-30 days
15	Delta Print Co	INV-2371	Oct 2, 2026	46327	715	0	715	0	Current
16	Evergreen Cafe	INV-2378	Oct 8, 2026	46333	1040	0	1040	0	Current
17	Harbor Lights Marina	INV-2383	Oct 13, 2026	46338	3275	0	3275	0	Current
18	Granite Peak Builders	INV-2389	Oct 19, 2026	46344	5120	0	5120	0	Current
19	Fairway Auto Body	INV-2394	Oct 22, 2026	46347	860	0	860	0	Current
20	Ironwood Furniture	INV-2398	Oct 27, 2026	46352	2430	0	2430	0	Current

How the formulas work

Days past due as of a fixed date

H5=IF(A5="","",IF(G5<=0,0,MAX(0,$B$2-D5)))

The aging date in B2 minus the due date gives the days past due; MAX(0,...) turns invoices that are not yet due into 0. Paid invoices also return 0. Using an as-of date instead of TODAY() means the report matches your month-end close and gives the same answer every time you open it. Change B2 to re-age the whole list.

Put each invoice in an aging bucket

=IF(A5="","",IF(G5<=0,"Paid",IF(H5=0,"Current",IF(H5<=30,"1-30 days",IF(H5<=60,"31-60 days",IF(H5<=90,"61-90 days","Over 90 days"))))))

The nested IF tests the smallest bucket first, so each invoice lands in exactly one bucket. The labels include the word days on purpose: a bare "1-30" can be read by Excel as a date in a SUMIF criterion and match nothing.

Total the balance in each bucket

E28=SUMIF($I$5:$I$24,A28,$G$5:$G$24)

SUMIF adds the open balance of every invoice whose bucket matches A28. Column F divides each bucket by the total open balance. In the sample, more than half of the open balance is past due and Alder Creek has an invoice over 90 days, which is where collection calls should start.

One customer's past-due balance

E38=SUMIFS($G$5:$G$24,$A$5:$A$24,B36,$H$5:$H$24,">0")

SUMIFS adds balances that meet both conditions: the customer is the one typed in B36 and the invoice is past due. Type another customer name in B36 before you pick up the phone. The name must match the list exactly, so watch for trailing spaces.

Make it yours

Due dates use the default terms in B3 (invoice date plus 30). For a customer on different terms, overwrite that row's due date with =C5+45, or type the due date directly.
Color the buckets: select I5:I24, then Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, and add one rule each for "61-90" and "Over 90" with stronger fills.
Sort by Days past due (largest first) before a collections call: select A4:I24, Data > Sort, sort by Days past due, Largest to Smallest. The formulas move with their rows.
Paste a fresh export of open invoices from your accounting software each month into A5:F24 and the rest recalculates. Keep the export's columns in the same order or map them first.

FAQ

How do I calculate days past due in Excel?

Subtract the due date from the date you are aging to, for example =$B$2-D5, and wrap it in MAX(0,...) so invoices that are not yet due show 0. Dates are stored as day numbers, so the subtraction gives whole days.

What are the usual aging buckets?

Current (not yet due), 1-30, 31-60, 61-90 and over 90 days past due. Some businesses age from the invoice date instead of the due date; to do that, replace D5 with C5 in the days formula and say so on the report.

Why not use TODAY() for the aging date?

TODAY() changes every day, so last month's report would show different numbers when you reopen it. A typed as-of date such as the last day of the month keeps the report fixed and matches your books.

Functions used

Related guides

More free templates

Updated November 2, 2026