The reverse VAT formula
The gross price is the net price plus VAT on the net price: gross = net × (1 + rate). Turn that around and you get the two formulas you need:
Net = Gross ÷ (1 + rate)VAT = Gross − Net, or in one stepVAT = Gross × rate ÷ (1 + rate)
Example at 20%: a chair costs $1,440.00 including VAT. Net = 1,440 ÷ 1.20 = $1,200.00, and VAT = 1,440 − 1,200 = $240.00. Check: 1,200 × 1.20 = 1,440. The check always works if the net figure is right.
Why "minus 20%" gives the wrong answer
The rate applies to the net price, not to the gross. Taking 20% off $1,440 gives a VAT of $288.00 and a net price of $1,152.00. Add 20% back to that net price and you get $1,382.40, not $1,440. The error is always too much VAT and too little net, and on a full set of purchase invoices it adds up quickly.
Worked example: VAT out of five gross prices
Gross prices and rates are inputs. The net price is rounded to cents and the VAT is the difference, so every row adds up exactly.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Gross price | VAT rate | Net price | VAT |
| 2 | Office chair | 1,440.00 | 20% | 1,200.00 | 240.00 |
| 3 | Printer toner | 89.99 | 20% | 74.99 | 15.00 |
| 4 | Child car seat | 157.50 | 5% | 150.00 | 7.50 |
| 5 | Book | 18.90 | 0% | 18.90 | 0.00 |
| 6 | Laptop (19% rate) | 1,071.00 | 19% | 900.00 | 171.00 |
| 7 | Total | 2,777.39 | 2,343.89 | 433.50 |
- D2
=ROUND(B2/(1+C2),2)Net from gross - E2
=B2-D2VAT as the difference - E7
=SUM(E2:E6)Total VAT
The rates and items are illustrative. For a real return, use the rates and rules of the country where the sale takes place.
The VAT fraction: a quick mental check
Because VAT = gross × rate ÷ (1 + rate), the VAT share of a gross price is a fixed fraction for each rate:
| VAT rate | Divide gross by | VAT share of gross |
|---|---|---|
| 20% | 1.20 | 20/120 = 1/6 |
| 19% | 1.19 | 19/119 |
| 10% | 1.10 | 10/110 = 1/11 |
| 5% | 1.05 | 5/105 = 1/21 |
So at 20%, one sixth of any VAT-inclusive price is VAT: $240 is one sixth of $1,440. Rates differ from country to country and between goods, so look up the rate that applies before you calculate.
Setting it up in Excel
Keep the rate in its own column, typed as a percentage (20%, not 20), so one formula works for every rate:
- Net price:
=ROUND(B2/(1+C2),2) - VAT:
=B2-D2
Round the net price to cents and calculate VAT as the difference. Then net + VAT always equals the gross price exactly. If you round both separately, they can miss the gross by a cent. Zero-rated items fit the same formula: at 0%, the net equals the gross and the VAT is 0.
Rounding per line or per invoice
On an invoice with many lines, rounding the VAT on every line and adding the results can differ by a cent or two from calculating VAT once on the invoice total. Which method is allowed depends on local rules and on your accounting software. Choose one, apply it consistently, and make sure the VAT in your books matches the VAT printed on the invoice.
Why businesses need the VAT inside a receipt
Most purchase receipts show gross amounts. A VAT-registered business can usually reclaim the VAT on its business purchases as input tax, but only the VAT, not the whole price. So every gross receipt has to be split: the net amount is the cost that goes to the expense account, and the VAT goes to a separate input-tax account that is offset against the VAT charged on sales. Whether a purchase qualifies depends on local rules, and some costs are partly or fully non-deductible.
Going the other way: net to gross
From a net price, multiply: VAT = net × rate and gross = net × (1 + rate). In Excel: =ROUND(B2*C2,2) for VAT and =B2+D2 for the gross. Keep a clear label on every price column, saying net or gross, because mixing them up is the most common source of VAT errors in spreadsheets.
Common mistakes
- Multiplying the gross price by the rate. 20% of a gross price is not the VAT. Divide by 1 + rate first.
- Typing the rate as 20 instead of 20%. Excel then divides by 21 instead of 1.20.
- Rounding net and VAT separately, so that the two no longer add up to the gross price.
- Mixing net and gross prices in one column, for example supplier prices without VAT next to shop receipts with VAT.
- Assuming one rate for everything. Many countries have reduced rates, zero-rated items and exempt supplies.
Frequently asked questions
How do I remove 20% VAT from a price?
Divide the gross price by 1.20 to get the net price. The VAT is the gross price minus the net price, which is one sixth of the gross price.
What is the formula for VAT from a gross amount in Excel?
With the gross price in B2 and the rate in C2, the net price is =ROUND(B2/(1+C2),2) and the VAT is =B2-D2, where D2 holds the net price.
Is reverse VAT the same as reverse charge?
No. Reverse VAT, as used here, means calculating the VAT contained in a gross price. Reverse charge is a rule for certain cross-border or domestic supplies, under which the buyer accounts for the VAT instead of the seller.
Does this work for GST and sales tax?
Yes for GST and other value-added taxes, which work the same way. US-style sales tax is usually added at the till rather than included in the shelf price, but the maths of removing it from a total is identical.