sheetsmith

Templates › Business and freelance

Freelance Invoice & VAT Tracker

Made for freelancers and sole traders who invoice and are VAT registered.

Tracks every invoice sent, what is outstanding, what is overdue, and how much VAT is due this quarter, with a reusable client list. The summary carries a chart that redraws itself as you type.

Tabs
3
Columns
25
Fill themselves in
22

£7one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 460 rows.

Clients

Your regular clients. Add to this list and the names appear in the Invoices dropdown. Room for 60 rows.

ColumnTypeWorked out as
Clienttext
Contacttext
Emailtext
Payment Terms (Days)number
Day Ratecurrency
Statuschoice (list)
Total Invoiced (Net)currencyIF(ISBLANK([client_name]), "", SUMIF([Invoices!client], [client_name], [Invoices!net]))
OutstandingcurrencyIF(ISBLANK([client_name]), "", SUMIFS([Invoices!gross_amount], [Invoices!client], [client_name], [Invoices!payment_status], "Unpaid") + SUMIFS([Invoices!gross_amount], [Invoices!client], [client_name], [Invoices!payment_status], "Part Paid"))
Notestext

Invoices

One row per invoice sent. Fill in the white columns; the shaded ones work themselves out. Room for 400 rows.

ColumnTypeWorked out as
Invoice Notext
Invoice Datedate
Clientchoice (from Clients › client_name)
Work Descriptiontext
Categorychoice (list)
Net Amountcurrency
VAT Ratepercent
VATcurrencyIF(ISBLANK([net]), "", ROUND([net] * [vat_rate], 2))
Gross TotalcurrencyIF(ISBLANK([net]), "", ROUND([net] + [net] * [vat_rate], 2))
Due DatedateIF(ISBLANK([invoice_date]), "", [invoice_date] + IFERROR(INDEX([Clients!payment_terms_days], MATCH([client], [Clients!client_name], 0)), 30))
Statuschoice (list)
Date Paiddate
Amount Receivedcurrency
Balance DuecurrencyIF(ISBLANK([net]), "", IF([payment_status] = "Written Off", 0, ROUND([net] + [net] * [vat_rate] - IF(ISBLANK([amount_received]), 0, [amount_received]), 2)))
Days OverduenumberIF(ISBLANK([invoice_date]), "", IF(OR([payment_status] = "Paid", [payment_status] = "Written Off"), 0, MAX(0, TODAY() - ([invoice_date] + IFERROR(INDEX([Clients!payment_terms_days], MATCH([client], [Clients!client_name], 0)), 30)))))
VAT QuartertextIF(ISBLANK([invoice_date]), "", YEAR([invoice_date]) & " Q" & ROUNDUP(MONTH([invoice_date]) / 3, 0))

Summary

The headline answers: what is owed to you, what is late, and what VAT is due.

MeasureWorked out as
Invoices raised (all time)COUNTA([Invoices!invoice_no])
Total invoiced this year (net)SUMIF([Invoices!quarter], YEAR(TODAY()) & " Q1", [Invoices!net]) + SUMIF([Invoices!quarter], YEAR(TODAY()) & " Q2", [Invoices!net]) + SUMIF([Invoices!quarter], YEAR(TODAY()) & " Q3", [Invoices!net]) + SUMIF([Invoices!quarter], YEAR(TODAY()) & " Q4", [Invoices!net])
Total received (all time)SUM([Invoices!amount_received])
Outstanding balance (gross)SUM([Invoices!balance_due])
Unpaid invoicesCOUNTIF([Invoices!payment_status], "Unpaid") + COUNTIF([Invoices!payment_status], "Part Paid")
Overdue invoicesCOUNTIF([Invoices!days_overdue], ">0")
Overdue amount (gross)SUMIF([Invoices!days_overdue], ">0", [Invoices!balance_due])
Worst overdue (days)IFERROR(MAX([Invoices!days_overdue]), 0)
Current VAT quarterYEAR(TODAY()) & " Q" & ROUNDUP(MONTH(TODAY()) / 3, 0)
VAT charged this quarterSUMIF([Invoices!quarter], YEAR(TODAY()) & " Q" & ROUNDUP(MONTH(TODAY()) / 3, 0), [Invoices!vat_amount])
VAT on payments received this quarterSUMIFS([Invoices!vat_amount], [Invoices!date_paid], ">=" & DATE(YEAR(TODAY()), (ROUNDUP(MONTH(TODAY()) / 3, 0) - 1) * 3 + 1, 1), [Invoices!date_paid], "<=" & TODAY(), [Invoices!payment_status], "Paid")
Net invoiced this quarterSUMIF([Invoices!quarter], YEAR(TODAY()) & " Q" & ROUNDUP(MONTH(TODAY()) / 3, 0), [Invoices!net])
Average invoice value (net)IFERROR(SUM([Invoices!net]) / COUNT([Invoices!net]), 0)
Active clientsCOUNTIF([Clients!status], "Active")