Value Added Tax, often abbreviated VAT, is a tax added to the sales of goods and services.
For example, suppose the price of a given product is $10.
If the VAT rate is 20%, then the final price of the product after VAT is added is:
Price with VAT = $10 * (1 + 0.20) = $10 * 1.2 = $12
You can use the following formulas to add or remove VAT from prices in Excel:
Formula 1: Add VAT to Price
=B2*(1+$F$1)
Formula 2: Remove VAT from Price
=B2/(1+$F$1)
Both formulas assume the price of a good is located in cell B2 and the VAT tax rate is located in cell F1.
The following examples show how to use each formula in practice.
Example 1: Add VAT to Prices in Excel
Suppose we have the following list of products with prices in Excel:
We can type the following formula into cell C2 to add a VAT rate of 20% to the price in cell B2:
=B2*(1+$F$1)
We can then click and drag this formula down to each remaining cell in column C:
Column C now shows each price in column B with the VAT added to it.
For example:
- A $10 item with a 20% VAT rate becomes $12.
- A $15 item with a 20% VAT rate becomes $18.
- A $20 item with a 20% VAT rate becomes $24.
And so on.
Example 2: Remove VAT from Prices in Excel
Suppose we have the following list of products with prices that already have VAT added to them in Excel:
We can type the following formula into cell C2 to remove a VAT rate of 20% from the price in cell B2 to find the price before the VAT was added:
=B2/(1+$F$1)
We can then click and drag this formula down to each remaining cell in column C:
Column C now shows each price in column B with the VAT removed from it.
For example:
- A $12 item with a 20% VAT rate removed becomes $10.
- A $18 item with a 20% VAT rate removed becomes $15.
- A $24 item with a 20% VAT rate removed becomes $20.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Find the Top 10% of Values in an Excel Column
How to Calculate Cumulative Percentage in Excel
How to Multiply Column by a Percentage in Excel
