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

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.

Example 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,

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.

• 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.

• 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,

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.

• 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: Reverse Tax Calculation Formula in Excel

Example 2: Calculating Income Tax on Salary 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.

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.

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.

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,

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.

• Firstly, select cell E8.
• In cell E8, write the following formula,

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.

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,

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.

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

Read More: How to Calculate Sales Tax in Excel

Example 3: Inserting Excel SUMPRODUCT Function to Calculate Income Tax on Salary

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.

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

• 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,

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 the 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.

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

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

Example 4: Using VLOOKUP Function to Determine Income Tax on Salary

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,

• 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.

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

Example 5: Using Nested IF Function to Calculate Income Tax 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.

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,

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.

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

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

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,

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

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

Finally, you have calculated Tax for all the employees.

Example 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.

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,

• Now, press ENTER to get the Tax amount.

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

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

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.

Download Practice Workbook

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

• How to Calculate Marginal Tax Rate in Excel
• How to Calculate Social Security Tax in Excel
• Formula for Calculating Withholding Tax in Excel
• How to Calculate Federal Tax Rate in Excel

<< Go Back to Excel Tax Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel