# 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.
`=(C5*D5)`

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/(1+D5))*D5`

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.
`=C5+(C5*D5)`

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.

## 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.
`=C5/(1+D5)`

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.

## Vat Formula In Excel: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!
Sowmik Chowdhury

Sowmik Chowdhuri, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a crucial Excel & VBA Content Developer at ExcelDemy. His profound passion for research and innovation seamlessly aligns with his unwavering dedication to Excel. In this role, Sowmik not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, highlighting his steadfast commitment to consistently deliver content of exceptional quality and value. Read Full Bio