To calculate VAT in Excel, you need to know the exact tax percentage, then multiply it by the gross amount to get the vat tax amount. After that, add that tax amount to the actual invoice amount.
In this tutorial, we write a formula to calculate the VAT. Let’s check this out.
Calculate VAT Separately
You can use the below steps:
- First, refer to the cell with the invoice amount.
- After that, enter the asterisk (*) operator for multiplication.
- Now, refer to the cell where you have the VAT percentage.
- In the end, hit enter to get the tax amount.
=tax_percentage * invoice_amount
Once you hit enter, you’ll get the VAT amount in the cell.
In the next part, you need to add the tax to the total amount. For this, you need to refer to the cell with the gross invoice amount, enter the addition operator, and refer to the cell with the VAT amount (TAX) you have calculated.
=vat_amount + invoice_amount
This will give you the total amount, including the VAT amount.
Calculating VAT in the Total Amount
If somehow you want to get the VAT amount included in the total amount, you can use the formula below:
=(tax_percentage * invoice_amount) + invoice_amount
With the above formula, you don’t need a separate column for the VAT amount. The total amount includes the tax and the invoice amount.
There’s one more way to write this formula. In this formula, you can use the percentages to get the total amount with the VAT.
=invoice_amount * (100% + vat_percentage)
This formula works exactly like the first one, but we use the percentage instead.
When you use 100% and add the tax percentage, for example, 10%, multiply it with the invoice amount. It increases the amount with the exact tax percentage, and you get the total amount, including tax.