To remove the VAT of a price in Excel, you must consider 2 situations
- Calculate the original price (price without the VAT)
If you have a list of prices that include the VAT, the next formula will return the price without VAT
To remove VAT (Value Added Tax) from a price using Excel, you can use a simple formula to calculate the original price before VAT - Calculate the amount of the VAT
This second formula will extract only the VAT amount of the price
Calculate the original price
In a cell where you want the original price without VAT, enter the following formula:
=Price with VAT/(1+Tax rate)
In our example, the formula is
=B4/(1+$C$1)
You divide the price of the product by 1 + VAT rate.
- For the product 1, the price without VAT is 75 😀
Extract the VAT of the price
The formula to extract only the VAT is a little bit more complex with Excel. The formula is the following:
=(Full price * Tax rate)/(1+Tax rate)
=(B4*$C$1)/(1+$C$1)
Now in column D, you have only the amount of the tax. And of course, if you subtract this amount from the price with VAT, you find the price without VAT
Price without VAT = Full Price - VAT amount
11/08/2025 @ 11:39
Corrections to this page are required.
If I start with a value of £100 that includes VAT at the 20% rate the tutorial suggests I do the following:
(100 * 20) / (1+20)
= 2000 / 21
=95.238
This number is of absolutely no use to anybody. It is not the original price excluding VAT and it is not the VAT amount. Nor would it be the VAT amount if it were subtracted from the price including VAT.
As Seth tried to point out almost two years ago there is an accurate way to do this:
VAT AMOUNT = PriceIncVAT - ((PriceIncVAT/(1+(VAT RATE/100)))
or
16.67 = 100 - (100/(1+(20/100)))
16.67 = 100 - (100/1.2)
16.67 = 100 - 83.333
This correctly identifies the VAT amount and can be readily confirmed by calculating the final price from the ex-VAT price:
83.333 * 1.2 = 100.
10/01/2025 @ 17:00
what fomular did you use for the other part
10/01/2025 @ 23:47
Which "other part"?
31/08/2023 @ 00:06
Sorry but it doesn't make sense to me, this works for me.
="cell of the price"-("cell of the price"/1."VAT Rate")
20% VAT
=A1-(A1/1.2)
10% VAT
=A1-(A1/1.1)
5% VAT
=A1-(A1/1.05)