## Wednesday, January 30, 2013

### Income Tax Calculation formulas in Excel

Friends,   How to calculate income tax in excel.   No doubt there are lot of tax calculator available in market.   But there is no detail  how they have calculated the same.   Many time we feel requirement of income tax calculation in excel.  The said formula calculates Income Tax  of  man, women,  senior citizen and very senior citizen for assessment year 2013-14.  Picture view with detailed formula's is given below :-

The above calculations are simple and can be used through copy and paste etc.   The above sheet is available in two part i.e. one is shown in green colour for used of user and second is shown in white colour which is only for calculation not for end use.   Date of above picture is also given below for easy reading otherwise picture can be enlarged to view to formula's

Column A                                                           Column B
 Income Tax Calculator for Financial Year 2012-13 or assessment Year 2013-14 Taxable Income 1) Income from Salary Head 1500000 2) Income from Business & Profession 0 3) Income from House Property 0 4) Income from Capital Gain 0 5) Income from Other Sources Total Taxable Income =SUM(B5:B9) for Man / Women (Amount in Rs.) Income Tax =\$K\$14 Education Cess  2% =+B13*0.02 Secondary Higher Education Cess 1% =+B13*0.01 Total Income Tax =SUM(B13:B15) for Senior Citizen => 60 .and. < 80 years (Amount in Rs.) Income Tax =\$K\$21 Education Cess  2% =+B19*0.02 Secondary Higher Education Cess 1% =+B19*0.01 Total Income Tax =SUM(B19:B21) for vary Senior Citizen => 80 years (Amount in Rs.) Income Tax =\$K\$28 Education Cess  2% =+B25*0.02 Secondary Higher Education Cess 1% =+B25*0.01 Total Income Tax =SUM(B25:B27)
Column H Column I            Column J                                           Column K
 Tax Slabs Rate Bifurcation of Income Income Tax 200000 0 =IF(\$B\$10 >= H10,H10,\$B\$10) =+J10*0 500000 10 =IF(\$B\$10 >= H11,300000,MAX(\$B\$10-H10,0)) =+J11*0.1 1000000 20 =IF(\$B\$10 >=H12,500000,MAX((\$B\$10-500000),0)) =+J12*0.2 > 1000000 30 =IF(\$B\$10 >H12,MAX(\$B\$10-H12,0),0) =+J13*0.3 =\$B\$10 =SUM(K10:K13) Tax Slabs Rate Bifurcation of Income Income Tax 250000 0 =IF(\$B\$10 >= H17,H17,\$B\$10) =+J17*0 500000 10 =IF(\$B\$10 >= H18,250000,MAX(\$B\$10-H17,0)) =+J18*0.1 1000000 20 =IF(\$B\$10 >=H19,500000,MAX(\$B\$10-500000,0)) =+J19*0.2 > 1000000 30 =IF(\$B\$10 >H19,MAX(\$B\$10-H19,0),0) =+J20*0.3 =\$B\$10 =SUM(K17:K20) Tax Slabs Rate Bifurcation of Income Income Tax 500000 0 =IF(\$B\$10 >= 500000,H24,\$B\$10) =+J24*0 1000000 20 =IF(\$B\$10 >=H25,500000,MAX(\$B\$10-500000,0)) =+J25*0.2 > 1000000 30 =IF(\$B\$10 >H25,MAX(\$B\$10-H25,0),0) =+J26*0.3 =\$B\$10 =SUM(K24:K27)