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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

2 Examples to Calculate VAT from Gross Amount in Excel

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.

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.

2 Examples to Calculate VAT from Gross Amount in Excel

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

2 Examples to Calculate VAT from Gross Amount in Excel

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

VAT = Gross Price / 1.05 * 0.05

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

2 Examples to Calculate VAT from Gross Amount in Excel

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

2 Examples to Calculate VAT from Gross Amount in Excel

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

2 Examples to Calculate VAT from Gross Amount in Excel

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


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.

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

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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo