Accounts Receivable Template for Excel: Aging Report, Collections and DSO
Build an accounts receivable template in Excel: invoice register, due dates, partial payments, days past due, an AR aging report by bucket, customer summary, collection steps and DSO, with a worked example.
An accounts receivable (AR) template in Excel is a register of every unpaid customer invoice — invoice date, due date, amount, payments received and open balance — with formulas that calculate days past due, sort each balance into an aging bucket (current, 1–30, 31–60, 61–90, 90+ days) and total what each customer owes. The result is an AR aging report you can read in a minute: who to call this week, how much cash is stuck in overdue invoices and whether customers are paying slower over time. For small businesses that invoice on terms, it is one of the most useful spreadsheets for cash flow.
This guide is part of our Bookkeeping & Income Tracking section. Your AR total should agree with the accounts receivable balance in your books — the general ledger template is where you check it.
Template structure
| Sheet | One row per… | Key columns |
|---|---|---|
| Customers | Customer | ID, name, billing contact, payment terms (days), credit limit |
| Invoices | Invoice | Number, date, customer ID, amount, due date, paid, balance, days past due, bucket, last reminder |
| Payments | Payment received | Date, invoice number, amount, method |
| Aging | Customer | Total open and split by bucket, over-limit flag |
Recording payments on their own sheet keeps a clean history when a customer pays one invoice in several installments.
Formulas
Due date, paid and balance
- Due date:
=B5+XLOOKUP(C5,Customers!A:A,Customers!D:D,30)(invoice date + the customer’s terms, default net 30). - Paid:
=SUMIFS(Payments!C:C,Payments!B:B,A5) - Balance:
=D5-F5
Days past due and aging bucket
- Days past due:
=IF(G5<=0,0,MAX(0,TODAY()-E5)) - Bucket:
=IF(G5<=0,"Paid",IF(H5=0,"Current",IF(H5<=30,"1-30",IF(H5<=60,"31-60",IF(H5<=90,"61-90","90+")))))
Conditional formatting: yellow for 1–30, orange for 31–60, red for 61+.
Aging by customer
With customer IDs in A5:A30 and bucket names in B4:F4: =SUMIFS(Invoices!$G:$G,Invoices!$C:$C,$A5,Invoices!$I:$I,B$4). A PivotTable (customer in rows, bucket in columns, balance as values) works just as well.
Worked example: AR aging as of November 6, 2026
| Invoice | Customer | Date | Due | Amount | Paid | Balance | Past due | Bucket |
|---|---|---|---|---|---|---|---|---|
| INV-1020 | Customer E | Jun 1 | Jul 1 | $1,250 | $0 | $1,250 | 128 days | 90+ |
| INV-1035 | Customer D | Jul 15 | Aug 14 | $1,900 | $0 | $1,900 | 84 days | 61-90 |
| INV-1041 | Customer A | Aug 20 | Sep 19 | $3,800 | $0 | $3,800 | 48 days | 31-60 |
| INV-1050 | Customer B | Sep 10 | Oct 25 (net 45) | $5,600 | $2,000 | $3,600 | 12 days | 1-30 |
| INV-1056 | Customer C | Sep 28 | Oct 28 | $2,450 | $0 | $2,450 | 9 days | 1-30 |
| INV-1062 | Customer A | Oct 12 | Nov 11 | $4,200 | $0 | $4,200 | — | Current |
| INV-1068 | Customer F | Oct 30 | Nov 14 (net 15) | $2,700 | $0 | $2,700 | — | Current |
| Bucket | Balance | Share |
|---|---|---|
| Current | $6,900 | 34.7% |
| 1–30 days | $6,050 | 30.4% |
| 31–60 days | $3,800 | 19.1% |
| 61–90 days | $1,900 | 9.5% |
| 90+ days | $1,250 | 6.3% |
| Total AR | $19,900 | 100% |
Reading it: $13,000 — 65.3% of receivables — is past due. Customer A owes $8,000 in total, including $3,800 that is 48 days late; resolve that before shipping more on credit. Customer E’s $1,250 is 128 days overdue and is the hardest to collect. Figures are illustrative.
A simple collections process
| When | Action |
|---|---|
| 3–5 days before due | Friendly reminder with invoice number, amount and payment link or bank details |
| 1–7 days late | Email check-in: did they receive and approve the invoice? Any dispute? |
| 8–30 days late | Phone call to accounts payable, then a written follow-up with a promised pay date |
| 31–60 days late | Pause new work or shipments on credit; owner-to-owner conversation |
| 60+ days late | Formal demand letter; gather the contract, invoices and correspondence and consider professional collection or legal advice |
Late fees and interest can only be charged if your contract or invoice terms allow them and within what state law permits — check before adding them. Log every reminder date in the Invoices sheet; it matters if an invoice is ever disputed.
Metrics to watch
- Percent past due: past-due balance ÷ total AR (65.3% in the example — high).
- Days sales outstanding (DSO): AR balance ÷ credit sales for the period × days in the period. For example, $19,900 ÷ $58,000 of credit sales over 92 days × 92 ≈ 31.6 days.
- Concentration: share of AR owed by your top three customers.
- 90+ bucket trend: if it keeps growing, discuss with your accountant whether some balances should be reserved or written off.
Put expected collections into your 13-week cash flow forecast in the week you realistically expect payment, not on the due date. Customer contact details can come from your CRM spreadsheet, and new invoices from the invoice template.
Common mistakes
- Overwriting the paid amount instead of logging each payment — you lose the history.
- One payment term for everyone: due dates and alerts come out wrong.
- Several invoices on one row: aging can’t be calculated.
- Never reconciling to the books: the sheet and the AR account drift apart.
- Broken formulas: lock calculated columns — see how to protect formulas in Excel.
FAQ
How do I apply a payment that covers several invoices?
Split it across rows in the Payments sheet, one per invoice, applying it to the oldest invoice first unless the customer specifies otherwise.
Can I use the same template for accounts payable?
Yes — swap customers for vendors and “paid” for “paid out” to schedule your own bills and avoid late fees.
Should I offer early-payment discounts?
Terms like “2/10 net 30” can speed up collection but cost margin. Calculate the effective annual cost of the discount before offering it, and track whether customers actually take it.
Does the template work in Google Sheets?
Yes. XLOOKUP, SUMIFS, IF, TODAY and conditional formatting all work in Google Sheets.
Next step: enter your open invoices, log partial payments and review the aging table to pick the three customers to contact this week. More finance tools are in the Small Business Finance hub.