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.


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

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

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.


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.

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


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!

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo