Forum Discussion

Shannon_McClurg29's avatar
Shannon_McClurg29
Copper Contributor
Jul 01, 2024

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

excel

1 Reply

Sort By
  • djclements's avatar
    djclements
    Bronze Contributor
    Jul 02, 2024

    Shannon_McClurg29 

     

    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.

Resources