An accounts receivable (AR) aging report lists every unpaid client invoice and sorts it by how far past due it is: current, 1 to 30, 31 to 60, 61 to 90 and over 90 days.
Read it from the oldest column back, client by client. Anything over 60 days needs a named action this week, and a total that keeps growing month after month points to a billing or collection problem.
Step by step
1Run it as of the last day of the month
Use the same date every month, after the month is closed, so one report compares fairly with the last. Age by due date unless your accountant prefers invoice date.
2Check the total
The report total should match accounts receivable on your balance sheet, or the unpaid total on your invoice log. If it does not, look for payments not applied to an invoice and credits not matched.
3Read the oldest column first
Start with over 90 days, then 61 to 90. These are the invoices least likely to be paid without action.
4Read it client by client
Look at each client's row across all the columns. One client with invoices in three columns is a relationship conversation, not three reminders.
5Look for odd rows
Negative amounts (credits or overpayments), partial payments, retainers shown as open, and invoices that were never sent. Fix them before you chase anyone.
6Give every row over 30 days an action and an owner
Write the next step next to the row: a reminder, a call, a payment plan or a notice. Name who does it and by when.
7Compare with last month
Note the total, the share over 60 days and which clients moved into an older column. The trend tells you more than any single month.
Free tool
AR aging builder
Paste your unpaid invoices, one per line, and pick the as-of date. It sorts them into aging columns by client and flags what needs action. Nothing leaves your browser.
Client
Current
1 to 30
31 to 60
61 to 90
Over 90
Total
Ages by due date. Paste from your invoice log or a CSV export; commas or tabs both work. Negative amounts are treated as credits.
What each column means
Column
What is in it
What it usually needs
Current
Invoices not yet due
Nothing, or a check a week before the due date
1 to 30
Up to a month past due
The reminder schedule
31 to 60
One to two months past due
A reason, a payment date, a senior contact
61 to 90
Two to three months past due
A written plan, or notice under the agreement
Over 90
More than three months past due
A final notice, legal advice, and a talk with your accountant
Clients and amounts as of the last day of the month.
Client
Current
1 to 30
31 to 60
61 to 90
Over 90
Total
Client A
8,000
0
0
0
0
8,000
Client B
5,000
5,000
5,000
0
0
15,000
Client C
0
0
0
0
3,500
3,500
Client D
-1,200
0
0
0
0
-1,200
Client B has paid nothing for three monthly invoices. That is one conversation, using the statement email below, not three separate reminders.
Client C is small but over 90 days. Check whether it is real (a missed payment match, a disputed line) before you chase it.
Client D shows a credit. It may be an overpayment or a retainer recorded as a payment. Fix it with your bookkeeper; a credit hides real unpaid amounts in the total.
Where to get the report
QuickBooks Online and Xero both include an aged receivables report; the exact name and options vary. If your invoices live in a sheet, the formulas in template 1 below build the same report, and the tool above builds it from a paste.
Whichever you use, set the as-of date to the month end. A report run on a random Tuesday is hard to compare with the last one.
From aging to action
The report is only useful if every row over 30 days leaves the meeting with a next step and an owner. That list then drives the week's reminders, which is the part worth automating. The invoice chaser drafts those reminders for review from your own invoice list.
For a single number to track month to month, days of fees in accounts receivable (DSO) is explained in the financial scorecard. If invoices are slow because they go out late, start with progress billing.
Copy-paste templates for the monthly AR review
The formulas to build an aging report from an invoice log, a review note and a statement email for a client with several open invoices. Replace everything in [square brackets].
When: Set up once in the sheet you already use for invoices.
Subject: Due date in column G, status in column H, amount in column F
Days past due (column I):
=IF(H2="Paid", "", [as-of date cell] - G2)
Bucket (column J):
=IF(I2="", "", IFS(I2<=0, "Current", I2<=30, "1-30", I2<=60, "31-60", I2<=90, "61-90", TRUE, "Over 90"))
Total per bucket (one cell each):
=SUMIFS(F:F, J:J, "Current")
=SUMIFS(F:F, J:J, "1-30")
=SUMIFS(F:F, J:J, "31-60")
=SUMIFS(F:F, J:J, "61-90")
=SUMIFS(F:F, J:J, "Over 90")
Put the as-of date in one cell (the last day of the month) rather than using TODAY(), so the report does not change after you run it.
2. Monthly AR review note
When: After the month closes. Keep each month's note so you can compare.
Subject: AR review as of [date]
Total open: [total] Last month: [total]
Over 60 days: [amount] ([share]% of total) Last month: [share]%
Matches balance sheet AR: [yes / no, difference and reason]
Over 90 days:
- [Client], invoice [number], [amount]: [next step], [owner], by [date]
61 to 90 days:
- [Client], invoice [number], [amount]: [next step], [owner], by [date]
31 to 60 days:
- [Client], invoice [number], [amount]: [next step], [owner], by [date]
Clients that moved to an older column: [names]
Odd rows fixed: [credits applied, retainers reclassified, invoices re-sent]
3. Statement email for a client with several open invoices
When: When one client has invoices in more than one column.
Subject: [Client name]: statement of open invoices as of [date]
Hi [first name],
Attached is a statement of the open invoices on your account as of [date]:
- Invoice [number], [project], [amount], due [due date]
- Invoice [number], [project], [amount], due [due date]
- Invoice [number], [project], [amount], due [due date]
Total open: [total]. Could you check this against your records and tell me when we can expect payment? If any invoice is missing on your side, I will send a copy today.
I will call you on [day] to go through it.
[your name]
[firm name]
Frequently asked questions
What is an AR aging report?
A list of every unpaid client invoice, grouped by how long past due it is, usually in 30-day columns. AR stands for accounts receivable: money clients owe the firm for work already invoiced.
Should I age invoices by invoice date or due date?
Due date is easier to act on, because current then means not yet due. Invoice date shows how long money has been out, regardless of terms. Pick one with your accountant and keep it the same every month.
What does a good aging report look like?
Most of the total in the current column, little or nothing over 60 days, and no client in three columns at once. We do not quote an industry benchmark here, because we have not found one for small architecture firms we would trust.
How often should a small firm run it?
Monthly, after the close, as the review. Check the over 30 day rows weekly if you are chasing several invoices at once.
What should I do about invoices over 90 days?
Confirm they are real, find the reason, and move to the escalation steps in the unpaid invoice guide: a senior contact, a written plan or a formal notice. Ask your accountant whether any should be written off, and a lawyer before any legal step.
Archon & Partners