How Do You Create an AR Aging Report?

Every business that invoices on terms eventually asks the same question: of all the money customers owe us, how much is late, and how late is it? An accounts receivable aging report is the answer, and it takes about twenty minutes to build in Excel from a list of open invoices.

The reason most people search for this is not curiosity about accounting conventions. It is that someone has asked for a collections priority list, a cash flow forecast, or a bad debt estimate, and none of those can be produced from a flat list of unpaid invoices. The aging report is the structure that turns that list into a decision.

This guide covers what the report is, the one modelling choice that determines whether it is useful or misleading, the exact formulas to build it, how to summarise it by customer, and where the report stops being able to help you.

What is an AR aging report?

An AR aging report is a list of your unpaid customer invoices, grouped by how long each one has been outstanding. Each row is an invoice or a customer, and each column is a time bucket: current, 1 to 30 days past due, 31 to 60, 61 to 90, and over 90.

The output is a grid. Read down a column and you see how much of your receivables sit in one severity band. Read across a row and you see one customer's payment behaviour at a glance — a customer with everything in the current column is healthy, and one with a balance sitting in the 90-plus column is a problem you already have, whether or not you have noticed it yet.

That is the whole idea. The report does not calculate anything you could not work out by hand from the same invoice list. What it does is make the distribution visible, and the distribution is the thing that drives action.

A well-built aging report is also the fastest sanity check on your own billing. If a surprising share of the balance is sitting past due, the cause is as likely to be an invoicing process that sends documents to the wrong address as it is customers who will not pay — and the report is where that pattern first becomes visible.

Most sites never find out why their best pages do not rank. Get a free SEO audit and see yours.

Age from the due date, not the invoice date

This is the single decision that determines whether your report is worth anything, and it is the most common mistake in free templates.

An invoice dated 1 March on net-30 terms is not overdue on 15 March. It is not due yet. If you age invoices from the invoice date, that invoice lands in your "1 to 30 days" bucket and reads as late when the customer is behaving perfectly. Do that across a few hundred invoices and the report tells you that a third of your book is delinquent when none of it is.

Age from the due date instead. An invoice is current until its due date passes, and only then does it start accumulating days past due. The buckets then mean what their labels say: 31 to 60 means the customer is a month or two beyond the date they agreed to pay.

This matters more the more varied your terms are. If every customer is on net-30 the two methods differ by a constant thirty days and you could mentally adjust. If some customers are net-15 and some are net-60, aging from the invoice date mixes punctual net-60 customers and badly late net-15 customers into the same bucket, and the report becomes actively misleading rather than merely wrong.

Your reporting is only half the problem. Get a free SEO audit and see what is holding your traffic back.

What counts as an open invoice

Only unpaid and partially paid invoices belong in the report. A fully settled invoice has no balance and no business being there.

For partial payments, use the remaining balance rather than the original invoice amount, and age it from the original due date. A customer who paid half of a 90-day-old invoice last week has not reset the clock on the other half.

Credit memos and unapplied customer payments are the awkward cases. Both reduce what a customer owes without being tied to a specific invoice, so they usually appear as negative amounts in the current bucket. That is conventional, and it is also why an aging report sometimes shows a negative total for a customer who has overpaid.

How to build the report in Excel

Start with a table of open invoices. The minimum columns are customer name, invoice number, invoice date, due date and outstanding balance. If your accounting system exports a due date, use it; if it only exports terms, derive the due date first.

Step 1 — format the data as a table. Select the range and press Ctrl+T. This is not cosmetic: a table gives you structured references like [@[Due Date]], and it means formulas extend automatically when next month's export is longer than this month's.

If your export only gives you terms rather than a due date, derive it first. For a terms column holding a number of days, =[@[Invoice Date]]+[@Terms] is enough; for text terms like Net 30, strip the number with =[@[Invoice Date]]+VALUE(SUBSTITUTE([@Terms],"Net ","")). Do this in its own column rather than inside the aging formula, so that a bad terms value is obvious instead of silently producing a wrong bucket.

Step 2 — add a days past due column. With a table column named Due Date, the formula is:

=TODAY()-[@[Due Date]]

This returns a negative number for invoices not yet due, which is correct and useful. Format the column as a number, not a date, or Excel will helpfully show you a day in 1900.

Step 3 — assign each invoice to a bucket. In Excel 2019 and later, and in Microsoft 365, IFS reads far better than nested IF statements:

=IFS([@[Days Past Due]]<=0,"Current",
     [@[Days Past Due]]<=30,"1-30",
     [@[Days Past Due]]<=60,"31-60",
     [@[Days Past Due]]<=90,"61-90",
     TRUE,"90+")

The final TRUE is the catch-all, equivalent to ELSE. On older versions without IFS, the nested-IF equivalent does the same job:

=IF([@[Days Past Due]]<=0,"Current",IF([@[Days Past Due]]<=30,"1-30",IF([@[Days Past Due]]<=60,"31-60",IF([@[Days Past Due]]<=90,"61-90","90+"))))

Step 4 — get the bucket totals. A single SUMIFS per bucket gives you the summary row:

=SUMIFS(Invoices[Balance],Invoices[Bucket],"31-60")

Step 5 — build the customer matrix with a PivotTable. This is the step that turns a long invoice list into the grid people actually want. Insert a PivotTable from your table, put customer name in Rows, Bucket in Columns, and Balance in Values as a sum.

Order the bucket columns manually once — Excel sorts them alphabetically by default, which puts 1-30 before Current and reads badly. Drag them into the order current, 1-30, 31-60, 61-90, 90+ and the layout sticks on refresh. If PivotTables are unfamiliar territory, our guide to what a pivot table is in Excel covers the mechanics in detail.

Add a second PivotTable value showing a count of invoices alongside the sum. One customer with a single large invoice in the 90-plus bucket is a collections call; the same balance spread over fourteen small invoices is usually a process failure, and the totals alone cannot tell you which situation you are in.

Step 6 — add conditional formatting. Select the 61-90 and 90-plus columns and apply a colour scale or a simple cell-fill rule for balances above a threshold you care about. The point of the report is that the problems jump out, and colour does more for that than another column of numbers.

Make the as-of date explicit

Put the report date in a cell at the top and reference it instead of calling TODAY() inside every row:

=$B$1-[@[Due Date]]

TODAY() is volatile, so a workbook built with it shows different numbers every time it is opened. That is fine for a live working file and terrible for anything you send to someone else, reconcile against the general ledger, or file. An aging report is a snapshot of one moment, and the moment should be written down.

Stop guessing which pages drive revenue. Get a free SEO audit and find out.

What the report is actually used for

Collections prioritisation. This is the daily use. Sort by the 61-90 and 90-plus columns and you have a call list ranked by money at risk rather than by whoever shouted most recently.

Cash flow forecasting. Historical collection rates per bucket let you weight what you expect to collect. If invoices in the 31-60 bucket historically convert to cash at ninety per cent and the 90-plus bucket at forty, the aging report plus those rates is a defensible short-term cash forecast.

Reporting upward. The summary row of an aging report is one of the few finance numbers a non-finance audience reads without explanation, because the buckets are self-describing. If you report to a board or an owner, the five bucket totals plus the prior month's figures are usually the entire conversation.

Estimating bad debt. The allowance for doubtful accounts is conventionally built from an aging schedule, applying a higher loss rate to older buckets. Your auditors will ask for the aging report by name, and they will ask for the same report at the prior period end to see how the estimate performed.

Credit decisions. Before extending terms or raising a credit limit, the customer's row in the aging report is the most direct evidence you have of how they pay.

Pairing it with DSO. Days sales outstanding tells you how long on average it takes to get paid; the aging report tells you where the delay is concentrated. DSO moving in the wrong direction is the alarm, and the aging report is what you open next to find out which bucket caused it.

Spotting process problems rather than payment problems. A cluster of invoices from one month all sitting in the same bucket usually means a billing error, a disputed delivery, or an invoice that never reached the customer's accounts payable inbox. That is not a collections problem and calling the customer about payment will not fix it.

Where the report falls short

It is a snapshot, not a trend. A single aging report cannot tell you whether things are improving. Two reports a month apart can, which is why the useful habit is keeping the monthly snapshots rather than overwriting one file.

It does not distinguish a dispute from a delay. An invoice held up because the customer is contesting a line item looks identical to one held up because the customer is short of cash. The remedies are completely different. Add a status column and keep it current, because the aging buckets will never surface that difference on their own.

It flatters you if the data is stale. An aging report built on an export that predates last week's payment run shows money as outstanding that is already in the bank. Rebuild from a fresh export every time, and reconcile the total to the receivables balance in your general ledger before circulating it.

It has no view on collectability. A ninety-day-old invoice from a long-standing customer in a slow-paying industry and a ninety-day-old invoice from a customer who has stopped answering the phone occupy the same cell. Age is a proxy for risk, not a measure of it.

The buckets are a convention, not a law. Thirty-day bands suit monthly terms. If you invoice weekly, or your terms are net-7, thirty-day buckets hide everything that matters and you should use shorter bands.

It says nothing about concentration risk. Receivables can look perfectly current while sixty per cent of the balance sits with one customer. The aging report will not flag that, and it is a larger risk than a scattering of late invoices.

Your competitors are getting found in AI search. Get a free SEO audit and see where you stand.

Excel, accounting software, or a BI dashboard?

Your accounting system probably already has one. QuickBooks, Xero, Sage and NetSuite all ship an aging report, and theirs is reconciled to the ledger by construction. If you only need the standard view, run it there rather than rebuilding it — the main reason to use Excel is that you need buckets, groupings or calculations the built-in report will not give you.

Excel wins on flexibility. Custom buckets, a blended view across two entities, your own weighting for the cash forecast, a column for collections notes: all trivial in a spreadsheet and often impossible in a packaged report.

A BI tool wins on trend. The weakness of the Excel version is that it is one snapshot per file. Loading monthly exports into Power BI or Looker Studio and plotting bucket balances over time answers the question the snapshot cannot, which is whether your collections are getting better or worse.

Dedicated AR automation wins on follow-up. If the real problem is that nobody chases the invoices in the 31-60 bucket, a tool that sends the reminders is worth more than a better report. The report tells you who to chase; it does not do the chasing.

One thing worth resisting is maintaining two versions in parallel. If the Excel report and the accounting system disagree, somebody will eventually make a decision from the wrong one. Treat the ledger as authoritative, reconcile to it, and keep the spreadsheet as a view rather than as a second set of books.

For most finance teams the honest answer is the built-in report for the monthly close, an Excel version for anything bespoke, and a dashboard once anyone starts asking about trend.

Final Thoughts

An AR aging report is a simple object: open invoices, bucketed by how far past due they are. The build is six steps in Excel and most of the work is getting a clean export.

The decision that matters is aging from the due date rather than the invoice date. Get that wrong and every number downstream — the collections list, the cash forecast, the bad debt estimate — is wrong in the same direction, and the report will quietly overstate how badly your customers pay.

Everything after that is housekeeping. Write the as-of date in a cell, reconcile the total to the ledger before you send it, keep the monthly snapshots so you can see the trend, and add a status column so a dispute never gets mistaken for a slow payer.

If you only take one habit from this, make it the monthly save. A folder of dated snapshots costs nothing and turns a report that can only describe today into one that can show you the last twelve months, which is the version anyone will actually ask you for.

Build it once as a table with a PivotTable on top, and next month is a paste and a refresh rather than twenty minutes of formulas.