Preview Filled with sample values. Click to enlarge.
An accounts receivable (A/R) ledger tracks money customers owe you, invoice by invoice. In this template you enter invoices on the Receivables sheet and money received on the Payments sheet; payments are matched by invoice number, and balance and status (Open, Partly paid, Paid, Overdue) are calculated automatically. The By customer sheet breaks each customer's balance into aging buckets.
What you enter and what is automatic
| Column | What it holds | Input |
|---|---|---|
| Invoice date · invoice no. | The date and a unique number of each invoice | You |
| Customer · contact | Who owes the money and whom to call | You |
| Description · amount | What the invoice is for and the amount including tax | You |
| Due date | The payment due date from your terms | You |
| Received · balance | Sum of all payments with the same invoice no., and what is left | Automatic |
| Status · days overdue | Open, Partly paid, Paid, Overdue and days past due | Automatic |
How to use the Excel template
- One row per invoiceEach time you invoice, add the invoice date, number, customer, amount and due date on the Receivables sheet.
- One row per paymentWhen money arrives, add the payment date, invoice number and amount on the Payments sheet. For split payments, add several rows with the same invoice number.
- Status updates itselfReceived, balance, status and days overdue change immediately. Unpaid invoices past their due date turn red.
- Review by customerThe By customer sheet shows each customer's invoiced, received and open amounts and the aging buckets: not yet due, 1-30, 31-60, 61-90 and over 90 days.
- Follow up oldest firstCall the customers with the oldest balances first and note the promised payment date in the Notes column.
How to read the aging report
Aging splits open balances by how many days they are past the due date. The same amount means something very different when it is not yet due than when it is over 90 days late.
The total row at the bottom of the By customer sheet shows the aging profile of your whole receivables at a glance.
Tips to reduce overdue invoices
- Set the due date when you invoice; without it, nothing can be flagged as overdue.
- Record payments the day they arrive, so you always know who still owes what.
- Match by invoice number: ask customers to quote it on their payment.
- Review overdue items weekly using the totals at the top of the Receivables sheet.
Exchanging invoices and payment notices by email?
AiutoForm reads invoices from email with AI and logs them in an invoice sheet. When you enter a payment it calculates the balance and status (unpaid, partly paid, paid, overdue), and it puts due dates on your Google Calendar.
See the food manufacturer example or try the demo.
Frequently asked questions
How do I record a payment that covers part of an invoice?
Add a row on the Payments sheet with that invoice number and the amount received. Add another row when the rest arrives; the ledger adds them up.
The Payments sheet says "Invoice no. not in ledger". Why?
The invoice number on that row does not match any number on the Receivables sheet. Check for typos or extra spaces.
Does it work in Google Sheets?
Yes. Balance, status, aging and the red highlighting all work in Google Sheets.
Can I use this template for my business?
Yes. It is free to download, change and use.
Will it work with your inbox?
Tell us which email service and document formats you use, and we will show you how it fits.