... ...

Accounts Receivable Ledger

Use this free accounts receivable ledger in Excel to enter all your business sales invoices to your customers and to track payments your customers make against them.

This ledger should only be used for customers whom you allow to buy products and services from you now but only have to pay for later. 

If the customer buys something from you and pays for it immediately it is unnecessary to enter it into the workbook because they owe nothing to you.

Free Excel Cash Book

Click above button to get our most popular Excel Template for easy bookkeeping! It's free.

Accounts Receivable Ledger Download

Tap the download button which will import a copy of the Excel accounts receivable ledger to your device. 

Below is a screenshot of the what the ledger looks like and further down are some explanations of how to use it.

Download Accounts Receivable Template

Accounts Receivable Ledger Example

What you get with this Receivables ledger

  • A debtors sheet with 2000 formatted rows and 31 formatted columns
  • An examples sheet to show you how to enter your sales invoices details and payments as well as credits or refunds.
  • A current total of outstanding sales invoices at the top of the sheet which updates automatically
  • Automatic calculations to update the totals due on each invoice after you enter customer payments
  • The ability to filter information so you can, for example, only see unpaid invoices.

After downloading the document and saving it to your computer you can enter your business name into the cell that says [your business name] - just type over the wording. There are some great tips in our computer filing system article to show you how to file your digital documents.

First Six Columns

Accounts Receivable Ledger First Six Columns

When you issue a sales invoice to your customer enter the following information into your ledger starting in Row 4 of the Debtors sheet:-

  1. Invoice Date: Enter the date showing on the invoice
  2. Invoice Number: Type in the invoice number
  3. Customer's Name: Enter the name of the customer
  4. Description: Put a brief description of the type of document ie sale or credit
  5. Total Amount: The full amount showing on the invoice that the customer must pay.
  6. Due Date: The date by which your customer is expected to pay you.

Accounts Receivable Ledger
Remaining Columns Explained

In our example below, the total of 210.00  is the overall sum of all invoices that are still due for payment. This amount reduces as payments are received or increases as new invoices are added.

The cells in the balance due column indicate how much is left to pay for each invoice - this is calculated automatically because of the formulas we have included. The formula takes the Total Invoice Due amount and subtracts any payments made already. 

The columns headed Payment and Date are where you will enter each payment received from your customer and date the payment is received. 

Accounts Receivable Ledger Payments Columns

Accounts Receivable Ledger
How to Use from Month to Month

The Examples sheet in this template shows how to enter the invoice details and the payments, as well as credits and refunds.

Every time you issue an invoice to a customer enter it in the next available (blank) row on the Debtors sheet.

Every time a customer makes a payment, look through the Debtors sheet for their invoice and record the payment and the date it was received.

When a customer has fully paid you could hide the row containing the paid invoice to keep only the unpaid invoices to view. 

Every time you enter new data to the spreadsheet, remember to Save before exiting unless your Excel settings automatically save the template.

My recommendation is to save the spreadsheet once a month with a new name.

For example, if it is the month of May and you are entering all May dated invoices then save your spreadsheet with the name Accounts Receivable - May 20xx. 

When June starts save a fresh copy of this workbook and start entering your June sales invoices and any payments received. Call it Accounts Receivable - June 20xx

Keeping copies like this ensures that if you make any huge mistakes in your current spreadsheet you always have last month's version to start afresh again.

Accounts Receivable Ledger
Notes Sheet

The ledger contains a Notes sheet where you can record all details of phone calls, emails or texts between you and the customer when you are following up any overdue payments.

This is something you need to do regularly (maybe even once a week) to avoid landing in financial difficulties because of unpaid sales invoices.  As soon as you have updated the Debtors sheet with all the latest customer payments, run your eyes down the list and highlight the ones you need to contact about any overdue accounts.

First check your Notes sheet for what was said last time you contacted them. Then contact them again now and discuss appropropriately.

Once you have contacted them, write down on the Notes sheet what was said, when payment was promised etc. Diarise to follow up and check that a promised payment has been received by the due date. If not, contact the customer again and make another note on the Notes sheet.

This is not an easy or comfortable task and you may be better off outsourcing it to professionals in order to keep your relationship with your customer as intact and happy as possible.

Take a look at our Accounts Receivable Collections page that explains in more detail some examples of how to contact overdue customers.

Accounts Receivable ProceduresAccounts Receivable Procedures

You may also like to see Accounts Receivable Procedures for more information on how to initiate customer accounts and maintaining this system.

Share this page

Facebook Comments

Have your say about what you just read!
Leave me a comment in the box below.