Income Tax Computation in Excel Format (4 Suitable Solutions)

You can see a sample data set given below that will help us compute income tax in Excel format.

Method 1 – Computing Income Tax for Flat Rates in Excel Format

Steps:

• Add more cells to calculate taxable income from gross income.

• Subtract total deductions and total exemptions from gross income in cell E5. Use the following formula:

• Press Enter to see the taxable income in cell E5.

• Suppose that, for income below $10,500, the tax rate is a flat 10%. Calculate the tax in cell E6 using the following formula.

• Press Enter to apply.

Read More: Computation of Income Tax Format in Excel for Companies

Method 2 – Using Tax Brackets for Computation of Income Tax in Excel Format

Tax brackets are typically determined progressively by the federal government for different income ranges. So, you’ll need to create a table detailing the tax brackets and their rates.

Steps:

• Calculate taxable income from gross income like the previous method and create helper cells to do so.

• Subtract the total deductions and total exemptions from gross income in cell B8:

• Hit Enter.

• Divide the taxable income according to the tax bracket.
• Since the taxable income is between $89,075 and $170,050 (4th tax bracket) in the taxable income bracket, the tax will be calculated for four brackets: 10%, 12%, 22%, and then 24% on income.
• Calculate the tax for the first $10,275 with a tax rate of 10% from the taxable income in cell F11 using the following formula.

• Subtract $10,275 from $41,775 and then calculate the tax with the associated tax rate in cell F12 with the formula written below.

• Subtract $41,775 from $89,075 and calculate the tax with the associated tax rate in cell F13 just like the previous step by using the formula below.

• For the last tax bracket, we will subtract $89,075 from $115,000 since that’s the taxable income.
• Calculate the tax with the associated tax rate in cell F14 using the following formula:

• Add all the tax values from the previous step and get the total tax by using the following equation in cell E8:

• Press Enter.

Method 3 – Applying IF Function for Computation of Income Tax in Excel Format

• Consider the following data set for computing taxes by using the IF function.

Steps:

• Use the following formula to compute the income tax for Nathan in cell D5:

• Pess Enter to see the value of income tax in cell D5.

• Use the fill handle to drag the formula to the other cells.

• Compute the highest tax in cell E12 by using the following formula.

• Press Enter to see the result in cell E12.

• Use AutoFill for the other cells.

• Compute the cumulative tax in cell F12 by applying the following formula.

• Press Enter.

• Type the following formula in cell F13 to get the cumulative tax value:

• Press Enter to get the value.

• Use AutoFill for the rest of the column.

• To compute the individual cumulative tax for Nathan, type the following formula in cell E5.

• Press Enter.

• AutoFill the other cells by dragging the fill handle.

Method 4 – Inserting VLOOKUP Function for Computation of Income Tax in Excel Format

• Consider the following data set to compute tax using the VLOOKUP function.

Steps:

• Use the following formula of the VLOOKUP function to compute the income tax for Nathan in cell D5.

• Then, press Enter to see the value of income tax in cell D5.

• Drag the formula to the lower cells via the fill handle.

• Compute the highest tax in cell E12 using the following formula:

• Hit Enter.

• Use the fill handle to AutoFill the rest of the cells in the colum.

• Compute the cumulative tax in cell F12 using the following formula.

• Press Enter.

• Type the following formula in cell F13 to get the cumulative tax value:

• Press Enter to get the value.

• Use the fill handle to drag the formula to the other cells.

• To compute the individual cumulative tax for Nathan, type the following formula in cell E5:

• Press Enter.

• AutoFill the column via the fill handle.

Things to Remember

• While computing income tax by using the VLOOKUP function, you must lock the value of the table array using F4. Otherwise, you won’t get accurate results when you use AutoFill to drag the formula in the lower cells.
• Similarly, while using the IF function, you must lock the value of the logical test using F4.

Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.

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