Excel guide · 5 min read
How to make an accounts receivable aging report in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Customer | Due date | Amount | Open | Bucket | Receipt for | Received | As of | Days past due from | ||
| 2 | INV-2041 | Northwind Dental | Jul 20, 2026 | 1840 | 840 | Over 90 | INV-2041 | 1000 | Oct 31, 2026 | 0 | ||
| 3 | INV-2047 | Bluebird Bakery | Aug 15, 2026 | 620 | 620 | 61-90 days | INV-2052 | 2300 | 1 | |||
| 4 | INV-2052 | Northwind Dental | Sep 5, 2026 | 2300 | 0 | Paid | INV-2058 | 200 | 31 | |||
| 5 | INV-2055 | Carver & Sons | Sep 22, 2026 | 975.5 | 975.5 | 31-60 days | INV-2061 | 1250 | 61 | |||
| 6 | INV-2058 | Bluebird Bakery | Oct 10, 2026 | 410 | 210 | 1-30 days | 91 | |||||
| 7 | INV-2061 | Ridgeway Gym | Oct 28, 2026 | 1250 | 0 | Paid | ||||||
| 8 | INV-2063 | Carver & Sons | Nov 14, 2026 | 3100 | 3100 | Current | ||||||
| 9 | INV-2066 | Ridgeway Gym | Nov 30, 2026 | 780 | 780 | Current |
Download this sheet as an Excel practice file (.xlsx)
What you need
An aging report answers "who owes us what, and how late is it?" You need two lists: open invoices (A:D, with due dates) and receipts applied to them (G:H). Most accounting systems export both. Columns E and F are helper columns with live formulas, and the steps below explain them.
Put the as-of date in its own cell (J2) instead of using TODAY(). A month-end aging run on 3 November should still say 31 October, and anyone who opens it next week should get the same numbers you reported.
Age from the due date, as here, to see how late customers are. Some businesses age from the invoice date instead; the formulas are the same with the invoice date in place of C.
Step 1: Open balance per invoice
Subtract everything received against the invoice. SUMIFS adds every receipt for that invoice number, so two part-payments are handled, and an invoice with no receipt subtracts 0. This is column E, copied down.
Result: 840
840: Northwind Dental paid 1,000.00 of the 1,840.00 on INV-2041.
Step 2: Days past due
The as-of date minus the due date. Invoices not due yet give a negative number, so MAX(0, ...) turns those into 0, which the bucket table reads as Current.
Result: 77
77: INV-2047 was due 15 August, 77 days before 31 October.
Step 3: Buckets from a lookup table, not nested IFs
L2:M6 lists the lower bound of each bucket: 0 Current, 1 for 1-30 days, 31, 61 and 91 for Over 90. VLOOKUP with TRUE finds the largest bound that's less than or equal to the days, so 39 lands on 31-60 days. The table must be sorted smallest to largest.
Why a table? Nested IFs bury the bucket edges inside every formula. With a table, changing to 0-15-30-45 buckets or adding a 120+ line is a change to a few cells. Column F wraps it in IF(E2=0,"Paid",...) so settled invoices don't land in a bucket.
A small Excel gotcha: type bucket labels like "1-30 days", not just 1-30. Excel reads 1-30 typed into a cell as a date (30 January) and the label turns into a number.
Result: Over 90
Over 90: INV-2041 was due 20 July, 103 days past due.
The XLOOKUP version (Excel 365 / 2021)
Match mode -1 means "exact or next smaller", the same idea. If the days are below the first bound (not due yet), XLOOKUP finds nothing and returns its if-not-found value, so you can skip the MAX.
Result: 31-60 days
31-60 days: INV-2055 is 39 days past due.
Step 4: Totals by bucket and by customer
With the open amount and bucket in columns, SUMIFS does the summary. Give it the whole list of bucket names and it returns one total per bucket.
Result: spills 5 rows × 1 column
| 3880 |
| 210 |
| 975.5 |
| 620 |
| 840 |
3,880.00 Current, 210.00 in 1-30 days, 975.50 in 31-60 days, 620.00 in 61-90 days and 840.00 Over 90, spilling down next to the bucket names.
Customer-by-bucket grid in one formula
UNIQUE lists the customers down the side and TRANSPOSE turns the bucket names into columns, so SUMIFS fills the whole grid. Put the bucket names across row 1 of your summary as headings. Excel 365 or 2021; in older versions, write one SUMIFS per cell with the customer and bucket from the headings, or use a PivotTable with Customer in Rows and Bucket in Columns.
Result: spills 4 rows × 5 columns
| 0 | 0 | 0 | 0 | 840 |
| 0 | 210 | 0 | 620 | 0 |
| 3100 | 0 | 975.5 | 0 | 0 |
| 780 | 0 | 0 | 0 | 0 |
A 4 x 5 grid: Northwind Dental 840 Over 90, Bluebird Bakery 210 in 1-30 and 620 in 61-90, Carver & Sons 3,100 Current and 975.50 in 31-60, Ridgeway Gym 780 Current.
Share of receivables over 60 days
A one-number health check worth tracking month to month.
Result: 0.22373764462 shows as 22% with Percent format
0.2237: about 22% of what's owed is more than 60 days late.
Step 5: Tie it back to the ledger
An aging report you can't tie out isn't finished. Total open across all buckets should equal total invoiced minus total received, and both should match the receivables balance in your accounts on the as-of date.
Here: invoices 11,275.50 minus receipts 4,750.00 is 6,525.50, the same as the bucket totals. If the check isn't 0, look for a receipt keyed to an invoice number that isn't in the list (it reduces the ledger but no invoice), a credit note, or an invoice that's missing from the export. A negative open balance means a customer overpaid or a receipt was applied twice: show it, don't hide it.
To turn aging into a single ratio, see days sales outstanding. To set due dates from payment terms, see calculating due dates. Prefer a ready layout? The free accounts receivable aging template is set up this way.
Result: 0
0: the aging ties back to invoices minus receipts.
Common mistakes
Using TODAY() for the as-of date
The report changes every day you open it. Put the period-end date in a cell and point every formula at it.
Aging the invoice amount instead of the open balance
Subtract receipts first. A part-paid invoice should only show what's still owed.
Bucket edges hard-coded in nested IFs
Keep the edges in a sorted lookup table so one change updates every row.
No tie-out
Check bucket totals against invoices minus receipts and the ledger balance. Investigate any difference.
FAQ
How do I create an aging report in Excel?
Calculate each invoice's open balance and days past due from an as-of date, assign a bucket with a lookup table, then total by customer and bucket with SUMIFS or a PivotTable.
What is the formula for aging buckets in Excel?
=VLOOKUP(MAX(0,AsOf-DueDate),BucketTable,2,TRUE), where the table lists each bucket's lowest day count in ascending order. XLOOKUP with match mode -1 does the same.
Should I age by invoice date or due date?
Due date shows how late customers are against their terms, which is what most collections work needs. Invoice date shows how old the sale is. Pick one, label the report, and use it consistently.
Can a PivotTable make an aging report?
Yes. Add the open balance and bucket helper columns first, then put Customer in Rows, Bucket in Columns and Open in Values. The formulas above are easier to keep identical month to month.
Functions used
SUMIFSAdds with several conditionsMAXLargest numberVLOOKUPVertical lookupXLOOKUPModern lookup in any directionUNIQUERemoves duplicates, spillsTRANSPOSEFlips rows and columns, spillsIFPicks a result from a testSUMAdds numbers
Related guides
- How to build a 13-week cash flow forecast in Excel
- How to make an accounts payable aging report in Excel
- How to calculate days sales outstanding (DSO) in Excel
- Excel test for accounting roles: what it covers and how to pass (or set) one
- How to calculate due dates and days overdue in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate the debt-to-equity ratio in Excel
Updated