Finance Templates

Accounts Receivable Ageing Report: Format, Buckets and Free Excel Template

Updated 5 min read
Quick answerAn accounts receivable ageing report lists unpaid client invoices by how long they are overdue, usually in buckets of not yet due, 1-30, 31-60, 61-90 and 90+ days, with a total per client. Review it weekly: invoices moving into 31-60 days need a call, and 61+ days are at risk.
On this page 7 sections

An accounts receivable ageing report answers one question: of the money clients owe you, how much is fresh and how much is getting old? It lists every unpaid invoice, works out how many days each is past its due date, and groups them into age buckets. Five minutes with it every week tells you who to chase, who to call, and which client is quietly turning into a bad debt.

The standard ageing buckets

Receivables ageing buckets and what to do in each
BucketMeaningAction
Not yet dueInvoiced, inside the payment termsHeads-up reminder a few days before the due date
1-30 days overdueLate, usually a process delayPolite reminders, ask for a payment date
31-60 days overdueA real problem is startingPhone call, escalate to the decision maker
61-90 days overdueAt riskFinal reminder, consider pausing work
90+ days overdueLikely dispute or cash trouble at the clientFormal recovery steps; talk to your CA about provisioning

Some businesses age invoices from the invoice date instead of the due date. Either works, but use one method consistently and say which one in the report heading. Ageing from the due date is better for collections, because "30 days overdue" means the same thing for a 15-day client and a 45-day client.

Ageing report format

A useful ageing report has two parts: an invoice-level list and a client-wise summary.

Invoice-level list

Columns for the invoice-level ageing list
ColumnExample
ClientKestrel Media
Invoice numberINV/26-27/041
Invoice date01-09-2026
Due date01-10-2026
Invoice amount (incl. GST)Rs 1,18,000
Received so farRs 50,000
OutstandingRs 68,000
Days overdue27
Bucket1-30
Last follow-up and next stepCalled 20 Oct, promised by 31 Oct

Client-wise summary

One row per client, with the outstanding amount in each bucket and a total. This is the view to look at first, because collections happen client by client. A client with Rs 4 lakh spread across five invoices in the 31-60 bucket needs a different conversation from a client with one small invoice two days late.

How to build it in Excel

  1. Keep one row per invoice with client, invoice number, invoice date, due date, amount and received.
  2. Outstanding: =E2-F2 (amount minus received).
  3. Days overdue: =IF(G2<=0,"",MAX(0,TODAY()-D2)). Blank once fully paid.
  4. Bucket: =IF(G2<=0,"Paid",IF(TODAY()<=D2,"Not yet due",IF(H2<=30,"1-30",IF(H2<=60,"31-60",IF(H2<=90,"61-90","90+")))))
  5. Client-wise summary: on a second sheet, list each client once and use =SUMIFS(Invoices!G:G,Invoices!A:A,$A2,Invoices!I:I,B$1), where row 1 holds the bucket names.
  6. Highlight risk: conditional formatting in red for 61-90 and 90+.

The free template above already has all of this set up, including a "% over 60 days" figure per client.

How to read the report

  • Watch the share over 60 days. If more than about a tenth of what you are owed is older than 60 days, collections need attention now, not next month.
  • Look for movement, not just totals. An invoice that moves from 1-30 to 31-60 without any payment is the signal to call.
  • Compare clients with their terms. A client on 45-day terms will always look older than one on 15-day terms if you age from the invoice date.
  • Pair it with DSO. The ageing report shows which invoices are stuck; Days Sales Outstanding (DSO) shows whether collections overall are getting faster or slower.

What to do with each bucket

Use a fixed follow-up schedule so every invoice gets the same treatment. Our payment reminder templates map to these buckets, from a friendly heads-up to a final notice. For invoices past 90 days, see how to recover unpaid invoices from clients in India.

Common mistakes

  • Building the report once a month at closing instead of every week.
  • Ignoring part payments, so an invoice looks fully overdue when most of it is paid.
  • Mixing invoices with and without GST in the same totals without saying so.
  • Looking only at the total, never at the client-wise view.
  • Keeping invoices that were cancelled or replaced by a credit note in the list.

Frequently Asked Questions

What is an accounts receivable ageing report?

A list of unpaid customer invoices grouped by how long they have been outstanding, usually in buckets of 0-30, 31-60, 61-90 and 90+ days, with totals for each client.

Should ageing be calculated from the invoice date or the due date?

Either is acceptable if used consistently. The due date is better for chasing payments; the invoice date is common in audit and lender reports.

How often should I run an ageing report?

Weekly for collections. Monthly for management review and when your CA or bank asks for it.

What is the difference between a debtors ageing and a receivables ageing report?

None. "Debtors" is the term used in Indian accounting and Tally; "accounts receivable" is the international term for the same thing.

Can I get an ageing report from Tally?

Yes. Tally's bill-wise outstanding reports show ageing for parties if bill-wise details are maintained. The Excel template is useful when your invoices are not in Tally or when you want a client-wise collections view.

Run a service business? See every client's money on one screen.

MoneyGence shows who has paid, who is overdue and by how many days, with GST-ready invoices, bank reconciliation and renewal alerts alongside your Finance Templates work. Free for up to 2 clients, no credit card needed.

Start free with 2 clients See how it works →