Accounts Receivable Aging Analysis Calculator

What an AR aging analysis actually tells you

An accounts receivable aging analysis takes one number you already know, the total you are owed, and splits it by how long each part has been waiting.

That split is the whole point. A $250,000 book is healthy if most of it was invoiced last week, and a serious problem if a quarter of it has sat past 90 days since spring. Until you age it, the two look identical in your accounts.

Four people read the same table, for four different reasons:

• Finance works it as a list. It ranks which balances need a call today.
• A lender decides what to advance against it. Balances past 90 days are usually excluded from the borrowing base outright.
• An acquirer reads a fat tail in the old buckets as a discount on the purchase price.
• Your auditor takes it as the standard evidence for the allowance for doubtful accounts.

The aging schedule format

The aging schedule is the table the analysis produces. One row per customer, one column per age bucket, a total column on the right, and a totals row along the bottom. Those bottom row totals are the figures you convert into percentages.

Illustrative schedule, not real customer data. Delta Freight is highlighted because it carries the largest over 90 balance, which is the row a weekly review should open with.

Two formats answer different questions. A summary schedule gives one row per customer, and shows you the shape of the book. A detail schedule gives one row per invoice, and is what you work from, because you can only chase an invoice number.

Both are standard exports. QuickBooks calls them A/R Aging Summary and A/R Aging Detail. Xero calls them Aged Receivables Summary and Aged Receivables Detail.

Aging analysis, aged receivables and accounts receivable age analysis all name this same report. The extra letter is the spelling used outside the United States, not a different method.

The aging formula

The arithmetic is trivial. Everything that goes wrong happens in the three steps before the division.

A worked example

A distribution business is owed $250,000 across four customers. The aging schedule above totals to these five buckets.

• Current: 162,500 / 250,000 = 0.65, so 65.0 percent
• 1 to 30 days: 37,500 / 250,000 = 0.15, so 15.0 percent
• 31 to 60 days: 25,000 / 250,000 = 0.10, so 10.0 percent
• 61 to 90 days: 12,500 / 250,000 = 0.05, so 5.0 percent
• Over 90 days: 12,500 / 250,000 = 0.05, so 5.0 percent

Past due in total is 250,000 minus 162,500 = $87,500, or 35.0 percent. The share past 60 days is 12,500 + 12,500 = $25,000, or 10.0 percent, which is the headline figure the calculator reports.

Average days past due weights each bucket by its midpoint, using 0, 15, 45, 75 and 120 days.

(0 x 162,500) + (15 x 37,500) + (45 x 25,000) + (75 x 12,500) + (120 x 12,500) = 4,125,000. Divide by 250,000 and you get 16.5 days past due on average.

On net 30 terms, that means the average invoice is about 46 days old.

The bad debt estimate applies your own rate to each bucket. At 1, 3, 10, 25 and 50 percent: 1,625 + 1,125 + 2,500 + 3,125 + 6,250 = $14,625, which is 5.9 percent of the book. Those five rates are assumptions, which is why the calculator lets you change them.

What each bucket signals

Each band means something specific, and each has a different cheapest response. That is the value of the report: it tells you which conversation to have.

The 31 to 60 band deserves the closest reading. An invoice a week late is usually a timing accident. An invoice six weeks late almost never is: it was disputed, never approved, never received, or the customer has a cash problem they have not mentioned. Each of those has a fix, and each gets harder the longer it waits.

How to read your number

Read the share past 60 days first, then the direction of travel, then the concentration. A single customer holding 80 percent of your over 90 balance is a different problem from thirty customers holding it evenly, even though the aging percentages are identical.

These bands track the calculator above. Change a bucket and the band your book falls into is marked.

The collection risk score weights the same buckets by age. Each bucket's share is multiplied by 0 for current, then 0.25, 0.5, 0.75 and 1.0 for over 90 days, and the results are added up.

A book entirely current scores 0. A book entirely past 90 days scores 100. Use it to track your own trend, not as an external rating.

Aging benchmarks

Two sets of figures sit below, and they are kept in separate tables because they come from different places and count different things. The first is our own customer data, broken down by sector. The second is national data published by two named organizations, which neither of them breaks down by industry. Every row says where it came from.

Paidnice customer figures, small businesses on Xero and QuickBooks

Source: Paidnice customer data, August 2026.

Published national figures, United States

Sources. Credit Research Foundation, National Summary of Domestic Trade Receivables, first quarter 2026, and Atradius, Payment Practices Barometer, B2B payment practices trends in North America, 2025, from a sample of 240 US interviews across manufacturing, wholesale, retail and services. Reviewed 15 August 2026.

The two published figures measure different things, so do not add them. The Credit Research Foundation reports a snapshot of the open ledger at quarter end, which is why so much of it is current. Atradius reports the share of a year's invoice value that goes past its due date at any point. A ledger can be 87 percent current on any given day and still have 43 percent of the year's invoices arrive late.

Aging by due date or by invoice date

Age by due date to see how late a customer is. Age by invoice date to see how long your cash has been out. It is the only real method choice in an aging analysis, and the one most often left undeclared.

On one set of terms for everyone, the two bases sit a fixed distance apart and the choice hardly matters. On terms ranging from net 7 to net 60 they tell different stories, and a book that looks clean by due date can still have cash out for a long time.

Bucket width is the other variant. Thirty day bands are standard, but a business on net 7 terms learns more from 7, 14, 30 and 60 day bands. Keep whatever you choose stable, because changing bucket widths resets your trend to nothing.

How aging relates to DSO

Aging and days sales outstanding measure the same reality from opposite ends. DSO compresses your entire receivables position into one number of days. Aging expands it back out into where those days actually live. Neither is complete alone.

The pairing is diagnostic. DSO up while the aging profile holds its shape means mix or terms changed: a larger customer on longer terms, or invoicing that landed late in the period. DSO up while the 31 to 60 and 61 to 90 buckets swell means collections slipped, and the aging report already holds the list of accounts to call.

It runs the other way too. A strong month of new sales can hide a growing pile of old invoices behind a flat DSO, and checking the over 60 share catches that. AR turnover and collection efficiency show the same picture as a ratio and as a percentage.

When an aged balance becomes bad debt

No accounting rule turns an invoice into bad debt on a particular day. What changes with age is the chance of collecting it: close to certain inside 30 days, still good at 60, noticeably harder at 90, and a matter of negotiation beyond that.

Two separate things happen as balances age. The allowance for doubtful accounts is an estimate held against the whole ledger, not a decision about any one invoice. The aging method of estimating it is what the bad debt panel does: apply an expected loss rate to each bucket, then add them up.

The write off is the second, and it removes one specific invoice once recovery is genuinely unlikely. The bad debt expense calculator works both, including the journal entry.

What triggers escalation is silence, not age. A 100 day balance on an account that answers the phone and has agreed a plan is in better shape than a 65 day balance from a customer who has stopped replying.

What pushes the aging profile the wrong way

A worsening aging report rarely has one cause. These five account for most of it, and only three are collections problems.

• Invoicing after the work, not with it. Every day between finishing and invoicing is a day added to that balance, and it never reads as a collections failure because the clock had not started.
• Unresolved disputes. A queried invoice ages in silence. A first careful review usually turns up several balances stuck for months over a small question nobody owned.
• Chasing when someone remembers. Without a schedule, the oldest invoices are the ones most likely to be forgotten.
• Missing the payment run. Large customers pay on fixed days. An invoice arriving a day after the cutoff waits a full extra cycle and lands a bucket further along.
• Credit extended without a check. A fat top bucket is often a credit approval problem, not a collections problem. The cheapest overdue invoice is the one never issued.

How to work the aging report every week

The report is not a monthly summary to read, it is a weekly work list to clear. Ninety minutes on the same morning each week is enough for most businesses.

1. Refresh the aging and reconcile it. Export it, check the total agrees with the receivables control account, and note the change in the over 60 share since last week. That one comparison is your entire dashboard.
2. Work the 31 to 60 bucket first, not the oldest one. Middle band balances are still recoverable at low cost, and stopping them crossing 60 days beats another attempt at something 200 days old.
3. Sort by value inside each bucket. Twenty percent of the accounts hold most of the money. Call those, and let automation handle the tail of small balances.
4. Make the routine follow up automatic. Reminders before the due date and at fixed intervals after it should not depend on anyone's memory. Automated email and SMS reminders fire on schedule whatever else the week held.
5. Send a statement to anyone with more than one open invoice. Some accounts payable teams pay from a statement rather than an invoice. Automated customer statements catch those.
6. Apply the late fee you already stated. Consistency is what makes a fee work as leverage. Automated late fees keep it impersonal.
7. Offer a plan before you write it off. A balance that will never arrive in one payment is often still collectable in six. Payment plans move money out of the over 90 bucket.
8. Track the profile, not just this week's list. Keep the five percentages every week. AR reporting holds that history without a manual rebuild, and the trend is what tells you whether any of this works.

Building an aging report in Excel

Export your open invoices with the outstanding amount in column A, the invoice date in column B and the due date in column C. Then:

• Days since the invoice date: =TODAY()-B2
• Days past due, floored at zero: =MAX(0,TODAY()-C2)
• Bucket label in column E: =IF(D2=0,"Current",IF(D2<=30,"1-30",IF(D2<=60,"31-60",IF(D2<=90,"61-90","90+"))))
• Total the current bucket: =SUMIF($E$2:$E$500,"Current",$A$2:$A$500)
• Or total a band straight from the day count, skipping the label: =SUMIFS($A$2:$A$500,$D$2:$D$500,">30",$D$2:$D$500,"<=60")
• Bucket share, with the bucket total in H2 and the grand total in H8: =H2/$H$8
• Weighted average days past due: =SUMPRODUCT($A$2:$A$500,$D$2:$D$500)/SUM($A$2:$A$500)
• Estimated uncollectible, with bucket totals in H2:H6 and your loss rates in I2:I6: =SUMPRODUCT(H2:H6,I2:I6)

A pivot table over the bucket label column gives you the customer by bucket schedule in one step, with customers as rows and the bucket label as columns.

AR aging against the other receivables metrics

Aging is the only one of these that gives you names and amounts rather than a single figure. The others are better for tracking, this one is better for acting.

Run aging weekly and DSO monthly. Aging changes every day and rewards frequent attention, while DSO is noisy over short windows and only becomes meaningful across several periods.

Common mistakes

• Aging the invoice value instead of the balance. A part paid invoice must age at what is still outstanding, or the buckets overstate the book.
• Leaving credit notes and unapplied payments out. They belong in the schedule as negatives, or the aging will not reconcile to the balance sheet.
• Switching between due date and invoice date aging. On net 30 terms that shifts every balance a full bucket and invents an improvement or a crisis that never happened.
• Reading percentages while the book is growing. Fast sales growth shrinks the old buckets as a share even while their dollar value rises. Track both.
• Treating the report as reading rather than work. A report reviewed but not acted on has only told you the shape of a problem you already had.
• Accepting a bad debt estimate you did not set. Loss rates per bucket are a judgment about your customers. A default rate buried in a tool is a guess about someone else's.

Sources

• Paidnice customer data, reviewed 15 August 2026. The sector aging table and the healthy book bands only. Drawn from our own customers' Xero and QuickBooks ledgers. It is not a survey and carries no published sample size.
• Credit Research Foundation, National Summary of Domestic Trade Receivables, first quarter 2026. Percent current, percent past 91 days, DSO, best possible DSO and average days delinquent.
• Atradius, Payment Practices Barometer, B2B payment practices trends in North America, 2025. Share of B2B invoice value paid on time, overdue and written off.
• APQC, accounts receivable and collections key benchmarks.
• National Association of Credit Management, credit management standards and education.

Sources are cited for the benchmark figures only. The schedule, the risk score and the bad debt exposure on this page are computed from the figures you enter. Reviewed 15 August 2026.