Reverse Tax Calculation Formula in Excel (Apply with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

The reverse tax calculation formula is basically a backward process in which we calculate the actual price of a product and the tax amount added to it to finalize MRP (Maximum Retail Price). It is a helpful process especially for the customer group if they need to justify what they are paying because they can only see the MRP and Tax rate in the money receipt. In this article, we will be following a step-by-step guideline to find out how to do the reverse tax calculation formula in excel.

Table of Contents hide

Practice Workbook

Step by Step Guidelines for Reverse Tax Calculation Formula in Excel

Step 1: Tax Amount Calculation

Step 2: Calculation of Actual Price

Conclusion

Practice Workbook

Download this sample workbook and practice by yourself.

Reverse Tax Calculation Formula.xlsx

Step by Step Guidelines for Reverse Tax Calculation Formula in Excel

Here is a sample dataset to calculate the reverse tax calculation formula. The dataset comprises product names, MRP and Tax rates in cells B4:D9.

Now follow the steps below to calculate the reverse tax formula in excel:

Step 1: Tax Amount Calculation

At first, we will calculate the tax amount of each product with this formula:

=(MRP*Tax Rate)/(1+Tax Rate)

Now follow the process below:

In the beginning, insert the Tax Amount formula in cell E5 according to the dataset.

=(C5*D5)/(1+D5)

Then, press Enter. It will show the amount of tax charged for the product ‘Pant’.

Following the same formula, calculate the tax amount of other products as well. You can insert the formula in cells E6:E9 or just drag the bottom right corner of cell E5 up to cell E9.

Finally, we have completed the tax amount calculation.

Similar Readings

Step 2: Calculation of Actual Price

After this, we will now calculate the actual price of each product with this formula:

=MRP-((MRP*Tax Rate)/(1+Tax Rate))

Let’s look at the steps below:

Firstly, insert the formula in cell F5.

=C5-((C5/(1+D5)*D5))

Then, press Enter. You can see that we have got the actual price of ‘Pant’ before adding the tax.

In continuation to that, insert the same formula in cells F6:F9 or just drag the bottom corner of cell F6 up to cell F9.

That’s it, we have our final results of the actual price.
Another formula can be used to calculate the actual price which states below:

=MRP/(1+Tax rate)

Now insert this formula according to the dataset. We have shown in cell F5.

=C5/(1+D5)

Next, use the Fill Handle tool to autofill the next cells.
Finally, it will show the same amount of actual price for the products.

Conclusion

Concluding the article we have successfully done the reverse tax calculation formula in excel to find out the tax amount and the actual price of products. Follow the ExcelDemy website to find out more excel tips and tricks. Let us know your insightful suggestions in the comment section.