VAT return workings spreadsheet template (UK)
A working Excel template for the figures behind a UK VAT return. List your sales invoices and your purchase invoices with net and VAT; choose the period; the sheet works out boxes 1, 3, 4, 5, 6 and 7 and tells you whether you pay or reclaim. Each box carries HMRC's own wording. It is for standard VAT accounting, and it is a workings sheet, not a way to file. Free, no sign-up and no email address.
What is in it
| Sheet | One row per | What it does for you |
|---|---|---|
| Sales | Sales invoice | Invoice date, customer, invoice number, net and VAT. Gross is worked out. |
| Purchases | Purchase invoice | Invoice date, supplier, invoice number, net and VAT. Gross is worked out. |
| VAT return | Box | The nine boxes for the dates at the top. Boxes 1, 4, 6 and 7 are live SUMIFS over your invoices; box 3 and box 5 follow from them. Boxes 2, 8 and 9 are for goods moving between Northern Ireland and the EU and are entered by hand. A check line counts invoices in the period with no VAT figure, so a blank is not mistaken for zero. |
What each box is
Box 1 is the VAT due on your sales. Box 4 is the VAT you are reclaiming on purchases. Box 3 is box 1 plus box 2, and box 5 is the difference between box 3 and box 4: if box 3 is larger you pay HMRC that amount, and if box 4 is larger you reclaim it. Box 6 is the value of your sales excluding VAT and box 7 the value of your purchases excluding VAT. Zero-rated, reduced-rate and exempt sales still belong in box 6.
Boxes 2, 8 and 9 concern goods bought from or supplied to EU member states by businesses in Northern Ireland. For most businesses in Great Britain they are zero, which is what the template starts with.
What it does not do
It does not file anything. Most VAT-registered businesses must keep digital records and submit returns through compatible software under Making Tax Digital; a spreadsheet can hold the records, but it needs bridging software to send the figures. Check HMRC's list of compatible software.
It assumes standard VAT accounting, where each invoice counts in the period of its invoice date. The Flat Rate Scheme has its own rules for boxes 1, 4, 6 and 7, and cash accounting counts invoices when they are paid, so neither should use this sheet as it stands. Some costs, such as wages, are left out of box 7 altogether, and not all VAT on purchases can be reclaimed. Treat the result as tidy workings to check against HMRC's guidance or with your accountant, not as tax advice.
Enter 0, not nothing
A zero-rated or exempt invoice has VAT of 0. Type the 0. An empty VAT cell means you have not entered the figure yet, and the check line on the VAT return sheet counts those so you can find them before relying on the totals.
Filling it in without typing
The slow part is getting a quarter of purchase invoices into the Purchases sheet. That is what Invoices to Spreadsheets does: upload the PDFs, scans or photos and its export gives date, supplier, invoice number, net, VAT and total for each, checked so that net plus VAT equals the total. Try one invoice free, without an account.
Is the VAT return workings template free?
Yes. Download it, use it, change it. No account, no email address, no card.
Can I submit my VAT return from this spreadsheet?
Not directly. It works out the figures. Under Making Tax Digital most VAT-registered businesses must submit through compatible software, so a spreadsheet needs bridging software to send its figures to HMRC.
How is box 5 worked out?
Box 5 is the difference between box 3 (total VAT due) and box 4 (VAT reclaimed). If box 3 is larger, that is what you pay HMRC. If box 4 is larger, that is what you reclaim. The template shows the amount and says which.
Does it work for the Flat Rate Scheme or cash accounting?
No. It is built for standard VAT accounting, where an invoice counts in the period of its invoice date. The Flat Rate Scheme completes boxes 1, 4, 6 and 7 differently, and cash accounting goes by payment dates.
What are boxes 2, 8 and 9 for?
They cover goods moving between Northern Ireland and EU member states. For most businesses in Great Britain they are zero. The template leaves them for you to enter by hand because a list of invoices does not say which goods crossed that border.
Does it work in Google Sheets or Numbers?
Yes. It is a standard .xlsx file using SUMIFS and COUNTIFS. Set your locale to United Kingdom before importing so dates are read day first.
7 days · 50 documents · No card needed
All free tools and templates · Invoice tracker template · Self-employed income and expenses template