Calculate total taxable amount or tax applied on profit amount using Google sheets formula

Question

Following to my previous question, thanks to @rockinfreakshow and @Martin I got the optimal solution which I was looking for. Now I need your expert opinion to confirm whether it is possible, so please bear with me due to my beginner skills in Google sheets formulas.

Let's say I have:

profit before tax (PBT) amount = $97,839

I want to use this value to calculate tax to pay or taxable amount on this value following the same tax table,

So let's say, the formula should be:

 [taxable income]$132,476 - [Tax to pay]$34,637 = [Profit]$97,840

so assuming I just know the profit [$97,840], can we devise a formula to calculate either the tax to pay [$34,637] or taxable income [$132,476]. Any help would be much appreciated.

score 2 · Accepted Answer · answered Feb 27 '23 at 16:58

2

Using the same tax slab structure as in your earlier question; here's a bit of hard approach solution:

=let(x,E1,
     a,sequence(x*139%-x*110.5%,1,x*110.5%,1),
     b,byrow(a,lambda(y,if(y="",,lambda(z,offset(z,2,0)+((y-z)*offset(z,1,0)))(index(G1:K1,xmatch(y,G1:K1,-1)))))),
xlookup(x,index(a-b),a,,1))

Also its advisable(not mandatory) to use 2 decimals for profit value rather than a rounded number for pinpointing more accurate Income value with negligible variations. the screenshot should give an idea of what I mean. the rounded(green) values could vary b/w 6 Income values(yellow) whilst decimal values should close this gap.

answered Feb 27 '23 at 16:58

rockinfreakshow

15,077
3
12
19

thank you for the answer, I shall check it out. Can you please guide me how this formula works, I just want to learn the working of this – EagleEye Feb 27 '23 at 17:05
one thing I also noticed, it takes 10-15 seconds in processing, can we speedup like your previous formula – EagleEye Feb 27 '23 at 17:13
1

this is a very hard-coded approach & the reason for processing. its best to wait and see if hopefully someone else would pitch in with a simpler approach. – rockinfreakshow Feb 27 '23 at 17:16

score 1 · Answer 2 · answered Feb 27 '23 at 18:51

Try this out: I've created a table that can be used inside the formula itself. The table is organized like this:

=LAMBDA(table,{table,
SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2)))),
BYROW({table,SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2))))},LAMBDA(p,INDEX(p,,3)*INDEX(p,,1))),
SCAN(,BYROW({table,SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2))))},LAMBDA(p,INDEX(p,,3)*INDEX(p,,1))),LAMBDA(a,v,a+v)),
INDEX(INDEX(table,,2)-SCAN(,BYROW({table,SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2))))},LAMBDA(p,INDEX(p,,3)*INDEX(p,,1))),LAMBDA(a,v,a+v)))


})
({10.5%,0;
10.5%,14000;
17.5%,48000;
30%,70000;
33%,180000;
39%,999^99})

This table inside the formula allows you to make this formula for taxable amount, that finds the value of profit into the 6th column of the table, and then calculates the difference with dividing by (1-percentage):

=LAMBDA(values,
BYROW(A2:A,LAMBDA(pr,IF(pr="","",LET(l,XMATCH(pr,INDEX(values,,6),-1),
 INDEX(values,l,2)+(pr-INDEX(values,l,6))/(1-INDEX(values,l+1,1))

))))


)(LAMBDA(table,{table,
SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2)))),
BYROW({table,SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2))))},LAMBDA(p,INDEX(p,,3)*INDEX(p,,1))),
SCAN(,BYROW({table,SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2))))},LAMBDA(p,INDEX(p,,3)*INDEX(p,,1))),LAMBDA(a,v,a+v)),
INDEX(INDEX(table,,2)-SCAN(,BYROW({table,SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2))))},LAMBDA(p,INDEX(p,,3)*INDEX(p,,1))),LAMBDA(a,v,a+v)))


})
({10.5%,0;
10.5%,14000;
17.5%,48000;
30%,70000;
33%,180000;
39%,999^99}))

And if you want the tax with a similar process:

=LAMBDA(values,
BYROW(A2:A,LAMBDA(pr,IF(pr="","",LET(l,XMATCH(pr,INDEX(values,,6),-1),
 INDEX(values,l,5)+(pr-INDEX(values,l,6))/(1-INDEX(values,l+1,1))*INDEX(values,l+1,1)

))))




)(LAMBDA(table,{table,
SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2)))),
BYROW({table,SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2))))},LAMBDA(p,INDEX(p,,3)*INDEX(p,,1))),
SCAN(,BYROW({table,SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2))))},LAMBDA(p,INDEX(p,,3)*INDEX(p,,1))),LAMBDA(a,v,a+v)),
INDEX(INDEX(table,,2)-SCAN(,BYROW({table,SCAN(,SEQUENCE(COUNTA(INDEX(table,,2))),LAMBDA(a,v,IF(v=1,0,INDEX(table,v,2)-INDEX(table,v-1,2))))},LAMBDA(p,INDEX(p,,3)*INDEX(p,,1))),LAMBDA(a,v,a+v)))


})
({10.5%,0;
10.5%,14000;
17.5%,48000;
30%,70000;
33%,180000;
39%,999^99}))

Here it is in columns B and C:

If it happens to be slow with a big amount of data, just create the table in an actual cell. In my case in E2 with the first formula. Then you can upload the next formula as:

=LAMBDA(values,
BYROW(A2:A,LAMBDA(pr,IF(pr="","",LET(l,XMATCH(pr,INDEX(values,,6),-1),
 INDEX(values,l,2)+(pr-INDEX(values,l,6))/(1-INDEX(values,l+1,1))

))))

)(E2:J)

Calculate total taxable amount or tax applied on profit amount using Google sheets formula

2 Answers2