What an AR aging report shows
An accounts receivable aging report splits what you are owed by how overdue it is. The total on its own tells you almost nothing. $12,000 owed by people who are three days past terms is a good week. The same $12,000 sitting at ninety days is a problem you should already have acted on. Aging shows where within the balance the trouble sits; the accounts receivable turnover ratio shows whether the whole balance is moving faster or slower than it used to.
Money behaves differently at different ages. An invoice a week past terms usually needs a reminder. One at ninety days usually needs a phone call and a decision about whether to keep working for that client. That is why finance teams read the columns rather than the total. The calculator works the split out from today, and nothing is stored or sent anywhere.
How to calculate accounts receivable aging
- List every unpaid invoice with its amount and invoice date.
- Work out the due date: invoice date plus your terms.
- Days past due is today minus the due date. Zero or less means current.
- Put each invoice in its bucket: current, 1 to 30, 31 to 60, 61 to 90, over 90.
- Total each bucket and divide by the whole balance to get its share.
Aging from the due date rather than the invoice date is the detail people get wrong. An invoice raised forty days ago on net 30 terms is ten days past due, not forty. The calculator subtracts your terms before it buckets anything.
An aging report example
The figures already in the calculator are five invoices on net 30 terms, totalling $12,400:
- Current: $4,900 (40%), invoices 12 and 3 days old.
- 1 to 30 days past due: $1,800 (15%), 38 days old.
- 31 to 60 days past due: $2,600 (21%), 64 days old.
- 61 to 90 days past due: $0.
- Over 90 days past due: $3,100 (25%), 125 days old.
A quarter of the balance is past ninety days, and that one invoice is the one to act on. Weighted by amount, the balance averages 32 days past due.
What to do with each column
Current needs nothing. One to thirty gets a statement, because a statement changes the subject from one invoice they might dispute to a balance they have to account for. Thirty-one to sixty gets a call, not an email. Past sixty you should be deciding whether to keep supplying, and past ninety you are deciding what the debt is worth rather than whether it will arrive. The guide to chasing an overdue invoice has the wording.
Aging in Excel or Google Sheets
Put the invoice date in column B and the amount in column C. Days past due in D is =MAX(0, TODAY()-(B2+30)), with 30 replaced by your terms. The bucket in E is =IF(D2=0,"Current",IF(D2<=30,"1-30",IF(D2<=60,"31-60",IF(D2<=90,"61-90","90+")))). A SUMIF on column E totals each bucket.
This calculator takes five invoices, which is enough to see the shape of one account. For a whole ledger, the statement generator prints the aging on the statement you send.
Aging and bad debt
The aging method of estimating the allowance for doubtful accounts applies a higher percentage to each older bucket, using rates from your own collection history. The older the bucket, the less of it tends to be collected. The bad debt calculator works out the rate from what you have written off.
Related calculators
- Days past due calculatorHow late it is, and what that age usually calls for
- DSO calculatorDays sales outstanding, and what shortening it releases
- Accounts receivable turnover calculatorHow many times the balance turns over in a period
- Bad debt calculatorWrite-offs as a percentage, and what they cost to replace
Questions people ask
What is an AR aging report?
A breakdown of everything you are owed, grouped by how overdue it is: current, 1 to 30 days, 31 to 60, 61 to 90, and over 90. It shows where the risk sits rather than just how much there is.
How do you calculate AR aging?
For each unpaid invoice, subtract the due date from today to get days past due, then place it in a bucket: current, 1 to 30, 31 to 60, 61 to 90 or over 90. Total each bucket and divide by the whole balance for its share.
Should aging run from the invoice date or the due date?
From the due date. An invoice raised forty days ago on net 30 terms is ten days past due, not forty. This page subtracts your terms before bucketing.
What are the standard aging buckets?
Current, 1 to 30, 31 to 60, 61 to 90 and over 90 days is the common set, and it is what most accounting software and most finance teams expect to see.
How do you read an aging report?
Look at the share in each column rather than the total, and at what moved since last month. Money sliding from 1 to 30 into 31 to 60 needs a call. Anything over 90 needs a decision: a payment plan, a stop on new work, a claim or a write-off.
Why does the over 90 column matter so much?
Because collection rates fall as debt ages. Past ninety days the question usually changes from when you will be paid to how much of it you will get, and that changes what you should do about it.
What is weighted average days past due?
The average days overdue across the balance, weighted by invoice size. It stops one small very old invoice from making the whole account look worse than it is.
How often should I run an aging report?
Monthly at the least, on the same day each month, so each report can be compared with the last. Weekly is better if you have many open invoices or one large customer.
Can I send this to my customer?
Send a statement rather than an aging report. The statement generator lists every invoice, credit and payment with a running balance and prints the aging on it, which is the version a customer's accounts team can act on.