Finding Discounts for Multiple Items Accounting for Tax

Question

I need a formula and/or a programmatic function (in any language) which will 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 out-the-door price is not necessarily known until checkout and is kind of a bargaining tactic. For instance:

• Customer wishes to buy multiple items with a subtotal of \$6,000
• There is a 6% Sales Tax on all items
• There is an additional 1% on the first \$5,000 of all items.

Item	Price	Quantity	Total	Amount Surtaxed	0.01 Surtax Amount	Pctg of Order Total
Thing 1	\$100.00	1	\$100.00	\$100.00	\$1.00	1.56%
Thing 2	\$5,500.00	1	\$5,500.00	\$5,000.00	\$50.00	85.74%
Thing 3	$400.00	1	\$400.00	\$400.00	\$4.00	6.24%
		Subtotal	\$6,000.00
		Base Sales Tax (6%)	\$360.00
		County Surtax (1%)	\$55.00
		Total Tax	\$415.00
		Order Total	\$6,415.00

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 each item to get to a new subtotal which when the taxes are applied our new Order Total is exactly \$6,000. The customer ends up paying the tax on lower priced items. (Note that there’s multiple ways to adjust prices; you can come up with your own criteria.)

We've come up with a couple formulas for this and it works fine when we're only worried about a singular tax rate or just a single item. When we need to handle multiple tax rates and multiple items, 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.

Answer 1

Suppose that there are $n$ commodities, labeled with integers $1,\cdots,n$. Denote the price vector of the commodities as $\boldsymbol p=(p_1,\cdots,p_n)$. For a given consumption bundle $\boldsymbol x=(x_1,\cdots,x_n)$, where $x_i$ is the amount of commodity $i$ purchased, the total expenditure is $$ W=\boldsymbol p\cdot\boldsymbol x+t(\boldsymbol p,\boldsymbol x), $$ where $t(\boldsymbol p,\boldsymbol x)$ is the amount of tax payed for purchasing the bundle $\boldsymbol x$ at price $\boldsymbol p$ (we make no assumptions about the function $t$, other than that it is continuous and monotonically increasing w.r.t. $p_i$ and $x_i$ for all $i$).

We want to adjust the price $\boldsymbol p$ to $\boldsymbol p^*$ such that the total expediture equals an agreed-upon amount $W^*$: $$ W^*=\boldsymbol p^*\cdot \boldsymbol x+t(\boldsymbol p^*,\boldsymbol x). $$ Note that the solution is, in general, not unique. However, we can impose the restriction that $$ \boldsymbol p^*=\lambda\boldsymbol p $$ where $\lambda$ is the “discount rate”. Fixing $\boldsymbol p$ and $\boldsymbol x$, we can express the expenditure as a function of $\lambda$: $$ W(\lambda)=\lambda\boldsymbol p\cdot \boldsymbol x+t(\lambda\boldsymbol p,\boldsymbol x). $$ Note that $W(\lambda)$ is strictly increasing w.r.t. $\lambda$. Therefore if $W(0)<W^*<W(1)$ then there exists a unique solution $0<\lambda^*<1$. Now you can use any root-finding algorithm to obtain the value of $\lambda^*$.

---

Edit: Here's a simple Python program I wrote to solve the problem in your question:

import numpy as np
from scipy.optimize import fsolve
def tax(price, consumption):
    surtax_amount = np.minimum(price * consumption, 5000)
    return 0.06 * np.dot(price, consumption) + 0.01 * np.sum(surtax_amount)
def adjust_price(initial_price, consumption, adjusted_expenditure):
    excess_expenditure = lambda c: c * np.dot(initial_price, consumption) + tax(c * initial_price, consumption) - adjusted_expenditure
    discount_rate = fsolve(excess_expenditure, [0.75])[0]
    return discount_rate, initial_price * discount_rate
p = np.array([100.00, 5500.00, 400.00])
x = np.array([1, 1, 1])
r, p0 = adjust_price(p, x, 6000)
print(f"{100 * (1 - r):.2f}% discount, adjusted price vector is {p0}")

Output:

6.52% discount, adjusted price vector is [  93.47996858 5141.3982718   373.91987431]