How to calculate VAT: formulas and worked examples
VAT really comes down to two formulas. This guide covers both: adding VAT to a price, and taking VAT back out of one, plus a formula you can paste straight into Excel or Google Sheets.
The two VAT formulas
| Direction | Formula | Example (20% VAT, £100 net) |
|---|---|---|
| Add VAT (net → gross) | Gross = Net × (1 + rate) | £100 × 1.20 = £120 |
| Remove VAT (gross → net) | Net = Gross ÷ (1 + rate) | £120 ÷ 1.20 = £100 |
| VAT element | VAT = Gross − Net | £120 − £100 = £20 |
Worked example: adding VAT
A freelance designer invoices £450 for a project, before VAT. At the standard 20% rate:
- VAT = £450 × 0.20 = £90
- Gross invoice total = £450 + £90 = £540
Worked example: removing VAT
A till receipt shows a total of £84, including VAT at the standard rate. To find the net cost:
- Net = £84 ÷ 1.20 = £70
- VAT element = £84 − £70 = £14
A common mistake is taking 20% off the gross figure directly (£84 × 0.80 = £67.20). That's wrong, because the 20% was originally calculated on the smaller net amount, not the gross total.
Excel / Google Sheets formula
With a gross price in cell A1 and a 20% VAT rate:
- Net amount:
=A1/1.2 - VAT amount:
=A1-(A1/1.2) - For a variable rate held in cell
B1(e.g. 0.2 for 20%):=A1/(1+B1)
Quick mental-maths shortcut
For standard-rated (20%) prices, remembering "divide by 1.2" to go from gross to net, and "multiply by 1.2" to go from net to gross, covers most everyday VAT calculations without needing a calculator at all.
Try it yourself
Enter an amount in pounds and pence.
Frequently asked questions
What is the VAT-inclusive and VAT-exclusive formula? +
VAT-exclusive (net) to VAT-inclusive (gross): Gross = Net x (1 + VAT rate). VAT-inclusive to VAT-exclusive: Net = Gross ÷ (1 + VAT rate). For 20% VAT, that's Gross = Net x 1.20, and Net = Gross ÷ 1.20.
What is the Excel formula to remove VAT? +
If your gross price is in cell A1 and the VAT rate is 20%, use =A1/1.2 to get the net amount, and =A1-(A1/1.2) to get the VAT element. For a variable rate in cell B1 (as a percentage), use =A1/(1+B1).
How do I calculate VAT from a gross figure quickly? +
Divide the gross figure by 1.20 (for 20% VAT) to get the net amount instantly, then subtract that from the gross figure to find the VAT. This "divide by 1.2" shortcut is the fastest mental-maths method for standard-rated UK prices.