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 initial price and price with tax in excel are also shown in this article.


Download Practice Workbook

You can download the practice workbook from the link below.


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.

2 Ways to Calculate VAT in Excel

There are two types of circumstances from which you may need to calculate VAT in excel. Now, I will show you two ways to calculate VAT from both of those circumstances.

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.

how to calculate vat in excel dataset 1

Steps:

  • First, add a column for the VAT Amount.
  • Second, select cell E5 and insert the following formula.
=(C5*D5)

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.

vat from initial price

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

vat from initial price


2. Finding out 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.

dataset 2

Steps:

  • At the very beginning, add a column for VAT Amount.
  • Next, select cell E5 and insert the following formula.
=(C5/(1+D5))*D5

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

from price with VAT

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

from price with VAT


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

how to calculate vat in excel dataset 1

Steps:

  • First, add a column for the Price with VAT.
  • Second, select cell E5 and insert the following formula.
=C5+(C5*D5)

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.

calculate price with VAT in excel

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

calculate price with VAT in excel


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

dataset 2

Steps:

  • First, add a column for Initial Price.
  • Next, select cell E5 and insert the following formula.
=C5/(1+D5)

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.

calculate initial price in excel

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

calculate initial price in excel


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. Also, if you want to read more articles like this, you can visit our website ExcelDemy.

Sowmik Chowdhury
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo