Formula for Calculating Withholding Tax in Excel: 4 Effective Variants

Method 1 – Using Arithmetic Formula to Calculate Withholding Tax

Steps:

• Input the tax rate first(i.e. 50%) as this method is only applicable for the fixed tax rate.

• The taxable income is deducted by subtracting the related factors. Input the following formula to do so.

Here,
C5 = Gross Income
C6 = Total Deductions
C7 = Total Exemptions

• Press ENTER to have the taxable income.

• Input the formula mentioned below to calculate the withholding tax.

Here,
C9 = Taxable Income
C10 = Tax rate

• Press ENTER to have the withholding tax value.

Method 2 – Calculate Withholding Tax with Nested IF Function

Steps:

• Find the Taxable Income for this, input the following formula to do so.

Here,
D5 = Gross Income
D6 = Total Deductions
D7 = Total Exemptions

• Press ENTER.

• Apply the following formula to have the tax value.

=IF($C$15<=$C$10, $D$10,

    IF($C$15<=$C$11, ($C$15-$B$11)*$D$11,

        IF($C$15<=$C$12, ($B$12-$B$11)*$D$11 + ($C$15-$B$12)*$D$12,

            ($B$12-$B$11)*$D$11 + ($B$13-$B$12)*$D$12 + ($C$15-$B$13)*$D$13

        )

    )

)

The IF function searches for the condition related to the taxable income value and returns the Tax amount. The IF functions traverse until the conditions are met. Each IF checks whether the value presents within the range

• Press ENTER to have the tax amount.

Method 3 – Formula with IFS Function to Calculate Withholding Tax

Steps:

• Calculate the Taxable Income. Input the following formula to do so.

Here,
D5 = Gross Income
D6 = Total Deductions
D7 = Total Exemptions

• Press the ENTER button.

• Input the following formula to calculate the withholding tax in the selected cell.

• Hit ENTER to have the result.

Method 4 – Using SUMPRODUCT Function to Calculate Withholding Tax

Steps:

• Add 2 new columns to the dataset. We named them Differential and Amount.

• Calculate the Taxable Income. Input the following formula.

• Hit the ENTER button.

• Find the difference in rate in the Differential column.
  // Insert 0% in cell E10 and use the following formula to do so in cell E11:

We subtracted the tax rates close to one another:

• Press ENTER to have the difference in rates.

• Use Fill Handle to AutoFill the rest cells.

• Input the income amount related to the range in the first cell of the Amount column (i.e. $43210.00).
• Calculate the tax amount in the first range where the tax is allowed.
  Use the following formula in cell F11:

Here,
C15 = Taxable income
C10 = Highest amount in the first range

• Press ENTER.

• AutoFill the remaining cells.

• Use the following formula to have the tax amount.

Two arrays ranging from E10:E13 and F10:F13 are multiplied first and then added to the multiplied result.

• Hit ENTER to have the result.

Download Practice Workbook

Related Articles

• How to Calculate Marginal Tax Rate in Excel
• How to Calculate Social Security Tax in Excel
• How to Calculate Federal Tax Rate in Excel
• How to Calculate Income Tax on Salary with Example in Excel
• How to Calculate Sales Tax in Excel

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