VAT on a column of prices
Paste the column into the price list and the table underneath applies the rate to every line and adds them up. The part worth knowing is that the total is the sum of the rounded lines, not the rounding of the summed lines. Those two differ by a penny or two on any list long enough to matter, and the invoice is the first of them: rounding once at the bottom produces a total that does not reconcile against the lines printed above it.
Line by line
| Line 1 | $10.82 | $2.17 | $12.99 |
| Line 2 | $3.74 | $0.75 | $4.49 |
| Line 3 | $26.25 | $5.25 | $31.50 |
| Line 4 | $7.29 | $1.46 | $8.75 |
| Total | $48.10 | $9.63 | $57.73 |
An estimate, not legal advice. Rules vary by jurisdiction and change over time. Check the current position with a qualified professional before relying on it.
Round each line, then add. Adding first and rounding once gives a total that disagrees with the lines above it.
The disagreement grows with the number of lines rather than with their size: fifty lines can be a few pennies out either way.
Every line here uses the same rate, so a mixed-rate invoice has to be run once per band.
How it works
The formula
Adding VAT to a price that excludes it:
vat = round(net x rate / 100)
gross = net + vat
Removing VAT from a price that includes it, by the VAT fraction:
vat = round(gross x rate / (100 + rate))
net = gross - vat
where
rate = the rate as a percentage, so 20 rather than 0.2
round = to the nearest 0.01, or towards zero when
"round the VAT down" is chosen
At 20% the VAT fraction is 20/120, which is one sixth.
Only the VAT is rounded. The third figure is derived from
the other two, so net + vat = gross to the penny, always.
Down a pasted column, each line is rounded and the totals
are the sums of the rounded lines — never the rounding of
the summed lines, which is a different number.
What it assumes
- The rate is whatever you type. This tool holds no rate table for any country, which is the reason nothing on this page can go out of date and the reason it cannot tell you which rate applies to your supply.
- Rounding is applied to the VAT, and the third figure is derived from the other two. Rounding all three independently would leave them a penny apart whenever both halves fall on a half-penny, which is how an invoice ends up not adding to its own total.
- The currency is presentational and no exchange rate is applied anywhere. VAT arithmetic is the same in pounds, euros or dollars, so the tool follows whichever currency your locale uses rather than pinning one — which is why the worked examples below quote bare figures. A symbol written into the prose would contradict the result panel for every reader outside one country.
- In the price list, a comma is removed as a grouping separator and never read as a decimal point. A pasted "1,234" is one thousand two hundred and thirty-four here. Deciding this from your locale instead would make the same list total two different ways on two machines.
- Which rounding rule is correct is a rule of the tax authority you file with, not a preference. Both options are offered because this page belongs to no country and is in no position to choose one for you.
Common questions
Why does my spreadsheet total differ by a penny or two?
Almost always because it rounds once, at the bottom. A VAT invoice states a tax amount per line, and each of those is rounded before anything is added, so the correct total is the sum of the rounded lines. A spreadsheet that keeps full precision down the column and rounds the SUM is computing a different and slightly smaller number.
Can I mix rates in one list?
Not in one pass. The list applies a single rate to every line, because that is what a rate band means. For an invoice carrying standard-rated and reduced-rated lines, run the standard ones, note the totals, then run the reduced ones separately — which is also how the invoice itself has to state them.
Sources
Method written and checked by Tessalor on Jul 31, 2026.
The full method, worked example and every assumption behind this figure are on VAT Calculator.