How to Calculate VAT in Excel (Formula)

Last Updated: November 21, 2023
puneet-gogia-excel-champs

- Written by Puneet

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.

calculate-vat

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:

  1. First, refer to the cell with the invoice amount.
  2. After that, enter the asterisk (*) operator for multiplication.
  3. Now, refer to the cell where you have the VAT percentage.
  4. In the end, hit enter to get the tax amount.
calculate-vat-separately
=tax_percentage * invoice_amount

Once you hit enter, you’ll get the VAT amount in the cell.

resulted-vat-amount

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.

add-tax-to-the-amount
=vat_amount + invoice_amount

This will give you the total amount, including the VAT amount.

total-amount-including-vat

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:

calculate-vat-in-total-amount
=(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)
other-formula-to-calculate-vat-in-total

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.

Get the Excel File