**Tax **is a compulsory contribution to the stateâ€™s revenue imposed by the government on workersâ€™ income or business profit. It is also added to the cost of some goods, services, and transactions. As an ideal citizen of a country, it is a must to give a certain amount of tax in time. So, we need a way to calculate the withholding tax. In this article, I am going to discuss 4 effective ways **to calculate withholding tax with formula** in Excel.

For more clarification, I will use a Dataset with incomes and related deducted values. There is also a chart regarding the tax rate with the income amount.

**Table of Contents**hide

## Formula for Calculating Withholding Tax in Excel: 4 Effective Variants

### 1. Using Arithmetic Formula to Calculate Withholding Tax

We can calculate the withholding tax with simple arithmetic formula. It is a matter of great concern that this method is only applicable for a fixed amount of tax rate.

** Steps**:

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

- Next, deduce the taxable income 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.

- Now, input the formula mentioned below to calculate the withholding tax.

`=C9*C10`

Here,**C9** = Taxable Income**C10 **= Tax rate

- Finally, press
**ENTER**to have the withholding tax value.

**Read More:** Reverse Tax Calculation Formula in Excel

### 2. Formula with VLOOKUP Function to Calculate Withholding Tax

The **VLOOKUP function **is another option to calculate withholding tax. The steps are mentioned below with a clear explanation.

** Steps**:

- First of all, calculate the
**Taxable Income**. Input the following formula to do so.

`=C5-C6-C7`

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

- Next, press on the
**ENTERÂ**button.

- Add a new column (i.e.
**Cumulative Amount**) to have the Tax amount. - Input the following formula to have the tax amount. But keep in mind this formula is applicable to the second last range of your taxable income.

`=(C10-B10)*D10`

Here, The amount is calculated by subtracting the upper value and lower value in the range and multiplying it by the tax rate.

** **

- Hit
**ENTER**to have the tax amount in that range.

**AutoFill**till the second last range of the taxable income.

- After that, use the following formula to calculate the tax in the range of the tax amount.

`=(C15-B13)*D13`

Here,**C15 **= Taxable Income**
B13 **= Lower limit of the range where the taxable amount lies

**= Tax rate**

D13

D13

- Now, press the
**ENTER**button to have the output.

- Now, input the following formula to calculate the withholding tax in the selected cell.

`=VLOOKUP(C15,B10:D13,4,TRUE)+(C15-VLOOKUP(C15,B10:D13,1,TRUE))*VLOOKUP(C15,B10:D13,3,TRUE)`

__Formula Breakdown__

**VLOOKUP(C15,B10:D13,3,TRUE) â€”> **returns the tax value from the range matched to the taxable income.** Output: **1802.25

**C15-VLOOKUP(C15,B10:D13,1,TRUE) â€”> **The **VLOOKUP** function looks for **C15** in the range **B10:D13**. Returns the value from the 1st column. Next, deducts the output from **C15**.** Output: **7209

**VLOOKUP(C15,B10:D13,3,TRUE) â€”>Â **returns the rate of interest** Output: **0.25

**VLOOKUP(C15,B10:D13,3,TRUE)+(C15-VLOOKUP(C15,B10:D13,1,TRUE))*VLOOKUP(C15,B10:D13,3,TRUE)**

**1802.25 + 7209*0.25 â€”>**returns the final tax amount.

**3604.50**

__Output__:- Hit
**ENTER**to have the result.

**Read More:** How to Calculate Income Tax in Excel Using IF Function

### 3.Â Calculate Withholding Tax with Nested IF Function

Nested **IF function **is another effective way to calculate withholding tax. The whole process is discussed below.

** Steps**:

- Find the
**Taxable Income**For this, input the following formula to do so.

`=C5-C6-C7`

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

- Afterward, press on
**ENTER**.

- Now, apply the following formula to have the tax value.

`=IF(C15<=C10,"$0",IF(C15<=C11,(C15-C10)*D11,IF(C15<=C12,(C15-C11)*D12+1300,(C15-C12)*D13+3500)))`

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

- Finally, press
**ENTER**to have the tax amount.

### 4. Using SUMPRODUCT Function to Calculate Withholding Tax

In order to **calculate withholding tax**, we can use the **SUMPRODUCT function **too.

** Steps**:

- First of all, add 2 new columns to the dataset. Here, I named them
**Differential,**and**Amount**. - Find the difference in rate in the
**DifferentialÂ**column.

Use the following formula to do so in cell**E11**:

`=D11-D10`

Here, I subtracted the tax rate close to one another

- Press
**ENTER**to have the difference in rates.

- Use
**Fill Handle**to**AutoFill**the rest cells.

- Then, calculate the
**Taxable Income**. For this, input the following formula to do so.

`=C5-C6-C7`

Where,**C5 **= Gross Income**C6 **= Total Deductions**C7 **= Total Exemptions

- Along that, hit the
**ENTERÂ**button.

- Now, input the income amount related to the range in the first cell of the
**Amount**column (i.e.**$43210.00**). - Then, calculate the tax amount in the first range where the tax is allowed.

Use the following formula for this:

`=$C$15-C10`

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

- Press
**ENTER**.

**AutoFill**the remaining cells.

- Now, use the following formula to have the tax amount.

`=SUMPRODUCT(E10:E13,F10:F13)`

Here, Two arrays ranging **E10:E13 **and **F10:F13 **are multiplied first and then added the multiplied result.

- Then, hit
**ENTER**to have the result.

**Read More:** How to Calculate Income Tax on Salary with Old Regime in Excel

## Practice Section

You can practice here for further expertise.

**Download Practice Workbook**

## Conclusion

I have tried to explain 4 effective ways **to calculate withholding tax with formula** in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below.