Calculate VAT in Excel

In this tutorial, we explain two ways how to calculate Value Added Tax (VAT) in Excel.

VAT is a type of indirect tax that is applied to goods and services. It is levied at various points of the supply chain where value is added from the beginning of production to the point of sale.

There are two situations in which we can calculate the VAT amount: calculate the VAT amount based on the initial price and calculate the VAT amount based on the final price.

Calculate the VAT amount based on the Initial Price

Sometimes we may want to calculate the VAT amount to charge based on the initial price of the products we are retailing.

We will look at how to do the VAT calculation based on varying tax rates and a single tax rate for different products.

Based on varying tax rates

The formula for calculating the VAT amount based on the Initial Price (IP) is:

VAT Amount = IP * VAT%

In this formula, IP stands for the Initial Price of the product or service in currency units. The initial price is the price without tax or VAT. VAT% stands for the percentage of VAT levied on the product or service.

We will use the following dataset with varying tax rates to show how we can use the formula to generate VAT amount based on the Initial Price:

We use the following steps:

1. Select cell D2 and type in the formula:

1. Enter the formula by pressing the Enter key on the keyboard or Enter option on the formula bar:

1. In cell D2 double-click or drag down the fill handle to copy the formula down the column:

Based on a single tax rate

The formula for calculating the VAT amount to charge on the Initial Price (IP) is:

VAT Amount = IP * VAT%

Suppose the tax rate for all the products we are retailing is 20%. Instead of repeating the value for each product in a VAT% column, we can enter this tax rate in a cell and refer to it with absolute reference in our formula.

We will use the following dataset to show how we can achieve this:

Follow the steps below:

1. Select cell A8 and type in “Tax Rate:“
2. Select cell B8 and type in 20%.
3. Select cell C2 and type in the formula =B2*$B$8 as below:

Instead of typing the dollar signs in cell reference $B$8 manually, we can press the F4 key after we type the cell reference and Excel automatically makes the cell reference absolute.

Absolute cell reference means that the cell reference does not change as we copy the formula down the column.

1. Enter the formula by pressing the Enter key on the keyboard or Enter option on the formula bar.
2. In cell C2 double-click or drag down the fill handle to copy the formula down the column:

One advantage of calculating the VAT amount this way is that should the tax rate change, we need to change it in only one cell instead of changing it in many cells, and the data updates accordingly. This saves time and reduces the chances of making errors.

Calculate the VAT amount based on the Final Price

Sometimes we may want to calculate the amount of VAT we have paid on the goods we have bought.

We will demonstrate how to do the VAT calculation based on varying tax rates and a single tax rate for different products.

Based on varying tax rates

The formula for calculating the VAT amount based on the Final Price (FP) is:

VAT Amount = (FP / (1 + VAT%) * VAT%)

In this formula, FP stands for the Final Price which is the price with VAT. VAT% stands for the percentage of VAT levied on the products or services.

We will use the following dataset with varying tax rates to demonstrate how we can use the formula to calculate the VAT amount based on the Final Price:

We use the following steps:

1. Select cell D2 and type in the formula:

1. Enter the formula by pressing the Enter key on the keyboard or Enter option on the formula bar:

1. In cell D2 double-click or drag down the fill handle to copy the formula down the column:

Based on a single tax rate

The formula for calculating the VAT amount based on the Final Price (FP) is:

VAT Amount = (FP / (1 + VAT%) * VAT%)

Suppose the tax rate for all the products in the dataset is 15%. Instead of repeating this tax rate for each product in a VAT% column, we can enter this tax rate in a cell and refer to it with absolute reference in our formula.

We will use the following dataset to show how we can do this:

We use the following steps:

1. Select cell A8 and type in “Tax Rate:“
2. Select cell B8 and type in 15%.
3. Select cell C2 and type in the formula =(B2/(1+$B$8))*$B$8 as below:

Instead of typing the dollar signs in the cell reference $B$8 manually, we can press the F4 key after we type the cell reference and Excel automatically makes the cell reference absolute.

Absolute cell reference means that the cell reference does not change as we copy the formula down the column.

1. Enter the formula by pressing the Enter key on the keyboard or Enter option on the formula bar.
2. In cell C2 double-click or drag down the fill handle to copy the formula down the column:

If the tax rate changes, we need to change it in only one cell, and the data updates automatically. This saves time and reduces data entry errors.

Additional information

We can confirm that absolute cell references do not change as we copy the formula down the column by showing the formulas instead of the formula results. We do this by selecting cell C2 and pressing Ctrl + (this key is on top of the Tab key).

Press Ctrl + again to hide the formulas.

Conclusion

In this tutorial, we have demonstrated different ways of calculating VAT in Excel.

We looked at how to calculate the VAT amount based on the initial price and the final price. In both situations, we demonstrated how to calculate the VAT amount based on varying tax rates and a single tax rate.