Free aging report template for Excel (accounts receivable and payable)
An aging report lists every unpaid invoice (accounts receivable, AR) or unpaid bill (accounts payable, AP) and sorts the open balance by how late it is: Current, 1–30, 31–60, 61–90 and over 90 days past due. This free Excel template has an AR aging sheet and an AP aging sheet. Type the aging date and your open invoices or bills; the due dates, days past due, buckets, a summary by customer or vendor and a check against the balance in your books calculate. Below: what's in the file, how aging works, a worked example for each, how to build one from invoices and bank statements, and what to do with the numbers.
Short answer: download the aging report template (.xlsx). On the AR aging sheet, enter the aging date (usually month end), then each invoice's customer, number, date, terms and amount, plus anything paid to date. The open balance lands in Current, 1–30, 31–60, 61–90 or Over 90 by days past the due date, with totals, % past due and a summary by customer. The AP aging sheet does the same for vendor bills. The Example is a fictional design studio at September 30, 2026: 18,450.00 owed by customers, 55.3% past due and 1,350.00 over 90 days; 4,125.80 owed to vendors, 17.0% past due. Both tie to the A/R and A/P balances in its books.
Download the free aging report template (.xlsx)
Five sheets: a blank AR aging (customer invoices), a blank AP aging (vendor bills), a filled-in Example AR aging and Example AP aging, and How to use. 300 lines and 40 summary names per sheet. It uses only SUM, SUMIF, COUNT, COUNTIF, MAX, ROUND, IF, AND, OR, N and TEXT, so it works in Excel, Google Sheets (File, Import), LibreOffice and Numbers. No sign-up, no email, no macros. It prints landscape on one page width.
- What's in the template
- How an aging report works
- Worked example: accounts receivable aging
- Worked example: accounts payable aging
- How to make an aging report from invoices and bank statements
- When the aging doesn't match your books
- Reading it: what to do with each bucket
- Aging reports in QuickBooks Online and Xero
- FAQ
1. What's in the template
- Inputs at the top: business name, aging date (as of), default terms in days (30 for Net 30) and, optionally, the A/R or A/P balance in your books.
- The list: customer (or vendor), invoice (or bill) number, date, terms, due date (date + terms), amount, paid to date, open balance, days past due and the open balance in its bucket: Current, 1–30, 31–60, 61–90 or Over 90. A filter is on the header row.
- Totals for every column and each bucket's % of the open balance.
- Summary by customer or vendor (on the right): one row per name with its buckets and total, the layout an aging summary report uses.
- Checks and key figures: total open balance, past due, % past due, over 90 days, the oldest open line in days, open lines and any not aged (no date), open balance for names missing from the summary, and the difference from your books.
- Status line, such as 55.3% past due, 1,350.00 over 90 days; ties to the books, or Differs from the books by 500.00: look for a missing or doubled invoice, or a payment recorded in only one place.
Yellow cells are inputs. Everything else is a formula. A line with its own terms uses them; a blank terms cell uses the default. Type a due date over the formula if an invoice has a fixed one.
2. How an aging report works
Each open invoice or bill is aged by days past due = aging date − due date, and its open balance goes in one bucket. This template ages by due date, the usual choice for collections; aging by invoice date makes every Net 30 invoice look 30 days older.
| Bucket | Days past due | Means |
|---|---|---|
| Current | 0 or less | Not due yet, or due on the aging date. |
| 1–30 | 1 to 30 | Recently late. Usually a reminder is enough. |
| 31–60 | 31 to 60 | A payment cycle missed. Call or send a statement. |
| 61–90 | 61 to 90 | Seriously late. Escalate and stop new work until it's sorted. |
| Over 90 | 91 or more | At risk of never being paid. Payment plan, collections or, with your accountant, a write-off. |
Fully paid lines have no open balance, so they drop out of the buckets but stay on the list for the record. A part payment leaves the rest in the bucket of the original due date. A credit note or overpayment can go in as a negative amount.
3. Worked example: accounts receivable aging
The Example AR aging sheet is the same fictional design studio as our trial balance and balance sheet templates, aged at September 30, 2026. Ten invoices, one paid in full (INV-1059) and one half paid (INV-1041, 1,200.00 of 2,400.00):
| Customer | Current | 1–30 | 31–60 | 61–90 | Over 90 | Total |
|---|---|---|---|---|---|---|
| Bluebird Dental | 1,900.00 | 2,850.00 | 4,750.00 | |||
| Harbor Yoga Studio | 1,350.00 | 1,350.00 | ||||
| Lakeside Brewing Co. | 2,150.00 | 1,200.00 | 3,350.00 | |||
| Maple & Co. Realty | 2,750.00 | 2,750.00 | ||||
| Northwind Cafe | 1,450.00 | 1,600.00 | 3,050.00 | |||
| Summit Outdoor Supply | 3,200.00 | 3,200.00 | ||||
| Total | 8,250.00 | 3,200.00 | 4,450.00 | 1,200.00 | 1,350.00 | 18,450.00 |
| % of open balance | 44.7% | 17.3% | 24.1% | 6.5% | 7.3% | 100% |
- Invoices total 21,450.00, payments 3,000.00, so 18,450.00 is open across 9 invoices. That's the studio's accounts receivable on its trial balance before adjusting entries.
- Past due 10,200.00 (55.3%), everything outside Current.
- The oldest is Harbor Yoga Studio's INV-1038: dated May 20, Net 30, due June 19, 103 days past due, the only line over 90.
- Northwind Cafe is on Net 15, so INV-1057 from August 14 was due August 29 and is 32 days late, while INV-1068 from September 22 is due October 7 and still Current.
- The balance sheet example's A/R is 22,050.00 because it also includes 3,600.00 of September work invoiced on October 2. Work that hasn't been invoiced isn't in an aging; if your books include it, expect that difference.
4. Worked example: accounts payable aging
The Example AP aging sheet is the same studio's unpaid bills at September 30, 2026:
| Vendor | Bill | Due | Days past due | Open | Bucket |
|---|---|---|---|---|---|
| Metro Courier | MC-1189 | 08/01/2026 | 60 | 290.00 | 31–60 |
| Riverside Office Supply | RO-4471 | 09/09/2026 | 21 | 412.20 | 1–30 |
| Brightline Web Hosting | BW-7781 | 09/30/2026 | 0 | 189.00 | Current (due today) |
| Pixel Print Shop | PP-2291 | 10/05/2026 | -5 | 1,284.60 | Current |
| Copperleaf Freelance | CF-0912 | 10/12/2026 | -12 | 1,950.00 | Current |
| Total | 4,125.80 | Current 3,423.60 |
Past due is 702.20 (17.0%), nothing over 90 days, and the oldest bill is 60 days late. The total ties to the trial balance's accounts payable of 4,125.80. The 286.40 September electric bill that arrived October 6 is an accrual in the books, not a bill on September 30, so it isn't in the aging. To see which bills are due this week rather than how late they are, keep a bill tracker and copy its unpaid lines here at month end.
5. How to make an aging report from invoices and bank statements
- Pick the aging date, usually the last day of the month, so it lines up with your bank statement and books.
- List every invoice (or bill) that wasn't fully paid by the start of the period, plus every new one: name, number, date, terms and amount. Your invoicing tool or a sales list exports these.
- Match payments from the bank statement. Every customer deposit up to the aging date goes against the invoice it paid (vendor payments for AP). Put the amount in Paid to date. A deposit that doesn't match any invoice is either a new sale, a payment for an invoice you didn't list, or an overpayment; find out which.
- Type each name once in the summary on the right. Sorting the list by name (the filter on the header) shows them. The checks box shows any open balance for a name you haven't added.
- Tie out: type the A/R (or A/P) balance from your books. The status line says ties to the books or shows the difference.
- Roll it forward each month: change the aging date, add new invoices, add the month's payments. Keep paid lines or delete them; they don't affect the totals. If you'd rather log every invoice as you send it and mark payments as they arrive, keep an invoice tracker through the month and copy its unpaid lines here at month end.
Statements only as PDFs? Convert them to Excel first, so every deposit sits in a column you can sort and match. If you track all bank activity in a spreadsheet, the bookkeeping template or cash book template gives you that list with a category on each line.
6. When the aging doesn't match your books
| Aging vs books | Usual cause | Fix |
|---|---|---|
| Aging lower | An invoice or bill is in the books but not on the list | Compare invoice numbers; add the missing one. |
| Aging higher | A payment was recorded in the books (or deposited) but not applied to the invoice | Match the bank deposit to the invoice and fill in Paid to date. |
| Off by one invoice twice | An invoice entered twice, or a payment entered twice | Sort by number and look for doubles. |
| Off by unbilled or accrued amounts | Adjusting entries (unbilled income, accrued bills) are in the books | Expected: they aren't invoices or bills yet. Compare before adjustments. |
| Off by a deposit that isn't a payment | A deposit was coded to A/R when it was a refund, loan or owner's money | Recode it in the books. |
7. Reading it: what to do with each bucket
- % past due is the headline: the share of what you're owed that's already late. Watch it month to month; a rising figure is an early warning for cash.
- Work from the right: Over 90 first, then 61–90. One customer with several late invoices usually needs a call, not more reminders.
- Look at the summary by customer for concentration: one or two customers holding most of the balance is a risk even if they're current.
- For AP, the aging tells you what to pay first and which bills are about to cost a late fee or a supplier relationship. Pair it with a cash flow forecast to time payments against expected customer receipts.
- Invoices you no longer expect to collect: talk to your accountant about an allowance or a write-off rather than deleting them from the list.
8. Aging reports in QuickBooks Online and Xero
If you invoice in accounting software, it builds the aging for you. In QuickBooks Online, go to Reports, then Standard reports: Accounts receivable aging summary and aging detail are in the Who owes you section, and the accounts payable versions are in What you owe. Xero has the Accounts Receivable Aging Summary and Accounts Payable Aging Summary (Aged Receivables and Aged Payables outside the US) under Reporting, All reports, with aging by due date or invoice date. A spreadsheet aging is still useful when invoices live outside the software, for catch-up work before you import, or to check the software's report against the bank.
Matching payments from bank statement PDFs?
Utilly's converter reads a bank statement PDF, downloaded or scanned, into Excel, CSV, QuickBooks or Xero files and checks the rows against the statement's beginning and ending balance, so no deposit is missing when you match payments to invoices. The preview is free with no sign-up. Try it on the sample bank statement first: every download of the sample is free because the data is fictional.
Method: the template ages open balances by due date (invoice date + terms) at the aging date, in Current (0 days or less), 1–30, 31–60, 61–90 and Over 90 day buckets, the standard ranges QuickBooks Online and Xero use by default. Report names per Intuit's "Run an accounts receivable aging report" help article and Xero Central's Accounts Receivable Aging Summary article, checked November 3, 2026. The example business, customers and vendors are fictional; the totals equal the A/R (18,450.00) and A/P (4,125.80) on our trial balance template's example before adjusting entries. Template built and checked November 3, 2026: both Example sheets recalculate in LibreOffice to the figures above and the status ties to the books.
FAQ
Is the aging report template free?
Yes. Download the .xlsx with no sign-up or email address. It has a blank AR aging and AP aging sheet, a filled-in example of each and instructions, and it works in Excel, Google Sheets (File, Import), LibreOffice and Numbers.
What is an accounts receivable aging report?
A list of the invoices customers haven't paid, with each open balance sorted by how late it is: Current (not yet due), 1-30, 31-60, 61-90 and over 90 days past due, usually with a total per customer. It shows who to chase first and how much of what you're owed is already late.
How do you calculate days past due in Excel?
Days past due = aging date - due date, where due date = invoice date + terms in days (for example 30 for Net 30). Zero or less means the invoice is current. The template does this on every line and puts the open balance in the matching bucket.
What's the difference between AR aging and AP aging?
AR aging is money customers owe you (unpaid invoices); AP aging is money you owe vendors (unpaid bills). They're built the same way. AR aging drives collections; AP aging tells you what to pay first.
Should the aging report equal accounts receivable on the balance sheet?
Yes, on the same date, apart from amounts that are in the books but aren't invoices yet (such as unbilled work booked as an adjusting entry). Type the A/R balance from your books into the template and the status line says whether it ties or shows the difference.
Does QuickBooks or Xero have an aging report?
Yes. QuickBooks Online has Accounts receivable aging summary and detail reports under Reports, Standard reports, Who owes you (accounts payable versions under What you owe). Xero has the Accounts Receivable and Accounts Payable Aging Summary reports (Aged Receivables and Aged Payables outside the US). A spreadsheet aging is for invoices kept outside the software or for checking the software against the bank.
Related guides
- Free invoice tracker template for Excel (paid, open and overdue)
- Free bill tracker template for Excel (due soon, paid and overdue)
- Free balance sheet template for Excel (small business, with formulas)
- Free trial balance template for Excel (adjusted and extended)
- Free cash flow forecast template for Excel (12-month and 13-week)
- Free small business bookkeeping template for Excel (income, expenses and a bank check)
Related tools
- Bank reconciliation checker: compare a bank statement to your books and list missing, duplicate and mismatched transactions.
- Receipt to CSV: turn PDF or photo receipts and invoices into CSV, Excel, or QuickBooks and Xero bill imports.
