The formulas to include or exclude tax

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

=B4*C1

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

=B4+C4

You can also calculate the value of your product with tax in a single formula.

=75+75*16%    =>87

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.

=75*(1+16%)

And if you replace the value of the formula by the references of the cells, your formula is

=B4*(1+$C$1)

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)

=B4/(1+$C$1)

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)

=(B4*$C$1)/(1+$C$1)

Related posts


Permanent link to this article: https://www.excel-exercise.com/the-formulas-to-include-or-exclude-tax/


Leave a Reply

Your email address will not be published.