While working with **Microsoft Excel **from business, sometimes we are only allowed to view the VAT rate and the gross amount of any product. But we don’t have any idea about the VAT amount. When a customer wants the details about the VAT we may not be able to provide them immediately. That’s why it’s important to know the calculation of the VAT amount from the gross amount. In this article, we will demonstrate different examples to calculate **VAT** from the gross amount in Excel.

**Table of Contents**hide

**Download Practice Workbook**

You can download the workbook and practice with them.

**What Is VAT?**

A consumption tax is known as a **value-added tax** (**VAT**). It is imposed on services and products at every step of the supply chain sequence in which the revenue is generated. The cost of goods less any expenses of components that are already taxed. In contrast to earnings, VAT is determined by consumption. Actually, the value-added tax (**VAT**) is a type of indirect tax.

**2 Examples to Calculate VAT from Gross Amount in Excel**

The ability to apportion the amount of tax to various manufacturing phases depending on the quality added at each stage is a fundamental benefit of **VAT** over sales tax. Suppose, we have the following dataset. The dataset contains some products and the gross amount from each product also the VAT rate. Now, we need to calculate VAT from the gross amount.

The formula for calculating the VAT from gross is.

**VAT = Gross Price / (1 + VAT Rate) * VAT Rate**

Let’s see the examples to generate the vat amount from gross with an Excel spreadsheet.

**1. Calculate Included VAT of 20% from Gross Amount**

In the first example, we have a **VAT** of **20%**. Now, if we want to calculate **VAT** from the gross amount, the formula will be.

**VAT = Gross Amount / 1.20 * 0.20**

Let’s follow the steps down to calculate vat from the gross amount.

**STEPS:**

- Firstly, select the cell where you want to put the formula to calculate
**VAT**from the gross amount. So, we select cell**E5**. - Secondly, enter the below formula into that selected cell.

`=C5/1.2*D5`

- Thirdly, press
**Enter**to see the result in that selected cell.

- Now, you will be able to see the result in the selected cell and the formula will appear in the formula bar.
- Further, to copy the formula over the range, drag the
**Fill Handle**down. Or, double click on the plus (**+**) sign to**AutoFill**the range.

- And, that’s it! The
**VAT**from the gross amount is calculated in the resulting cell.

**2. Compute 5% of VAT Included from Gross Amount in Excel**

The **VAT** in the first scenario is **5%**. The calculation will be if we wish to generate VAT from the gross amount.

**VAT = Gross Price / 1.05 * 0.05**

Let’s see the procedures to calculate vat of **5%** from the gross amount.

**STEPS:**

- To begin with, choose the cell where you want to see the calculation of
**VAT**from the gross amount. In this case, we choose cell**E5**. - Then, type the formula below into that cell.

`=C5/1.05*D5`

- Next, hit the
**Enter**key to complete the process.

- You will now be able to analyze the data in the selected cell, as well as the formula in the formula bar.
- Now, drag the
**Fill Handle**down to duplicate the formula over the range. Or, to**AutoFill**the range, double-click on the plus (**+**) symbol.

- Finally, column
**E**shows the result of the**VAT**from the gross amount.

**Calculate Net Amount Without VAT of 20% from Gross in Excel**

The net amount is the amount that a good or service is sold for after all taxes and other expenses are applied and all reductions are deducted. Mainly, whatever a consumer pays is the net price. The formula for calculating net price without a VAT of 20% is.

**Net Price = Gross Price / 1.20**

Let’s find the net amount for each product.

**STEPS:**

- Firstly, to compute the net amount without
**VAT**, choose the cell into which the formula will be typed. As a consequence, we have decided to go with cell**E5**. - Secondly, type the formula below into the selected cell.

`=C5/1.2`

- Thirdly, press the
**Enter**key to finish the procedure.

- The result, as well as the formula in the formula bar, will now appear in the selected cell.
- After that, to copy the formula across the range, drag the
**Fill Handle**down. Alternatively, double-click the plus (**+**) sign to**AutoFill**the range.

- That’s everything for now! Last but not least, column
**E**shows the net amount for each product.

**Note: **If the vat rate is **5%**, the formula will be changing. And the formula will be **Gross Price / 1.05 = Net Price.**

**Calculate Gross Amount with VAT of 20% in Excel**

Gross refers to the whole or entire amount of something. The formula for calculating the gross amount with vat 20% is.

**Gross Price = Net Price * 1.20**

Now, let’s evaluate the gross amount with VAT.

**STEPS:**

- In the beginning, similarly as before, to calculate the gross amount with
**VAT**, select the cell where the formula will be entered. As a result, we choose cell**E5**as our selection. - Then, in that selected cell, type in the formula below.

`=C5*1.2`

- Press
**Enter**key from your keyboard.

- The result will now display in the selected cell, along with the formula in the formula bar.
- Further, drag the
**Fill Handl**e down to duplicate the formula across the range. Alternatively, to**AutoFil**l the range, double-click the plus (**+**) symbol.

- Lastly, you can see the result for the gross amount in column
**E**.

**Note: **The calculation will change if the vat rate reaches **5%**. Additionally, the formula is **Net Price * 1.05 = Gross Price**.

## Conclusion

The above examples will assist you to **Calculate VAT from Gross** **in Excel**. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can have a glance at our other articles in the **ExcelDemy.com** blog!