Formulas to include or exclude tax

Reading Time: 2 minutes

What is VAT?

VAT (Value Added Tax) is a tax that is often added to goods or services. Each country or state defines it's own VAT rates (in percent). Also, for companies it is important to separate the price and the VAT.

Basic calculations in Excel

If you have some problems with percentage calculations, have a look at this video for the basic rules for calculations in Excel.

Formulas to calculate price including tax

For all examples, we will use a VAT rate of 16%.

Calculate Tax (VAT)

If you have a product priced at 75 (excluding VAT), the VAT amount can be calculated as follows:

=75*16%     => 12

But in Excel, values are stored in cells. So your formula must use the cell references like this


Formula to calculate the tax only

Price including VAT (Price + Tax)

To calculate the price including VAT, you just have to add the product price + the VAT amount.


Formula to add the price and the tax
Formula to add the price and the tax

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 take 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


Formula to add the tax directly to the price

The dollars around the reference C1 mean that the reference is locked. It is call an absolute reference.

In this way you can copy the formula, and all the formulas are linked to cell C1.

Animated VAT example

Remove the tax (VAT)

The most interesting thing to do is to split a price into:

  • The price excluding tax
  • The tax

Calculate the price excluding tax (VAT)

To calculate the price of product excluding 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 is:

=(Full price * Tax rate)/(1+Tax rate)


Related posts

Statistics functions in Excel

Frédéric LE GUEN

Basic calculation

Frédéric LE GUEN

Understand the Standard Deviation

Frédéric LE GUEN


ogh 13/09/2021 at 10:51

Do you have example that includes tax and discount?

Frédéric LE GUEN 13/09/2021 at 16:17

What do you mean by discount? An amount or a percentage?

Otim Alfred 18/08/2021 at 22:16

I thank you so much for the microsoft excel lessons which I have learned from you.

Otim Alfred 18/08/2021 at 22:00

I thank you so much for the Microsoft excel lessons which I have learned from you and please keep it up with the good Spirit in you.

Amandeep Channi 15/08/2021 at 07:21

Thanks a ton.. you made it look so easy.. this helped me save hours of calculation and running around people.

SHAJAN 14/01/2021 at 06:21

how to calculate Product value 50 tax 15% Vat 5%

Tholakele Majola 21/11/2020 at 13:12


AMOL HULE 08/01/2020 at 15:25



Leave a Comment