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.
Step 1 – Tax Amount Calculation
Calculate the tax amount of each product with this formula:
=(MRP*Tax Rate)/(1+Tax Rate)- Insert the Tax Amount formula in cell E5 according to the dataset:
=(C5*D5)/(1+D5)
- Press Enter.
- 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.
Read More: Formula for Calculating Withholding Tax in Excel
Step 2 – Calculation of Actual Prices
Let’s calculate the actual price of each product with this formula:
=MRP-((MRP*Tax Rate)/(1+Tax Rate))- Insert this formula in cell F5.
=C5-((C5/(1+D5)*D5))
- Press Enter.
- Insert the same formula in cells F6:F9 or just drag the bottom corner of cell F6 up to cell F9.
Another formula can be used to calculate the actual price which states below:
=MRP/(1+Tax rate)- You can insert this formula in cell F5, for example.
=C5/(1+D5)
- Use the Fill Handle tool to autofill the next cells.
Practice Workbook
Related Articles
- How to Calculate Marginal Tax Rate 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 Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
