Forum Discussion
calculating tax collected from total
I have a total sales including sales tax. I can't remember the formula to use to calculate the correct sales tax collected out of that total. been way too long. HELP
TotalSales / (1 + TaxRate) will give you the amount before tax. You can either subtract that from total sales to get the total tax collected, or you can multiply that by the individual tax rate (if there are more than one tax rates included in the total). For example, if total sales included 5% GST and 6% PST, and you want to calculate the GST collected only, the formula would be:
= TotalSales / (1 + GSTRate + PSTRate) * GSTRate
= TotalSales / (1 + 0.05 + 0.06) * 0.05
If TotalSales were located in column A, GSTRate in column B, and PSTRate in column C, the formula would look like the following:
=A2/(1+B2+C2)*B2
You may also want to look into using the ROUND function to round the results to 2 decimal places.