Proper Spreadsheets

Templates › Business

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.

Of the 25 columns, you fill in 17 and the other 8 work themselves out, and all 14 figures on the summary do too. There is room for 460 rows of your own.

£4.99one 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.

Already have a spreadsheet of your own? Check it for mistakes, free.

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. What the film shows, in order:
  1. A new row is typed into the Invoices tab, one cell at a time, in the white cells a person fills in.
  2. The shaded cells in that row fill themselves in.
  3. The figures on the Summary tab change to include the new row.
  4. The chart, Net invoiced by type of work, redraws with it.
The chart, drawn from the totals in the table beside it. See it full size
Where you type, and the shaded columns that work themselves out. See it full size

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. Open any tab to see its columns.

Clients

9 columns

Your regular clients. Add to this list and the names appear in the Invoices dropdown. Ready for 60 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
ClientUse a short, consistent name: this is what you pick on the Invoices tab.Type any text.
ContactType any text.
EmailType any text.
Payment Terms (Days)Days from invoice date until payment is due. Typically 14, 30 or 45.Type a number.
Day RateType an amount in £.
StatusPick one: Active, Occasional or Dormant.
Total Invoiced (Net)Fills itself in.

The total of Net Amount on Invoices where Client matches this row's Client.

The formulaIF(ISBLANK([client_name]), "", SUMIF([Invoices!client], [client_name], [Invoices!net]))
OutstandingFills itself in.

Worked out from this row's Client, using Gross Total, Client and Status on Invoices.

The formulaIF(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"))
NotesType any text.

Invoices

16 columns

One row per invoice sent. Fill in the white columns; the shaded ones work themselves out. Ready for 400 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
Invoice NoType any text.
Invoice DateType a date.
ClientPick one of the Client entries on Clients.
Work DescriptionType any text.
CategoryPick one: Design, Development, Consultancy, Copywriting, Retainer, Expenses or Other.
Net AmountType an amount in £.
VAT RateUsually 0.20. Use 0 for zero-rated or outside-scope work.Type a percentage, like 20%.
VATFills itself in.

The Net Amount times the VAT Rate.

The formulaIF(ISBLANK([net]), "", ROUND([net] * [vat_rate], 2))
Gross TotalFills itself in.

Worked out from this row's Net Amount and VAT Rate.

The formulaIF(ISBLANK([net]), "", ROUND([net] + [net] * [vat_rate], 2))
Due DateInvoice date plus the client's payment terms. Defaults to 30 days if the client is not listed.Fills itself in.

Worked out from this row's Invoice Date and Client, using Payment Terms (Days) and Client on Clients.

The formulaIF(ISBLANK([invoice_date]), "", [invoice_date] + IFERROR(INDEX([Clients!payment_terms_days], MATCH([client], [Clients!client_name], 0)), 30))
StatusPick one: Unpaid, Part Paid, Paid or Written Off.
Date PaidType a date.
Amount ReceivedType an amount in £.
Balance DueFills itself in.

Worked out from this row's Net Amount, Status, VAT Rate and Amount Received.

The formulaIF(ISBLANK([net]), "", IF([payment_status] = "Written Off", 0, ROUND([net] + [net] * [vat_rate] - IF(ISBLANK([amount_received]), 0, [amount_received]), 2)))
Days OverdueFills itself in.

Worked out from this row's Invoice Date, Status and Client, using Payment Terms (Days) and Client on Clients.

The formulaIF(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 QuarterCalendar quarter the invoice falls in, e.g. 2026 Q3.Fills itself in.

Worked out from this row's Invoice Date.

The formulaIF(ISBLANK([invoice_date]), "", YEAR([invoice_date]) & " Q" & ROUNDUP(MONTH([invoice_date]) / 3, 0))

Summary

14 figures

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

FigureWorked out as
Invoices raised (all time)

The number of rows on Invoices with Invoice No filled in.

The formulaCOUNTA([Invoices!invoice_no])
Total invoiced this year (net)

Worked out from VAT Quarter and Net Amount on Invoices.

The formulaSUMIF([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)

The total of Amount Received on Invoices.

The formulaSUM([Invoices!amount_received])
Outstanding balance (gross)Everything still owed to you, including VAT.

The total of Balance Due on Invoices.

The formulaSUM([Invoices!balance_due])
Unpaid invoices

Worked out from Status on Invoices.

The formulaCOUNTIF([Invoices!payment_status], "Unpaid") + COUNTIF([Invoices!payment_status], "Part Paid")
Overdue invoices

The number of rows on Invoices where Days Overdue is above 0.

The formulaCOUNTIF([Invoices!days_overdue], ">0")
Overdue amount (gross)

The total of Balance Due on Invoices where Days Overdue is above 0.

The formulaSUMIF([Invoices!days_overdue], ">0", [Invoices!balance_due])
Worst overdue (days)

The largest Days Overdue on Invoices.

The formulaIFERROR(MAX([Invoices!days_overdue]), 0)
Current VAT quarter

Worked out from today's date.

The formulaYEAR(TODAY()) & " Q" & ROUNDUP(MONTH(TODAY()) / 3, 0)
VAT charged this quarterVAT on invoices dated in the current calendar quarter (accrual basis).

Worked out from VAT Quarter and VAT on Invoices.

The formulaSUMIF([Invoices!quarter], YEAR(TODAY()) & " Q" & ROUNDUP(MONTH(TODAY()) / 3, 0), [Invoices!vat_amount])
VAT on payments received this quarterUse this figure instead if you are on the VAT cash accounting scheme.

Worked out from VAT, Date Paid and Status on Invoices.

The formulaSUMIFS([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 quarter

Worked out from VAT Quarter and Net Amount on Invoices.

The formulaSUMIF([Invoices!quarter], YEAR(TODAY()) & " Q" & ROUNDUP(MONTH(TODAY()) / 3, 0), [Invoices!net])
Average invoice value (net)

Worked out from Net Amount on Invoices.

The formulaIFERROR(SUM([Invoices!net]) / COUNT([Invoices!net]), 0)
Active clients

The number of rows on Clients where Status is “Active”.

The formulaCOUNTIF([Clients!status], "Active")