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.


Reverse Tax Calculation Formula in Excel: Apply with Easy Steps

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.

Reverse Tax Calculation Formula in Excel

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)

Reverse Tax Calculation Formula in Excel

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

Reverse Tax Calculation Formula in Excel

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

Read More: Formula for Calculating Withholding Tax in Excel


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))

Reverse Tax Calculation Formula in Excel

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

Reverse Tax Calculation Formula in Excel

  • 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)

Reverse Tax Calculation Formula in Excel

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


Practice Workbook

Download this sample workbook and practice by yourself.


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. Let us know your insightful suggestions in the comment section.


Related Articles


<< Go Back to Excel Tax Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo