Free Client Billing Tracker (Excel with Formulas, Fillable PDF, Word)

What is on the page

Invoice number and date

Copied from the invoice. Keeping the log in invoice-number order makes gaps and duplicates obvious.

Due date

The date on the invoice. "Days outstanding" and the aging buckets are measured from here, not from the invoice date.

Paid and date paid

What has actually arrived, when. Partial payments go in as paid, and the balance column shows the remainder.

Balance

Amount less paid. The log's outstanding total is your accounts receivable; it should match the aging sheet.

Aging buckets

How long each balance has been past due: current (not yet due), 1-30, 31-60, 61-90, over 90. Each bucket needs a different response, from a friendly reminder to a demand letter.

Overdue percentage

Overdue balances as a share of total receivables. Above 20 to 25% is a warning sign for a small business; above 40% is a cash-flow problem.

Running balance

On the client sheet, the balance after each invoice or payment. The last line is what the client owes now; the opening balance carries from the previous sheet.

Terms and rate

Recorded per client so the invoice and the reminder letters quote the right ones, and so a rate change is dated.

A weekly receivables routine

1. Log every invoice the day you send it, and every payment the day it arrives. Five minutes.
2. Each Monday, update the aging sheet: move balances into the right bucket by comparing due dates with today. Excel users can add a days-overdue column (today minus due date) and sort.
3. Act by bucket: 1-30 days, a friendly reminder; 31-60, a second notice and a call; 61-90, a final notice; over 90, the formal demand or a decision to write off.
4. Month end, reconcile: the log's outstanding total should equal the aging total and the sum of all client-sheet balances. If it doesn't, a payment was logged in one place and not the other.

Reading the aging report

Most of the money should be in the current column. A healthy small-business aging report has 70% or more current, most of the rest in 1-30, and very little past 60. A client who appears in two or three buckets at once is paying selectively or has a dispute; call them. A single large balance in the over-90 column distorts everything else and is usually the one you've been avoiding. The summary row gives three numbers worth tracking month to month: total receivables, overdue percentage, and the amount over 60 days. Lenders and buyers of a business ask for exactly this report, and in exactly this format.

How to use the sheets

• Invoice log: start a new page each quarter or when a page fills. Enter paid amounts as they arrive; the balance and totals update in Excel.
• Aging summary: one row per client with any open balance. Enter the amounts in the bucket matching days past due; Excel totals rows, columns and the overdue percentage. Use the notes box for the call list.
• Client history: one sheet per client. Enter the opening balance from the previous sheet, then invoices and payments in date order; Excel carries the running balance. The notes box is for how the client pays which turns out to be the most useful thing to know about them.

When to write a debt off

An invoice unpaid at 120 days with no response to a formal demand is unlikely to be paid without legal action. Decide: file in small claims (for amounts within your state's limit, cheap and often effective against a solvent client), hand it to a collections agency (they keep 25 to 50%), or write it off. A written-off invoice is a bad debt expense; if you report on the accrual basis it reduces taxable income, and if you report on a cash basis there is nothing to deduct because the income was never recognized. Either way, document the attempts to collect (the log and the letters) and issue a credit note so the client's balance and your books agree.

Editing the Excel versions

In Excel the log calculates each balance and totals billed, received and outstanding; the aging sheet totals every bucket and the summary row; the client sheet runs the balance forward from the opening figure. Add rows inside a table and the totals follow. For a days-overdue column, insert a column with =IF(balance=0,"",TODAY()-due_date), formatted as a number. Build the invoices themselves from the invoice template or its freelance variant, and give a receipt for each payment logged.

Questions

What is accounts receivable aging?

A report that groups unpaid invoices by how long they have been outstanding, usually in 30-day buckets. It shows how much is owed, by whom, and how overdue it is, so collection effort goes where it matters.

How often should I update the tracker?

Log invoices and payments as they happen (daily or every few days), review the aging weekly, and reconcile monthly. A tracker updated once a quarter is a history, not a tool.

What percentage of receivables is normal to have overdue?

For small businesses, under 20% overdue and under 5% past 60 days is healthy. Industries with long payment cycles (construction, consulting to large companies) run higher. The trend matters more than the number.

Can I use this instead of accounting software?

For a freelancer or very small business with a few dozen invoices a year, yes, alongside a bank account and an expense record. Once you have more than a handful of clients or need to issue statements regularly, invoicing software that produces the aging report automatically pays for itself.

Should partial payments be logged?

Yes, on the date received, against the invoice they relate to. The balance column shows the remainder and the aging sheet carries only the unpaid part.