Tax Brackets and Volume Discounts using Excel

Question

It's the end of January and that magical feeling of "tax time" is starting up around the world. A common problem is to calculate tax owed based on a set of tax brackets and rates. This is also similar to calculating total order costs based on a quantity and volume discounted prices.

I've seen some old questions/answers based on this problem type, but none are treating it as a general function, so I thought I would post one here. The idea is to create a general solution using LET which can be wrapped into LAMBDA when that is available.

US Federal Income Tax Rates 2021

So, let's start with the 2021 US Federal Income Tax Tables as an example:

Tax rate	Single From	Single To	Head of household From	Head of household To	Married filing jointly From	Married filing jointly To	Married filing separately From	Married filing separately To
10%	-	9,950	-	14,200	-	19,900	-	9,950
12%	9,951	40,525	14,201	54,200	19,901	81,050	9,951	40,525
22%	40,526	86,375	54,201	86,350	81,051	172,750	40,526	86,375
24%	86,376	164,925	86,351	164,900	172,751	329,850	86,376	164,925
32%	164,926	209,425	164,901	209,400	329,851	418,850	164,926	209,425
35%	209,426	523,600	209,401	523,600	418,851	628,300	209,426	314,150
37%	523,600		523,600		628,300		314,151

So the question is - what is the total tax owed for a given taxable income? For example, 100k. (The correct answer is 18021)

Volume Discount Pricing

Let's also take another example of the same family - volume discounted pricing. Here is a table showing order quantity and unit prices for each tranche of order quantity.

Qty From	Qty To	Unit Price
0	100	16.00
101	250	14.40
251	500	12.96
501	1000	11.66
1001	2000	10.49
2001	5000	9.44
5001	10000	8.50

A question might be: what would an order of 1200 units cost? (The correct answer is 14928)

How the Calculation Works

Both the tax table and volume discounting calculate by accumulating across the brackets at each tax rate or discounted unit price.

Example - tax calc A Single Person with taxable income of 50,000 would have taxes of:

9950 * 10% +

(40525-9950) * 12% +

(50000-40525) * 22% = 6748.50

Example - volume discounting An order of 220 units would pay 16 per unit for the first 100 and and 14.40 per unit of the next 120:

100 * 16.00 +

(220-100) * 14.40 = 3328

Objective

Have a formula that can be used for general purpose volume discounting or tax table calculation.

I will post my own solution, but I will not mark is as the answer - I'm sure there are better solutions out there and perhaps someone with LAMBDA can show how this can be built as a general solution.

Related Questions

IF statement to return different values for 3+ conditions

Complicated formula required to work out costs including multiple discounts

Array formula basics instead of nested if-else

Using Excel is it possible to plot a graph without explicitly generating table data, but using only a formula?

Answer 1

I had to change this because I realized after looking at Solar Mike's reply that I was miscalculating the bracket sizes.

So here is my approach. With Office 365, I would use a LET as follows:

=LET( a, $K2,
        from, tTax2021[Single From],
        to, tTax2021[Single To],
        m, tTax2021[Tax rate],

        t, IF( ISBLANK( to ), a, to ),
        i, SEQUENCE( ROWS(t),,0 ),
        f, IF( i = 0, 0, INDEX( t, i ) ),
        bracketIdx, MATCH( a, from, 1 ),
        bracketSize, t-f,
        mb, m*bracketSize,
        rmb, LET( array, mb,
                   rSeq, SEQUENCE( ROWS( array ) ),
                   ones, --(rSeq >= TRANSPOSE( rSeq )),
                   MMULT( ones, array ) ),
       cumBase, INDEX( rmb, MAX(1,bracketIdx-1) ) * SIGN( bracketIdx-1 ),
       prorataBracket, ( a - INDEX( f, bracketIdx ) ) / INDEX( bracketSize, bracketIdx ) * INDEX( mb, bracketIdx ),
       cumBase + prorataBracket )

Where:

a is the amount that I put in cell K2. In the tax example, it would be 100000.

f is the from column that defines the brackets.

t is the to column that defines the brackets.

m is the marginal rate for each bracket.

Tax Table Case

Here is a view of it deployed with an Excel Table called tTax2021.

Volume Discount Case

Here is the same formula applied to the Volume Discount problem above:

Simplified Table

Some tax and discounting tables have only a From column and no To column. In reality, the To column is superfluous in any case, so here is a version that only accepts the From column:

=LET( a, $K2,
        from, tTax2021[Single From],
        m, tTax2021[Tax rate],

        i, SEQUENCE( ROWS(from),,0 ),
        to, IFERROR( INDEX( from, i+2 ), a ),
        bracketSize, to-from,
        bracketIdx, MATCH( a, from, 1 ),
        mb, m*bracketSize,
        rmb, LET( array, mb,
                   rSeq, SEQUENCE( ROWS( array ) ),
                   ones, --(rSeq >= TRANSPOSE( rSeq )),
                   MMULT( ones, array ) ),
       cumBase, INDEX( rmb, MAX(1,bracketIdx-1) ) * SIGN( bracketIdx-1 ),
       prorataBracket, ( a - INDEX( from, bracketIdx ) ) / INDEX( bracketSize, bracketIdx ) * INDEX( mb, bracketIdx ),
       cumBase + prorataBracket )

LAMBDA Function

OK - so I finally have access to LAMBDA which allows this to be a more generalized function. So here is a LAMBDA version based on the simplified table above:

// Applies degressive pricing, volume discounting, or progressive taxation to 
//  a given Amount based on marginal units (or rates) for each Bracket.
//  amount : the sales order or income amount [value, array or range]
//  fromBrackets : the minumum amount for each bracket [array or range]
//  marginalUnits : the unit price or the tax rate within each bracket [array or range sized the same as fromBrackets]
BRACKETSUM =
LAMBDA( amount, fromBrackets, marginalUnits,

     LET(
            i, SEQUENCE( ROWS(fromBrackets),,0 ),
            to, IFERROR( INDEX( fromBrackets, i+2 ), a ),
            bracketSize, to-fromBrackets,
            bracketIdx, MATCH( amount, fromBrackets, 1 ),
            mb, marginalUnits*bracketSize,
            rmb, SCAN(0,mb,LAMBDA(a,b,a+b)),
           cumBase, INDEX( rmb, MAX(1,bracketIdx-1) ) * SIGN( bracketIdx-1 ),
           prorataBracket, ( amount - INDEX( fromBrackets, bracketIdx ) ) / INDEX( bracketSize, bracketIdx ) * INDEX( mb, bracketIdx ),
           cumBase + prorataBracket )
)

Answer 2

I will go with the simple vlookup as so:

=VLOOKUP(E2,A2:C8,3,1)*E2

I get 11329.26 using:

(((VLOOKUP(E2,$A$2:$C$8,3,1)-VLOOKUP(E2+1000,$A$2:$C$8,3,1))/1000*E2)+VLOOKUP(E2+1000,$A$2:$C$8,3,1)*E2)