How to Calculate the VAT in Excel – 2 Methods

What Is the VAT?

VAT is the short form or the abbreviated form for Value-Added Tax.

Arithmetic Formula to Calculate VAT

There are two arithmetic formulas to calculate VAT. One formula will calculate VAT from the Initial Price or the Net Amount, whereas the other will calculate VAT from the Price with VAT or the Gross Amount.

Arithmetic Formula to Calculate the VAT from the Initial Price

VAT Amount = IP * VAT %

In this formula,

• VAT Amount = The amount of VAT in currency units.
• IP = Initial price or net amount in currency units.
• VAT % = The percentage of VAT imposed on the product or service.

Arithmetic Formula for Calculating the VAT from a Price with VAT

VAT Amount= (FP/(1+VAT%))*VAT%

In this formula,

• VAT Amount = The amount of VAT in currency units.
• FP = Price with VAT or the Gross Amount in currency units..
• VAT % = The percentage of VAT imposed on the product or service.

Method 1 – Calculating the VAT from the Initial Price (Net Amount) in Excel

This is the sample dataset.

Steps:

• Add a column for the VAT Amount.
• Select E5 and enter the following formula.

E5 is the first cell in the column VAT Amount. C5 and D5 are the first cells in the columns Initial Price and VAT %.

• Drag the Fill Handle to Autofill the rest of the cells.

Method 2 – Calculating the VAT from a Price with VAT (Gross Amount) in Excel

This is the sample dataset.

Steps:

• Add a column for VAT Amount.
• Select E5 and enter the following formula.

C5 is the first cell in the column Price with VAT.

• Use the Fill Handle to see the Initial Price values in the rest of the cells.

How to Calculate Price with VAT (Gross Amount) in Excel

The arithmetic formula to calculate Price with VAT is:

FP=IP+(IP*VAT %)

In this formula,

• FP = Price with VAT or the Gross Amount in currency units..
• VAT % = The percentage of VAT imposed on the product or service.
• IP = Initial price or net amount in currency units.

This is the sample dataset.

Steps:

• Add a column for the Price with VAT.
• Select E5 and enter the following formula.

E5 is the first cell in the column Price with VAT. C5 and D5 are the first cells in the columns Initial Price and VAT%.

• Drag the Fill Handle to fill the rest of the cells.

Read More: Formula for Adding VAT in Excel

How to Calculate the Initial Price (Net Amount) in Excel

The arithmetic formula to calculate the Initial Price is:

IP=FP/(1+VAT%)

In this formula,

• FP = Price with VAT or the Gross Amount in currency units.
• VAT % = The percentage of VAT imposed on the product or service.
• IP = Initial price or net amount in currency units.

This is the sample dataset.

Steps:

• Add a column for Initial Price.
• Select E5 and enter the following formula.

E5 is the first cell in the column Initial Price. C5 and D5 are the first cells in the column Price with VAT and VAT %.

• Use the Fill Handle to see the Initial Price values in the rest of the cells.

Download Practice Workbook

Download the practice workbook here.

Vat Formula In Excel: Knowledge Hub

• How to Calculate VAT from Gross Amount in Excel
• How to Remove VAT Using Excel Formula
• How to Calculate 15% VAT in Excel

<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel