An accounts receivable aging report lists every unpaid customer invoice on one date and groups the open amounts by days past due: current, 1 to 30, 31 to 60, 61 to 90 and over 90. ORM's free Excel and Google Sheets template buckets by formula, totals each customer, and turns the buckets into an allowance and DSO.
Get the AR aging report template
Standard .xlsx, five tabs, room for 200 open invoices, no macros. Opens in Excel, and in Google Sheets through Drive (Open with > Google Sheets) or File > Import. Yellow cells are inputs. Everything else is a formula.
Quick check: your aging buckets, allowance and DSO
Enter the bucket totals from your aging report and the loss rate for each bucket. Defaults are the worked example. The loss rates are illustrative: the first four are the rates in FASB's own example, the 90+ rate is a placeholder. Replace them with your own history.
| Bucket | Open balance ($) | Loss rate (%) |
|---|---|---|
| Current (not yet due) | ||
| 1 to 30 days past due | ||
| 31 to 60 days past due | ||
| 61 to 90 days past due | ||
| Over 90 days past due |
| Bucket | Balance | % of AR | Expected loss |
|---|---|---|---|
| Current | $592,800 | 65.9% | $1,778 |
| 1 to 30 | $100,500 | 11.2% | $8,040 |
| 31 to 60 | $90,600 | 10.1% | $23,556 |
| 61 to 90 | $58,000 | 6.4% | $33,640 |
| Over 90 | $58,200 | 6.5% | $52,380 |
| Total | $900,100 | 100% | $119,394 |
Total AR is $900,100, and 34.1% of it is past due (6.5% over 90 days). At these loss rates the allowance should be $119,394, a 13.3% blended rate, so the books need an increase of $29,394. Net AR is $780,706. DSO is 53.1 days against a best possible DSO of 35.0, which puts 18.1 days on late payment.
Get The Forecast Brief
You just aged your receivables and sized the allowance. The Forecast Brief is a short email from Pete Furseth, ORM's COO, built on what ORM sees in its customers' forecasts.
What is in the template, tab by tab
The workbook starts from the export every accounting system can produce, one row per open invoice, and builds the rest with formulas. You paste invoices and type one date. The buckets, customer totals, allowance and DSO update from there.
| Tab | What it does | What you enter |
|---|---|---|
| Start here | Steps, the aging rules the formulas follow, and a note on the loss rates | Nothing |
| Invoices | Open amount, days past due, bucket, and the amount in each of the five bucket columns for up to 200 invoices, plus a total to tie to the ledger | As-of date, then customer, invoice number, invoice date, due date, amount and paid to date per invoice, and notes |
| Aging by customer | Each customer's balance in every bucket, total, % past due, % over 90 and share of total AR, a % of total row, and a check for customers you have not listed | Customer names, once each |
| Allowance | Balance times loss rate for each bucket, required allowance, blended rate, the adjustment against the allowance on the books, and net AR | Loss rates and the current allowance |
| DSO | DSO, best possible DSO, average days delinquent, % past due, % over 90 and average days past due weighted by amount | Billings for the period and the days in it |
The ranking pages for this search mostly explain the report and show a screenshot. None of the pages we read, including Business-Software.com's guide with tables to copy, offers a file with working formulas, and none ties its loss rates to FASB's own example.
How do I prepare an accounts receivable aging report?
Pick the as-of date, list what is still unpaid on that date, measure each invoice against its due date, and add up the buckets. With the template it is six steps.
- Set the as-of date. Use the last day of the month you are closing, so the report matches the balance sheet. Type it in cell B3 of the Invoices tab.
- Export open invoices. From your accounting or billing system, pull every invoice with an open balance on that date: customer, invoice number, invoice date, due date, original amount and payments applied. Partial payments stay in, at the open amount.
- Paste and check the total. The open AR total at the top of the Invoices tab should equal the accounts receivable balance in the general ledger. If it does not, the usual causes are unapplied cash, credit memos, a different as-of date or journal entries posted straight to the AR account. Fix the gap first, as part of your month-end close checklist.
- Read the buckets. The sheet ages each invoice from its due date. Sort by days past due and you have this week's collections list.
- List the customers. On the Aging by customer tab, type each customer once. The check cell turns to OK when every open dollar is assigned to a listed customer.
- Update the allowance and DSO. Set the loss rates, enter the allowance already booked, and enter billings for the period. Save the file with the month in the name so you can compare the trend.
The aging formulas, in Excel and Google Sheets
Row 6 is the first invoice on the Invoices tab, and cell B3 holds the as-of date. Examples come from the worked example, as of October 31, 2026 (illustrative).
| Measure | Formula in words | Excel or Sheets | Example (illustrative) |
|---|---|---|---|
| Open amount | Invoice amount minus paid to date | =IF(E6="","",E6-F6) | Customer H, INV-1034: $54,000 - $27,000 = $27,000 |
| Days past due | As-of date minus due date | =IF(G6="","",IF(G6<=0,"",$B$3-D6)) | Oct 31 minus Oct 20 = 11 days |
| Bucket | Zero or less is current, then 30-day steps | =IF(H6<=0,"Current",IF(H6<=30,"1-30",IF(H6<=60,"31-60",IF(H6<=90,"61-90","90+")))) | 11 days: 1-30 |
| Amount in a bucket column | Open amount if the bucket matches the column header | =IF($I6=J$5,$G6,0) | $27,000 in the 1-30 column, zero in the others |
| Customer total per bucket | Sum the bucket column for that customer | =SUMIFS(Invoices!K$6:K$205,Invoices!$A$6:$A$205,$A7) | Customer J, 31-60: $42,000 |
| % in a bucket | Bucket total divided by total AR | =IFERROR(F27/$G$27,0) | Over 90: $58,200 / $900,100 = 6.5% |
| Expected loss | Bucket balance times loss rate | =B10*D10 | $58,200 x 90% = $52,380 |
| DSO | AR divided by billings, times days | =IFERROR(B4/B5*B6,0) | $900,100 / $1,560,000 x 92 = 53.1 days |
The full bucket formula in the file also returns "Paid" when the open amount is zero or less, so paid and overpaid invoices drop out of every total. The formulas use only functions both apps share. To age by invoice date instead, replace D6 with C6 in the days formula, and change it on every row and in every month.
Accounts receivable aging report example
This is the worked example in the example file. The company, customers and amounts are illustrative, made up to show the mechanics. It sells subscriptions on net 30 terms (two customers on net 45), bills annual contracts upfront and some customers monthly, and closes its books on October 31, 2026.
| Customer (illustrative) | Current | 1-30 | 31-60 | 61-90 | 90+ | Total |
|---|---|---|---|---|---|---|
| Customer A | $96,000 | $24,000 | - | - | - | $120,000 |
| Customer B | $48,000 | $12,000 | - | $6,000 | - | $66,000 |
| Customer C | $120,000 | - | $18,000 | - | - | $138,000 |
| Customer D | $36,000 | $7,500 | - | - | - | $43,500 |
| Customer E | $210,000 | - | - | $10,000 | - | $220,000 |
| Customer F | $64,800 | - | $21,600 | - | - | $86,400 |
| Customer G | $18,000 | - | - | - | $11,400 | $29,400 |
| Customer H | - | $27,000 | $9,000 | - | - | $36,000 |
| Customer I | - | $30,000 | - | - | $4,800 | $34,800 |
| Customer J | - | - | $42,000 | $42,000 | $42,000 | $126,000 |
| Total | $592,800 | $100,500 | $90,600 | $58,000 | $58,200 | $900,100 |
| % of total AR | 65.9% | 11.2% | 10.1% | 6.4% | 6.5% | 100% |
Four things the example shows that a bucket total alone would hide:
- One customer is most of the old debt. Customer J has three unpaid $42,000 invoices, the oldest due in June. Its $84,000 over 60 days is 72.3% of the $116,200 the company has over 60 days. That is a phone call and a credit hold, not a dunning email.
- The biggest balances are not the riskiest. Customer E owes $220,000, but $210,000 is a new annual contract invoiced three days before month end and not due until November 27.
- Partial payments stay visible. Customer H paid half of a $54,000 invoice. The open $27,000 ages from the original due date, and the note column records the promise to pay.
- Paid invoices drop out. Two invoices in the list were paid in full before the as-of date. They show as Paid and are excluded from every total, so you can paste a full export without deleting rows.
On the DSO tab, $900,100 of AR against $1,560,000 of billings over 92 days is a DSO of 53.1 days. Best possible DSO, the figure the current balance alone would give, is 35.0 days ($592,800 / $1,560,000 x 92), so 18.1 days come from late payment. The amount-weighted average invoice that is past due is 57.7 days late.
Allowance for doubtful accounts by aging percentage
The aging method (also called the percentage of receivables method) applies a loss rate to each bucket and adds the results. The total is the allowance the balance sheet needs. Under US GAAP the account is now called the allowance for credit losses, and FASB's credit loss standard, ASC 326, includes an example that estimates expected losses on trade receivables with an aging schedule (ASC 326-20-55-37, as listed in ASU 2025-05).
In July 2025, FASB published ASU 2025-05. It lets any company elect a practical expedient to assume that "current conditions as of the balance sheet date do not change for the remaining life of the asset" for current receivables and contract assets from revenue contracts, and lets private companies also consider cash collected after the balance sheet date. It is effective for annual periods beginning after December 15, 2025, with early adoption permitted. The update's Example 5A shows the aging method with these rates:
| Past-due status | FASB Example 5A loss rate | Template default (illustrative) | Worked example balance | Expected loss |
|---|---|---|---|---|
| Current | 0.3% | 0.3% | $592,800 | $1,778 |
| 1 to 30 days | 8% | 8% | $100,500 | $8,040 |
| 31 to 60 days | 26% | 26% | $90,600 | $23,556 |
| 61 to 90 days | 58% | 58% | $58,000 | $33,640 |
| Over 90 days | 82% (91 to 120), 99% (over 120) | 90% (placeholder) | $58,200 | $52,380 |
| Total | 13.3% blended | $900,100 | $119,394 |
FASB's rates come from its example of a manufacturer selling to retail stores on 30-day terms (ASC 326-20-55-40B to 55-40C), not from a survey, so treat them as a starting layout. Your own rates come from history: of the invoices that reached each bucket over the last two or three years, what share did you write off? In the example, the books carry a $90,000 allowance, the aging says $119,394, so the adjustment is a $29,394 increase to the allowance and to credit loss expense, and net AR is $780,706.
Customer J alone produces $73,080 of the $119,394 (illustrative). FASB's Case 2 in the same example takes a customer in bankruptcy out of the buckets and reserves its balance on its own. If one account dominates your old buckets, assess it the same way and leave the bucket rates for everyone else. This page is a working method, not tax or accounting advice: confirm your method and any election with your auditor. For tax, the IRS lets a business deduct a bad debt only in the year that specific debt becomes worthless (IRS Topic 453), so the book allowance and the tax deduction are separate numbers.
Invoice date or due date?
The template ages from the due date, which shows how late a customer is against the terms it agreed to. Some reports age from the invoice date instead. Indiana University's controller's office, for example, counts days outstanding from the invoice approval date to the report run date (IU AR Aging and Detail Report). On invoice-date aging, a customer on net 60 terms looks overdue a month before it is. Pick one basis, write it on the Start here tab, and keep it every month.
What an aging report means in a SaaS business
Subscription companies have three patterns that make a generic aging report misleading.
- Annual invoices swell the current bucket. A $210,000 annual contract invoiced on October 28 is 23.3% of the example's AR on October 31, and it is not late. Look at the past-due buckets in dollars, not only as a share of a total that one invoice can move.
- Quarter-end billing moves DSO. Pete Furseth, ORM's COO, sees the same pattern across ORM's customers: "Within a quarter the third month is always the largest and the first month is always the smallest," with a large share of business closed in the last week. Invoices issued in that last week sit in AR on the last day of the quarter, so DSO rises with no change in how customers pay. Best possible DSO separates the two: if it rose by the same amount, the jump is timing.
- An unpaid renewal invoice may not be a credit loss. If a customer does not renew, the invoice for the next term is often cancelled with a credit memo against deferred revenue, because the service was never delivered. Find these before you reserve for them, and check the treatment with your accountant.
The renewal case is also a revenue signal. Pete puts it plainly: "If you send them an invoice 30 days before their renewal and never talk to them, you are putting that renewal at more risk." A large renewal invoice sitting unpaid in the current bucket is worth a call from the account owner, not only a reminder from AR. The gap between invoiced and banked dollars is covered in ORM's guide to billings vs cash collections.
From the aging report to DSO, turnover and the cash forecast
The aging report is the input to three other measures. Each answers a different question.
| Measure | Question it answers | ORM tool |
|---|---|---|
| AR aging report | Who owes us, how much, and how late, on one date? | This template |
| DSO and best possible DSO | How many days of billings are sitting in AR, and how many of those days are late payment? | DSO calculator |
| AR turnover | How many times a year do receivables turn into cash? | Accounts receivable turnover calculator |
| Collections forecast | Which week will each open invoice turn into cash? | 13-week cash flow template |
The 13-week cash flow template takes the same invoice list, applies a collection rate and days to pay for each bucket, and places expected cash in a week. Paste this month's aging there and the first weeks of the cash forecast come from real invoices. For an outside reference on DSO, Upflow's 2024 data on more than 500 companies put the median SaaS company at about 59 days and the top quarter at 38 (Upflow, State of B2B Payments 2024).
One naming note: "pipeline aging" is a different report. It ages open CRM opportunities by days in stage, not invoices by days past due. That one is explained in ORM's pipeline aging report glossary entry.
Common aging report mistakes the template catches
- Not tying to the ledger. The total at the top of the Invoices tab is there to compare with the AR account. An aging that does not tie gives a wrong allowance.
- Changing the aging basis. Moving between invoice date and due date makes buckets jump with no change in payment behavior.
- Reserving only the 90+ bucket. The aging method applies a rate to every bucket, current included, as FASB's example does.
- Dropping partial payments. A half-paid invoice keeps aging from its original due date at the open amount.
- Reading only percentages. One large annual invoice can make every past-due percentage fall while the late dollars stay the same.
- One snapshot. The report matters as a trend. Keep one file per month and compare % past due, % over 90 and DSO against best possible DSO.
ORM's take: the aging report shows last quarter's sales, the forecast shows next quarter's cash
Every invoice on an aging report started as a deal or a renewal months earlier. The report tells finance what is late. It cannot say how much will be billed next quarter, because that depends on which deals close, at what size and in which week, and on which renewals are at risk before the invoice goes out.
ORM forecasts new, expansion and renewal revenue from your own CRM data and updates it as the quarter moves, so finance sees the billings that will feed next quarter's receivables before they are invoiced. ORM targets 95% forecast accuracy on the forecasts it builds.
Common questions
What is an AR aging report?
An AR aging report lists every unpaid customer invoice on one date and sorts the open amounts by how many days past due they are, usually current, 1 to 30, 31 to 60, 61 to 90 and over 90 days. Totals run by customer and by bucket. Finance uses it to pick which customers to chase, to estimate the allowance for doubtful accounts at month end and to explain changes in DSO.
How do you calculate accounts receivable aging?
For each open invoice, subtract the due date from the as-of date to get days past due. Zero or less is current, 1 to 30 goes in the first past-due bucket, and so on. Then add the open amounts in each bucket, by customer and in total, and divide each bucket by total AR to get the percentage. In Excel the days formula is the as-of date cell minus the due date cell.
Should an aging report use the invoice date or the due date?
Use the due date for collections, because it shows how late a customer is against the terms they agreed to. Aging from the invoice date mixes customers on net 30 and net 60 terms, so a net 60 customer looks late a month before it is. Some systems default to invoice date, so check the setting, and keep the same basis every month or the buckets move with no change in behavior.
What is a good AR aging percentage?
There is no reliable published benchmark for the share of receivables in each bucket, so compare against your own trend and your terms. The useful checks are the share of AR past due and the share over 90 days, month over month, and DSO next to best possible DSO. For an outside reference on DSO, Upflow's 2024 data put the median SaaS company at about 59 days.
How do you calculate the allowance for doubtful accounts from an aging report?
Multiply the balance in each aging bucket by an expected loss rate for that bucket, then add the results. That total is the allowance you need. Subtract the allowance already on the books, and the difference is the adjustment to credit loss expense. Rates come from your own write-off history, adjusted for current conditions. FASB's own example uses 0.3% for current balances up to 99% for balances over 120 days.
How do I make an AR aging report in Excel or Google Sheets?
Export open invoices with customer, invoice date, due date, amount and amount paid. Add an open amount column, a days past due column (as-of date minus due date), and a bucket column with nested IF formulas. Then use SUMIFS to total each bucket by customer. The template on this page has those formulas built for 200 invoices and works in both Excel and Google Sheets.
See next quarter's billings before they become receivables
This template reports what customers owe today. ORM shows, week by week, what this quarter's deals and renewals will bill.
Schedule a Demo