You can easily compute his tax liabilities for salaried individuals with the help of Income Tax Calculation Formula With IF Statement in MS Excel with the help of Nested IF Function. “IF” and “Nested IF” functions are very popular functions in “Logical functions” category in MS Excel. Nested IF function is used when another IF function is assigned inward the first IF function in order to check extra situations.
Income Tax Calculation Formula With IF Statement in Excel:
For example, IF you want to calculate income tax, education cess and secondary higher education-cess as per slab for income tax of AY 2013-14, the slab rate of income tax is given below: Individual resident whose age is lower than sixty years (That means whose date of birth is on or after the 1st Apr. , 1953) or any Non Resident Indian / Hindu UN-Divided Family / Association of Person / Body of Individuals / Artificial Judicial Person Income-tax: Calculate Liability for Tax for AY 2013-14
Additional Tax : “No Surcharge”Education Cess: 2% of the Income Tax Amount.Secondary Higher Education Cess: 1% of the Income Tax Amount.
To calculate income tax as per given table you have to take following steps in excel sheet:
|S. No.||Income Slabs||Income Tax Rate|
|1.||Where the total earning is not greater than equal to Rs. 200000/-.||–|
|2.||Where the total earning is greater than Rs. 2,00,000/- but less than Rs. (500000) five lakhs.||10% of the sum of earning which greater than Rs. 2,00,000/-|
|3.||Where the total earning is greater than Rs. (5,00,000) lakhs but less than Rs. (1000000) lakhs.||Rs. (30,000) Thirty Thousand + 20% of the whole amount which is the whole earning larger than Rs. (500000) five lakhs/-.|
|4.||Where the total income greater than Rs. (1000000) Ten Lakhs.||Rs. (130,000) One Lakh and Thirty Thousand + 30% of the whole amount which is the whole earning larger than Rs. (1000000) Ten Lakhs.|
Step 1: Prepare following sheet in excel as per given below.
Step 2: Type the formula to calculate income tax amount in cell C13 =IF(B13<$B$2,”Nil”,IF(B13<=$B$3,(B13-200000)*$C$3,IF(B13<=$B$4,(B13-500000)*$C$4+30000,(B13-1000000)*$C$5+130000)))
Step 3: Type given formula to calculate education cess @ 2% on income tax in cell D13: =IF(C13=”Nil”,0,C13*2%)
Step 4: Type given formula to calculate S.H.Education Cess @ 1% on income tax in cell E13: =IF(C13=”Nil”,0,C13*1%)
Step 5: Now calculate total of tax amount in cell F13: =SUM(C13:E13)
Step 6: Copy formula of income tax, edu. cess and s.h.edu.cess by drag and drop method in given range C14:F18
Step 7: Finally you get total tax amount as per given income tax slab of AY 2013-14.
I hope you like this post. This post is useful to calculate income tax every year, but you have to change income tax slab as per given income tax department. Give your important suggestion regarding this post in comment box.and also share this article with your friends to show your knowledge in excel. Thanks.