View Question
Q: A formula for backing out exact taxes. ( Answered ,   3 Comments )
 Question
 Subject: A formula for backing out exact taxes. Category: Science > Math Asked by: stevenc413-ga List Price: \$5.00 Posted: 02 Jul 2003 14:58 PDT Expires: 01 Aug 2003 14:58 PDT Question ID: 224425
 ```I run a cart and we don't deal with change so that we charge whole doller amounts and then back out the tax. I have been just guessing and checking to get the correct amount. For instance for a 5 doller item we need to charge the MA tax of 5% so we set the actual item price to be 4.76 so that it is about 5.00 in price. I would love to have an easy formula I could plug into a spredsheet for this. I know its possible to do, just don't remember enough math to figure it out. If anyone could help me out that would be great! Oh and as many decimal places as needed (within the restraint of Quickbooks) could be used.```
 Subject: Re: A formula for backing out exact taxes. Answered By: hammer-ga on 02 Jul 2003 15:35 PDT Rated:
 ```Hi Stevenc413, Divide the final dollar amount by 1 plus the tax percentage. In your example above, you have a tax rate of 5%, which is the same as .05. You want to end up with a final result of \$5.00. \$5.00 / 1.05 = \$4.76. If your tax rate is 5.25%, you would divide by 1.0525. And so on. Additional Info ---------------- Just as a side note, there is a nice little algebra formula I learned in school for deriving all sorts of percentage calculations: Is % -- * --- Of 100 In your case, you wanted the Of, so we replace Of with the variable x ... 5 is 105% of x Insert the numbers into the formula ... 5 105 --- * --- x 100 Cross multiply to get ... 105x = 500 Divide both sides by 105 to solve for x ... x = 4.76 -------------------------- Please let me know if you any additional information or clarification! - Hammer```
 stevenc413-ga rated this answer: ```Exactly what i needed thanks a lot! I knew that formula from school I just couldn't remember. Now the question is do I have to tell the boss how I figured it out? :-)```

 ```Hi stevenc413: Having filled out the Massachusettes Sales Tax return, I'm reading a little deeper into your question, as I believe I know what you're getting at. I speculate you are trying to determine what your sales are before taxes (so you can multiply your sales times 5% and pay your sales tax). Since you mentioned an Excel spreadsheet, here are the formulas. In cell A2: Key in your Sales (the amount you collected including tax) In cell B2: Enter the formula: =A2/1.05 (this will calculate your sales) In cell C2: Enter the formula: =B2*0.05 (this will calculate your tax to pay) Then copy these formulas down columns B & C the number of times you have sales entries (ie. if you have 500 sales entries, copy the formula from B2 to B501) I'm using 3 sales here as an example, but your spreadsheet (answers) should look something like this: Coll'd Sales Tax 5.00 4.761904762 0.238095238 8.00 7.619047619 0.380952381 10.00 9.523809524 0.476190476 ----------------------------------- 23.00 21.9047619 1.095238095 TOTALS Hope this information helps.```
 ```Hi can you Please help me out. I have same problem only difference is the tax rate which 8.75% in NY. Can you show steps for 8.75% Rate in Excel. ================== Need steps for 8.75%======================= In cell A2: Key in your Sales (the amount you collected including tax) In cell B2: Enter the formula: =A2/1.05 (this will calculate your sales) In cell C2: Enter the formula: =B2*0.05 (this will calculate your tax to pay) I tired changing # in excel with this formula, didn't work out.. Thank you```
 ```Hi can you Please help me out. I have same problem only difference is the tax rate which 8.75% in NY. Can you show steps for 8.75% Rate in Excel. ================== Need steps for 8.75%======================= In cell A2: Key in your Sales (the amount you collected including tax) In cell B2: Enter the formula: =A2/1.05 (this will calculate your sales) In cell C2: Enter the formula: =B2*0.05 (this will calculate your tax to pay) I tired changing # in excel with this formula, didn't work out.. Thank you ==================== Well you are asking for free help here not sure if I'm breaking any rules, but basically you just have to change the 1.05 to 1.085 in cell B2. Here is an example. Say you sell an item for \$2.00 which as far as your concerned already included the tax. In order to get this to work in your books the actual sale price would have to be \$2.00/1.085 or \$1.843. To check this you can multiply 1.843 times your tax rate 1.085 and of course you should get \$2.00. Hope that helps!```