How to work out a VAT return from a spreadsheet
Total the period’s sales and purchases by VAT rate: the VAT goes in boxes 1 and 4, the net values in boxes 6 and 7, boxes 3 and 5 fall out of the arithmetic — and then the nine figures have to be submitted through software HMRC has recognised, because a spreadsheet cannot send them.
17 min read · 8 steps · 6 ways it goes wrong
What you need before you start
- A list of sales for the period: date, reference, customer, net, VAT, gross, and either a rate or a VAT code on every row. The rate column is the one people leave out and the one that decides four of the nine boxes.
- The same for purchases, and only for purchases you hold a VAT invoice for. A purchase you cannot evidence is not a purchase you can reclaim on, however certain you are about it.
- Which scheme you are on: ordinary accrual accounting, cash accounting, or the flat rate scheme. The three put different things in the boxes and there is no way to work out which you are on from the spreadsheet.
- Your VAT period start and end dates, and a decision about what to do with rows dated outside them.
- Software HMRC has recognised, to actually submit through. Working the figures out and filing them are two different jobs.
Fix the period, and decide what a row belongs to
Under ordinary accrual accounting a sale belongs to the period its tax point falls in — normally the invoice date — whether or not anybody has paid. Under cash accounting it belongs to the period the money moved in, so an invoice raised in one quarter and paid in the next is on the later return, and an unpaid invoice is on no return at all yet.
This is the single decision that most changes the answer, and it is not visible in the numbers. Get it wrong and every box is wrong by the same consistent, plausible-looking amount.
Get the two lists into one shape
Sales on one sheet, purchases on another, or one sheet with a direction column. Each row needs a date, a reference, a net amount, a VAT amount, and a rate or VAT code. Gross is useful because it lets the sheet be checked against itself.
If your rows carry a package’s VAT codes rather than rates — T0, T1, T9, S, Z, E and their neighbours — they are recognised. The one to look at is the code your package uses for "no VAT", because there are two entirely different meanings hiding under it: a zero-rated supply, which is taxable at nothing and belongs in the turnover, and a supply outside the scope of UK VAT, which does not belong in the turnover at all.
The tool for this step: VAT Return from a Spreadsheet (the nine boxes) — turns the two lists into the nine boxes, opens every box up to show the rows behind it, and checks the sheet against itself before it totals anything.
Work out boxes 1 and 4 — the VAT itself
Box 1 is the VAT you charged on sales and other outputs in the period. Box 4 is the VAT you are reclaiming on purchases and other inputs. Under ordinary accounting both are simply the VAT columns added up, with credit notes in as negatives.
Box 2 is VAT due on goods acquired in Northern Ireland from an EU member state. If your business does not move goods that way it is nil, and nil is the right answer for most businesses — it is not a box you fill in because it looks empty.
Box 3 = Box 1 + Box 2 Box 5 = the difference between Box 3 and Box 4
Boxes 3 and 5 are arithmetic, not judgement. If your software lets you type into them independently, do not: the two identities are the first thing anybody checks.
Work out box 6 — and know what does not belong in it
Box 6 is the total value of sales and all other outputs excluding VAT. It is the box that is most often wrong, and there are four separate ways to get it wrong.
- It is net, not gross. Putting the VAT-inclusive total in box 6 overstates turnover by the VAT — under ordinary accounting, that is. Under the flat rate scheme it is the other way round and box 6 is the VAT-inclusive figure, which is one of the reasons the two schemes should never be worked out in the same spreadsheet.
- Zero-rated sales belong in it. A zero-rated supply is a taxable supply taxed at nothing, and its net value is part of box 6 even though it contributed nothing to box 1.
- Exempt supplies belong in it too. They carry no VAT and they are still outputs.
- Supplies outside the scope of UK VAT do not belong in it. Neither does anything that is not a supply at all: a bank transfer between your own accounts, a loan drawn down, money you paid in as capital, a grant with nothing given in return.
Made-up figures, to show the shape of box 6 rather than any real business.
| What it is | Net | VAT | In box 6? |
|---|---|---|---|
| Standard-rated sales | 40,000 | 8,000 | Yes — 40,000 |
| Zero-rated sales (food) | 12,000 | 0 | Yes — 12,000 |
| Exempt supplies (rent of a flat) | 6,000 | 0 | Yes — 6,000 |
| Services to a business customer outside the UK | 9,000 | 0 | No — outside the scope |
| Transfer from the deposit account | 5,000 | 0 | No — not a supply |
| Box 6 | 58,000 | Whole pounds |
Box 7 is the same idea on the purchase side: the total net value of purchases and other inputs. Wages are not a purchase for VAT. Neither is a payment to HMRC, a drawing, or a transfer between accounts.
If you are on the flat rate scheme, stop and do it differently
The flat rate scheme is not an adjustment to the ordinary calculation, it is a different calculation. Box 1 is a percentage of your VAT-inclusive turnover — the percentage for your sector, which you must get from the notice rather than from anybody’s memory. Box 6 is that VAT-inclusive turnover. Box 4 is normally nil, because the point of the scheme is that you do not reclaim input VAT.
The sector percentage is not guessed for you anywhere on this site, on purpose. A wrong sector percentage is a wrong box 1 and a wrong payment, and the table is long and full of near-neighbours.
Check the spreadsheet against itself before you believe any total
These are the checks worth running on every row, every quarter, and they take a computer no time at all.
- Net plus VAT does not equal the gross that was typed. Usually a discount applied to one column and not the others.
- The VAT is not the stated rate of the net — with a penny of rounding told apart from a real mismatch, because a penny is not a problem and a percentage point is.
- The same invoice reference twice. A credit note reissued under the original number does this, and so does a copy-paste.
- Rows dated outside the period. They should be excluded and counted, not silently dropped, because a large count means the period is set wrongly.
- Rows with no rate and no VAT code at all, which otherwise land wherever the default sends them.
- Negative rows. Usually credit notes, perfectly legitimate, and worth seeing as a list once a quarter.
Every one of those should name the row number and the reference so you can go and look at it. A check that says "3 problems found" and not where is not a check.
Round the boxes the way the return expects
Boxes 1 to 5 are pounds and pence. Boxes 6 to 9 are whole pounds. Getting that wrong will not usually change what you pay, but it will make your return disagree with itself in a way that is easy to avoid.
File it — which is a separate job from working it out
Under Making Tax Digital a VAT return has to be submitted through software HMRC has recognised, using their interface. A spreadsheet cannot do it and neither can this site. What you need is bridging software: something recognised whose only job is to read nine figures out of a spreadsheet and submit them.
There is a second rule that catches people out. The link between your records and the figures you submit has to be digital — a formula, a link, an import — rather than a person reading a total off one screen and typing it into another. Working the figures out in a spreadsheet is fine. Retyping them into the submission by hand is the part that is not.
Keep the working. A box with a figure and no rows behind it is the hardest kind of return to defend, and the easiest kind to produce by accident.
Where this usually goes wrong
6 things that actually happen, rather than a note asking you to be careful.
- Box 6 filled with the gross. Under ordinary accrual or cash accounting box 6 is the net value of outputs — the VAT comes out. Under the flat rate scheme box 6 is the VAT-inclusive turnover — the VAT stays in. Both are correct in their own scheme and both are badly wrong in the other. The mistake survives quarter after quarter because the return still balances internally and the payment still looks plausible; it only surfaces when turnover is compared against the accounts and is out by something suspiciously close to the standard rate.
- Zero-rated sales left out, and out-of-scope income put in. These are two opposite errors made by the same instinct, which is that box 6 is "the sales I charged VAT on". It is not. A zero-rated supply is taxable at a rate of nothing and its net value belongs in box 6. A supply outside the scope of UK VAT does not belong in box 6 at all, and neither does a transfer between your own bank accounts, a loan, a capital introduction or a grant given for nothing in return. The package code that says "no VAT" covers both cases and will not tell you which one you meant.
- The cash accounting quarter that is really an invoice quarter. On cash accounting the return follows the money, not the invoice. A spreadsheet built around invoice dates will quietly produce an accrual return while you believe you are on the scheme — which overstates box 1 in a good quarter and understates it in a bad one, and gets progressively harder to unpick the longer it runs. The tell is an unpaid sales invoice appearing in box 1 in the quarter it was raised. If you are on the scheme, the sheet needs a paid date and a received date, and rows without them are on no return yet.
- The same invoice twice, under two references. A credit note issued under the original invoice number, an invoice reissued after a change of address, a row pasted in twice when two people were maintaining the sheet — all three produce a duplicate that no amount of staring at a total will reveal. Checking for a repeated reference on each side takes a second and finds them. It also finds the opposite case, where a genuine second invoice happens to share a reference with the first, which is worth knowing about for different reasons.
- Reclaiming VAT you cannot evidence. Box 4 is VAT on purchases you hold a valid VAT invoice for. A card receipt with no VAT number, a pro-forma, a supplier statement, a screenshot of an order confirmation — none of those is a VAT invoice, and a spreadsheet will add up their VAT column as happily as any other. Purchases from a supplier who is not registered have no VAT to reclaim at all, no matter what the column says. The place to catch this is at entry, with a column that says whether the invoice is on file.
- A rate column that is empty on some rows. Rows with no rate and no code do not announce themselves: they fall to whatever default the sheet or the tool applies, and a default is a guess. A handful of blank rate cells in a thousand-row sheet can move box 1 by a noticeable amount and leave every internal check passing, because the arithmetic is consistent with the guess. Count the blanks before you total anything, and be suspicious of any period where that count is not zero.
How long this should take
An hour the first time, most of it spent deciding what your own columns mean and finding the four rows where net plus VAT does not equal gross. Twenty minutes a quarter after that, assuming the spreadsheet keeps its shape. If it takes you a whole day every quarter, the problem is the record-keeping rather than the return, and the fix is a rate or code column on every row as it is entered.
Frequently asked questions
Can I file my VAT return from a spreadsheet?
Not directly. The figures can come from a spreadsheet, and for most small businesses they do, but the submission has to be made by software HMRC has recognised. Bridging software is exactly that category — software whose job is to read the nine boxes from a sheet and submit them — and it is what a spreadsheet-based business needs alongside the spreadsheet.
What are boxes 2, 8 and 9 about now?
Goods moving between Northern Ireland and EU member states, rather than between Great Britain and the EU. Box 2 is the VAT due on goods acquired in Northern Ireland from an EU member state, box 8 the value of goods despatched from Northern Ireland to them, box 9 the value of those acquisitions. If your business does not move goods that way, all three are nil and that is the correct answer.
Do I have to include exempt income in box 6?
Yes. Exempt supplies carry no VAT and are still outputs, so their net value is part of box 6. The thing to keep out of box 6 is income that is outside the scope of VAT altogether, and receipts that are not supplies at all.
What if a row is dated just outside the period?
Exclude it and count it. One or two either side of a quarter end is normal and they belong to the neighbouring return. Dozens of them means the period dates are wrong, which is worth knowing before rather than after you file.
The tools this uses
Each one described in its own words, read from its own page. Everything here runs in your browser unless it says otherwise.