Accounts receivable template in Excel: ageing and collection

Accounts receivable template in Excel: ageing and collection

Selling on credit is one of the most common decisions in a small business and, at the same time, one of the most delicate. Once you hand over the goods and agree to be paid later, the sale is done on paper but the money has not reached the till; in between those two moments sits your receivables ledger. If nobody keeps track of who owes what and since when, the balance turns into fog: the big customers are remembered, the small ones are forgotten, and the business ends up feeling profitable while the bank account says otherwise.

This accounts receivable template in Excel is built to control your ledger document by document: due dates, payments on account, days overdue and the status of each invoice, with the balance calculated automatically. It is, quite simply, the record of the money that customers owe you. The file is downloaded, opened and put to work the same day, with no formulas to invent and no columns to build by hand.

What accounts receivable are and who this format is for

Accounts receivable are the amounts your customers still owe you for invoices, bills or delivery notes already handed over and not yet paid. This format gathers them all onto a single sheet, sorted by customer and by due date, so you can read the whole ledger in seconds and answer the three questions that come up in a business every single day: how much am I owed in total, how much of that is already overdue, and who should be called first.

It is designed for businesses that sell on credit as a matter of routine: distributors that leave goods out on eight or fifteen day terms, workshops, hardware stores, stationers and service providers that invoice companies and collect at month end; in general, any business where the sale and the payment do not happen on the same day. It also works when the volume is small: half a dozen unpaid invoices are enough to justify the record, because the problem with receivables is rarely their size, but the lack of memory.

One difference is worth clearing up from the start. Accounts receivable are the money third parties owe you, and the format in this article deals with exactly that. Its close relative, accounts payable, is the opposite: the obligations you have towards your suppliers. They are two lists worth reading together, but they are not the same list and they do not follow the same rules, because a customer gets chased and a supplier gets paid.

What the template includes

The file comes with a worksheet ready to fill in, with the calculation already solved in every column that depends on the data you type. This is what you find when you open it:

The sheet carries no macros and no passwords: it is an ordinary Excel file, with visible formulas, that you can adapt to your operation without depending on anyone. If you need more than two hundred rows, copy the last one and drag the formulas down; if you run several lines of business, add a cost centre column and filter by it.

The columns on the sheet

Every column has a specific job and, in practice, three of them carry the weight: the due date, the balance and the days overdue. This is the full list:

How the calculation works, with an example

Suppose on 5 September you issue invoice 1045 and agree payment for 5 October. On 20 October you cut off the ledger and review it. This is the document's journey:

Fifteen days overdue means fifteen days of money standing still: goods that left your warehouse and have not turned into cash. If you also hold three current invoices worth 2,400,000, the total ledger stands at 3,400,000 and the overdue part at 1,000,000, which means close to one third of your receivables sits outside the agreed terms. That single figure changes the conversation: it stops being a hunch and becomes a number you can act on.

How to fill it in, step by step

Order matters, because each column rests on the one before it. This is the recommended flow:

1. Write the cut-off date in the header. The days overdue and the status both build on it, so if you leave it empty the sheet loses its value.
2. Record each document with its customer, number, issue date, due date and value. One document, one row; never group several invoices into a single line.
3. Enter every payment in its column and let the balance work itself out. If the customer pays in instalments, add the payments in the same cell and keep the detail in your notes.
4. Read the days overdue column and the status column. Flag the overdue documents and sort them from the oldest to the newest.
5. Write down in Notes what you have done: the call, the date of the visit, the promise to pay and the agreement reached. Without that note, the next call starts from scratch.
6. Update the sheet every week. A ledger reviewed every fortnight ages on its own and ends up treating a customer five days late the same as one three months late.

Tips and common mistakes when managing receivables

Most ledgers that turn uncollectable are not born of customers acting in bad faith, but of handling habits that repeat themselves. These are the most common:

• Collecting against memory instead of the document: calling without the invoice number in front of you is the fastest way to lose an argument with a customer. You collect against the document, with its number, its date and its balance.
• Calling everyone every day: a ledger is worked by ageing ranges. Current documents are watched, those overdue by up to thirty days are called with firmness, and those beyond thirty days are escalated or negotiated in writing.
• Letting payments run unrecorded: when a part payment is not written down, the balance looks bigger than it is and the customer loses trust in your figures.
• Mixing receivables with payables: the fact that a supplier owes you does not entitle you to forget what you owe. They are two separate lists and each one is handled on its own.
• Not documenting payment agreements: a verbal promise to pay on Friday, with no date written down, is a promise that does not exist.
• Looking only at the total: knowing that you are owed a lot tells you nothing useful. What does help is knowing how much of that total is overdue and since when.

When to move to receivables software

The template handles manual control very well when volumes are moderate, but it has a clear limit: the file lives only on the computer of whoever keeps it, and nothing automatically links the invoice issued to the payment received or to the customer who signed for it. That job still rests on the discipline of people, and as the business grows, discipline becomes the most fragile link in the operation. A system such as Kardex Tauro takes receivables from the invoice onwards: every credit sale is recorded with its due date, every payment is deducted from the balance and every customer carries their payment history, so the receivables report builds itself and is always up to date.

To be blunt: if you issue few invoices a month, the Excel template is enough and it is the right first step towards order. Software earns its place when you want the figures not to depend on someone retyping them, when several salespeople serve the same customer, or when you need to know, without hunting for it, how much money you have sitting outside its terms.

Receivables are money that is already yours but still sits in someone else's hands. Keep it on an orderly sheet, review it every week by ageing range and always call with the invoice in hand. Download the template free of charge and start today with the simplest and most profitable control there is: knowing exactly who owes you and since when.