Try the sheet here, on the example
The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.
Example from the sheet
| As of | 46342 | Change the as-of date on the Settings sheet. Everything here is measured on that date. | ||||
|---|---|---|---|---|---|---|
| Total receivable | 32,370.90 | Invoiced this month | 7,205.40 | |||
| Overdue | 16,290.00 | Received this month | 4,470.00 | |||
| Share overdue | 50.3% | Customers over credit limit | 1 | |||
| Customers with a balance | 9 | Largest balance | Harper Dental Group | |||
| Aging bucket | Amount | Customer (largest balances) | Balance | Month | Invoiced | Received |
| Not yet due | 16,080.90 | Harper Dental Group | 10,050.00 | Jun 2026 | 0.00 | 0.00 |
| 1-30 days overdue | 7,900.00 | Pinecrest Hardware | 5,950.00 | Jul 2026 | 2,100.00 | 0.00 |
| 31-60 days | 6,790.00 | Oakline Fitness | 5,650.00 | Aug 2026 | 6,000.00 | 500.00 |
| 61-90 days | 0.00 | Brookside Catering | 3,075.50 | Sep 2026 | 18,540.00 | 3,050.00 |
| Over 90 days | 1,600.00 | Ellis & Co | 2,800.00 | Oct 2026 | 17,435.50 | 12,340.00 |
| Redwood Interiors | 2,070.00 | Nov 2026 | 7,205.40 | 4,470.00 | ||
| Northgate Print Shop | 1,290.00 | |||||
| Sutton Veterinary Clinic | 860.00 |
Example from the “Summary” tab, with the numbers and formulas that are in the file.
What is inside
- Settings: Your business name, address and contact line, currency symbol, payment instructions printed on every statement, and the as-of date used for aging.
- Customers: One row per customer with contact, address, email, payment terms in days and an optional credit limit. The grey columns show what was charged, what was paid and the balance, and flag customers over their limit or in credit.
- Transactions: The ledger. One row per invoice, payment, credit note or opening balance, in date order. It works out the due date from the customer's terms and flags rows with a missing date, unknown customer or missing amount.
- Statement: The printable statement of account for the customer and period you choose: opening balance, up to 40 lines with a running balance, closing balance and the customer's aging at the as-of date.
- Aging: Every customer's balance split into not yet due, 1-30, 31-60, 61-90 and over 90 days overdue, with totals. Payments are applied to the oldest charges first.
- Summary: Total receivable, how much is overdue, customers over their credit limit, invoiced and received this month, and three charts: receivables by age, who owes the most, and invoiced against received by month.
The file opens on a start tab (“Start here”) that explains how to use it.
What makes it different
You type each transaction once. The statement, the balance per customer and the aging all come from that one list, so they cannot disagree with each other. Terms, credit limit and payment instructions are settings, and nothing is tied to a currency or a tax regime.
How do I make a customer statement in Excel?
Keep a ledger with one row per event: the date, the customer, what it was (invoice, payment or credit note), a document number and a positive amount. A statement is that ledger filtered to one customer and one period, with a running balance. The opening balance is everything charged before the start date minus everything paid before it.
In this workbook, choose Ellis & Co and the period 2026-10-01 to 2026-11-16 on the Statement sheet. It opens with 4,650.00 owed, adds 1,500.00 of new invoices, takes off 3,350.00 of payments and credits, and closes at 2,800.00. Each of the four lines shows its own running balance, so the customer can follow the figure line by line.
Print the sheet or save it as a PDF. Your payment instructions from the Settings sheet appear at the bottom.
How do I age accounts receivable in Excel?
Aging answers one question: how late is each unpaid amount? For every charge work out its due date (invoice date plus the customer's payment terms), subtract the payments that cover the oldest charges first, and group what is left by the number of days past the due date as of a chosen date.
On the sample data, total receivable is 32,370.90. Of that, 16,080.90 is not yet due, 7,900.00 is 1-30 days overdue, 6,790.00 is 31-60 days overdue, nothing is 61-90 days and 1,600.00 is over 90 days. Harper Dental Group has the biggest balance, 10,050.00, which is above its 8,000.00 limit, so the sheet flags it.
A customer who has paid more than they were billed shows a negative balance. Tidewater Marine Supply is at -250.00 on the sample, labelled Credit balance and left out of the aging columns.
What should a statement of account include?
Most statements show the customer name and address, the statement date or period, the opening balance, each invoice, payment and credit with its date and document number, a running balance, the closing balance, any overdue amounts and how to pay. The Statement sheet has all of these. Invoice lines also show their due date, and overdue lines turn red while a balance is owed.
Whether you must send statements, and what they must say, depends on your country and your agreements with customers. Check your local rules.
How to use it
- On the Settings sheet type your business details, currency symbol, payment instructions and the as-of date (today, or the date you want the aging measured on).
- On the Customers sheet list each customer with their payment terms in days and, if you use one, a credit limit.
- On the Transactions sheet log every invoice, payment and credit note in date order: date, customer, type, document number, description and a positive amount. Use Opening balance for what a customer already owed when you started.
- Open the Statement sheet, pick a customer and the period, and print it or save it as a PDF to send. It shows the opening balance, every line with a running balance, and the closing balance.
- Open Aging to see each customer's balance split into not yet due and 1-30, 31-60, 61-90 and over 90 days overdue.
- Open Summary for the total receivable, how much is overdue, the biggest balances and a month-by-month view of invoicing against payments.
Before you start:
- Capacity: 100 customers and 1,500 transactions.
- Enter transactions in date order. Every amount is a positive number; the Type column decides whether it adds to or reduces the balance.
- Invoice and Opening balance add to what the customer owes. Payment and Credit note reduce it.
- Due date = invoice date + the customer's payment terms in days. An Opening balance is treated as due on its own date.
- Aging applies payments and credit notes to the oldest charges first. A customer who has paid more than they owe shows a negative balance and nothing in the aging columns. Total receivable on Summary adds up what customers owe and does not net off those credit balances.
- The statement shows up to 40 lines for the chosen period. A warning appears if there are more; shorten the period.
- Transactions dated after the as-of date are left out of Customers, Aging and Summary, but they still appear on a statement whose period includes them.
- Use one currency in one workbook. The currency symbol on Settings is only a label.
Still running this on Excel?
This sheet needs someone to copy every invoice and payment into it. In Accountwiro an invoice and a payment are recorded the moment they happen, and the customer's balance shows in a single statement of account.
| In this sheet | In Accountwiro |
|---|---|
| You copy every invoice and payment into the sheet by hand. | It shows the customer's invoices, payments and outstanding balance in a single statement of account. |
| Invoices are typed or copied by hand. | It creates an invoice by scanning a barcode or searching by product name, calculates discounts and taxes, and sends it to the customer over WhatsApp. |
| Stock shortages are discovered late. | It updates quantities after every sale and alerts you when a product drops below the required level. |
| Anyone who opens the sheet sees every number. | Permissions per user: the sales employee issues invoices, and the warehouse employee manages stock without access to financial data. |
Frequently asked questions
Is this customer statement template free?
Yes. Download it, change it and use it for your business without paying or signing up.
Does it work in Google Sheets and LibreOffice?
Yes. It uses ordinary formulas (SUMIFS, COUNTIFS, INDEX/MATCH, IFERROR), so it opens in Excel 2016 or later, LibreOffice and Google Sheets.
How do I send a statement to a customer?
Choose the customer and period on the Statement sheet, then print it or use File > Save As > PDF and email the PDF. Change the customer to produce the next one.
How does it decide what is overdue?
An invoice is due on its date plus the customer's payment terms in days. Payments and credit notes are applied to the oldest charges first, and whatever is left is counted by days past its due date on the as-of date you set.
What if a customer has more transactions than fit on one statement?
The statement shows up to 40 lines for the chosen period and warns you if there are more. Shorten the period (for example one month) and the opening balance will carry everything before it.
Is the template free?
Yes, with no sign-up and no email address. Use it, edit it and share it as you like.
Does it work in Google Sheets and on mobile?
The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.
What is the difference between the white and the gray cells?
You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.
Can I use the sheet from an AI assistant?
Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html
More free templates for: Shops and stores
More free templates
- Cash Book Template for Excel
- Invoice Template with Invoice Log (Excel)
- Inventory Tracking Spreadsheet for Excel
- Installment Payment Tracker for Excel
- Clothing Store Inventory Spreadsheet (Size and Color) for Excel
- Multi-Location Inventory Spreadsheet for Excel
- Gym Membership Tracker for Excel
- Freelance Project and Invoice Tracker for Excel
- Event Planning Checklist and Budget Template for Excel
- Fleet Fuel and Maintenance Log Template for Excel
- Ad Performance Tracker for Excel (ROAS, CPA)
- A/B Test Log Template for Excel (Experiment Tracker)
- Ecommerce Profit Margin Calculator for Excel
- Social Media Content Calendar Template for Excel