Calculate the VAT is not difficult to do in Excel
- No Excel function needed
Only basic mathematical operators
- 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?
VAT (Value Added Tax) is a tax that is 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 the examples in this article, we will use a VAT rate of 16%. 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
Price including VAT (Price + Tax)
To calculate the price including VAT, you just have to add the product price AND the VAT amount.
You can also calculate the value of your product with tax in a single formula.
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.
And if you replace the value of the formula with the references of the cells, your formula is
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 👍
You can practice, you can do these exercises
Now, if you want to remove VAT of the price of your product, you will find the formula in this article.