Computing the Income Tax in Excel – 4 Methods

Method 1 – Use the Taxable Income to Compute the Income Tax in Excel

STEPS:

• Enter the Gross Income: the whole amount received from all sources, including refunds, discounts, and allowances, before subtracting any costs or taxes.
• Enter the Total Deductions and the Total Exemptions.

• Select a cell to compute the taxable income.

To calculate the taxable income, subtract total deductions and total exemptions from gross income:

• Enter the formula:

• Press Enter to see the result.

• For an income below $10,500, the tax rate is 10%.
• Enter the formula:

• Press Enter. The formula will be displayed in the formula bar.

Method 2 – Computing the Income Tax with Tax Brackets

STEPS:

• Enter the Gross Income, Total Deductions and Total Exemptions.
• Set the information of the Taxable Income Bracket and the Tax Rate for every range in Taxable Income.

To compute the tax based on the tax bracket:

• split the taxable income into tax brackets. Assume that your taxable income is within $90,000 and $180,000, which is included in the 4th tax bracket.

The fourth tax band has rates of 10%, 12%, 22%, and 24% . Get the Tax Rate of Taxable Amount by subtracting the upper limit and the lower limit of the Taxable Income Bracket. For a 10% tax rate the taxable income bracket ranges $0 to $10,275. So, ($10,275 – $0) = $10,275. Calculate the tax with a tax rate of 10%.

• Enter the formula:

• Press Enter to see the result.

The taxable income range for a 20% tax rate is $10,275 to $41,775. So, ($41,775 – $10,275) equals $31,500. For a tax rate of 20%:

• Enter the formula in C6.

• Press Enter to see the result.

The range for a 22% tax rate is $41,775 to $89,075. The result of the subtraction is ($89,075 – $41,775) = $47,300. For a tax rate of 22%:

• Enter the formula in C7.

• Press Enter to see the result.

For a 24% tax rate, the taxable income bracket ranges $89,075 to $170,050. So, ($10,275 – $89,075) = $25,925.

• Enter the formula:

• Press Enter to see the result.

Calculate the Taxable Income,

• Select the cell and enter the formula:

• Press Enter.

Compute the Total Tax:

• Enter the formula:

• Press Enter to see the result.

Method 3 – Use the IF Function to calculate the Income Tax in Excel

STEPS:

• Enter data: the Name of the employees and their Total Income.
• Enter the Lower Limit and the Upper Limit of the Tax Rate.

• Select the cell and enter the formula to calculate the Income Tax.

• Press Enter to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

You can see the Income Tax:

• Select a cell where to get the Highest Tax.
• Enter the formula:

• Press Enter to see the result.

The result will be displayed in the selected cell, and the formula in the formula bar.

• Drag down the Fill Handle to see the result in the rest of the cells.

The Highest Tax is calculated.

To find the Cumulative Tax (the tax payable on an employee’s total income from January 1 to the present):

• Select the cell to see the cumulative tax.
• Enter the formula:

• Press Enter.

For the second cumulative tax:

• Enter the formula:

• Press Enter to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

To calculate the individual cumulative tax:

• Select a cell to see the result.
• Enter the formula (the summation of the Cumulative Tax and the Income Tax).

• Press Enter to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Method 4 – Applying the VLOOKUP Function to Compute the Income Tax in Excel

STEPS:

• Select a cell to calculate the income tax for an employee.
• Enter the formula:

• Press Enter to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

• Select a cell to see the highest tax rate.
• Enter the formula:

• Press Enter to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

The Highest Tax is calculated. To determine the cumulative tax:

• Select a cell to display the cumulative tax.
• To calculate the first cumulative tax, enter the formula:

• Press Enter to see the result.

Calculate the second cumulative tax:

• Enter the formula in the cell you want to see the outcome

• Press Enter to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

The cumulative tax is displayed. To calculate the individual cumulative tax.

• Choose a cell to show the result.
• Enter the formula:

• Press Enter to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Read More: Income Tax Computation in Excel Format

Things to Remember

• When using the VLOOKUP function, you must press F4 key to lock the value of the table array. Otherwise, If you use the Autofill and drag the formula, you won’t obtain the true number.
• You must press F4 to lock the result of the logical test when using the IF function.

Download Practice Workbook

Download the workbook.

Related Articles

• Self Employment Tax Calculator in Excel Spreadsheet
• How to Create GST Late Fees Calculator in Excel

<< Go Back to Finance Templates | Excel Templates