How to track accounts receivable: spreadsheet, aging report and app
Accounts receivable (AR) is the money customers owe your business for goods or services you have already delivered on credit. Tracking accounts receivable means recording every credit sale and every payment per customer, knowing at any moment who owes how much and since when, and chasing what is overdue before it turns into a bad debt. This guide shows how to do it with a spreadsheet, how to read an accounts receivable aging report, which numbers to watch, and which tools do the work for you — including Account Ledger by Ofisx, an iPhone app that keeps a running balance for each customer and sends reminders.
Short answer
- What to track for every customer: each credit sale (date, reference, amount, due date), each payment or credit, and the running balance. From those three you get the total owed, what is overdue and by how many days.
- A handful of credit customers: an accounts receivable spreadsheet in Excel or Google Sheets is enough — one row per invoice, a payments column, SUMIFS for balances and a TODAY() formula for days overdue.
- You already invoice in accounting software: QuickBooks, Wave and Zoho Books track receivables from the invoices you issue and print an AR aging report.
- You sell on account and collect on the go: Account Ledger by Ofisx keeps one account per customer on the iPhone, flags upcoming and overdue due dates, and sends the balance or a PDF statement by WhatsApp.
- Weekly habit that matters most: every week, sort customers by the oldest unpaid amount and contact everyone past due — before the next order on credit, not after.
What accounts receivable means
When you deliver first and get paid later, the unpaid amount is an account receivable. On the balance sheet the total of all of them is a current asset called accounts receivable. The mirror image is accounts payable — what you owe your suppliers.
Accounts receivable is not cash yet. A business can show a profit and still run out of money because its sales are sitting in receivables. That is why tracking AR is less about bookkeeping and more about knowing who to call this week.
| Accounts receivable | Accounts payable | |
|---|---|---|
| Who owes | Your customers owe you | You owe your suppliers |
| Created by | A sale on credit (invoice, delivery note) | A purchase on credit (supplier bill) |
| Reduced by | Customer payment, credit note, return, write-off | Your payment, supplier credit, return |
| Balance sheet | Current asset | Current liability |
| Your goal | Collect on time | Pay on time, not early |
Accounts receivable spreadsheet: layout and formulas
The simplest reliable layout is one row per invoice on a sheet called Invoices, with payments recorded against the invoice they pay. Columns and the formulas that make it work (Excel and Google Sheets use the same functions):
| Column | What goes in | Formula / note |
|---|---|---|
| A · Customer | Customer name, always spelled the same | Use a drop-down (Data validation) from a customer list |
| B · Invoice no. | INV-1041 | Unique; the customer quotes it when paying |
| C · Invoice date | 10/3/2026 | — |
| D · Terms (days) | 30 | Net 30, Net 15, due on receipt = 0 |
| E · Due date | 11/2/2026 | =C2+D2 |
| F · Amount | $860.00 | — |
| G · Paid | $500.00 | Total of payments and credits against this invoice |
| H · Open | $360.00 | =F2-G2 |
| I · Days past due | 12 | =IF(H2>0, MAX(0, TODAY()-E2), 0) |
| J · Aging bucket | 1–30 | =IF(H2<=0,"Paid",IF(I2=0,"Current",IF(I2<=30,"1–30",IF(I2<=60,"31–60",IF(I2<=90,"61–90","90+"))))) |
On a second sheet, list each customer once and total the open amount with =SUMIFS(Invoices!H:H, Invoices!A:A, A2), and each bucket with =SUMIFS(Invoices!H:H, Invoices!A:A, A2, Invoices!J:J, "31–60"). That second sheet is your aging report.
Where spreadsheets break: a payment that covers two invoices has to be split by hand, a new row outside the formula range silently drops out of the totals, and nobody updates the file from a customer's shop floor. If those start to happen every week, it is time for software.
Accounts receivable aging report example
An aging report groups each customer's open balance by how long it has been past due. Example for a small building-supply business on October 31, 2026:
| Customer | Current | 1–30 | 31–60 | 61–90 | 90+ | Total |
|---|---|---|---|---|---|---|
| Hart Plumbing LLC | $1,105 | $1,105 | ||||
| Mason Roofing | $2,400 | $1,800 | $4,200 | |||
| Lopez Remodeling | $950 | $620 | $1,570 | |||
| Quick Fix Handyman | $480 | $480 | ||||
| Brightline Electric | $3,150 | $3,150 | ||||
| Total | $6,655 | $1,800 | $950 | $620 | $480 | $10,505 |
| Share | 63% | 17% | 9% | 6% | 5% | 100% |
How to read it: 63% is not yet due, which is healthy. The calls to make this week are Lopez Remodeling ($1,570, part of it over 60 days — stop new credit until it pays down) and Quick Fix Handyman ($480 over 90 days — call, agree a date, and decide whether it is collectable). Mason Roofing's $1,800 in 1–30 is a reminder, not a problem yet.
Accounts receivable numbers worth watching
Two ratios tell you whether collections are getting faster or slower. Calculate them monthly from the same data:
- Days sales outstanding (DSO) = accounts receivable ÷ credit sales for the period × days in the period. Example: $10,505 AR, $14,000 credit sales in October (31 days) → 10,505 ÷ 14,000 × 31 ≈ 23 days. If your terms are Net 30 and DSO stays under 30, customers are paying roughly on time.
- Accounts receivable turnover = credit sales ÷ average accounts receivable. Higher means you collect faster. Compare it with your own previous months rather than with other industries.
- Share over 60 days — the simplest warning light. If the 61–90 and 90+ columns keep growing month after month, tighten credit before sales volume hides the problem.
- Largest single balance — if one customer holds a large part of your receivables, a single late payment becomes your cash-flow problem. Set a credit limit for them.
Ways to track accounts receivable compared
| Option | Best for | How receivables are recorded | Aging and reminders | Platforms | US App Store ratings (Oct 21, 2026) |
|---|---|---|---|---|---|
| Account Ledger by Ofisx | Shops, wholesalers and trades that sell on account and collect in person | Per customer account: sales on credit, payments, partial payments, returns, instalment plans | Due-date alerts for upcoming and overdue payments; balance, reminder and PDF statement via WhatsApp; no bucketed aging report | iPhone (US App Store); Android on Google Play in Turkey only | New in the US store — no ratings yet |
| Intuit QuickBooks | Businesses whose books are already in QuickBooks | From invoices and received payments | A/R aging summary and detail reports; invoice reminders by email | iPhone, Android, web | 273,010 ratings, 4.7 |
| Wave | Very small businesses that invoice every sale | From invoices issued in Wave | Aged receivables report; invoice reminders by email | iPhone, Android, web | 6,665 ratings, 4.4 |
| Zoho Books | Small businesses keeping full books in Zoho | From invoices, payments and credit notes | AR aging reports; automated payment reminders | iPhone, Android, web | 5,264 ratings, 4.8 |
| Excel / Google Sheets | Under about 20 credit customers, updated at a desk | One row per invoice, typed by hand | Aging with formulas (see above); reminders are up to you | Any computer; awkward on a phone | — |
The options in detail
Account Ledger by Ofisx
Account Ledger keeps one card per customer (and per supplier, so payables sit in the same app). You record a sale on credit when it happens and a payment when it arrives — including partial payments — and the customer's balance updates itself. Due dates raise alerts before and after they pass, and you can send the balance, a reminder or a PDF statement by WhatsApp, email or any messaging app. Data exports to Excel if you want your own aging sheet or your accountant asks for it.
It is a credit ledger rather than invoicing software: it does not issue sales-tax invoices and it does not print a 30/60/90-day aging table. It fits a business where the problem is remembering and collecting what customers owe, not producing formal invoices.
Intuit QuickBooks
QuickBooks records receivables from the invoices you create and the payments you apply to them, and produces A/R aging summary and detail reports plus customer statements. It is the natural choice when your accountant already works in QuickBooks; for a shop that only needs to know who owes what, it is a full accounting system to learn.
Wave
Wave tracks receivables from the invoices you send through it and has an aged receivables report. Its free starter plan covers invoicing and bookkeeping; online payments and some features are paid. Works best when every credit sale already goes out as an invoice.
Zoho Books
Zoho Books includes AR aging reports and scheduled payment reminders by email, alongside invoices, bills and the rest of a full accounting package. Like QuickBooks it is built around invoices and a chart of accounts.
Excel / Google Sheets
A spreadsheet costs nothing if you have Excel or Google Sheets and gives you full control over the aging buckets. It needs discipline: every payment entered against the right invoice, formulas extended to new rows, and a weekly look at column I.
A weekly accounts receivable routine
- Set credit terms before the first sale — Agree the terms (Net 15, Net 30) and, for larger customers, a credit limit. Put the terms on every invoice or delivery note so the due date is never a matter of opinion.
- Record every credit sale the same day — Date, reference, amount and due date. A sale that is written down a week later is the one that gets forgotten.
- Record payments against what they pay — When a customer pays, note which invoice(s) the money covers. Partial payments reduce the oldest open amount unless the customer says otherwise.
- Review the aging every week — Sort by the oldest unpaid amount. Current balances need nothing; 1–30 days get a friendly reminder; over 30 days get a call and an agreed date.
- Send statements monthly — A monthly statement lets the customer check your figures against theirs and catches disputes early. See the statement of account template.
- Decide on old balances — Anything over 90 days needs a decision: a payment plan, holding new orders, a collection agency, or writing it off with your accountant. Leaving it in the report forever only makes the numbers look better than they are.
Limits — what Account Ledger does not do
- Account Ledger does not create sales-tax invoices, keep a general ledger or print a bucketed (30/60/90-day) aging report; use QuickBooks, Wave or Zoho Books if you need those, or export to Excel and build the aging sheet above.
- It does not connect to bank feeds or take card payments; payments are recorded by you.
- In the US the app is on the App Store for iPhone; the Android version is on Google Play in Turkey only for now.
- It is a paid subscription after the free trial; the App Store shows current plans and prices.
Frequently asked questions
What is accounts receivable?
Accounts receivable is the money customers owe a business for goods or services already delivered on credit. It is recorded as a current asset on the balance sheet until the customer pays.
How do you track accounts receivable in a small business?
Record each credit sale with its date, reference, amount and due date; record each payment against the sale it pays; keep a running balance per customer; and review an aging list every week to follow up on anything past due. A spreadsheet works for a few customers; accounting software or a ledger app saves time beyond that.
What is the best way to track accounts receivable in Excel?
Use one row per invoice with customer, invoice number, date, terms, due date (=date+terms), amount, paid and open (=amount−paid). Add days past due with =IF(open>0, MAX(0, TODAY()−due date), 0), group them into aging buckets, and total each customer with SUMIFS on a second sheet.
What is an accounts receivable aging report?
It is a table of each customer's unpaid balance split by how long it has been past due — usually current, 1–30, 31–60, 61–90 and over 90 days. It shows which customers to contact first and how much of your receivables may be hard to collect.
What is a good days sales outstanding (DSO)?
A DSO close to or below your payment terms is good — for example under 30 days when you sell on Net 30. More useful than a benchmark is the trend: if DSO rises month after month, customers are paying more slowly.
Is there an app to track accounts receivable on iPhone?
Yes. QuickBooks, Wave and Zoho Books have iPhone apps that track receivables from invoices. Account Ledger by Ofisx is built for businesses that sell on account: a running balance per customer, due-date alerts, and balance or statement reminders by WhatsApp, with a free trial and then a subscription.
What is the difference between accounts receivable and accounts payable?
Accounts receivable is what customers owe you for sales on credit; accounts payable is what you owe suppliers for purchases on credit. Receivables are an asset, payables a liability.