How to Track Invoices and Payments in a Spreadsheet (Without Losing Money)

You can probably say roughly what you billed last quarter. Ask what you’re actually owed right now, and by whom, and how long it’s been sitting there, and the honest answer is a search through sent mail and a bank statement.

That gap is expensive in a specific way. It isn’t that you forget to invoice — most people don’t. It’s that an invoice with nobody watching it quietly becomes a donation somewhere between day 45 and day 120, and you find out at tax time when the deposit that was supposed to arrive in March never shows up in the year’s totals.

The fix is not accounting software. It’s seven fields per invoice and a sheet that does the subtraction.

The Seven Fields, and Why Those Seven

Every question a small business asks about receivables is arithmetic on the same seven facts:

1. Invoice number — the handle. One per invoice, never reused, sequential.
2. Client — so totals can be grouped by who, not just by when.
3. Issue date — when you sent it. Starts the clock.
4. Payment terms — Due on receipt, Net 15, Net 30, Net 45. This is a number of days, not a vibe.
5. Amount — the invoice total.
6. Amount received — which is often not the amount. Deposits and part-payments are normal.
7. Date received — when the money actually landed in your account.

From those seven, formulas produce everything else:

• Due date = issue date + terms
• Balance = amount − amount received
• Days overdue = today − due date (only if there’s a balance)
• Status = Draft / Sent / Partial / Paid / Overdue, derived, never typed
• Aging buckets, collection rate, days-to-pay by client, revenue by month

The important one is status must be a formula. A typed status is correct on the day you type it and wrong forever after. Sheets where somebody typed “Sent” in February are the reason “total outstanding” and reality diverge.

Note what’s not on the list: line items, hours, tax rates, notes. Those belong on the invoice itself. The tracker is a ledger, and a ledger that takes thirty seconds per row survives a busy month.

A Worked Quarter

Here’s a first quarter for a one-person design studio — twelve invoices, six clients, three different payment terms. The as-of date is March 31.

Invoiced $29,290. Collected $15,290. Outstanding $14,000 — 47.8% of the quarter’s billing is still sitting somewhere other than the bank.

That’s the headline number, and taken alone it’s misleading in both directions. Here’s what the sheet actually tells you.

There are two collection rates and only one of them is real

Collected ÷ invoiced = 52.2%. That reads like a business in trouble.

But $8,800 of the $14,000 outstanding sits on invoices that aren’t due yet — INV-1049 is due April 1, INV-1051 April 10, INV-1052 May 7. You cannot fail to collect money that nobody owes you yet.

Measured against the $20,490 that had actually come due by March 31:

74.6% is still not good — a quarter of everything that came due is late — but it’s a diagnosable number pointing at two specific invoices, where 52.2% is just anxiety. Any dashboard that divides by total invoiced will make a growing business look like a failing one every time it has a good month.

The aging report names the problem

Split the $14,000 by how far past due each balance is:

Now the quarter reads completely differently. Only $5,200 is actually late, and one invoice — INV-1044, Calder Studios, 34 days past due — is the piece worth acting on today. The $2,800 from Vega is five days past a 45-day term, which is a reminder email, not a problem.

This is the entire argument for an aging report over a single outstanding figure: $14,000 outstanding sounds like a cash-flow crisis and prompts panic emails to six clients, three of whom aren’t late. $2,400 at 34 days is one phone call. Here’s the escalation ladder for the one that’s actually late.

Days-to-pay tells you who your clients really are

For every fully paid invoice, take date paid − issue date:

Marlow & Co is your biggest paying client — $6,400 collected — and pays 8 days late, every time, on both invoices. That’s not a delinquency, that’s a pattern: their accounts payable runs a monthly cycle and your Net 30 invoice lands wherever it lands in it. The correct response is not a late fee, it’s invoicing them a week earlier, or moving them to Net 15 so their habitual eight-day slip lands on day 23 instead of day 38.

Northgate Dental pays in 13.3 days on Net 15 terms — reliably inside terms. That’s the client you protect.

Calder Studios has two invoices, $4,200 total, and has paid nothing. Two open invoices to a client who has never paid one is the pattern worth catching early, because the second invoice ($1,800, issued March 2) was work you did after the first one went unpaid. A tracker that flags “client has an overdue balance” before you start new work is worth more than any late fee you’ll ever collect.

Concentration is a risk number hiding in a revenue number

Vega is 29% of the quarter’s revenue and 41% of the outstanding balance, on the longest terms you grant. That combination — biggest client, slowest terms — is the most common way a profitable freelance business runs out of cash. Nothing is wrong; you just have $5,700 of your own money financing someone else’s payment cycle. Whether your terms are the actual problem.

The Money You Never See Is the Money You Never Invoiced

One column earns its place beyond the seven: work completed but not yet invoiced.

Every solo business has some. The revision that went out on the 28th, the extra half-day, the project that ended quietly and never got a final invoice because the client stopped emailing. It doesn’t appear in an aging report, because there’s no invoice to age.

A simple habit fixes it: a row in the tracker with status Draft the moment work is delivered, amount filled in, invoice number blank. Now unbilled work shows up in the same list as unpaid work, and the weekly question is “what’s in Draft?” rather than “did I bill that?”

The Tax Angle: Store Both Dates

Most sole proprietors file on the cash method — income counts when you receive it, not when you bill it (see IRS Publication 334 for the small-business rules and Publication 538 on accounting methods). Under the accrual method, income counts when you earn it.

You don’t have to choose in the spreadsheet. Because you’re storing issue date and date received as separate fields, the same twelve rows produce both answers:

• Cash view (total by date received): $15,290 for the quarter — the figure that goes on a cash-basis Schedule C.
• Accrual view (total by issue date): $29,290 — what you earned.

A single “date” column collapses those into one wrong number. It also makes the January invoice paid in February land in the wrong month for both views.

This is general information, not tax advice — confirm your own accounting method with a qualified preparer.

Building It: Four Tabs

1. Clients. Name, contact, default rate, default terms, service type. Entered once so the invoice log is dropdowns instead of typing, and so “Net 30” means the same thing every time.

2. Invoice log. The seven fields plus formula columns for due date, balance, days overdue and status. One row per invoice. This is the whole system; everything else reads from it.

3. Aging report. The five buckets, calculated from the log, with the client name against each overdue balance. If you build only one summary, build this one.

4. Dashboard. Total invoiced, collected, outstanding, past due, collection rate on due invoices, revenue by month, revenue by client, and average days-to-pay by client.

Two refinements worth adding once the log has a few months in it:

• Recurring invoices on their own tab, for retainer clients, with the monthly amount and annual value — so you can see contracted revenue separately from project revenue.
• Expenses tagged by client, so the profitability question (“this client pays, but what do they cost me?”) is answerable without a second system.

Handling the Awkward Cases

Partial payments. Amount and amount received are separate fields precisely so a $5,600 invoice with $2,800 received shows a $2,800 balance and a Partial status — not a binary paid/unpaid flag that forces you to lie in one direction. How to invoice a deposit and keep the balance tracked.

Late fees. Add them as their own row referencing the original invoice number, never by editing the original amount. Editing the original destroys the record of what you actually billed and breaks any comparison against what the client agreed to. What a late fee should actually be, and when it works.

Refunds and credits. Negative-amount rows, with the original invoice number in a reference column. Same principle: never rewrite history, always add a row.

Foreign currency or platform fees. Record the amount you invoiced and the amount that landed. The difference is a real expense and it belongs somewhere you can total it — most people lose 2–3% here without ever seeing the annual figure.

Start With Your Last Ten Invoices

Not the year. Ten rows, from your sent folder, backwards.

Fill in the seven fields, add the due-date and balance formulas, and sort by days overdue. In almost every case one of two things happens: you find an invoice you’d stopped thinking about, or you find out that your “slow payers” are actually paying inside terms and your cash-flow problem is a terms problem, not a client problem.

Both of those are worth ten rows of typing.

Featured on ReadySheetGo

Small Business Invoice & Client Manager — $17.99

Eleven tabs and 1,167 auto-calculating formulas, built exactly as above. A Client Database stores up to 30 clients with rates, contacts and service types, and auto-generates client IDs. The Invoice Generator builds professional invoices with auto-calculated line items, tax and discount fields, pulling your business details from the My Business tab. The Invoice Tracker takes 100 invoices with a Draft / Sent / Paid / Overdue / Partial status, payment dates and running balance, colour-coded green for paid and red for overdue. The AR Aging Report buckets every outstanding balance by Current / 30 / 60 / 90+ days. The Revenue Dashboard returns monthly revenue trends, collection rate and revenue by client. A Recurring Invoices tab handles retainer billing with automatic annual values, Client Expenses tracks project costs per client, the Profitability Report turns those into profit margin per client, and the Tax Summary totals quarterly income against Schedule C categories.

Works in Microsoft Excel and Google Sheets.

Get the Small Business Invoice & Client Manager →

Sources: IRS Publication 334, Tax Guide for Small Business · IRS Publication 538, Accounting Periods and Methods