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

Question

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.

Answer 1

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)))