How to Calculate VAT from Gross Amount in Excel (2 Examples)

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.

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 is less than 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.

How to Calculate VAT from Gross Amount in Excel: 2 Examples

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 as well as the VAT rate. Now, we need to calculate VAT from the gross amount.

The formula for calculating the VAT from gross is.

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

1. Calculate the Included VAT of 20% from the 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.

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.

• 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.

Read More: How to Calculate 15% VAT in Excel

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.

Let’s see the procedures to calculate a 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.

• 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.

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.

• 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.

Calculate the 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.

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 chose cell E5 as our selection.
• Then, in that selected cell, type in the formula below.

• Press the Enter key from your keyboard.

• The result will now be displayed in the selected cell, along with the formula in the formula bar.
• Further, drag the Fill Handle down to duplicate the formula across the range. Alternatively, to AutoFill the range, double-click the plus (+) symbol.

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

Download Practice Workbook

You can download the workbook and practice with them.

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 blog!

Related Articles

• How to Remove VAT Using Excel Formula

<< Go Back to Vat Formula In Excel | Excel Formulas for Finance | Excel for Finance | Learn Excel