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.
Download this sample workbook and practice by yourself.
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.
- 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.
- How to Calculate Social Security Tax in Excel
- Formula for Calculating Withholding Tax in Excel (4 Effective Variants)
- Computation of Income Tax Format in Excel for Companies
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.
- 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:
- Now insert this formula according to the dataset. We have shown in cell F5.
- Next, use the Fill Handle tool to autofill the next cells.
- Finally, it will show the same amount of actual price for the products.
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.
- Tax Invoice Format in Excel (Download the Free Template)
- How to Calculate Marginal Tax Rate in Excel (2 Quick Ways)
- Self Employment Tax Calculator in Excel Spreadsheet (Create with Easy Steps)
- How to Calculate Income Tax on Salary with Old Regime in Excel
- Income Tax Computation in Excel Format (4 Suitable Solutions)
- How to Calculate Income Tax in Excel Using IF Function (With Easy Steps)