Softwiro
Free Excel template

Customer Statement Template with Aging (Excel)

A customer statement is a one-page record of what a customer was billed, what they paid and what they still owe. Most free templates give you the page and leave you to retype the numbers for every customer. This workbook keeps one list of invoices, payments and credit notes, and builds the statement from it: pick a customer and a period, and the opening balance, every line with a running balance and the closing balance fill in. The same list drives an aging report that shows who is overdue and by how long.

Excel file (.xlsx) · 247 KB · 8 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

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

Example from the “Summary” tab, with the numbers and formulas that are in the file.
As of46342Change the as-of date on the Settings sheet. Everything here is measured on that date.
Total receivable32,370.90Invoiced this month7,205.40
Overdue16,290.00Received this month4,470.00
Share overdue50.3%Customers over credit limit1
Customers with a balance9Largest balanceHarper Dental Group
Aging bucketAmountCustomer (largest balances)BalanceMonthInvoicedReceived
Not yet due16,080.90Harper Dental Group10,050.00Jun 20260.000.00
1-30 days overdue7,900.00Pinecrest Hardware5,950.00Jul 20262,100.000.00
31-60 days6,790.00Oakline Fitness5,650.00Aug 20266,000.00500.00
61-90 days0.00Brookside Catering3,075.50Sep 202618,540.003,050.00
Over 90 days1,600.00Ellis & Co2,800.00Oct 202617,435.5012,340.00
Redwood Interiors2,070.00Nov 20267,205.404,470.00
Northgate Print Shop1,290.00
Sutton Veterinary Clinic860.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

  1. 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).
  2. On the Customers sheet list each customer with their payment terms in days and, if you use one, a credit limit.
  3. 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.
  4. 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.
  5. 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.
  6. 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.

Excel file (.xlsx) · 247 KB · 8 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

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 sheetIn 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.

See Accountwiro · Ask for a demo

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

All templates · Free tools