formula for taxes

[Terri]

Hello:
In need a formula that will figure out someon'es federal taxwithholding on a
person weekly payroll:
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.

 

[JLatham]

Assuming you have the values for % to deduct in C4 and the extra in D4 then
this formula in E4 will give you the amount to withhold:
=B4*C4+D4
then to calculate what is left over, in F4
=B4-E4
of course there's more to take out, state taxes, etc.

Type the formulas in just as I've shown them, including the = symbol at the
beginning.

 

[joeu2004]

In need a formula that will figure out someon'es federal taxwithholding on a
person weekly payroll:
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.

Sigh, I do not see the lengthy (and informative, I hope) response that
I posted 6-7 hours ago. Of course, these things happen; just have to
wait and see. Rather than recreate the entire response, I will simply
ask this critical question....

US federal withholding does not work like you describe. You do not
withhold a fixed percentage; and the "exemption" is not an "extra
amount to take out", but an amount __less__ to withhold -- that is, a
reduction of withholding.

Are you asking about __US__ federal income taxes, or are you asking
about another country's "federal" taxes?

 

[joeu2004]

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.

 

[joeu2004]

Errata....

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)

That should be min(C4,...).

 

[JLatham]

If anyone wants those tables in Excel form, they could contact me via email:
(e-mail address removed) - I've already got them from the latest copy of
Publication 15. Same for the 2005 tax tables used with Form 1040, and I'll
be updating those next year.

As a matter of fact, heres a file where I started working with someone to
try to build up a home-made payroll calculating Excel app but we didn't
finish it, mostly because neither of us had the tax-law or CPA knowledge
needed to make sure that income/deductions were handled properly in it.
Things like 401K and Cafeteria Plans - so the project was dropped, but it
does make use of the Publication 15 tables (percentage method) and would show
how to get info out of them based on gross wages, and does so based upon the
payroll periods - tables in there for weekly, bi-weekly, monthly, etc. pay
periods.

http://www.jlathamsite.com/uploads/A_StartAt_DoingPayroll.xls

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.

 

[joeu2004]

As a matter of fact, heres a file where I started working with someone to
try to build up a home-made payroll calculating Excel app
[....]
http://www.jlathamsite.com/uploads/A_StartAt_DoingPayroll.xls

It appears that the Soc Sec computation fails to take the wage cap into
account. That changes every year. It is $94,600 for 2006. The cap
applies to Soc Sec, but not Medicare.

Also, the term "FICA" is used incorrectly. Apparently, the column is
intended to capture the required income tax withholding amount. But
FICA is defined as the sum of the Soc Sec and Medicare withholding.
See page 16 in the 2006 IRS Pub 15.

Finally, in your tax tables, you choose to make the "low limit" of one
row equal to the "hi limit" of the previous row. Although that works
for the federal tax tables, I chose to add $0.01 to the "lower limit"
in order to guarantee that the vlookup finds the correct row. Vlookup
matches the largest value "less than or equal to" the lookup value.
But in tax tables, the "lower limit" for a row really specifies an
amount that the lookup value should be "greater than", not equal to.
Normally, it does not matter because we expect that the largest amount
of tax computed in one row equals the smallest amount of tax computed
in the next row, if the same taxable wage amount is applied. But I
discovered one state's tax tables (Georgia) where that is not true.
(Surprise!)

(I suspect it is an error in the Georgia tables. But the error is not
consistent, and it is not a simple off-by-one numerical error.)

it does make use of the Publication 15 tables (percentage method) and would
show how to get info out of them based on gross wages, and does so based
upon the payroll periods

..... And based on filing status. That it does.

 

[JLatham]

Now you know why we abandonded the project! Good of you to point out those
shortcomings in it from a more experienced viewpoint. This is the kind of
expertise that we did not have available when we got into the middle of it.
The person I was helping out with it eventually decided to go with a
commercial payroll service. There was no loss of anything to anyone - he and
I are both members of another site and were just toying with the idea to see
if he could escape from the added cost of the commercial service. We finally
decided that going the commercial way was safest for the 'security' of his
payrolling.

In defense of using the last Hi Limit as the next Low Limit, I just did it
the way that the IRS did in the pub. Realizing exactly what you're speaking
of about VLOOKUP function.

But even with some minor alterations like that that might be needed to get
desired functionality, at least somone would have a basis for the tables as
opposed to having to create them from scratch.

I actually wasn't putting the file up for its "EmployeePayroll" sheet, but
for the Pub 15 tables - I realize how poorly implemented the technical side
of the EmployeePayroll sheet was - as you've accurately pointed out. Like I
said, I'm no tax guru and certainly not a CPA.

As a matter of fact, heres a file where I started working with someone to
try to build up a home-made payroll calculating Excel app
[....]
http://www.jlathamsite.com/uploads/A_StartAt_DoingPayroll.xls

It appears that the Soc Sec computation fails to take the wage cap into
account. That changes every year. It is $94,600 for 2006. The cap
applies to Soc Sec, but not Medicare.

Also, the term "FICA" is used incorrectly. Apparently, the column is
intended to capture the required income tax withholding amount. But
FICA is defined as the sum of the Soc Sec and Medicare withholding.
See page 16 in the 2006 IRS Pub 15.

Finally, in your tax tables, you choose to make the "low limit" of one
row equal to the "hi limit" of the previous row. Although that works
for the federal tax tables, I chose to add $0.01 to the "lower limit"
in order to guarantee that the vlookup finds the correct row. Vlookup
matches the largest value "less than or equal to" the lookup value.
But in tax tables, the "lower limit" for a row really specifies an
amount that the lookup value should be "greater than", not equal to.
Normally, it does not matter because we expect that the largest amount
of tax computed in one row equals the smallest amount of tax computed
in the next row, if the same taxable wage amount is applied. But I
discovered one state's tax tables (Georgia) where that is not true.
(Surprise!)

(I suspect it is an error in the Georgia tables. But the error is not
consistent, and it is not a simple off-by-one numerical error.)

it does make use of the Publication 15 tables (percentage method) and would
show how to get info out of them based on gross wages, and does so based
upon the payroll periods

..... And based on filing status. That it does.