Federal tax withholding calculations - using IF statements..?

[clintjjohnson]

I want to create a statement to calculate appropriate federal tax withholding
amounts based on gross income, which varies each pay period. The federal tax
tables specify minimum & maximum salary ranges, a withholding allowance,
multiplier percentages and a fixed withholding amount for each set of
calculations.

For example, a formula for an income figure between $389.00 and $1289.00 in
a pay period would be (assuming the gross income figure is in cell D6):
=((D6-130.77)-389)*.15+28.70

For an income figure between $1289.00 and $2964.00 in a pay period, the
formula would be:
=((D6-130.77)-1289)*.25+163.70

For an income figure between $2964.00 and $6262.00 in a pay period, the
formula would be:
=((D6-130.77)-2964)*.28+582.45

Now...how do I pull these all together into one statment that will look at
the gross income figure in cell D6 and produce the correct withholding figure
in another cell? (I currently use Excel 2000.)

Whew! Thanks...

 

[joeu2004]

I want to create a statement to calculate appropriate federal tax
withholding amounts based on gross income
[....]
Now...how do I pull these all together into one statment that will look
at the gross income figure in cell D6 and produce the correct withholding
figure

There are many ways to do this, each with their pros and cons.

Ostensibly (based on the table for Biweekly Single, which are the
numbers that you used):

=round(max((D6-102)*10%, (D6-389)*15%+28.70, (D6-1289)*25%+163.70,
(D6-2964)*28%+582.45, (D6-6262)*33%+1505.89, (D6-13525)*35%+3902.68),
2)

However, note that D6 is the amount subject to withholding, which is
the gross wages less pretax deductions and the withholding allowance.
If D4 is the gross wages less pretax deductions and D5 is the number
of allowances, D6 would be:

=D4 - 130.77*D5

 

[Stan Brown]

Sun, 8 Jul 2007 15:56:01 -0700 from clintjjohnson

Now...how do I pull these all together into one statment that will look at
the gross income figure in cell D6 and produce the correct withholding figure
in another cell? (I currently use Excel 2000.)

You *could* do it with an if statement, but I wouldn't. Instead, set
up the withholding table in three columns (min income, fixed dollars
to withhold, percent above min to withhold) and then use VLOOKUP.

Assuming the income figure is in D6, this formula will do it:

ROUND(VLOOKUP(D6,FederalTaxBrackets,3,TRUE)+VLOOKUP
(D6,FederalTaxBrackets,2,TRUE)*(D6-VLOOKUP
(D6,FederalTaxBrackets,1,TRUE)),2)

where FederalTaxBrackets is the named range that includes the table I
mentioned above.

 

[Ron Rosenfeld]

I want to create a statement to calculate appropriate federal tax withholding
amounts based on gross income, which varies each pay period. The federal tax
tables specify minimum & maximum salary ranges, a withholding allowance,
multiplier percentages and a fixed withholding amount for each set of
calculations.

For example, a formula for an income figure between $389.00 and $1289.00 in
a pay period would be (assuming the gross income figure is in cell D6):
=((D6-130.77)-389)*.15+28.70

For an income figure between $1289.00 and $2964.00 in a pay period, the
formula would be:
=((D6-130.77)-1289)*.25+163.70

For an income figure between $2964.00 and $6262.00 in a pay period, the
formula would be:
=((D6-130.77)-2964)*.28+582.45

Now...how do I pull these all together into one statment that will look at
the gross income figure in cell D6 and produce the correct withholding figure
in another cell? (I currently use Excel 2000.)

Whew! Thanks...

There are basically sixteen different percentage withholding tables to select
from. (Eight different payroll periods divided into Single and Married tables).

You quoted some figures from the Single/Biweekly Payroll period so I'll use
those. (You can download an Excel spreadsheet from www.irs.gov with all the
tables in it).

Set up a table someplace on your worksheet:

$ 0 $ 0.00 0%
$ 102 $ 0.00 10%
$ 389 $ 28.70 15%
$ 1,289 $ 163.70 25%
$ 2,964 $ 582.45 28%
$ 6,262 $1,505.89 33%
$13,525 $3,902.68 35%

I named it BiWeeklySingle

You obviously know that the withholding allowance amount for this table is
$130.77

You can use this formula:

=VLOOKUP(GrossIncome,BiWeeklySingle,2)+
(GrossIncome-VLOOKUP(GrossIncome,BiWeeklySingle,1))*
VLOOKUP(GrossIncome,BiWeeklySingle,3)

except that in place of GrossIncome you will need to substitute GrossIncome
minus 130.77 * the number of withholding allowances.





--ron

 

[clintjjohnson]

Thank you, that does the trick - being totally unfamiliar with just how those
functions work, I'm amazed... <g> I really appreciate your reply.

This will be used for only one "employee", there are no pretax deductions
and the withholding allowance will not change from the fixed "130.77" figure.
I've changed the formula slightly to accomodate this (where 130.77 is in cell
F6):
((D6-F6-102)*10%...

One further tweak would be nice - how do I prevent it from displaying
anything in its cell if D6 (the gross income) is empty?
--
- Clint Johnson

I want to create a statement to calculate appropriate federal tax
withholding amounts based on gross income
[....]
Now...how do I pull these all together into one statment that will look
at the gross income figure in cell D6 and produce the correct withholding
figure

There are many ways to do this, each with their pros and cons.

Ostensibly (based on the table for Biweekly Single, which are the
numbers that you used):

=round(max((D6-102)*10%, (D6-389)*15%+28.70, (D6-1289)*25%+163.70,
(D6-2964)*28%+582.45, (D6-6262)*33%+1505.89, (D6-13525)*35%+3902.68),
2)

However, note that D6 is the amount subject to withholding, which is
the gross wages less pretax deductions and the withholding allowance.
If D4 is the gross wages less pretax deductions and D5 is the number
of allowances, D6 would be:

=D4 - 130.77*D5

 

[joeu2004]

I've changed the formula slightly to accomodate this (where 130.77 is in cell
F6): ((D6-F6-102)*10%...

Yes, that works. But it is "inefficient" because you must do D6-F6 in
so many places. Then again, in a small worksheet, it is unlikely that
you will notice the extra microseconds (or multi-nanoseconds) on
today's fast CPUs. So whatever seems clearer and easier for you is
the thing to do.

One further tweak would be nice - how do I prevent it from displaying
anything in its cell if D6 (the gross income) is empty?

=if(D6="", "", round(max(...),2))

 

[joeu2004]

Set up a table someplace on your worksheet:

I agree that the table lookup method is easier to change later. But
the trick is to understand the tax tables well enough to set up the
table correctly.

$ 0 $ 0.00 0%
$ 102 $ 0.00 10%
$ 389 $ 28.70 15%
$ 1,289 $ 163.70 25%
$ 2,964 $ 582.45 28%
$ 6,262 $1,505.89 33%
$13,525 $3,902.68 35%
[....]
You can use this formula:
=VLOOKUP(GrossIncome,BiWeeklySingle,2)+
(GrossIncome-VLOOKUP(GrossIncome,BiWeeklySingle,1))*
VLOOKUP(GrossIncome,BiWeeklySingle,3)

Technically, your formula does not follow the logic of the tax
tables. For example, it should assess 10% tax only on the amount
__over__ $102, whereas your formula ostensibly assesses 10% tax on the
amount equal to $102. (But not really. Continue reading....)

Of course, your formula works for the federal tables because the tax
computed for the lowest limit of one tax bracket is the same amount
computed for the highest limit of the previous tax bracket. For
example, (389-389)*15%+28.70, which your formula computes, is the same
as (389-102)*10%, which is the correct formula to use. (And
(102-102)*10% is the same as (102-0)*0%.)

IMHO (and I'm sure you would agree), that should be true of any
"reasonable" tax table. So it is easy to become complacent.

But a year or two ago, I discovered that the Georgia tables did not
have that nice property. Arrgghh!

Since then, I have been careful to ensure that the lookup table
follows the logic of the tax tables to the letter. This makes the
lookup table slightly more complicated and a tad error-prone to set
up.

(For the same reason, I eschew formulas that rely on the "delta
percentages" between the tax brackets, including those formulas that I
promoted in the past.)

 

[Ron Rosenfeld]

Set up a table someplace on your worksheet:

I agree that the table lookup method is easier to change later. But
the trick is to understand the tax tables well enough to set up the
table correctly.

$ 0 $ 0.00 0%
$ 102 $ 0.00 10%
$ 389 $ 28.70 15%
$ 1,289 $ 163.70 25%
$ 2,964 $ 582.45 28%
$ 6,262 $1,505.89 33%
$13,525 $3,902.68 35%
[....]
You can use this formula:
=VLOOKUP(GrossIncome,BiWeeklySingle,2)+
(GrossIncome-VLOOKUP(GrossIncome,BiWeeklySingle,1))*
VLOOKUP(GrossIncome,BiWeeklySingle,3)

Technically, your formula does not follow the logic of the tax
tables. For example, it should assess 10% tax only on the amount
__over__ $102, whereas your formula ostensibly assesses 10% tax on the
amount equal to $102. (But not really. Continue reading....)

Of course, your formula works for the federal tables because the tax
computed for the lowest limit of one tax bracket is the same amount
computed for the highest limit of the previous tax bracket. For
example, (389-389)*15%+28.70, which your formula computes, is the same
as (389-102)*10%, which is the correct formula to use. (And
(102-102)*10% is the same as (102-0)*0%.)

IMHO (and I'm sure you would agree), that should be true of any
"reasonable" tax table. So it is easy to become complacent.

But a year or two ago, I discovered that the Georgia tables did not
have that nice property. Arrgghh!

Since then, I have been careful to ensure that the lookup table
follows the logic of the tax tables to the letter. This makes the
lookup table slightly more complicated and a tad error-prone to set
up.

(For the same reason, I eschew formulas that rely on the "delta
percentages" between the tax brackets, including those formulas that I
promoted in the past.)

The formula I wrote was designed to work with the Federal tax tables using the
logic with which they were constructed. It was NOT designed to also work with
the Georgia tax tables, or the NH tax tables, or any number of other untested
tables which may have been constructed using a different logic.
--ron

 

[Ron Rosenfeld]

Of course, your formula works for the federal tables because the tax
computed for the lowest limit of one tax bracket is the same amount
computed for the highest limit of the previous tax bracket. For
example, (389-389)*15%+28.70, which your formula computes, is the same
as (389-102)*10%, which is the correct formula to use. (And
(102-102)*10% is the same as (102-0)*0%.)

IMHO (and I'm sure you would agree), that should be true of any
"reasonable" tax table. So it is easy to become complacent.

But a year or two ago, I discovered that the Georgia tables did not
have that nice property. Arrgghh!

I just looked at the Georgia Tax Rate Schedule for 2006 and your notion that it
is constructed differently then the Federal Tax Table does not seem to be the
case.

What am I missing?

Single

$- $- 1%
$750.00 $7.50 2%
$2,250.00 $37.50 3%
$3,750.00 $82.50 4%
$5,250.00 $142.50 5%
$7,000.00 $230.00 6%

MFJ or HoH

$- $- 1%
$1,000.00 $10.00 2%
$3,000.00 $50.00 3%
$5,000.00 $110.00 4%
$7,000.00 $190.00 5%
$10,000.00 $340.00 6%

MFS

$- $- 1%
$500.00 $5.00 2%
$1,500.00 $25.00 3%
$2,500.00 $55.00 4%
$3,500.00 $95.00 5%
$5,000.00 $170.00 6%

--ron

 

[Stan Brown]

Sun, 8 Jul 2007 19:10:01 -0700 from clintjjohnson

One further tweak would be nice - how do I prevent it from displaying
anything in its cell if D6 (the gross income) is empty?

=if(D6, ..., "")

where ... is the actual formula.

 

[joeu2004]

The formula I wrote was designed to work with the Federal tax tables
using the logic with which they were constructed.

First, I freely admit that my comments were pedantic and much ado
about nothing. But since you take issue with them, I feel compelled
to elaborate.

Technically, your design (table and formula) does not follow the logic
of even the federal tables. By that I mean, for example, the federal
tables applies the 15% tax bracket only if the taxable income is
__over__ $389. But your design applies the 15% tax bracket if the
taxable income is __equal_to__ (or over) $389.

Nonetheless, as I noted previously, your design does work by accident
of implementation -- an "accident" that is very reasonable to expect,
I might add. By that I mean, for example, figuring the tax on $389
using the 15% bracket arrives at the same result as it would by using
the 10% bracket, as you should have.

So again, much ado about nothing.

 

[joeu2004]

I just looked at the Georgia Tax Rate Schedule for 2006 and your notion that it
is constructed differently then the Federal Tax Table does not seem to be the
case.

What am I missing?

Probably nothing.

First, I did say that my observation was "a year or two ago". Since
the earlier tables were screwed up (IMHO), it would not surprise me if
they were an anomaly that has been corrected. (I asked about a
correction at the time, but I never got a response, as I recall.)

Second -- and I hate to admit this -- I probably should have said "one
state's tables" instead of "Georgia's tables". I am "pretty sure" it
was indeed Georgia; but my recollection could be wrong. Moreover, I
cannot remember if they were withholding tables or actual tax tables.
I posted something about this in misc.taxes.moderated at the time.
But it is too difficult to find it now (sigh).

Finally, I did not say that the state table was "constructed
differently". Superficially, it looked the same. The devil was in
the details.

First, as I recall, the columns were labeled ambiguously -- not "over"
and "not over", but "is" and "not over". Thus, two brackets would
seem to apply to the boundary amount.

Second -- and this is the kicker -- the amount added was not always
the cumulative tax based on applying the earlier marginal rates.
Sometimes it was; sometimes it was higher; sometimes it was lower. Of
course, the difference was very small; you would notice it only if you
bothered to check. (I checked only because of the ambiguous labeling
of the columns, which caught my attention.)

So again, much ado about nothing.

 

[Ron Rosenfeld]

So again, much ado about nothing.

Since the results of our differing interpretations result in the same output,
and since you admit it to be much ado about nothing ... well, nuff said.
--ron