Terri said:
In need a formula that will figure out someon'es federal taxwithholding on a
person weekly payroll:
If you are referring to US federal income tax withholding, see IRS Pub
15 at
http://www.irs.gov/pub/irs-pdf/p15.pdf , notably the Percentage
Method on pages 34-36.
There are many ways to formulate this in Excel. One way is to create
the following table (assuming Married status):
X1:X7 is {0, 154.01, 440.01, 1308.01, 2440.01, 3759.01, 6607.01}
Y1:Y7 is {0, 0, 28.60, 158.80, 441.80, 811.12, 1750.96}
Z1:Z7 is {0, 0.10, 0.15, 0.25, 0.28, 0.33, 0.35}
Then E4 might be the required federal income tax withholding, computed
as follows:
=round(vlookup(B4, X1:Z7, 2) + vlookup(B4, X1:Z7, 3)*(B4 -
int(vlookup(B4, X1:Z7, 1))), 2)
Alternatively, E4 might be computed as follows:
=round(max(0, 10%*(B4-154), 15%*(B4-440)+28.60, 25%*(B4-1308)+158.80,
28%*(B4-2440)+441.80, 33%*(B4-3759)+811.12, 35%*(B4-6607)+1750.96), 2)
Change round(...,2) to round(...,0) if you want to round to dollars,
which is permitted and common for federal withholding, but not
required.
Example:
B4 is total wages per week (with overtime included), C4 is % of what they
should pay and D4 is the extra amount they should take out for there
exemptions.any help would be appriciated.
C4 is not useful, as defined. The US federal withholding tables are
based on marginal rates that depend on the amount subject to
withholding, not a fixed rate. Hence the vlookup() or the max()
formulation.
C4 could be used to contain an additional fixed amount to withhold, at
the employee's discretion. Some, but not all, employers permit that.
See the computation of F4 below.
D4 should be the number of allowances, not a fixed amount. Note that
this represents an amount __less__ to be taken out, not an amount more
to be taken out.
In the formulations above, B4 needs to be the "amount subject to
withholding". If A4 is the "taxable gross pay" (total wages less
pretax deductions such as 401(k) contribution), then B4 might be
computed as follows:
=A4 - B4*64.46
In the formulations above, E4 is the required federal income tax
withholding. If C4 contains an additional amount to withhold, at the
employee's discretion, F4 might be the total income tax withholding,
computed as follows:
=E4 + min(F4, A4-E4)
Finally, it should be noted this computes only the US federal income
tax withholding. There are other federal tax amounts to withhold based
on compensation, notably FICA (Social Security and Medicare).
Moreover, some states require additional amounts to be withheld (paid
to the state) based on compensation, both income tax and state "social
insurance" taxes (e.g. SDI and VDI [VP] in Calif).
Probably not the simple solution you were looking for. Hope it helps.
