How to Calculate VAT in Excel (2 Handy Ways)

In many situations, you may need to calculate value-added tax or VAT. When you have a tool like Microsoft Excel, you can easily perform this kind of task in bulk. This article demonstrates how to calculate VAT in Excel in two different ways. In addition, the processes of calculating the initial price and price with tax in Excel are also shown in this article.

What Is VAT?

VAT is the short form or the abbreviated form for Value-Added Tax. It is one type of indirect tax levied on products and services at every point of the supply chain i.e. beginning from the raw materials and all the way to the retail price.

Arithmetic Formula to Calculate VAT

In this section, I will show you two arithmetic formulas to calculate VAT. One formula will help calculate VAT from Initial Price or the Net Amount, and the other formula will help calculate VAT from the Price with VAT or the Gross Amount.

Arithmetic Formula for Calculating VAT from 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 VAT from 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.

1. Calculating VAT from the Initial Price (Net Amount) in Excel

Suppose you have a dataset with a list of beverage Items along with their Initial Price and the VAT % imposed upon them. At this point, you need to calculate the VAT Amount. Now, follow the steps below to do so.

Steps:

• First, add a column for the VAT Amount.
• Second, select cell E5 and insert the following formula.

In this case, cell E5 is the starting cell of the column VAT Amount. Cell C5 and Cell D5 are the cells indicating the first cells of the columns Initial Price and VAT % respectively.

• Finally, drag the Fill Handle to the rest of the cell from the remaining column.

2. Calculating VAT from Price with VAT (Gross Amount) in Excel

Now, let us assume that you have a dataset with a list of beverage Items along with their Price with VAT and VAT%. In this case, if you want to calculate their VAT Amount, you can follow the steps below.

Steps:

• At the very beginning, add a column for VAT Amount.
• Next, select cell E5 and insert the following formula.

In this case cell C5 indicates the first cell of the column Price with VAT.

• Lastly, use the Fill Handle to get your Initial Price values for the remaining cells of the column.

How to Calculate Price with VAT (Gross Amount) in Excel

In many cases, you may need to find out the total Price with VAT. The arithmetic formula to calculate Price with VAT is as follows:

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.

Now, suppose you have a dataset with a list of beverage Items along with their Initial Price and the VAT % imposed upon them. I will show you how to calculate Price with VAT in Excel using the arithmetic formula.

Steps:

• First, add a column for the Price with VAT.
• Second, select cell E5 and insert the following formula.

In this case, cell E5 is the first cell of the column Price with VAT. Cell C5 and cell D5 are the first cells in the column.

• Finally, drag the Fill Handle for the rest of the column.

Read More: Formula for Adding VAT in Excel

How to Calculate Initial Price (Net Amount) in Excel

Also, you may need to find out the Initial Price or the Net Amount in some cases. The arithmetic formula to calculate the Initial Price is as follows:

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.

Now, let us assume that you have a dataset with a list of beverage Items along with their Price with VAT and VAT%. In this case, I will show you how to calculate Initial Price in excel with the help of this arithmetic formula.

Steps:

• First, add a column for Initial Price.
• Next, select cell E5 and insert the following formula.

In this case, cell E5 indicates the first cell of the column Initial Price. Cell C5 and cell D5 are the first cells of the column Price with VAT and VAT % respectively.

• Lastly, use the Fill Handle to get your Initial Price values for the remaining cells of the column.

Download Practice Workbook

You can download the practice workbook from the link below.

Conclusion

Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below.

Vat Formula In Excel: Knowledge Hub

• How to Calculate VAT from Gross Amount in Excel
• How to Remove VAT Using Excel Formula
• How to Calculate 15% VAT in Excel

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