google-sheetsgoogle-sheets-formulacalculation

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


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,

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.


Solution

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

    enter image description here

    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.

    enter image description here