Invoice Tracker Template with Aging Report

About this template

An invoice tracker is the list behind the invoices: what was billed, to whom, when it is due and whether it has been paid. Accounting software keeps one automatically, but a freelancer or small business sending a handful of invoices a month often has the invoices themselves in a folder and no single place that says who still owes what. This workbook is that place.

Each row is one invoice. You type the number, client, invoice date, payment terms in days, the amount and anything paid so far; the sheet works out the due date, the balance, a status, how many days it is overdue and which aging bucket it falls in. Because status and days overdue are calculated against TODAY(), opening the file next week moves an open invoice into Overdue without anyone touching it.

Layout: the summary block sits in rows 3 to 9 - totals invoiced, collected and outstanding, the overdue amount and count, and the aging buckets. Row 12 is the table header: Invoice #, Client, Invoice date, Terms (days), Due date, Amount, Paid, Balance, Date paid, Status, Days overdue and Aging.

Net 30 is the usual US default for business-to-business invoices, but the Terms column is per invoice, so a net 15 client and a net 45 client sit side by side. Payments can be partial: an invoice with money received but a balance left shows as Partial until it is due, then Overdue.

How to use it

1. Log each invoice when you send it - Invoice number, client, invoice date, terms in days and the amount. The due date fills in as invoice date plus terms, and the balance equals the amount until a payment is recorded.
2. Record payments as they arrive - Type the amount received into Paid and the date into Date paid. A full payment sets the status to Paid and clears the days overdue; a partial payment leaves the remaining balance on the books.
3. Chase the red rows - Overdue rows are highlighted. The Days overdue column says how late each one is, and the aging summary at the top totals what is outstanding in each 30-day bucket.
4. Review the aging summary monthly - Anything in 61-90 or 90+ deserves a phone call rather than another reminder email. The overdue total is also the number to watch if cash is tight.
5. Filter or sort as the list grows - Use Data > Filter on the header row to show one client, or only Overdue invoices. The formulas work row by row, so sorting does not break them.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =IF(C13="","",C13+D13) - Due date: the invoice date plus the payment terms in days, once an invoice date is entered.
• =IF(A13="","",F13-N(G13)) - Balance: the invoice amount minus anything paid. N() treats an empty Paid cell as zero.
• =IF(A13="","",IF(H13<=0,"Paid",IF(TODAY()>E13,"Overdue",IF(N(G13)>0,"Partial","Open")))) - Status, checked in order: nothing left to pay is Paid; past the due date with a balance is Overdue; some money received is Partial; otherwise Open. TODAY() makes it current every time the workbook recalculates.
• =IF(OR(A13="",H13<=0),"",MAX(0,TODAY()-E13)) - Days overdue: today minus the due date, never negative, and blank once the invoice is paid.
• =IF(K13="","",IF(K13=0,"Current",IF(K13<=30,"1-30",IF(K13<=60,"31-60",IF(K13<=90,"61-90","90+"))))) - Aging bucket from the days overdue, in the standard 30-day steps.
• =SUM($F$13:$F$62) - Total invoiced in the summary block; total collected and outstanding are the same SUM over the Paid and Balance columns.
• =SUMIF($J$13:$J$62,"Overdue",$H$13:$H$62) - Overdue amount: the balances of every row whose status is Overdue.
• =COUNTIF($J$13:$J$62,"Overdue") - How many invoices are overdue.
• =SUMIF($L$13:$L$62,"Current",$H$13:$H$62) - The aging summary: outstanding balances in each bucket - Current here, then 1-30, 31-60, 61-90 and 90+ beside it.

Customising it

• Add a late fee column with a formula such as =IF(K13>30,H13*1.5%,0) if your terms allow interest on overdue balances.
• Keep one tracker per year and start the next year's sheet with only the unpaid rows carried over.
• Change the aging buckets by editing the thresholds in the Aging formula and the labels in the summary block together.
• Link invoice numbers to the PDFs: right-click the cell, choose Link, and point it at the invoice file on your drive.