"jgoblish" wrote:
I need help in setting up fed and state payroll tax deductions
using the percentage method. I have checked other payroll
tax templates and don't find what I am looking for. It would
be a look up table that the field would have to look at to see
what percentage of tax needs to be withheld, depending on
the wages for the period, such as every two week payperiod.

For one method, refer to http://www.mcgimpsey.com/excel/variablerate.html .

Alternatively and expanding, consider the following.

First, set up a table with the amounts for 1 allowance. For example:

Allowance
1 Weekly 67.31
2 Biweekly 134.62
3 Semimonthly 145.83
4 Monthly 291.67
5 Quarterly 875.00
6 Semiannually 1750.00
7 Annually 3500.00
8 Daily, Misc 13.46

Assume that B2 contains the gross wage subject to tax (after pre-tax
deductions), and C2 contains the payroll period index (1, 2, etc).

Then the amount subject to withholding can be computed in D2:

=B2 - vlookup(C2, Allowance, 3, 0)

Second, create a withholding table for each payroll period. For example,
for federal:

Weekly
0 0 0%
51 0 10%
198 14.70 15%
653 82.95 25%
1533 302.95 28%
3202 770.27 33%
6918 1995.89 35%

Then the federal withholding can be computed by:

=round( (D2 - vlookup(D2, choose(C2, Weekly, Biweekly, SemiMonthly, Monthly,
Quarterly, Semiannually, Annually, Daily), 1)
* vlookup(D2, choose(C2, Weekly, Biweekly, SemiMonthly, Monthly,
Quarterly, Semiannually, Annually, Daily), 3)
+ vlookup(D2, choose(C2, Weekly, Biweekly, SemiMonthly, Monthly,
Quarterly, Semiannually, Annually, Daily), 2), 2)

Note that other rounding options may be permitted.

Also note that technically, column of the withholding table should be one
less (i.e. 50, 197, 652, etc). However, usually marginal rate withholding
tables are designed so that it does not matter.

(I found one exception in a state withholding table some years ago. But I
am quite sure it was a mistake.)