Cellwise Start free

Excel guide · 5 min read

How to make an accounts receivable aging report in Excel

Quick answerFor each open invoice, work out the open balance (amount minus receipts applied, with SUMIFS) and days past due (an as-of date in a cell minus the due date). Look the days up in a small bucket table with VLOOKUP or XLOOKUP to get Current, 1-30, 31-60, 61-90 or Over 90. Then total the open balances by customer and bucket with SUMIFS, and check the total ties back to invoices minus receipts.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1InvoiceCustomerDue dateAmountOpenBucketReceipt forReceivedAs ofDays past due from
2INV-2041Northwind DentalJul 20, 20261840840Over 90INV-20411000Oct 31, 20260
3INV-2047Bluebird BakeryAug 15, 202662062061-90 daysINV-205223001
4INV-2052Northwind DentalSep 5, 202623000PaidINV-205820031
5INV-2055Carver & SonsSep 22, 2026975.5975.531-60 daysINV-2061125061
6INV-2058Bluebird BakeryOct 10, 20264102101-30 days91
7INV-2061Ridgeway GymOct 28, 202612500Paid
8INV-2063Carver & SonsNov 14, 202631003100Current
9INV-2066Ridgeway GymNov 30, 2026780780Current

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.

=D2-SUMIFS(H2:H6,G2:G6,A2)

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.

=MAX(0,J2-C3)

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.

=VLOOKUP(MAX(0,J2-C2),L2:M6,2,TRUE)

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.

=XLOOKUP(J2-C5,L2:L6,M2:M6,"Current",-1)

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.

=SUMIFS(E2:E9,F2:F9,M2:M6)

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.

=SUMIFS(E2:E9,B2:B9,UNIQUE(B2:B9),F2:F9,TRANSPOSE(M2:M6))

Result: spills 4 rows × 5 columns

0000840
021006200
31000975.500
7800000

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.

=(SUMIFS(E2:E9,F2:F9,"61-90 days")+SUMIFS(E2:E9,F2:F9,"Over 90"))/SUM(E2:E9)

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.

=SUM(E2:E9)-(SUM(D2:D9)-SUM(H2:H6))

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

Related guides

Updated