How to Calculate Income Tax on Salary with Example in Excel

In this article, we will demonstrate different methods to calculate Income Tax on Salary. To illustrate our methods, we’ll use the following sample dataset that contains Employee Name, Gross Salary, and Taxable Income for different employees.

Example 1 – Using Generic Formula

The simplest way to calculate income tax in Excel is using the generic formula. We’ll use a fixed tax rate for all the employees and calculate the income tax on their salaries.

Steps:

First, we calculate taxable income from the Gross Salary and Total Deduction.

• Select the cell in which to calculate the taxable income (cell E5).
• Enter the following formula:

Here, we simply subtract the Total Deduction from the Gross Salary.

• Press ENTER to return the Taxable Income for Jack.

• Drag the Fill Handle down to get Taxable Income for every employee.

Now, we can calculate the Tax on Salary.

• Select the cell in which to calculate the Tax on Salary (cell F5).
• Enter the following formula:

Here, E5 is the Taxable Income for that particular employee and C11 is the fixed Tax Rate. We multiply Taxable Income by Tax Rate to get that employee’s Tax on Salary.

• Press ENTER.

• Drag the Fill Handle down to get Tax on Salary for every employee.

The results are as follows:

Read More: Reverse Tax Calculation Formula in Excel

Example 2 – Different Tax Brackets and Tax Surcharge

Now let’s calculate the tax on salary when where there are different tax brackets (also called slabs) for different income ranges, and a surcharge is applied on the tax for some income ranges.

Steps:

• In cell D5 enter the following formula:

Here, C5 is the Taxable Income for Jack and C13 is the tax rate from the Income Tax Slab for that particular income range. The formula will multiply these two values and return the Income Tax for that employee.

• Press ENTER to return the Income Tax for Jack.

• In cell D6 enter the following formula:

Here, C6 is the Taxable Income for Jack and C12 is the tax rate from Income Tax Slab for that particular income range. This formula will multiply these two values and return the Income Tax for Jack.

• Press ENTER.

• Calculate Income Tax for every employee by following these exact steps.

In our dataset, a Surcharge is applied on the tax for some income ranges. No surcharge is applicable for Jack, Jerry, and Amy, because their Taxable Income does not fall in those ranges.

For Penny, a 5% surcharge is applicable. Let’s calculate her Surcharge.

• In cell E7, enter the following formula:

Here, D7 is the Income Tax for Penny and C19 is the Surcharge Rate. This formula will multiply Income Tax and Rate and return the Surcharge on tax for Penny.

• Press ENTER.

For Steve, a 10% surcharge is applicable.

To derive Steve’s Surcharge, we’ll follow the same steps as we followed for Penny.

• In cell E8, enter the following formula:

Here, D8 is the Income Tax for Steve and C20 is the Surcharge Rate. This formula will multiply Income Tax and Rate and return the Surcharge on tax for Steve.

• Press ENTER.

Now that we have income tax and surcharge for all the employees, we can calculate the total tax for every employee.

• In cell F5, enter the following formula:

Here, D5 is Income Tax and E5 is Surcharge. This formula will return the sum of Income Tax and Surcharge, which is the Total Tax for Jack.

• Press ENTER.

• Drag the Fill Handle down to get the Total Tax for every employee.

The results are as follows:

Read More: How to Calculate Sales Tax in Excel

Example 3 – Using Excel SUMPRODUCT Function

We can also calculate income tax on salary in Excel with the use of the SUMPRODUCT function.

Steps:

• Select the first row of the Income Tax Slab table.

• Right-click on the row and select Insert to insert a new row.

• In cell D5 enter the following formula:

Here, in the SUMPRODUCT function, we select D14:D18-D13:D17 as array1 which figures out the differential rate. For array2 we select 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 we use the N function which will return the value for C5>C14:C18 accordingly.

The SUMPRODUCT function will return the sum of the multiplied components of these 3 arrays.

In every case, we use absolute cell references so that the selected cell range remains fixed when using the AutoFill.

• Press ENTER to return the Income Tax for that specific employee.

• Drag the Fill Handle down to copy the formula to the rest of the cells below.

We have the Income Tax value for every employee.

Example 4 – Using VLOOKUP Function

To calculate income tax by using the VLOOKUP function, we add an extra column in the income tax slab, Cumulative, which represents the cumulative tax for that particular tax rate.

Steps:  

• In cell D5, enter the following function:

• Press ENTER to return the Income Tax value for Jack.

• Drag the Fill Handle down to get the Income Tax for every employee.

We have the Income Tax on salary for every employee.

Example 5 – Using Nested IF Function

In this example we consider 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.

Steps: 

First we calculate the Taxable Income using the Investment rate and Salary.

• In cell E5 enter the following formula:

Here, D5>=30% is the logical_test, C5-(C5*D5) is the value_if_true, and C5 is the 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 the result as Taxable Income, and if the logical test is FALSE then it will return the Salary as Taxable Income.

• Press ENTER to return the Taxable Income for Danial.

• Drag the Fill Handle down to get the Taxable Income for every employee.

The results are as follows:

Now we can calculate Income Tax.

• In cell F5 enter the following formula:

• Press ENTER to return the Tax for Danial.

• Drag the Fill Handle down to get the Tax for every employee.

The results are as follows:

Example 6 – Using Nested IF & AND Functions with Payment Type

This example considers Payment Type and Status. If someone’s payment type is Remittance and status is Filer, then he/she will have to pay 5% tax on salary. If someone’s payment type is Remittance and status is Non-Filer, then he/she will have to pay 7% tax on salary. For all the other cases, a 3% tax on salary is applied.

Steps:

• In cell F5, enter the following formula:

• Press ENTER to get the Tax amount for Danial.

• Drag the Fill Handle to automatically copy the formula to all the cells in the column.

The Tax for all the employees is returned.

Download Practice Workbook

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 Formulas for Finance | Excel for Finance | Learn Excel