What is the VAT?
The VAT (Value Added Tax) is a tax that is added to any products or good. The tax is an official rate defines by countries or states and it is express by a percentage.
For companies it is important to separate the prices with the taxes and the prices without taxes.
Formulas to calculate price with tax
For all the formulas, we consider that the tax rate is 16%
Amount of the tax
If you have a product that has a price of 75, the amount of the tax is
=75*16% => 12
But in Excel, values are stored in cells. So your formula must used the reference of the cells like this
Price with the tax
To know the value of a product with the tax, you just have to add the product price + the amount of tax
You can also calculate the value of your product with tax in a single formula.
Explanation of the calculation
- First, we use the price of the product (75)
- And we add the calculation of the amount of tax for this product (75*16%)
Of course, you have noticed that you have 2 times the value 75 in the formula. So we can use a maths rule to simplify the formula. We extract the value 75 and write the rest with parenthesis.
And if you replace the value of the formula by the references of the cells, your formula is
The dollars around the reference C1 mean that the reference is locked. It is call an absolute reference.
Like that, you can copy the formula and all the formulas are linked to the cell C1.
Remove the tax
Most interesting, is to split a price between
- The price without tax
- The tax only
Calculate the price without tax
To calculate the price of product without the VAT, you have to divide the price of the product by 1+VAT rate
=Full price/(1+Tax rate)
Extract the tax only
The formula to extract only the tax from a price you have to do the formula
=(Full price * Tax rate)/(1+Tax rate)