Home » Function » Statistics » How to calculate VAT in Excel?

How to calculate VAT in Excel?

Reading time: 2 minutes
Last Updated on 03/05/2023 by Frédéric LE GUEN

This article will show you the formulae to calculate VAT in Excel

Formula to add the tax directly to the price

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.

Calculate the tax only

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

If you have a product priced at 75, calculate VAT with Excel can be done with this simple formula:

=75*16%     => 12

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

=B4*C1

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 AND the VAT amount.

=B4+C4

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%)

Reduce the writing of the formula

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 parentheses.

=75*(1+16%)

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

=B4*(1+$C$1)

Formula to calculate VAT in Excel

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

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

Copy formula to calculate VAT in Excel

Remove VAT

Now, if you want to remove VAT of the price of your product, you will find the formula in this article.

11 Comments

  1. as
    02/06/2022 @ 07:43

    if we received payment against invoice and we know tds % Gst % how get taxable amount for tds and gst seprate .i mean is it possible reverse calculation for tds amount and gst amount.

    Reply

  2. KT
    20/01/2022 @ 10:24

    Thank you for this write up, very helpful!

    Reply

  3. ogh
    13/09/2021 @ 10:51

    Do you have example that includes tax and discount?

    Reply

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

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

      Reply

  4. Otim Alfred
    18/08/2021 @ 22:16

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

    Reply

  5. Otim Alfred
    18/08/2021 @ 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.

    Reply

  6. Amandeep Channi
    15/08/2021 @ 07:21

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

    Reply

  7. SHAJAN
    14/01/2021 @ 06:21

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

    Reply

  8. Tholakele Majola
    21/11/2020 @ 13:12

    I FOUND THIS VERY HELP FULL THANK YOU

    Reply

  9. AMOL HULE
    08/01/2020 @ 15:25

    QTY+RATE+GST9+GST9+AMOUNT VALUE +TOTAL AMOUNT FORMULA

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to calculate VAT in Excel?

Reading time: 2 minutes
Last Updated on 03/05/2023 by Frédéric LE GUEN

This article will show you the formulae to calculate VAT in Excel

Formula to add the tax directly to the price

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.

Calculate the tax only

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

If you have a product priced at 75, calculate VAT with Excel can be done with this simple formula:

=75*16%     => 12

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

=B4*C1

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 AND the VAT amount.

=B4+C4

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%)

Reduce the writing of the formula

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 parentheses.

=75*(1+16%)

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

=B4*(1+$C$1)

Formula to calculate VAT in Excel

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

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

Copy formula to calculate VAT in Excel

Remove VAT

Now, if you want to remove VAT of the price of your product, you will find the formula in this article.

11 Comments

  1. as
    02/06/2022 @ 07:43

    if we received payment against invoice and we know tds % Gst % how get taxable amount for tds and gst seprate .i mean is it possible reverse calculation for tds amount and gst amount.

    Reply

  2. KT
    20/01/2022 @ 10:24

    Thank you for this write up, very helpful!

    Reply

  3. ogh
    13/09/2021 @ 10:51

    Do you have example that includes tax and discount?

    Reply

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

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

      Reply

  4. Otim Alfred
    18/08/2021 @ 22:16

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

    Reply

  5. Otim Alfred
    18/08/2021 @ 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.

    Reply

  6. Amandeep Channi
    15/08/2021 @ 07:21

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

    Reply

  7. SHAJAN
    14/01/2021 @ 06:21

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

    Reply

  8. Tholakele Majola
    21/11/2020 @ 13:12

    I FOUND THIS VERY HELP FULL THANK YOU

    Reply

  9. AMOL HULE
    08/01/2020 @ 15:25

    QTY+RATE+GST9+GST9+AMOUNT VALUE +TOTAL AMOUNT FORMULA

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *