Most free accounts-receivable aging templates age the original invoice amount, even after the client has paid part of it — so a $900 invoice with $550 already paid still shows as $900 overdue. The free Excel invoice tracker below instead ages the remaining balance, splits it into the standard 0-30 / 31-60 / 61-90 / 90+ day buckets, and includes a reconciliation check that must read exactly $0.00 before you trust the report.
Download the free Invoice Tracker & AR Aging template (.xlsx)
Why free invoice aging templates get the numbers wrong
Accounts-receivable aging is a standard small-business report: every open invoice gets sorted into how many days past its due date it is, usually in 30-day bands, so you can see at a glance which clients are genuinely behind. The buckets themselves — 0-30, 31-60, 61-90, 90+ days — are the same ones used in professional AR reporting (confirmed against the Journal of Accountancy’s walkthrough of building an AR aging report in Excel, March 2025).
What that guide — and most of the free templates built the same way — leaves out is partial payments. They track one “Amount” per invoice and age that whole figure. The moment a client pays part of an invoice, the report is wrong: it either still counts the full original amount as overdue, or (if someone manually zeroes it out) loses track of the real balance entirely. Neither free template we checked reconciles its bucket totals back to a single “does this add up” number, so a broken formula can silently understate or overstate what’s actually owed.
What’s in the template: three sheets, three jobs
The workbook has three working sheets plus a “How to use” cover sheet. Each sheet does exactly one job, so a mistake in one doesn’t quietly corrupt another:
| Sheet | You do this | It does this |
|---|---|---|
| Invoices | Enter Invoice #, Client, Invoice Date, Due Date, Invoice Amount — one row per invoice | Nothing yet; it’s pure input |
| Payments | Log every payment as its own row (date, invoice #, amount) — including partial payments | Nothing yet; it’s pure input, and an invoice can have as many payment rows as it needs |
| Aging Report | Nothing — every cell is a formula | Totals each invoice’s payments, computes the unpaid balance, ages that balance, and checks that the buckets reconcile to the cent |
How the 0-30 / 31-60 / 61-90 / 90+ buckets are calculated
For each invoice, the Aging Report sheet pulls every matching payment with SUMIF, subtracts that from the invoice amount to get the balance, then counts days past the due date with TODAY(). An invoice due exactly today isn’t overdue yet — it’s “Current” — and a fully (or over-) paid invoice is excluded from every bucket no matter how late its due date is:
Amount Paid = ROUND(SUMIF(Payments!$B:$B, [Invoice#], Payments!$C:$C), 2)
Balance Due = ROUND(Invoice Amount - Amount Paid, 2)
Days PastDue = IF(Due Date = "", "", MAX(0, TODAY() - Due Date))
Bucket = IF(Due Date = "", "N/A",
IF(Balance Due <= 0, "Paid",
IF(Days PastDue = 0, "Current",
IF(Days PastDue <= 30, "1-30 days",
IF(Days PastDue <= 60, "31-60 days",
IF(Days PastDue <= 90, "61-90 days", "90+ days"))))))
Day 30 and day 31 land in different buckets on purpose — that boundary, plus day 60/61 and day 90/91, is exactly where a hand-built lookup table tends to be off by one. The template was tested against all three boundaries directly (see the verification section below).
Worked example: a $900 invoice paid in two parts
Say a freelancer invoices a client $900 with a 30-day term, and the client pays in two installments:
- Invoice #INV-1010 for $900 is entered on the Invoices sheet with a due date 45 days ago.
- The client pays $400 twenty-five days ago — logged as its own row on the Payments sheet.
- The client pays another $150 five days ago — a second row on the Payments sheet, same invoice number.
- The Aging Report sheet’s
SUMIFadds both payments: Amount Paid = $550. - Balance Due = $900 − $550 = $350 — not $900.
- The due date was 15 days ago, so Days Past Due = 15, and the bucket is “1-30 days” for exactly $350 — not the full $900 a simpler template would show.
What if an invoice doesn’t have a due date yet?
A draft or quoted invoice often has no due date yet, and dividing by or comparing against a blank date is a common source of #VALUE! errors in homemade trackers. This template checks for a blank due date first and routes those rows to their own “No due date (unaged)” column instead — so the invoice’s balance still shows up in your total receivables, but it’s labeled separately rather than silently missing or throwing an error.
Reconciling the report: the one number that catches broken formulas
At the bottom of the Aging Report sheet, two totals are computed independently — the sum of every invoice’s positive balance, and the sum of all six bucket columns (Current, 1-30, 31-60, 61-90, 90+, and No due date) — and then subtracted. That difference is labeled “Reconciliation check (must read $0.00)”. If you ever overwrite a formula, insert a row in the wrong place, or add a bucket column that doesn’t match the Bucket column’s exact text, this number moves off zero immediately and tells you something broke, instead of letting a silently wrong report go out to a client or a bookkeeper.
What was verified, and what wasn’t
Verified: the workbook was built with real formulas (no hard-coded values), recalculated with LibreOffice running headless, and the recalculated values were checked against an independent Python calculation of the same 15 test invoices — covering a blank due date, a $0 invoice, a fully paid invoice, an overpaid invoice (credit balance), a not-yet-due invoice, an invoice due exactly today, and the exact day-30/31, day-60/61, and day-90/91 bucket boundaries. All 15 matched, the reconciliation check read $0.00, and no cell anywhere on the Aging Report returned #DIV/0!, #REF!, #VALUE!, or any other error.
Not verified: the file was not opened in Microsoft Excel itself — only recalculated by LibreOffice, which is a different calculation engine. Google Sheets can open it directly (File > Import > Upload); every function used here (SUMIF, IF, MAX, TODAY, ROUND) is supported identically there. This is a tracking and reporting tool, not legal or tax advice — it does not calculate late fees, since late-fee rules vary by state and by contract.
Optional: email yourself a daily overdue-invoice reminder
The template includes an “Automation (optional)” sheet with a Google Apps Script that, once you’ve imported the file into Google Sheets, emails you a daily list of every invoice that moved into an overdue bucket. It reuses the same two building blocks already covered on this site: a time-driven daily trigger (see Email Yourself a Daily Google Sheets Summary, Automatically) instead of an edit-triggered one, because aging changes with the calendar even if nobody touches the sheet — and a conditional send that only fires when something needs attention (the same pattern as How to Send an Email When a Cell Changes in Google Sheets, adapted here to check the Bucket column instead of a single cell).
Using it with your own invoices
Download the template above, then open the “How to use” sheet first, and replace the sample rows on Invoices and Payments with your own — the Aging Report sheet needs no edits at all; every cell there is already a formula pointing at those two input sheets. If you’d rather work in Google Sheets, import the file (File > Import > Upload) and it behaves identically. Re-check the “Reconciliation check” cell any time the report looks off: $0.00 means the formulas are intact.
If your tenants pay rent instead of invoices, the same partial-payment and balance-carrying approach — plus a late fee that’s calculated instead of typed in by hand — is in the rent ledger template for small landlords.
Costing a recipe instead of an invoice? The same verified-formula approach applies to ingredient pricing — see the free recipe cost calculator with automatic unit conversion and yield-adjusted food cost %.