Formula for IT. [SOLVED]

[aratrika]

I used to calculate staff IT till FY 2012-13 which was

a) up to 2 lac =0,

b) 2 lac to 5 lac =10% of the amount by which the total exceeds 2 lac,

c) 5 lac to 10 lac = 30000+20% of the amount by which the total exceeds 5 lac and above

d) 10 lac = 130000 +30% of the amount by which the total exceeds 10 lac

with the following formula assuming taxable income at cell AC6 =IF(AC6<=200000,0,IF(AC6<500000,(AC6-200000)*10%,IF(AC6<1000000,30000+(AC6-500000)*20%,130000+(AC6-1000000)*30%)))

Now for the FY 2013-14 i have to incorporate (-)Tax credit 10% on taxable income subject to max Rs. 2000 for the b) category staffs. What would be the formula. Could any body be generous to help me out as always. Look forward to an early response.

Thanks & regards

 

[NARAYANK991]

Hi ,


If I understand you correctly , the tax credit is applicable only for those whose income is between 2 lakhs and 5 lakhs.


The taxable income is the income in excess of 2 lakhs.


10 % of this is (AC6-200000)*10 % ; imposing a maximum limit of 2000 on this is done by :


MIN(2000,(AC6-200000)*10%)


Your overall formula will now be :


=IF(AC6<=200000,0,IF(AC6<500000,(AC6-200000)*10%-MIN(2000,(AC6-200000)*10%),IF(AC6<1000000,30000+(AC6-500000)*20%,130000+(AC6-1000000)*30%)))


Narayan

 

[SirJB7]

Hi, aratrika!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Formula%20for%20IT%20%28for%20aratrika%20at%20chandoo.org%29.xlsx


If you have a tax information disposed in this way:

-----

[pre]

Lac	Rs		Fixed Amount	Deductable	Deductable Max	Percentage
0	0		0,00		0,0%		0,00		0,0%
2	200.000		0,00		10,0%		2.000,00	10,0%
5	500.000		30.000,00	0,0%		0,00		20,0%
10	1.000.000	130.000,00	0,0%		0,00		30,0%

[/pre]
-----


You could use this formula that even a bit more complex it'd let you get rid of any tax amounts, percentages and other stuff changes that might come in the future:

=BUSCARV(H2;B:F;2;VERDADERO)+(H2-ENTERO(BUSCARV(H2;B:F;1;VERDADERO)))*BUSCARV(H2;B:F;5;VERDADERO)-MIN(BUSCARV(H2;B:F;2;VERDADERO)+(H2-ENTERO(BUSCARV(H2;B:F;1;VERDADERO)))*BUSCARV(H2;B:F;5;VERDADERO)*BUSCARV(H2;B:F;3;VERDADERO);BUSCARV(H2;B:F;4;VERDADERO)) -----> in english: =VLOOKUP(H2,B:F,2,TRUE)+(H2-INT(VLOOKUP(H2,B:F,1,TRUE)))*VLOOKUP(H2,B:F,5,TRUE)-MIN(VLOOKUP(H2,B:F,2,TRUE)+(H2-INT(VLOOKUP(H2,B:F,1,TRUE)))*VLOOKUP(H2,B:F,5,TRUE)*VLOOKUP(H2,B:F,3,TRUE),VLOOKUP(H2,B:F,4,TRUE))


The fixed amount of column C is:

VLOOKUP(H2,B:F,2,TRUE)


The taxable amount is (assuming income in H2 as in the uploaded file):

H2-INT(VLOOKUP(H2,B:F,1,TRUE))


The basic tax is:

<previous formula>*VLOOKUP(H2,B:F,5,TRUE)


And the deduction is:

MIN(<previous formula>*VLOOKUP(H2,B:F,3,TRUE),VLOOKUP(H2,B:F,4,TRUE))


Just advise if any issue.


Regards!

 

[Smallman]

Interesting problem


Here is my take on the equation;


=SUMPRODUCT(--(A9>$A$2:$A$5), (A9-$A$2:$A$5), $C$2:$C$5)


With Increments in A, Rates in B and the Variance between each Rate in C. See attached which show workings. The hard coded long hand version of the above is;


=SUMPRODUCT(--(A22>{200001;500001;1000001;1300001}), (A22-{200000;500000;1000000;1300000}), {0.1;0.1;0.1;0})


The first two parts show the increments and the final part shows the difference between each of the tax brackets. First Tax bracket is 10% and second is 20% so {0.1;0.1} is the same as 20% etc.


Try it, works a treat. I found some variances between the two methods above and outlined them for you guys. I don't know who is most correct but have a sneaking suspicion i missed a trick somewhere along the lines of the Deductible Max from SirJB's table above (variances are minor in the lower end). That aside this works well for Aussie and UK tax rates where those sort of back door shenanigans are not part of the equation.


http://rapidshare.com/files/1031338014/SmallmanTaxRates.xlsm


Take care


Smallman

 

[SirJB7]

Hi, Smallman!

As I understood the problem, incomes of 2nd step, b), 200K-500K, are benefited with a 10% of discount in the payable tax amount, with a limit of 2000. That's why my columns D:E. If you set D3 to 0 (no need to do that in E3) both you and me arrive at the same results. So I believe you're missing the calculation of the 10% less for the 2nd category.

Regards!

 

[Smallman]

SirBJ


The example above is not just for the person posting in this post. Clearly I stated there were differences in the three methods above. It you open the file you will see as much.


The real benefit of my method won't be felt by Arataka but by the dozens of people who pass through this thread in future and find the above example. The example I provided is relevant to a great many taxation tables and is an extremely efficient way to calculate taxation.


Take care


Smallman

 

[Smallman]

I have altered the formula and the file attachment to cope with step B by incorporating Narayan's efficient formula. My formula becomes;


=IF(A9<A3,(A9-A2)*.1-MIN(2000,(A9-A2)*.1),SUMPRODUCT(--(A9>$A$2:$A$5), (A9-$A$2:$A$5), $C$2:$C$5))


Not too shabby. Uploaded and refreshed file below.


http://rapidshare.com/files/3100216618/SmallmanTaxRates1.xlsm


Now there is only one variance.


Smallman

 

[aratrika]

Dear all,

I am just speechless for the overwhelming response from you guys once again and I don't know how to thank you all. I will take the solution given by NARAYANK991 at the moment since I needn't have to change much in my existing setup and I will definitely look in to the other valued solutions in near future.However I express deep my gratitude to you all and the forum once again.


Thanks & regards