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

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