Payroll tax deductions

  • Thread starter Thread starter jgoblish
  • Start date Start date
J

jgoblish

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. Or is this too
much to program?
 
It's very easy to set up. You just need a lookup table. Your table would
have two columns: wages and tax rate. The entry for wages in each row would
be the amount where the rate changes, and the tax rate for that amount. Then
use Vlookup to get the correct tax rate.

After you've check out Vlookup in Help, post back if you need more help.

Regards,
Fred
 
jgoblish said:
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.
Click to expand...

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.)
 
Errata....

=round( (D2 - vlookup(D2, choose(C2, Weekly, Biweekly, SemiMonthly, Monthly,
Quarterly, Semiannually, Annually, Daily), 1)
Click to expand...

There is a missing right parenthesis. It should be:

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

Also note that technically, column of the withholding table should be one
less (i.e. 50, 197, 652, etc).
Click to expand...

I meant to write: "... column 1 of the withholding table ...".
 
Back
Top