How to calculate value based on tax bracket using Google sheet formula?

I want to calculate the tax on the amount based on tax bracket applied to it in Google sheet. Here is the screenshot of the tax bracket:

So if the amount is $17000, then 10.5% tax is applied on first $14000 and 17.50% tax should be applied on the remaining $3000. I have tried the following formula but I don't think this is the optimal way of calculating this, J13 cell has a value to be calulated for the taxable amount:

=IFS(J8<14001,14000*0.105,J8<48001,1470+(J8-14000)*0.175,J8<70001,7420+(J8-48000)*0.30) and so on for other tax ranges

I donot want to use this tax table in the formula, due to my beginner skills with the formulas, I am unable to devise a optimal formula which works without using tac table, any guidance would be much appreciated.

Within Sheets here's one approach. Please do test it out with various scenarios to see if there's any amiss..

• arrange the tax table as shown in Columns A,B,C

• the calculation behind the formula for this example scenario (amount of $17000) is

  (14000*10.5%)+((17000-14001)*17.5%) = 1994.825

Formula:

=ROUND(SUM(MAP(A1:A5,B1:B5,{C1:C4;99^99},LAMBDA(a,b,c,IFs(E1>c,c-b,E1>b,E1-b,TRUE,"")*a))),2)

Another approach:

=lambda(z,offset(z,2,0)+((E1-z)*offset(z,1,0)))(index(G1:K1,xmatch(E1,G1:K1,-1)))

You can try creating your table with curly brackets inside your formula. Use comma for dividing percentage and value, and semicolons to divide the different percentages. You can hav a first line with the value 0 and the last one with 999^99 which is an ultra-high value for the case when you overpass $180.000:

=LAMBDA(table,LET(l,MATCH(B1,INDEX(table,,2),1),INDEX(table,l,2)*INDEX(table,l,1)+(B1-INDEX(table,l,2))*INDEX(table,l+1,1)))
({10.5%,0;
10.5%,14000;
17.5%,48000;
30%,70000;
33%,180000;
39%,999^99})

As an array:

=INDEX(IF(LEN(B1:B),LAMBDA(table,LET(l,MATCH(B1:B,INDEX(table,,2),1),INDEX(table,l,2)*INDEX(table,l,1)+(B1:B-INDEX(table,l,2))*INDEX(table,l+1,1)))
({10.5%,0;
10.5%,14000;
17.5%,48000;
30%,70000;
33%,180000;
39%,999^99}),""))

I've rearranged rockinfreakshow's answer it in a LET expression so it is easy to customize:

=LET(
  value_, income,
  upper_limits, WRAPROWS(limits,1),
  rates_, WRAPROWS(rates,1),
  lower_limits, MAKEARRAY(ROWS(upper_limits),1,LAMBDA(r,c,IF(r >1,INDEX(upper_limits,r-1,c),0))),
  SUM(
    MAP(
      lower_limits, upper_limits, rates_, 
      LAMBDA(lower, upper, rate,
        IFS(
          AND(upper > 0, value_ > upper), upper - lower, 
          value_ > lower, value_ - lower, 
          TRUE, 0
        )*rate
      )
    )
  )
)

Change the variables income, limits and rates accordingly.
I recommend defining a named function like I've done in this sample spreadsheet

The other answers are needlessly over-complicated. Use this named function:

> TAX(taxable, brackets)
>
> Computes the tax on a given taxable amount according to specified tax brackets.
>
> - taxable: The amount to be taxed. Example: 69420.
> - brackets: A 2-column array in which the first column contains the bracket cutoffs and the second column contains the tax rates. Example: {0,10%; 11000,12%; 44725,22%; 95375,24%; 182100,32%; 231250,35%; 578125,37%}.
>
>
> =LET(
> n, ROWS(brackets),
> bounds, CHOOSECOLS(brackets, 1),
> rates, CHOOSECOLS(brackets, 2),
> SUMPRODUCT(
> MAP(bounds, LAMBDA(bound, MAX(taxable - bound, 0))),
> MMULT(ARRAYFORMULA(MUNIT(n) - MAKEARRAY(n, n, LAMBDA(r, c, r = c + 1))), rates)
> )
> )
>

To compute the tax in OP's example:

=TAX(17000, {0,10.5%; 14000,17.5%; 48000,30%; 70000,33%; 180000,39%})

The meat of the calculation is the SUMPRODUCT call, which is passed two arrays. The first array contains the amounts by which the taxable amount exceeds each bracket cutoff (or 0 if the amount is less than the cutoff). The second array contains the incremental tax-rate contributions of each successive bracket.

In OP's example, the two arrays passed to SUMPRODUCT would be:

• {17000; 3000; 0; 0; 0}
• {10.5%; 7%; 12.5%; 3%; 6%}

The tax is the sum of the products of corresponding elements in the two arrays:
17000×10.5% + 3000×7% + 0×12.5% + 0×3% + 0×6% = 1995.