USA Calculating Discounts for Tax Included or Out the Door Pricing

[JoeDavis]

I am trying to figure out how to properly calculate a product discount so a customer can pay a price agreed upon at checkout, including tax, with multiple taxes applied to different amounts of the item price. The price is unknown until checkout and is kind of a bargaining tactic. For instance:

Customer wishes to buy a widget with a price of $6,000. Sales Tax is 7% on amounts <= $5,000 and 6% on amounts > $5,000; these taxes are cumulative so, for our customer, 7% is applied to $5,000 and 6% is applied to $1,000.

Subtotal        $6,000
$5,000 X 0.07   $  350
$1,000 X 0.06   $   60
----------------------
Total Tax       $  410
----------------------
----------------------
Order Total     $6,410

The customer wants to only pay $6,000 so we agree on that amount. Our store needs to ensure that sales taxes are paid on the order but, of course, we don't want to pay those taxes. So, we want to discount the original $6,000 by an amount, which would then be taxed, resulting in the Order Total being exactly $6,000 (including tax). The customer ends up paying the tax on a lower item price.

We've come up with a couple formulas for this and it works fine when we're only worried a bout a singular tax rate. When we need to handle multiple tax rates, we have a hard time getting the correct total.

I can post what we've tried but I'm afraid that will muddy the waters. We call this an "out the door" price.

 

[kirby]

You can do this using Excel's What If function. Type this into an Excel sheet. I am showing the formulas.

So cell C5 is what you want to achieve. Now in Excel click on cell A7 then go to "Data" "What if Analysis" and select "Goal Seek". You will get a pop-up box and you fill it in with your data as shown:

Press "OK" and you will get your answer.

As shown, the sales price is to be $5,613,.21 and after tax of $386.79 the customer payment will be $6,000

 

[kirby]

Looks like this website needs you to log in to see the screen shots above.