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.
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.
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))
- 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.
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
- How to Calculate Marginal Tax Rate in Excel
- How to Calculate Income Tax on Salary with Old Regime in Excel
- How to Calculate Income Tax in Excel Using IF Function
- How to Calculate Social Security Tax in Excel
- How to Calculate Federal Tax Rate in Excel
- How to Calculate Income Tax on Salary with Example in Excel
- How to Calculate Sales Tax in Excel
<< Go Back to Excel Tax Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!