Customer Outstanding & Receivables Ageing Report in Excel (Free Template)

receivables ageing excel dashboard

Advertisement

Quick answer: A customer outstanding report lists every unpaid bill, who owes it and for how long. Receivables ageing splits that outstanding into time bands: not yet due, 1–30, 31–60, 61–90 and over 90 days past the due date. The older a bill, the harder it is to collect, so you chase the oldest and biggest amounts first. The free Xplorexcel tracker does this automatically from your bills and payments. It also matches payments bill-wise, prints a customer statement and writes a ready-to-send WhatsApp payment reminder.

Key takeaways

  • Enter bills and payments. The due date, balance, days overdue and age band are worked out for you.
  • Bill-wise matching: mark a payment against a bill, or leave it “on account” and it clears the oldest bills first.
  • Customer-wise ageing (not due, 1–30, 31–60, 61–90, 90+), with credit-limit alerts.
  • A printable statement and a WhatsApp reminder for any customer, with your UPI ID.
  • A dashboard showing total outstanding, overdue %, amounts due in the next 7 days, collections and DSO.

↓ Jump to the free download

FOR SHOPS & TRADERSBills, stock and GST returns — all in one Excel file.XploreBooks makes GST bills with barcode, updates stock and party khata, and gives GSTR-1, GSTR-3B, P&L and Balance Sheet.See XploreBooks — from ₹499 →⚡ Instant download · ♾️ Lifetime access · 💳 Pay onceXploreBooks

Receivables ageing Excel dashboard with total outstanding, overdue amount, DSO and ageing chart
The dashboard: outstanding, overdue %, amounts due this week, collections, DSO and the customers to chase first.

What is receivables ageing?

An ageing report (also written aging) groups the money customers owe you by how long it has been overdue. The bands this template uses are the common ones:

Age bandMeaningWhat to do
Not dueThe bill is still within the customer’s credit periodNothing yet. Remind a few days before the due date
1–30 daysJust missed the due dateSend a polite reminder
31–60 daysClearly lateCall, and send the statement
61–90 daysSeriously latePut further credit on hold
Over 90 daysAt risk of becoming a bad debtFollow up personally, and consider stopping supplies

Days overdue = ageing date − due date, where due date = bill date + the customer’s credit days. Example: a bill dated 8 September with 30 days’ credit is due on 8 October. On 15 October it is 7 days overdue and falls in the 1–30 band.

Accounts receivable ageing report in Excel by customer with 0-30, 31-60, 61-90 and over 90 days
Customer Ageing: every customer’s outstanding split by age, with the oldest overdue bill, last payment and credit-limit status.

Bill-wise payment matching (FIFO)

Customers often pay a round amount without saying which bill it is for. In the Receipts sheet you have two choices:

  • Against bill no.: type the bill number, and the payment reduces that bill only.
  • Leave it blank (on account): the payment is adjusted against the customer’s oldest unpaid bill first, then the next oldest, and so on. This is the first-in, first-out method most businesses and auditors follow.

Example from the sample file: Sharma Electricals has bills of ₹42,300 (12 July) and ₹19,970 (25 August), and paid ₹30,000 and ₹25,000 on account. The ₹55,000 first clears the July bill in full, and the remaining ₹12,700 goes against the August bill, leaving ₹7,270 outstanding on it, 21 days overdue.

Customer invoice tracker in Excel with due date, amount received, balance, days overdue and ageing
Invoices: the due date, received amount, on-account adjustment, balance, days overdue and status fill in by themselves.
Payment received register in Excel with mode, against bill number and on-account entries
Receipts: cash, UPI, bank, cheque, or discount / credit note, against a bill or on account.

Outstanding bills, most overdue first

The Outstanding Bills sheet lists every unpaid or part-paid bill, sorted by days overdue, with the customer’s phone number. Bills over 30 days late are highlighted, and bills over 90 days late turn red. Work down this list for your follow-up calls.

Advertisement
Outstanding bills report in Excel sorted by days overdue with balance and ageing
Outstanding Bills: the collection list, most overdue first.

Customer statement and WhatsApp payment reminder

Pick a customer on the Statement sheet to get a one-page statement of their unpaid bills, ready to print or save as PDF. Below it, a payment reminder is written for you with the total pending, the overdue amount, the oldest overdue bill and your UPI ID. Copy it into WhatsApp or SMS.

Customer outstanding statement in Excel with WhatsApp payment reminder message
Statement: the bill-wise balance for one customer and a ready-made payment reminder.

DSO: how many days of sales are stuck with customers?

Days Sales Outstanding (DSO) = total outstanding ÷ average daily credit sales. The template uses the last 90 days of bills: DSO = outstanding ÷ (bills of the last 90 days ÷ 90). In the sample, ₹2,88,510 outstanding against about ₹4.9 lakh of 90-day sales gives a DSO of 53 days. If your usual credit period is 30 days, customers are paying about three weeks late on average. A rising DSO is an early warning of cash-flow trouble.

How to use the receivables tracker (5 steps)

  1. Setup: enter your business name, WhatsApp number, UPI ID and default credit days. Leave “Ageing as on” blank to age up to today.
  2. Customers: add each customer, with their own credit days and credit limit if different.
  3. Invoices: enter every bill (total including GST). Enter old unpaid bills with their original dates.
  4. Receipts: enter every payment. Add the bill number if you know it, otherwise leave it blank.
  5. Follow up: check the Dashboard and Outstanding Bills, then send statements and reminders.

Download the Customer Outstanding & Receivables Ageing Tracker (Free)

File: Customer Outstanding and Receivables Ageing Tracker 1.0 · Format: .xlsx · Macros: none · Price: free.

Free download · one quick step

Unlock this free template

This one is a full working system, not a one-page format. It stays free. I only ask for a way to reach you, because:

  • It has several linked sheets with a filled-in sample, so you see it working before you type your own entries.
  • The formulas are locked so one wrong click can’t break your totals. If something still goes wrong, you’ll know where to ask.
  • Tax and labour rules change. When GST, TDS or payroll rules change, I send the corrected file to the number or email you give here.
  • No spam. A few messages a month at most, unsubscribe any time.

Made by Amanpreet Kaur Bharaj, XplorExcel, Hoshiarpur. You only need to do this once on this device.

Was this template useful?

The sample has 8 customers, 25 bills and 13 payments. Clear the yellow cells on Customers, Invoices and Receipts and enter your own.

Outgrown the free sheet? Here’s what XploreBooks adds

Free sheetXploreBooks
GST bills with barcode scan—✓
Stock and party khata update by themselves—✓
GSTR-1, GSTR-3B, P&L and Balance Sheet—✓
One-click new year—✓
Purchase & sales registers—✓
Fill-in forms & one-click PDF (macro edition)—✓

⚡ Instant download · ♾️ Lifetime access · 💳 Pay onceGet XploreBooks — from ₹499 →

Advertisement

Frequently asked questions

How do I make an ageing report in Excel?

List each unpaid bill with its due date and balance. Work out days overdue as the report date minus the due date, then group the balances into bands (0–30, 31–60, 61–90, 90+) with SUMIFS for each customer. This template does all of it, and also handles part payments and on-account payments.

What is the difference between “not due” and “overdue”?

A bill is not due while it is still inside the credit period you gave the customer. It becomes overdue the day after the due date. Only overdue amounts need chasing.

What does “on account” payment mean?

A payment the customer made without naming a bill. The template applies it to that customer’s oldest unpaid bills first, so your ageing stays realistic.

How do I record a discount or returned goods?

In Receipts, choose the mode “Discount / Credit note” and enter the amount. Fill in the bill number if it relates to one bill.

What is a good DSO?

Close to your normal credit period. If you give 30 days’ credit, a DSO of 30–40 days is healthy. A DSO far above your credit days means customers are paying late.

Can I use this in Google Sheets?

Yes. Upload the .xlsx file to Google Drive and open it with Google Sheets. It has no macros.

More free business templates from Xplorexcel

Last reviewed: October 2026.

Advertisement

Advertisement

Amanpreet Kaur Bharaj
I'm Amanpreet Kaur Bharaj from Hoshiarpur, Punjab. I've worked in Excel for about 12 years, and since 2022 I've run my own ethnic-wear boutique, Punjabi Libas, on Excel files I built myself. XplorExcel is where I share those files: free templates for everyday business work, and ready-made Excel software for shops, boutiques, clinics, dairies, mandis and offices. Every file comes with a filled-in sample so you can see how it works. Stuck with one? Message me and I'll help.