You can perform a reverse tax calculation to find the price of some item before tax was added.
You can use the following formula to do so:
Price Before Tax = Price After Tax / (1 + Tax Rate)
For example, suppose the price of an item after tax is $14 and you know the tax rate is 7%.
You can use the following formula to calculate the price of the item before tax was added:
- Price Before Tax = $14 / (1 +7%)
- Price Before Tax = $14 / 1.07
- Price Before Tax = $13.08
The price of this item before tax was added was $13.08.
The following example shows how to use this formula to perform a reverse tax calculation in Excel.
Example: Reverse Tax Calculation in Excel
Suppose we have the following list of products in Excel with prices that already have tax added to them:
Suppose we know that the tax rate for each item was 7% and we would like to find the original price of each product before tax was added.
We can specify this tax rate in cell F1 and then type the following formula into cell C2 to find the original price of the first product:
=B2/(1+$F$1)
We can then click and drag this formula down to each remaining cell in column C:
Column C now shows the price of each product before the 7% tax was added to it.
For example:
- A $14 product with a 7% tax rate was $13.08 before tax was added to it.
- A $18 product with a 7% tax rate was $16.82 before tax was added to it.
- A $20 product with a 7% tax rate was $18.69 before tax was added to it.
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
