How to Build an Accounts Receivable Aging Report in Excel

An accounts receivable aging report in Excel shows how much each customer owes and how late the money is, grouped into buckets such as Current, 1-30 days and Over 90 days. You build it with three formulas: a date subtraction for days past due, IFS for the bucket, and SUMIFS for the totals. This guide walks through each step on a small invoice list, so you can build one for work or for an accounting Excel test.

What an aging report shows#

An aging report answers two questions: who owes money, and how overdue is it? Finance teams use it to chase late payers, and managers use it to judge how much may never be paid.

The usual layout has customers down the side and age buckets across the top:

Most reports age invoices from the due date, which shows how overdue they are. Some age from the invoice date, which shows how old they are. Both are valid, so label which one you use.

Step 1: set up the invoice list and report date#

Start with one row per unpaid invoice, and put the report date in its own cell. Here the report date, 31 October 2026, is in H1, entered as =DATE(2026,10,31).

You could use =TODAY() in H1 instead. The TODAY function always returns the current date, so the report updates by itself. The downside is that the numbers change every day, and a month-end report should stay fixed at the cut-off date. A typed date is safer for anything you send to someone.

Step 2: calculate days past due#

Excel stores dates as whole numbers, so subtracting one date from another gives the number of days between them. In E2, type:

=$H$1-C2

Fill it down to E8. The dollar signs keep every row pointing at the report date. Format column E as a number if Excel shows a date.

A negative number means the invoice isn't due yet. INV-107 is due on the report date, so it shows 0 and still counts as current. The days between dates lesson covers date subtraction in more detail, and the guide to days until a deadline shows the same maths counting forwards.

Step 3: assign each invoice to a bucket with IFS#

The IFS function checks tests in order and returns the label for the first one that is TRUE. In F2, type:

=IFS(E2<=0,"Current",E2<=30,"1-30 days",E2<=60,"31-60 days",E2<=90,"61-90 days",TRUE,"Over 90 days")

Fill it down. INV-101 (16 days) becomes 1-30 days, INV-103 (41 days) 31-60 days, and INV-105 (108 days) Over 90 days.

The order of the tests matters. Start with the smallest limit, so 41 days fails E2<=30 and stops at E2<=60. The final TRUE is a catch-all for anything left over.

IFS needs Excel 2019 or Microsoft 365. In older versions, nest IF functions:

=IF(E2<=0,"Current",IF(E2<=30,"1-30 days",IF(E2<=60,"31-60 days",IF(E2<=90,"61-90 days","Over 90 days"))))

The nested IF and IFS lesson lets you practise both, and the guide to nested IF formulas explains the logic step by step.

Step 4: total each bucket with SUMIFS#

The SUMIFS function adds up the amounts that match a condition. Type the bucket labels in H4:H8 and, in I4:

=SUMIFS($D$2:$D$8,$F$2:$F$8,H4)

Fill it down to get the summary:

The share column divides each amount by the total, such as =I4/$I$9. Over a quarter of this balance is more than 90 days late, which is the figure a credit manager would look at first.

You can also skip the bucket column and total straight from the days. For 31 to 60 days:

=SUMIFS(D2:D8,E2:E8,">=31",E2:E8,"<=60")

This also returns 2,100 (1,500 + 600). Watch out when you type the label 1-30 into a cell: Excel may turn it into a date. Adding the word "days" keeps it as text.

Step 5: build the customer-by-bucket grid#

Add a second condition to SUMIFS to split each bucket by customer. Put customer names down H12:H14 and bucket labels across I11:M11. In I12:

=SUMIFS($D$2:$D$8,$B$2:$B$8,$H12,$F$2:$F$8,I$11)

The mixed references matter: $H12 keeps the customer column fixed as you fill right, and I$11 keeps the header row fixed as you fill down. Fill the formula across and down to get:

Birch has the largest overdue risk, even though Acme owes more in total. The COUNTIFS and SUMIFS lesson practises exactly this kind of two-condition total, and summing by category shows other ways to build summary tables.

How to check and present the report#

A report people trust has to add up. Before you send it:

1. Check the totals. The bucket totals and the customer totals must both equal =SUM(D2:D8), here 7,450. If not, an invoice has a blank or wrong date.
2. Look for odd days. A huge number, such as 46,326, usually means a due date is blank, because Excel treats a blank date as day 0.
3. Highlight late invoices. Use conditional formatting to colour rows where days late is over 60. The guide to highlighting overdue dates shows the rule.
4. Remove paid invoices. Aging is for open balances only. Filter out anything paid before the report date.

A PivotTable can build the same grid from the bucket column in a few clicks. The formula version is still worth knowing, because tests and many company templates use it.

Practise it#

An aging report is three skills joined together, so practise them one at a time. Start with the days between dates lesson, then sort values into bands in the nested IF and IFS lesson, and finish with bucket totals in the COUNTIFS and SUMIFS lesson. Each one runs in a live spreadsheet and checks your formula instantly. For a test-style run, try the finance Excel test.