Calculate federal withholding rates from tax table?

[charlesstricklin]

I'd like to calculate the amount of withholding for the federal taxes based on the current IRS rates. I could use a nested IF statement, but I'd prefer something using the actual table provided by the IRS, making it easier to update when the new tax codes come out.

To wit, the single, biweekly withholding table:

[TABLE="class: grid"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Base[/TD]
[TD="align: center"]Percent[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]$96.00[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]$480.00[/TD]
[TD="align: right"]$38.40[/TD]
[TD="align: right"]15%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]$1,656.00[/TD]
[TD="align: right"]$214.80[/TD]
[TD="align: right"]25%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]$3,877.00[/TD]
[TD="align: right"]$770.05[/TD]
[TD="align: right"]28%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]$7,983.00[/TD]
[TD="align: right"]$1,919.73[/TD]
[TD="align: right"]33%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]$17,242.00[/TD]
[TD="align: right"]$7,975.20[/TD]
[TD="align: right"]35%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]$17,313.00[/TD]
[TD="align: right"]$5,000.05[/TD]
[TD="align: right"]40%[/TD]
[/TR]
</tbody>[/TABLE]

So, if a person's biweekly gross income is $2,000.00, they'd be taxed based on line 4:

$214.80 + (25% * ($2,000.00 - $1,656.00))

$214.80 + (25% * $344.00)

$214.80 + (25% * $344.00)

$214.80 + $86

$300.80

So, they'd be taxes $330.80 on a biweekly gross income of $2,000.00

If there a calculation to handle this table data?

 

[joeu2004]

To wit, the single, biweekly withholding table:
[....]
So, if a person's biweekly gross income is $2,000.00, [....] they'd be taxes $330.80 on a biweekly gross income of $2,000.00[.] If there a calculation to handle this table data?

I have not vetted your withholding table. But add column D with formulas shown to the right.

[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]

Amount​

[/TD]
[TD]

Base​

[/TD]
[TD]

Percent​

[/TD]
[TD]

%Diff​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]$96.00
[/TD]
[TD="align: right"]-
[/TD]
[TD="align: right"]10%
[/TD]
[TD="align: right"]10.00%
[/TD]
[TD]D2: =C2
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]$480.00
[/TD]
[TD="align: right"]$38.40
[/TD]
[TD="align: right"]15%
[/TD]
[TD="align: right"]5.00%
[/TD]
[TD]D3: =C3-C2
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]$1,656.00
[/TD]
[TD="align: right"]$214.80
[/TD]
[TD="align: right"]25%
[/TD]
[TD="align: right"]10.00%
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]$3,877.00
[/TD]
[TD="align: right"]$770.05
[/TD]
[TD="align: right"]28%
[/TD]
[TD="align: right"]3.00%
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]$7,983.00
[/TD]
[TD="align: right"]$1,919.73
[/TD]
[TD="align: right"]33%
[/TD]
[TD="align: right"]5.00%
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]$17,242.00
[/TD]
[TD="align: right"]$7,975.20
[/TD]
[TD="align: right"]35%
[/TD]
[TD="align: right"]2.00%
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]$17,313.00
[/TD]
[TD="align: right"]$5,000.05
[/TD]
[TD="align: right"]40%
[/TD]
[TD="align: right"]5.00%
[/TD]
[TD="align: right"][/TD]
[/TR]
</TBODY>[/TABLE]

Copy D3 down through D8.

Then if the taxable income is in G1 (2000), the following formula calculates the withholding tax (300.80):

=SUMPRODUCT((G1>$A$2:$A$8)*(G1-$A$2:$A$8),$D$2:$D$8)

 

[joeu2004]

I have not vetted your withholding table.

FYI, the table you posted, and I copied and embellished, is the US 2015 withholding table for Single Semimonthly (Table 3), not Biweekly (Table 2). (Click here.)

 

[petertenthije]

For this calculation I am using your table above ($A$1:$C$8). For the income you want to know the taxes for I am using cell F2.

Here is the full formula:
=INDEX($B:$B;MATCH($F2;$A$1:$A$8;1);1)+($F2-INDEX($A$1:$A$8;MATCH($F2;$A$1:$A$8;1);1))*INDEX($C:$C;MATCH($F2;$A$1:$A$8;1);1)



Here is a breakdown of the individual steps:

This step looks up the base value (214,80$)
=INDEX($B$1:$B$8;MATCH($F2;$A$8:$A$8;1);1)

This step is used to calculate the percentage value (25%)
=INDEX($C$1:$C$8;MATCH($F2;$A$1:$A$8;1);1)

This step is used to calculate the dollars ($2000-$1656 = $344)
=($F2-INDEX($A$1:$A$8;MATCH($F2;$A$1:$A$8;1);1))



One minor but important caveat, with your table this formula will only work for incomes of 96 dollar and higher. If the income is less the 96 dollar then the formula won't work. To counter that you should add a row between the header and the first row with data. In this row you have to just type 0 for each cell. Of course it is highly unlikely for someone to have an income lower then 96$, so in real life this issue is not likely to occur.

 

[charlesstricklin]

FYI, the table you posted, and I copied and embellished, is the US 2015 withholding table for Single Semimonthly (Table 3), not Biweekly (Table 2). (Click here.)

Yes, I believe you're correct. Sorry for the mistake.Here's the correct table, taken directly from the IRS:

Also, can anyone explain what that last column represents?

Given that information, would you agree that this would be the best way of representing that table as a lookup table?


[TABLE="class: grid, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Wages[/TD]
[TD="align: center"]Percentage[/TD]
[TD="align: center"]Tax[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]$88.00
[/TD]
[TD="align: center"]10%[/TD]
[TD="align: right"]$0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]$443.00[/TD]
[TD="align: center"]15%[/TD]
[TD="align: right"]$35.50[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]$1,529.00[/TD]
[TD="align: center"]25%[/TD]
[TD="align: right"]$198.40[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]$3,579.00[/TD]
[TD="align: center"]28%[/TD]
[TD="align: right"]$710.90[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]$7,369.00[/TD]
[TD="align: center"]33%[/TD]
[TD="align: right"]$1,772.10[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]$15,915.00[/TD]
[TD="align: center"]35%[/TD]
[TD="align: right"]$4,592.28[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]$15,981.00[/TD]
[TD="align: center"]39.%[/TD]
[TD]$4,615.38[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

Here's the correct table, taken directly from the IRS [...]. Also, can anyone explain what that last column represents?

Your problem in understanding might be because "of excess over" is in the wrong place. This is what I find in IRS Pub 15 (HTML version; click here, then page down).

So it behaves exactly as you described in your original posting, correcting the numbers. For taxable wages of $2000, the tax is: 198.40 plus 25% of (2000 - 1529); that is, 25% "of excess over" 1529.

But note that the right-most column ("of excess over") is and always will be identical to the left-most column (taxable wages).

So only one column is not needed in an Excel implementation. It is best to retain the left-most column for look-up purposes.

Given that information, would you agree that this would be the best way of representing that table as a lookup table?

The table can be:

[TABLE="class: grid, width: 300"]
<TBODY>[TR]
[TD="align: right"][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD="align: right"]
1
[/TD]
[TD="align: right"]Taxl Wages
Over</SPAN>
[/TD]
[TD="align: right"]
Base Tax</SPAN>
[/TD]
[TD="align: right"]
Plus</SPAN>
[/TD]
[TD="align: right"]
Diff%</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]$0.00</SPAN>
[/TD]
[TD="align: right"]$0.00</SPAN>
[/TD]
[TD="align: right"]0.00%</SPAN>
[/TD]
[TD="align: right"]0.00%</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]$88.00</SPAN>
[/TD]
[TD="align: right"]$0.00</SPAN>
[/TD]
[TD="align: right"]10.00%</SPAN>
[/TD]
[TD="align: right"]10.00%</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]$443.00</SPAN>
[/TD]
[TD="align: right"]$35.50</SPAN>
[/TD]
[TD="align: right"]15.00%</SPAN>
[/TD]
[TD="align: right"]5.00%</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]$1,529.00</SPAN>
[/TD]
[TD="align: right"]$198.40</SPAN>
[/TD]
[TD="align: right"]25.00%</SPAN>
[/TD]
[TD="align: right"]10.00%</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]$3,579.00</SPAN>
[/TD]
[TD="align: right"]$710.90</SPAN>
[/TD]
[TD="align: right"]28.00%</SPAN>
[/TD]
[TD="align: right"]3.00%</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]$7,369.00</SPAN>
[/TD]
[TD="align: right"]$1,772.10</SPAN>
[/TD]
[TD="align: right"]33.00%</SPAN>
[/TD]
[TD="align: right"]5.00%</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]$15,915.00</SPAN>
[/TD]
[TD="align: right"]$4,592.28</SPAN>
[/TD]
[TD="align: right"]35.00%</SPAN>
[/TD]
[TD="align: right"]2.00%</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD="align: right"]$15,981.00</SPAN>
[/TD]
[TD="align: right"]$4,615.38</SPAN>
[/TD]
[TD="align: right"]39.60%</SPAN>
[/TD]
[TD="align: right"]4.60%</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

With taxable wages in G1 ($2000), the tax can be calculated using:

=ROUND(SUMPRODUCT((G1>$A$2:$A$9)*(G1-$A$2:$A$9),$D$2:$D$9), 2)

Note that B1:B9 (column B) is not needed. Also, A2:D2 (row 2) is not needed in this case because the tax is zero.

Also note that I add explicit rounding, which is a good idea when you want a dollars-and-cents result.

D2 is =C2, and D3 is =C3-C2. Copy D3 down through D9.

(If you omit row 2, D3 is =C3, and D4 is =C4-C3.)

-----

If you prefer petertenthije's approach, the tax can be calculated using:

=ROUND(VLOOKUP(G1,$A$2:$C$9,2) + VLOOKUP(G1,$A$2:$C$9,3)*(G1-VLOOKUP(G1,$A$2:$C$9,1)), 2)

Note that row 2 and column B are required for that formula, unlike the SUMPRODUCT formula above.

But note the D1:D9 (column D) is not needed.

-----

In either case, note that the left-most column is taxable wages.

Usually, that is not the same as gross wages, which you wrote in your original posting.

At a minimum, usually a withholding "allowance" is subtracted from gross wages.

In addition, other nontaxable amounts are also subtracted; for example, employee contribution to a
401(k).

The withholding "allowance" is determined by multiplying the number of allowances specified in the
W-4 form by the amount indicated in the following table. Usually, the number of allowances is at least 1, although it can be zero.

So if $2000 is truly gross wages, taxable wages (G1) is typically $1846.20 (2000 - 153.80).

This is not a tax advice forum. Refer to IRS Pub 15 for details.

 

[charlesstricklin]

Here is the full formula:
=INDEX($B:$B;MATCH($F2;$A$1:$A$8;1);1)+($F2-INDEX($A$1:$A$8;MATCH($F2;$A$1:$A$8;1);1))*INDEX($C:$C;MATCH($F2;$A$1:$A$8;1);1)

Here is a breakdown of the individual steps:

This step looks up the base value (214,80$)
=INDEX($B$1:$B$8;MATCH($F2;$A$8:$A$8;1);1)

This step is used to calculate the percentage value (25%)
=INDEX($C$1:$C$8;MATCH($F2;$A$1:$A$8;1);1)

This step is used to calculate the dollars ($2000-$1656 = $344)
=($F2-INDEX($A$1:$A$8;MATCH($F2;$A$1:$A$8;1);1))

Excel complains of a problem with each formula. I think something's wrong with the INDEX function. I've tried changing the array from $B:$B to B1:B8, but no luck.

 

[charlesstricklin]

Excel complains of a problem with each formula. I think something's wrong with the INDEX function. I've tried changing the array from $B:$B to B1:B8, but no luck.

I figured out why. The first array should be $B1:$B8, not $B:$B. Second, the semicolons should all be commas.

 

[charlesstricklin]

Note that B1:B9 (column B) is not needed.

So, for our purposes, it really just exists to remind us what bracket one falls under?

Also, given the example, the correct taxes withheld should be $316.15?

 

[joeu2004]

=ROUND(SUMPRODUCT((G1>$A$2:$A$9)*(G1-$A$2:$A$9),$D$2:$D$9), 2)

Note that B1:B9 (column B) is not needed.

So, for our purposes, it really just exists to remind us what bracket one falls under?

No, not at all. I was merely pointing out the obvious: column B is not used in the SUMPRODUCT formula.

I included column B in the table only for the VLOOKUP formula, an improvement of petertenthije's approach.

I would use the SUMPRODUCT formula.

For taxable wages of $2000, the tax is: 198.40 plus 25% of (2000 - 1529); that is, 25% "of excess over" 1529.

Also, given the example, the correct taxes withheld should be $316.15?

Sure; do the math. 198.40 + 25%*(2000-1529) = 198.40 + 25%*471 = 198.40 + 117.75 = 316.15.