Aged receivables report template and how to read it

An aged receivables report answers one question: of the money your customers owe you, how much is on time and how much is late? It lists what is still owed on each open invoice and sorts those balances by how overdue they are, so you can tell the invoice you can leave alone from the one to chase today. This page explains the report, what each age column means, and how to fill in a free template you can download below.

The template is a CSV, so it opens in any spreadsheet. The figures in it, and in the worked example further down, are made up to show how the columns add up. Replace them with your own.

What an aged receivables report is

Receivables are the invoices a customer has not paid yet. Aging them means grouping each unpaid balance by how long it has been past its due date, instead of looking at one big total. A total tells you how much you are owed; the aging tells you how much of it is overdue, and by how far. That is the difference between a number you note and a number you act on, because a balance that is three months late is a different conversation from one that is not due until next week.

The report shows what is still owed on each invoice, not the original amount. An invoice a customer has part paid counts only for what is left, so the report never asks for money that is already in the bank. Drafts and settled invoices are left out, because neither is money owed to you right now.

What the age columns mean

The template sorts every balance into five columns, by how many days past due the invoice is. These are the standard aging buckets:

current

Not yet due. Owed, but not late.

1-30

1 to 30 days past the due date.

31-60

31 to 60 days past the due date.

61-90

61 to 90 days past the due date.

91+

Over 90 days past due, the write-off conversation.

Current is money owed but not yet late, so it needs no chasing. The split at 90 days matters because that is usually the point you decide whether to make another phone call or write the balance off, and a report that stops at 60 cannot tell a two-month-old debt from a year-old one.

How to fill in the template

One row per open invoice. Fill in the customer, the invoice number, the invoice total and the amount paid so far. The still-owed column is the total less what has been paid. Then put that still-owed amount in the one age column that matches how late the invoice is: count the days from its due date to today, and use the bucket that range falls in.

• Skip drafts and anything already paid in full: a report of what you are owed holds only open balances.
• Put each balance in a single age column, not several, so each row’s age columns add up to its still-owed amount.
• Total each age column at the bottom to see how much of your receivable is current and how much is overdue.
• Date the report, because an invoice moves to an older column as the days pass, so the same book looks different next week.

A worked example

Say three customers owe you on five open invoices. The figures below are made up, to show how the worksheet adds up. One invoice, INV-1002, has been part paid: $1,500 billed with $500 paid, so $1,000 is still owed and that is what lands in its age column.

The five invoices come to $8,000 still owed. Of that, $2,000 is current and the other $6,000 is overdue, with $3,000 more than 90 days late. Reading across, the overdue total is the sum of every column but current, and reading down, each age column’s total is the balances that fall in that range. The 91+ column is the one to look at first.

Keep reading

Once you know who is overdue, the next step is asking for the money. The payment reminder email templates cover what to say at 7, 30, 60 and 90 days late, and the guide to invoices and statements explains the document you send a customer who owes you on more than one invoice.

How Bevr does this for you

If you would rather not keep this worksheet by hand, Bevr can age your receivables for you. Ask "who owes me money?" and Bevr answers with a real figure, not an estimate. It reads every invoice, subtracts what has already been paid, leaves out drafts and settled invoices, and ages the rest. You get the total owed, aged into current, 1-30, 31-60, 61-90, and 91+ days past due, the same buckets this worksheet uses. Part payments are handled properly, so you never chase money already in the bank. Break it down by customer, project, status, age, or the tags you use. Read more in the features, or see what Bevr costs.