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.
Tap the download button which will import a copy of the accounts receivable ledger to your computer.
Below is a screenshot of the what the ledger looks like and further down are some explanations of how to use it.
After downloading the document and saving it to your computer you can enter your business name into the cell that says [enter your business name here] - just over-type the wording.
You can see there are two rows of data already in the spreadsheet. These are just examples for you to look at and you can easily overtype them to get rid of them. Now you can enter your own information.
A Column - Enter the date of the invoice
B Column - Type in the invoice number
C Column - This is where you enter your customer name
D Column - You can input the total amount of the invoice
E Column - The date you expect your client to pay
The total of 10,620 heads up the F Column and is the total of all invoices that are due for payment. This column has formulas in it so it is very important that you do not type anything into the cells in this column because you will end up removing the formulas.
The Current Date is automatically set by Excel using the date setting on your computer.
F Column - The balance due amounts are automatically calculated by taking the sum of the payment amounts away from the total invoice amount found in Column D.
G to R Columns - Every time your customer makes a payment you will enter the payment amount into the next available column - so if H and I already had payments entered in you would enter the payment into column J.
Column G - Red Triangle - If you are familiar with Excel you will know that the red triangle indicates a comment. You can insert, edit or delete comments by clicking your right mouse button. This is great for entering the date a payment was received so you can easily track how fast the customer is paying off their invoice.
If the month is May and you are issuing invoices to customer all through May then you should save this ledger into your computer calling it something like Accounts Receivable - May 2015.
Once May is finished and June comes around, open up a new spreadsheet and call it Accounts Receivable - June 2015.
For those invoices that have not been paid by the end of May, simply copy the rows those invoices are in from May and paste them into June.
In your Excel ledger at the bottom (see screenshot below) you will see one tab named Debtors, this is where the above spreadsheet is stored.
The other tab is called Notes and if you click into this you will see the spreadsheet detailed below.
Once again, there is data entered here as an example for you to view only. You can over type the detail and enter with your own.
This page is a great place to record what your clients have said when you have called them about any of their overdue payments. Take a look at our Accounts Receivable Collections page that explains in more detail how to collect overdue payments from your customers.
You may also like to see Accounts Receivable Procedures for more information on how to initiate customer accounts and maintaining this system.