# 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.
`=C5-C6-C7`

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.
`=C9*C10`

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.
`=D5-D6-D7`

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.
`=D5-D6-D7`

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.

`=IFS(`

`Â Â Â  \$C\$15<=\$C\$10, \$D\$10,`

`Â Â Â  \$C\$15<=\$C\$11, (\$C\$15-\$B\$11)*\$D\$11,`

`Â Â Â  \$C\$15<=\$C\$12, (\$B\$12-\$B\$11)*\$D\$11 + (\$C\$15-\$B\$12)*\$D\$12,`

`Â Â Â  TRUE, (\$B\$12-\$B\$11)*\$D\$11 + (\$B\$13-\$B\$12)*\$D\$12 + (\$C\$15-\$B\$13)*\$D\$13`

`)`

• 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.
`=D5-D6-D7`

• 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:
`=D5-D6-D7`

We subtracted the tax rates close to one another:

• Press ENTER to have the difference in rates.

• 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:
`=\$C\$15-C10`

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.
`=SUMPRODUCT(E10:E13,IF(F10:F13<0,0,F10:F13))`

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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

1. Why are the results different for each method? shouldn’t they all come out with the same tax owed result. Seems like something is wrong here.

Lutfor Rahman Shimanto Jun 12, 2024 at 4:09 PM

Hello Michael A. Dunn

Thanks for visiting our blog and noticing a critical fact. You were right. Sorry for the inconvenience. We have improved all the formulas and modified the article.

Using the arithmetic formula would be best if you worked with a fixed rate. So, it may provide different results from other procedures. However, all the procedures except for using the arithmetic formula will calculate almost the same result. So, if you do not have a fixed rate, consider applying the formulas Using Nested IF, IFS and SUMPRODUCT functions.

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF