How to Build an Accounts Receivable Aging Report in Excel

What you will build

We will age the open receivables of Brightline Trading Co. at the end of September 2026. Ten unpaid invoices from six customers go in a simple list. Formulas work out how many days each one is past due, put it in a bucket, and summarize the buckets underneath.

All formulas here work in every current version of Excel, including Excel 2010 and later, Microsoft 365 and Excel for the web. If you track invoices as they are issued and paid, the invoice payment tracker is a good source list for this report.

How an aging report works

Each open invoice is measured against an as-of date, usually the last day of the month. The number of days past the due date decides its bucket:

Most collection-focused reports age from the due date, which is what we do here. Some businesses age from the invoice date instead. Both are valid; just say which one you used in the report title so nobody misreads it.

Step 1: List open invoices and an as-of date

1. In A2 type As of date and in B2 enter 9/30/2026. Format it as a date.
2. In A3:G3 enter the headers Customer, Invoice #, Invoice Date, Due Date, Open Balance, Days Past Due and Aging Bucket.
3. Enter the ten open invoices in rows 4 to 13. All are on Net 30 terms, so each due date is the invoice date plus 30 days. If every customer has the same terms you can use =C4+30 in D4 instead of typing.
4. In A14 type Total open receivables and in E14 enter =SUM(E4:E13). It should show $39,500.00.

Step 2: Calculate days past due

Excel stores dates as numbers, so subtracting one date from another gives the number of days between them. Click F4 and enter:

=MAX(0,$B$2-D4)

$B$2-D4 is the as-of date minus the due date. For an invoice that is not due yet, that would be negative, so MAX(0,...) turns it into 0. The dollar signs lock B2 so every row reads the same as-of date when you copy the formula down to F13. Press F4 while the cursor is on B2 to add them.

Want a live report that ages itself every day? Replace the date in B2 with =TODAY(). For month-end reporting, keep a typed date so the report still shows the same numbers when you reopen it next month.

Step 3: Assign each invoice to an aging bucket

Now turn the number of days into a bucket label. In G4 enter this nested IF (the IF formula guide explains how nesting works) and copy it down to G13:

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

Excel tests the conditions in order and stops at the first one that is true. 0 days is Current. Otherwise, up to 30 is 1-30 days, up to 60 is 31-60 days, up to 90 is 61-90 days, and anything larger is Over 90 days. Because each test only runs when the previous one failed, you never need to write “greater than 30 and less than or equal to 60”.

Prefer a lookup table to nested IFs? With XLOOKUP in approximate-match mode, the bucket formula becomes =XLOOKUP(F4,{0;1;31;61;91},{"Current";"1-30 days";"31-60 days";"61-90 days";"Over 90 days"},,-1). It finds the largest threshold that is less than or equal to the days, the same idea as the discount tiers in how to use XLOOKUP in Excel.

Step 4: Summarize the buckets with SUMIFS and COUNTIFS

1. In A16:F16 enter the headers Aging Bucket, Balance, % of Total, Invoices, Loss Rate and Allowance.
2. In A17:A21 type the five bucket labels exactly as the formula returns them: Current, 1-30 days, 31-60 days, 61-90 days, Over 90 days.
3. In B17 enter the balance formula and copy it down to B21:
   Copy

   =SUMIFS($E$4:$E$13,$G$4:$G$13,$A17)

4. In C17 enter =IF($B$22=0,0,B17/$B$22) for the share of the total, and in D17 enter =COUNTIFS($G$4:$G$13,$A17) for the number of invoices. Copy both down to row 21.
5. In row 22 add totals: =SUM(B17:B21) in B22, and the same pattern in C22 and D22.

Want the aging by customer as well? Add a customer criterion: =SUMIFS($E$4:$E$13,$A$4:$A$13,"Summit Logistics",$G$4:$G$13,"Over 90 days") returns 6,175.00, Summit’s one invoice over 90 days. The same multi-criteria pattern appears in summing monthly values with SUMIFS and counting overdue items with COUNTIFS.

Step 5: Estimate the allowance for doubtful accounts

The older an invoice, the less likely it is to be paid. Accountants use the aging buckets to estimate how much of the receivables balance will never be collected, and record that as an allowance for doubtful accounts. This is optional for a collections list, but it is what turns the aging report into a month-end accounting schedule.

1. In E17:E21 enter a loss rate for each bucket. The sample uses 1%, 3%, 10%, 25% and 50%.
2. In F17 enter =ROUND(B17*E17,2) and copy it down to F21.
3. In F22 enter =SUM(F17:F21). The required allowance is $6,501.30.

Read the result like this: of $39,500.00 owed by customers, about $6,501.30 is not expected to be collected, so the net amount you expect to collect is $32,998.70. On the balance sheet, receivables are shown at that net amount.

The $6,501.30 is the balance the allowance account should have, not automatically this month’s expense. If the Allowance for Doubtful Accounts already has a credit balance of, say, $4,200.00, the adjusting journal entry is only the difference:

Step 6: Verify the report

Two checks prove the report is complete and the formulas are behaving:

1. The buckets add back to the total. In G22 enter =IF(ROUND(B22-E14,2)=0,"Ties to total","Check buckets"). It should say Ties to total: $39,500.00 in the buckets and $39,500.00 in the invoice list.
2. One row checked by hand. INV-1027 for Lumen Architects was due on Sep 4, 2026. From Sep 4 to Sep 30 is 26 days, so it belongs in 1-30 days, which matches F9 and G9.
3. The total ties to the ledger. E14 should equal your Accounts Receivable balance on Sep 30, 2026.

Common mistakes and quick fixes

Frequently asked questions

Final checklist

• The as-of date is in B2 and every days formula points to $B$2.
• Open balances reflect partial payments and credit notes.
• Days past due use MAX(0,…) so not-yet-due invoices show 0.
• Boundary days (30, 60, 90) land in the right bucket.
• Summary labels match the formula text exactly and include the word days.
• The bucket total ties to the invoice total, and the invoice total ties to the ledger.
• Loss rates are based on your own collection history.

Aged receivables are one of the first things to review at month-end. Once they are clean, reconcile the bank account in Excel with XLOOKUP (customer payments that reached the bank but not the books show up there), then close the month with a monthly profit and loss statement, where bad debt expense appears as an operating expense.