# How to calculate VAT in Excel?

Last Updated on 30/03/2024
Reading time: 2 minutes

Calculate the VAT is not difficult to do in Excel

1. No Excel function needed

Only basic mathematical operators

2. Important to understand how to use the \$ in the formula

To avoid mistakes in your calculation, it's important to know how to block reference

## What is VAT?

• Value Added Tax (VAT) is a tax applied to goods and services.
• Individual countries or states determine the VAT rates and express them as percentages.
• It is crucial for businesses to distinguish between the price of an item or service and the VAT charged.

## Calculate the tax only

We will use a VAT rate of 16% for all the examples in this article. For instance, to calculate the VAT of a good with a price of 75, the formula is the following

=75*16%     => 12

But when you work with Excel, values are stored in cells. So it's easier to build your formula with the cell references like this

=B4*C1

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

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)

The dollars around reference C1 mean that the reference is locked. The dollars are important for copying the formula. As you can see in the next animation, all the formulae use the cell C1 👍

## Frédéric LE GUEN

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.

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

Thank you for this write up, very helpful!

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

Do you have example that includes tax and discount?

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

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

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

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

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.

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.

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

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

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

I FOUND THIS VERY HELP FULL THANK YOU

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

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

# How to calculate VAT in Excel?

Reading time: 2 minutes
Last Updated on 30/03/2024

Calculate the VAT is not difficult to do in Excel

1. No Excel function needed

Only basic mathematical operators

2. Important to understand how to use the \$ in the formula

To avoid mistakes in your calculation, it's important to know how to block reference

## What is VAT?

• Value Added Tax (VAT) is a tax applied to goods and services.
• Individual countries or states determine the VAT rates and express them as percentages.
• It is crucial for businesses to distinguish between the price of an item or service and the VAT charged.

## Calculate the tax only

We will use a VAT rate of 16% for all the examples in this article. For instance, to calculate the VAT of a good with a price of 75, the formula is the following

=75*16%     => 12

But when you work with Excel, values are stored in cells. So it's easier to build your formula with the cell references like this

=B4*C1

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

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)

The dollars around reference C1 mean that the reference is locked. The dollars are important for copying the formula. As you can see in the next animation, all the formulae use the cell C1 👍

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.

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

Thank you for this write up, very helpful!

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

Do you have example that includes tax and discount?

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

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

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

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

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.

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.

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

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

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

I FOUND THIS VERY HELP FULL THANK YOU

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

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