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