How to Calculate Income Tax on Salary with Example in Excel

If you want to calculate your income tax on salary Excel can be beneficial for you. In this article, I will be showing you different methods on how to calculate income tax on salary with example in Excel


Download Practice Workbook


6 Ways to Calculate Income Tax on Salary with Example in Excel

Here, I have used a sample dataset that contains Employee Name, Gross salary, and Taxable Income. For different methods and for different examples there are slight variations in the columns of the dataset. But, all of them contain salary details of different employees.

6 Ways to Calculate Income Tax on Salary with Example in Excel


1. Using Generic Formula to Calculate Income Tax on Salary in Excel

Using the generic formula to calculate income tax in Excel is the simplest way of calculating income tax. In this method, we used an example in which a fixed tax rate is applicable to all the employees.

Let me show you how to calculate income tax on salary with an example in Excel.

Steps:

In this example, at first, you have to calculate taxable income from gross salary and total deduction.

  • To begin with, select the cell in which you want to calculate your taxable income. here, I selected cell E5.
  • Next, in cell E5 write the following formula,
=C5-D5

1. Using Generic Formula to Calculate Income Tax on Salary in Excel

Here, I subtracted the Total Deductions from the Gross Salary. This will give you the Taxable Income for that particular cell

  • Now, press ENTER to get the Taxable Income.

Here, I got a Taxable Income for an employee named Jack.

1. Using Generic Formula to Calculate Income Tax on Salary in Excel

  • After that, drag the Fill Handle to get Taxable Income for every employee.

Now, you will get the Taxable Income for every employee. From which you will calculate the Tax on Salary.

1. Using Generic Formula to Calculate Income Tax on Salary in Excel

  • Next, select the cell in which you want to calculate the Tax on Salary. Here, I selected cell F5.
  • After that, in the cell F5 write the following formula,
=E5*$C$11

Here, E5 is the Taxable Income for that particular employee and C11 is the fixed Tax Rate. In this formula, I multiplied Taxable Income by Tax Rate will to get tax on Salary for the particular employee that the cell represents.

  • Now, press ENTER to get the Tax on Salary.

1. Using Generic Formula to Calculate Income Tax on Salary in Excel

  • Finally, drag the Fill Handle to get Tax on Salary for every employee.

Now, you will get Tax on Salary of every employee.

Read More: How to Create Salary Slip Format with Formula in Excel Sheet


2. Calculating Income Tax on Salary in Excel with Different Tax Slabs and Tax Surcharge

In this method, we used an example where there are different tax slabs for different income ranges. And a surcharge is applied on tax for some income ranges.

With this example, Let me show you how to calculate income tax on salary with different tax slabs and tax surcharges in Excel step by step.

Steps:

  • To begin with select the cell for which you want to calculate the Income Tax. Here, I selected D5, which is the Income Tax for an employee named Jack.
  • Next, in cell D5 write the following formula.
=C5*C13

2. Calculating Income Tax on Salary in Excel with Different Tax Slabs and Tax Surcharge

Here, C5 is the Taxable Income for the particular employee named Jack and C13 is the tax rate from Income Tax Slab for that particular income range. This formula will multiply both values then it will return you the Income Tax for that employee.

  • Now, press ENTER to get Income Tax.

Now, for cell D6, you can calculate the Income Tax by following the exact same steps.

  • Firstly, select cell D6.
  • Secondly, in cell D6 write the following formula.
=C6*C12

2. Calculating Income Tax on Salary in Excel with Different Tax Slabs and Tax Surcharge

Here, C6 is the Taxable Income for the particular employee named Jack and C12 is the tax rate from Income Tax Slab for that particular income range. This formula will multiply both values then it will return you the Income Tax for that employee.

  • Now, press ENTER to get Income Tax.

Finally, you will be able to calculate Income Tax for every employee by following these exact steps.

2. Calculating Income Tax on Salary in Excel with Different Tax Slabs and Tax Surcharge

Now, From the dataset, you can see that a certain surcharge is applied on tax for some income ranges. Here, we can see that no surcharge is applied for Jack, Jerry, and Amy. Because their Taxable Income does not fall in those particular ranges.

For Penny, we can see that a 5% surcharge is applied on tax. And we have to calculate the Surcharge from this information.

  • Firstly, select the cell you want to see the calculated surcharge in. I selected cell E7.
  • In cell E7, write the following formula,
=D7*C19

2. Calculating Income Tax on Salary in Excel with Different Tax Slabs and Tax Surcharge

Here, D7 is the Income Tax for that particular employee and C19 is the surcharge Rate. This formula will multiply Income Tax and Rate and return you the Surcharge on tax for that particular employee.

  • Now, press ENTER to get the Surcharge.

For Steve, we can see that a 10% surcharge is applied on tax. Now, you can calculate the surcharge on tax by following those previous steps that we followed for Penny.

2. Calculating Income Tax on Salary in Excel with Different Tax Slabs and Tax Surcharge

  • Firstly, select cell E8.
  • In cell E8, write the following formula,
=D8*C20

Here, D8 is the Income Tax for that particular employee and C20 is the surcharge Rate. This formula will multiply Income Tax and Rate and return you the Surcharge on tax for that particular employee.

  • Now, press ENTER and you will get the Surcharge.

2. Calculating Income Tax on Salary in Excel with Different Tax Slabs and Tax Surcharge

Now, we have got income tax and surcharge for all the employees. Using these two columns we can calculate the total tax for every employee.

  • Firstly, select the cell in which you want to calculate the total tax. Here, I selected cell F5.
  • Secondly, in cell F5 write the following formula,
=D5+E5

2. Calculating Income Tax on Salary in Excel with Different Tax Slabs and Tax Surcharge

Here, D5 is Income Tax and E5 is Surcharge. And this formula will return you the sum of Income Tax and Surcharge which is the Total Tax.

  • Now, press ENTER to get the Total Tax.

  • Finally, drag the Fill Handle to get the Total Tax for every employee.

2. Calculating Income Tax on Salary in Excel with Different Tax Slabs and Tax Surcharge

Now, you will get the Total Tax of every employee.

Read More: How to Make Salary Sheet in Excel with Formula (with Detailed Steps)


3. Use of SUMPRODUCT Function to Calculate Income Tax on Salary in Excel

By using the SUMPRODUCT function you can calculate tax on salary with some easy steps.

Let me show you how to calculate income tax on salary in Excel with the use of the SUMPRODUCT function.

Steps:

  • To begin with, select the first row of the Income Tax Slab table.

3. Use of SUMPRODUCT Function to Calculate Income Tax on Salary in Excel

  • After that, right-click on the row and select Insert to insert a new row.

3. Use of SUMPRODUCT Function to Calculate Income Tax on Salary in Excel

  • Next, select the cell in which you want to calculate the Income Tax. Here, I selected cell D5.
  • In the cell D5 write the following formula,
=SUMPRODUCT($D$14:$D$18-$D$13:$D$17,C5-$C$14:$C$18,N(C5>$C$14:$C$18))

Here, in the SUMPRODUCT function, I selected D14:D18-D13:D17 as array1 which figures out the differential rate. For array2 I selected C5-C14:C18 where C5 is the Taxable Income for that specific employee and  C5-C14:C18 returns the amount of each differential rate. And for array3 I used an N function which will return the value accordingly for C5>C14:C18.

Now, the SUMPRODUCT function will return us the Sum of multiplied components of these 3 arrays.

In every case, I used absolute cell reference so that the selected cell range remain fixed while using the AutoFill.

  • Press ENTER key and you will get the Income Tax for that specific employee.

3. Use of SUMPRODUCT Function to Calculate Income Tax on Salary in Excel

  • Now, drag the Fill Handle to get Income Tax values for every employee.

Finally, you will get Income Tax value for every employee.

3. Use of SUMPRODUCT Function to Calculate Income Tax on Salary in Excel

Read More: How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)


Similar Readings


4. Using VLOOKUP Function to Calculate Income Tax on Salary in Excel

You can calculate income tax by using the VLOOKUP function. For this method, we took an example that has one extra column in the income tax slab which is Cumulative. This column represents the cumulative tax for that particular tax rate.

Let me show you how to calculate income tax on salary in Excel with the use of the VLOOKUP function step by step.

Steps:  

  • First, select the cell in which you want to get the income tax value. Here, I selected cell D5.
  • Secondly, in cell D5, type the following function,
=VLOOKUP(C5,$B$13:$E$17,4,TRUE)+(C5-VLOOKUP(C5,$B$13:$E$17,1,TRUE))*VLOOKUP(C5,$B$13:$E$17,3,TRUE)

4. Using VLOOKUP Function to Calculate Income Tax on Salary in Excel

   Formula Breakdown

  • Here, for the first VLOOKUP function, I selected C5 as lookup_value, B13:E17 as table_array, 4 as col_index_number and for range_lookup I selected TRUE. This will return the cumulative tax for C5.
  • To get the income tax for C5 I have taken two more VLOOKUP functions.  For the second VLOOKUP function, I selected C5 as lookup_value, B13:E17 as table_array, 1 as col_index_number and for range_lookup I selected TRUE.
  • And for the third VLOOKUP function, I selected C5 as lookup_value, B13:E17 as table_array, 3 as col_index_number and for range_lookup I selected TRUE.
  • Now, The second part of the formula will return the additional tax amount for C5. By adding these two parts our formula will return the Income Tax value for C5.
  • While selecting the cell range, every time, I used absolute cell reference so that the selected cell range remain fixed while using the AutoFill.

  • Next, press ENTER and you will get the Income Tax value.

  • After that, drag the Fill Handle to get the Income Tax for every employee.

4. Using VLOOKUP Function to Calculate Income Tax on Salary in Excel

Finally, you will get Income Tax on the salary of every employee.


5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

To explain this method I have taken an example that considers Investment. If someone has invested 30% or more than 30% of their salary then the investment amount is deducted from the Salary to calculate Taxable Income.

5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

Let me show you step by step how to calculate income tax on salary in Excel with the use of IF and nested IF function.

Steps: 

In the beginning, we have to calculate the Taxable Income with the help of the Investment rate and Salary.

  • Firstly, select the cell in which you want to calculate the taxable income. Here, I have selected cell E5.
  • Secondly, in cell E5 write the following formula,
=IF(D5>=30%,C5-(C5*D5),C5)

5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

Here, I wrote D5>=30% as logical_test, C5-(C5*D5) as value_if_true, and C5 as value_if_false.  D5 is the investment rate, and C5 is the salary. If the logical test is TRUE the formula will deduct the Investment amount from the salary and return it as Taxable Income and if the logical test is FALSE then it will return the salary as Taxable Income.

  • Press ENTER and you will get the Taxable Income.

5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

  • Now, drag the Fill Handle to get Taxable Income for every employee.

5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

Finally, you will get the Taxable Income for every employee.

5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

Now, we will have to calculate income tax from this taxable income.

  • Firstly select the cell in which you want to calculate the income Tax. Here, I selected F5 .
  • After that, in cell  F5 write the following formula,
=IF(E5<=$C$12,"nil",IF(E5<=$C$13,(E5-$C$12)*$D$13,IF(E5<=$C$14,(E5-$C$13)*$D$14+($C$13-$C$12)*$D$13,(E5-$C$14)*$D$15+($C$14-$C$13)*$D$14+($C$13-$C$12)*$D$13)))

5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

Formula Breakdown

  • In this formula, for each logicat_test, I have given a value for value_if_true but for value_if_false I use another IF function.
  • If E5<=C12 then the formula will return the value “nil” and if not then it will go into another logical test. It will check if E5<=C13. If it’s TRUE then it will return the value for (E5-C12)*D13 and if the test result is FALSE then it will go into another IF function. It will check for E5<=C14. If the logical_test is TRUE then it will return the value for (E5-C13)*D14+(C13-C12)*D13 and if the logical_test is FALSE then it will return the value of (E5-C14)*D15+(C14-C13)*D14+(C13-C12)*D13.
  • Every time, I used absolute cell reference so that the selected cell range remain fixed while using the AutoFill.

  • Now, press ENTER and you will get a Tax for this particular employee.

5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

  • After that, by dragging the Fill Handle you will get Tax for every employee.

5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

Finally, you have calculated Tax for all the employees.

5. Use of Nested IF Function to Calculate Income Tax on Salary in Excel

Read More: Per Day Salary Calculation Formula in Excel (2 Suitable Examples)


6. Using Nested IF & AND Functions to Calculate Income Tax on Salary with Payment Type

For this method, I have chosen an example that considers payment type and status. If someone’s payment type is remittance and the status is filer then he/she will have to pay 5% tax on salary. And if someone’s payment type is remittance and the status is non-filer then he/she will have to pay 7% tax on salary. For all the other cases 3% tax on salary is applied.

6. Use of Nested IF and AND Functions to Calculate Income Tax on Salary with Payment Type

Let me show you how to calculate Calculate Income Tax on Salary with Payment Type in Excel by using Nested IF and AND functions.

Steps:

  • Firstly, select the cell in which you want to calculate the tax. Here, I selected F5.
  • In cell F5 write the following formula,
=IF(AND(D5="Remittence",E5="Filer"),C5*5%,IF(AND(D5="Remittence",E5="Non-Filer"),C5*7%,C5*3%))

6. Use of Nested IF and AND Functions to Calculate Income Tax on Salary with Payment Type

   Formula Breakdown

  • In this formula in the first IF function I used an AND function for the logicat_test, C5*5% as value_if_true, and another IF function as value_if_false.
  • In this second IF function I used another AND function as logical_test, C5*7% as value_if_true, and C5*3% as value_if_false.
  • Here, C5 is the Income. If the first AND is satisfied then the formula will return a 5% Tax on Income.
  • If the second AND function is satisfied then the formula will return a 7% Tax on Income and for all the other cases the formula will return a 3% Tax on Income.
  • In every case, I used absolute cell reference so that the selected cell range remain fixed while using the AutoFill.
  • Now, press ENTER to get the Tax amount.

6. Use of Nested IF and AND Functions to Calculate Income Tax on Salary with Payment Type

  • After that, drag Fill Handle to automatically copy the formula to all the cells of the column.

6. Use of Nested IF and AND Functions to Calculate Income Tax on Salary with Payment Type

Finally, you will get the Tax for all the employees.

6. Use of Nested IF and AND Functions to Calculate Income Tax on Salary with Payment Type


Things to Remember

  • It should be noted that while calculating income tax different tax slabs are applied for different regions.

Practice Section

Here, I have provided a practice sheet for you to practice How to Calculate Income Tax on Salary in Excel.

Practice Section


Conclusion

In this article, I covered different methods on How to Calculate Income Tax on Salary With Example in Excel. I used different examples from different situations for you to understand clearly. I hope this will be beneficial for you. In addition, I recommend you to practice more and more to get a better grip on excel. Last but not the least, if you have any questions or any suggestions feel free to let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo