Spreadsheets

Receivables ageing in Excel: 0-30, 31-60 and 61-90 day buckets

Build an accounts receivable ageing report in Excel: days overdue from the due date, SUMIFS buckets of 0-30, 31-60 and 61-90 days per customer, plus a pivot option.

Editorial illustration for Receivables ageing in Excel: 0-30, 31-60 and 61-90 day buckets

To build a receivables ageing report in Excel, calculate how many days each unpaid invoice is past its due date, then add up the outstanding amounts for each customer in buckets such as 0-30, 31-60 and 61-90 days. SUMIFS does the adding, and a PivotTable can do the same job with no formulas for the buckets.

What columns does the invoice list need?

  • A: customer
  • B: invoice number
  • C: invoice date
  • D: due date
  • E: invoice amount
  • F: amount received so far
  • G: outstanding = E2-F2
  • H: days overdue = $R$1-D2

Put the as-of date in R1. Type a fixed date such as the last day of the month when you are producing a month-end report, or use =TODAY() for a live view. A fixed date keeps last month's ageing from changing when you reopen the file. A negative number in H simply means the invoice is not yet due.

Which SUMIFS formulas build the buckets?

List each customer once in column J. Then in the bucket columns, filled across and down:

  • Not yet due (K): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, "<0")
  • 0-30 days (L): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, ">=0", $H$2:$H$300, "<=30")
  • 31-60 days (M): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, ">=31", $H$2:$H$300, "<=60")
  • 61-90 days (N): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, ">=61", $H$2:$H$300, "<=90")
  • Over 90 days (O): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, ">90")

Each condition pair says which days-overdue range to include. Add a total column that sums the five buckets, and compare it with =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2). If they differ, an invoice has fallen between your boundaries, which usually means a blank or text date.

How does it look on a few invoices?

Take an as-of date of 30 November and four made-up unpaid invoices for different customers:

InvoiceDue dateDays overdueOutstandingBucket
INV-40110 Nov2040,0000-30
INV-4025 Oct5625,00031-60
INV-40315 Sep7618,00061-90
INV-40415 Dec-1530,000Not yet due

The days are counted from the due date: 5 October to 30 November is 26 days left in October after the 5th plus 30 in November, which is 56. The grand total outstanding is 40,000 + 25,000 + 18,000 + 30,000 = ₹1,13,000, and only ₹83,000 of it is actually overdue.

Is there a PivotTable alternative?

Yes, and it needs only one helper formula. Add a bucket label in column I:

=IF(H2<0, "Not yet due", IF(H2<=30, "0-30", IF(H2<=60, "31-60", IF(H2<=90, "61-90", "Over 90"))))

Then select the list, choose Insert, PivotTable, put Customer in Rows, Bucket in Columns and Outstanding in Values as Sum. Refresh the pivot after changing the as-of date. The labels may sort alphabetically, so drag the columns into the order you want.

What do you do with the numbers?

Read the report from the right. Anything in 61-90 or over 90 deserves a personal call, not another automated reminder. The 31-60 bucket is where a polite email still works. If one customer holds most of the overdue total, look at the account before you look at the formulas.

Before you share the report

  1. Check that no outstanding amount is negative, which would be an overpayment or a credit note recorded wrongly.
  2. Make sure the totals agree with your ledger's receivables balance.
  3. Write the as-of date at the top.
  4. Remove paid invoices, or keep them with an outstanding of 0 so they drop out of the buckets.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports