how do I stop payroll tax deductions once FUTA limit is met?

[B-Cut]

what is the formula required to stop employee and employer paid taxes once a
tax limit criteria has been met? Ex. once 7000.00 in gross wages has been
met, you no longer have to take out FUTA.

It is easy to figure out the formula above and well below 7000.00, the
problem is that one pay period when they finally meet the 7000.00, but it
does not know what to do with the excess amount. Example. pay period 1 -
employee gross wages are 2000. Tax formula works fine. Pay perios 2,
another 2000 in wages, now YTD total is 4000. Still fine. Pay period 3,
another 2000, everything still fine because YTD is now 6000. But now on pay
period 4 - another 2000, but now it jumps to 8000 YTD. But my forumula does
not work, because he went over 7000, but it didn't know how to add just the
1000 difference to cap the FUTA at 7000.

anyone out there that knows what the formula would be, please help.

 

[Don Guillett]

Always nice to show us your formula. Take a look in the help index for MAX

 

[B-Cut]

Thanks Don, here are two different formula's that I have tried.

=IF(I24<=7000,B24," - ") This one works, but doesn't actually stop at
exactly 7000. It depends on close the prior YTD totals were to 7000.00 Ex:
if prior total was 6000.00, then when newest pay amount of 2000 gets, added,
it takes him to 8000, which of course then puts the value of " - " in
that filled, but leaves the cell stop at 6000 from that point on. I need to
figure out how to calculate the difference.

Then I tried this formula: =IF(I12<=7000,B12,IF(I12>7001,7000-K13))


This almost works, but the opposite thing happens. It does fill in the
difference the first time the criteria equals 7000, but then on the next pay
period when you would add another 2000 to YTD; it subtracts the difference
between YTD and 7000. Ex; if his YTD gross is now 10,000, then it subtracts
7000 and tries to calculate FUTA and other taxes on 3000 for that period.

I am thinking that a "MAX" command needs to be incorporated into the
formula, but I can't seem to figure out how to do it. I hope you can help.
THANKS

Rick

p.s. I have looked in the help index for MAX, but I am not getting it. They
show such poor examples.

 

[JoeU2004]

But my forumula does not work, because he went over 7000,
but it didn't know how to add just the 1000 difference to cap
the FUTA at 7000.

You could compute the limit based on the salary cap, $7000. I will show
that second. But I prefer to compute the limit based on the FUTA tax cap.
I will show that first.

By the way, to be sure your formula works, I would suggest that you change
your example to have quarterly wages of $3000, not $2000. Thus, the FUTA
salary cap is reached in the middle of period 3, and the FUTA tax for period
4 should be zero.

Suppose B1 is your FUTA tax rate -- typically 0.8%, but as much as 6.2% in
2009. And suppose C2 is the salary cap (7000), and B2 is the FUTA tax cap,
namely:

=round(C2*B1,2)

Suppose that the periodic wage is in column A starting with A4, and the FUTA
tax is in column B starting with B4. (Note: The following assumes that B3
is text or empty.) Then the periodic FUTA tax in B4 (and copied down the
column) is:

=ROUND(MIN(A4*$B$1, $B$2 - SUM($B$3:B3)), 2)

In other words, the FUTA tax for the current period is the lesser of the
gross wages times the FUTA tax rate and the FUTA tax cap less the sum of the
FUTA tax paid in previous periods.

Be careful to use the absolute and relative references exactly as shown.
That is key to permitting the formula to be copied down.

I would use the same paradigm for capping other taxes, e.g. Soc Sec.


If you wanted to base the limit on the salary cap instead, you might
consider the following paradigm:

=ROUND($B$1 * MIN($C$2, SUM($A$4:A4)) - SUM($B$3:B3), 2)


----- original message -----

 

[B-Cut]

Thanks Don, here are two different formula's that I have tried.

=IF(I24<=7000,B24," - ") This one works, but doesn't actually stop at
exactly 7000. It depends on close the prior YTD totals were to 7000.00 Ex:
if prior total was 6000.00, then when newest pay amount of 2000 gets, added,
it takes him to 8000, which of course then puts the value of " - " in
that filled, but leaves the cell stop at 6000 from that point on. I need to
figure out how to calculate the difference.

Then I tried this formula: =IF(I12<=7000,B12,IF(I12>7001,7000-K13))


This almost works, but the opposite thing happens. It does fill in the
difference the first time the criteria equals 7000, but then on the next pay
period when you would add another 2000 to YTD; it subtracts the difference
between YTD and 7000. Ex; if his YTD gross is now 10,000, then it subtracts
7000 and tries to calculate FUTA and other taxes on 3000 for that period.

I am thinking that a "MAX" command needs to be incorporated into the
formula, but I can't seem to figure out how to do it. I hope you can help.
THANKS

Rick

p.s. I have looked in the help index for MAX, but I am not getting it. They
show such poor examples.

Cell H Cell I Cell J Cell K

Futa
Net Pay YTD GROSS Date To $7000
0.00 0.00 1/15/2009 0.00
0.00 0.00 1/31/2009 0.00
1825.00 2000.00 2/15/2009 2,000.00
1825.00 4000.00 2/28/2009 2,000.00
1825.00 6000.00 3/15/2009 2,000.00
1825.00 8000.00 3/31/2009 0.00 <--- this cell S/B 1000.00
7300.00 First QTR. 6,000.00
7300.00 8000.00 YTD 6,000.00

 

[JoeU2004]

I am thinking that a "MAX" command needs to be incorporated into the
formula, but I can't seem to figure out how to do it.

Perhaps because, in part, it is MIN, not MAX, that you need. See my other
response for details.


----- original message -----

 

[B-Cut]

Thank Joe, I appreciate your help. Maybe we are talking about 2 different
problems. I think you are showing me how to calculate the FUTA tax
percentage. That part I understand, what I am trying to do is once the 7000
limit occurs, is stop the calucations, but more importantly, is have the cap
stop at exactly 7000. I know it is difficult to help someone without seeing
the entire spread sheet. It is up to you, but I have no problem sending it to
you. It only has sample data in it. So if you are willing I could email it to
you, but that is up to you. Just let me know. I know you would not want
your email address listed on this site, but if you want to send it to me at
the following addess, (e-mail address removed), I could then send it to you.
This is the last portion of the spread sheet I am trying to perfect, and it
is driving me nuts. I am using a FED/EDD (California) print form to base mine
on. I have basically replicated it cell by cell, and all my sample data adds
up exactly as theirs, with the expection of figuring out how to cap it at
7000. THANKS - RICK

 

[JoeU2004]

Thank Joe, I appreciate your help. Maybe we are talking about 2
different
problems. I think you are showing me how to calculate the FUTA tax
percentage. That part I understand, what I am trying to do is once the
7000
limit occurs, is stop the calucations, but more importantly, is have the
cap
stop at exactly 7000.

Actually, I intended to show you how to do the later. Perhaps you should
prototype the worksheet as I described in my paradigm. It's not that
difficult. Just put 3000 in each of A4:A7, and fill in the formulas that I
described. I think you would see that the formulas compute the tax, not the
tax percentage.

(FYI, there is no way that I could help you compute the tax percentage, if
by that you mean the FUTA tax rate. It is either 0.8% or 6.2% or something
in between based on the "credit against your FUTA tax for amounts that you
paid into state unemployment funds". Obviously I do have the latter
information.)

I know it is difficult to help someone without seeing the
entire spread sheet. It is up to you, but I have no problem
sending it to you.

If you still feel that is necessary, I'm okay with that. I'll send you
email.


----- original message -----

 

[JoeU2004]

Material typo....

(FYI, there is no way that I could help you compute the tax percentage
[...]. Obviously I do have the latter information.)

That is, I do __not__ have the necessary information. Oh that elusive word
"not". Aarrgghh!


----- original message -----

 

[cm]

Joe,

The total FUTA rate is 6.2%, however you rarely end up payiing that on the
annual FUTA return. The correct deposit rate is 0.8%. (you realize of course
that you do not deduct this from employee's paychecks - it is an
employer-paid tax)

cm

Material typo....

(FYI, there is no way that I could help you compute the tax percentage
[...]. Obviously I do have the latter information.)

That is, I do __not__ have the necessary information. Oh that elusive word
"not". Aarrgghh!


----- original message -----

Actually, I intended to show you how to do the later. Perhaps you should
prototype the worksheet as I described in my paradigm. It's not that
difficult. Just put 3000 in each of A4:A7, and fill in the formulas that
I described. I think you would see that the formulas compute the tax, not
the tax percentage.

(FYI, there is no way that I could help you compute the tax percentage, if
by that you mean the FUTA tax rate. It is either 0.8% or 6.2% or
something in between based on the "credit against your FUTA tax for
amounts that you paid into state unemployment funds". Obviously I do have
the latter information.)



If you still feel that is necessary, I'm okay with that. I'll send you
email.


----- original message -----

 

[JoeU2004]

The total FUTA rate is 6.2%, however you rarely end up payiing
that on the annual FUTA return. The correct deposit rate is 0.8%.

This is a response to a month-old thread. I resolved OP's problem in
private email. As it turns out, the OP was trying to reverse-engineer the
numerical examples in a slide that had been handed out at a payroll
workshop. They did indeed simply use the 0.8% FUTA tax rate.

As for CM's comment, the devil is in the details -- which is really what I
intended to convey in my comment which CM is responding to.

The OP was not clear about the context. I think my response is appropriate
if the OP was talking about accrual accounting. Arguably, CM's comment
seems correct if the OP was talking about quarterly FUTA deposits.

But actually, I think CM's comment is overly simplistic even for quarterly
deposits.

Both IRS Pub 15 and, surprisingly, the instructions for Form 940 (__annual__
FUTA return) note that the quarterly FUTA deposit tax rate is higher when
applied to employee wages that are exempt from state unemployment tax. See
the example in the Form 940 instructions.

Nonetheless, I would uncategorically agree with CM's subjective assessment
that it is "rare" for the applicable FUTA tax rate not to be 0.8%.

(Hint: Look up the word "categorical". It might surprise you. ;->)


----- original message -----

Joe,

The total FUTA rate is 6.2%, however you rarely end up payiing that on the
annual FUTA return. The correct deposit rate is 0.8%. (you realize of
course
that you do not deduct this from employee's paychecks - it is an
employer-paid tax)

cm

Material typo....

(FYI, there is no way that I could help you compute the tax percentage
[...]. Obviously I do have the latter information.)

That is, I do __not__ have the necessary information. Oh that elusive
word
"not". Aarrgghh!


----- original message -----

Thank Joe, I appreciate your help. Maybe we are talking about 2
different
problems. I think you are showing me how to calculate the FUTA tax
percentage. That part I understand, what I am trying to do is once the
7000
limit occurs, is stop the calucations, but more importantly, is have
the
cap
stop at exactly 7000.

Actually, I intended to show you how to do the later. Perhaps you
should
prototype the worksheet as I described in my paradigm. It's not that
difficult. Just put 3000 in each of A4:A7, and fill in the formulas
that
I described. I think you would see that the formulas compute the tax,
not
the tax percentage.

(FYI, there is no way that I could help you compute the tax percentage,
if
by that you mean the FUTA tax rate. It is either 0.8% or 6.2% or
something in between based on the "credit against your FUTA tax for
amounts that you paid into state unemployment funds". Obviously I do
have
the latter information.)


I know it is difficult to help someone without seeing the
entire spread sheet. It is up to you, but I have no problem
sending it to you.

If you still feel that is necessary, I'm okay with that. I'll send you
email.


----- original message -----

Thank Joe, I appreciate your help. Maybe we are talking about 2
different
problems. I think you are showing me how to calculate the FUTA tax
percentage. That part I understand, what I am trying to do is once the
7000
limit occurs, is stop the calucations, but more importantly, is have
the
cap
stop at exactly 7000. I know it is difficult to help someone without
seeing
the entire spread sheet. It is up to you, but I have no problem
sending
it to
you. It only has sample data in it. So if you are willing I could
email
it to
you, but that is up to you. Just let me know. I know you would not
want
your email address listed on this site, but if you want to send it to
me
at
the following addess, (e-mail address removed), I could then send it to
you.
This is the last portion of the spread sheet I am trying to perfect,
and
it
is driving me nuts. I am using a FED/EDD (California) print form to
base
mine
on. I have basically replicated it cell by cell, and all my sample
data
adds
up exactly as theirs, with the expection of figuring out how to cap it
at
7000. THANKS - RICK

:

But my forumula does not work, because he went over 7000,
but it didn't know how to add just the 1000 difference to cap
the FUTA at 7000.

You could compute the limit based on the salary cap, $7000. I will
show
that second. But I prefer to compute the limit based on the FUTA tax
cap.
I will show that first.

By the way, to be sure your formula works, I would suggest that you
change
your example to have quarterly wages of $3000, not $2000. Thus, the
FUTA
salary cap is reached in the middle of period 3, and the FUTA tax for
period
4 should be zero.

Suppose B1 is your FUTA tax rate -- typically 0.8%, but as much as
6.2%
in
2009. And suppose C2 is the salary cap (7000), and B2 is the FUTA
tax
cap,
namely:

=round(C2*B1,2)

Suppose that the periodic wage is in column A starting with A4, and
the
FUTA
tax is in column B starting with B4. (Note: The following assumes
that
B3
is text or empty.) Then the periodic FUTA tax in B4 (and copied down
the
column) is:

=ROUND(MIN(A4*$B$1, $B$2 - SUM($B$3:B3)), 2)

In other words, the FUTA tax for the current period is the lesser of
the
gross wages times the FUTA tax rate and the FUTA tax cap less the sum
of
the
FUTA tax paid in previous periods.

Be careful to use the absolute and relative references exactly as
shown.
That is key to permitting the formula to be copied down.

I would use the same paradigm for capping other taxes, e.g. Soc Sec.


If you wanted to base the limit on the salary cap instead, you might
consider the following paradigm:

=ROUND($B$1 * MIN($C$2, SUM($A$4:A4)) - SUM($B$3:B3), 2)


----- original message -----

what is the formula required to stop employee and employer paid
taxes
once
a
tax limit criteria has been met? Ex. once 7000.00 in gross wages
has
been
met, you no longer have to take out FUTA.

It is easy to figure out the formula above and well below 7000.00,
the
problem is that one pay period when they finally meet the 7000.00,
but
it
does not know what to do with the excess amount. Example. pay
period
1 -
employee gross wages are 2000. Tax formula works fine. Pay
perios
2,
another 2000 in wages, now YTD total is 4000. Still fine. Pay
period
3,
another 2000, everything still fine because YTD is now 6000. But
now
on
pay
period 4 - another 2000, but now it jumps to 8000 YTD. But my
forumula
does
not work, because he went over 7000, but it didn't know how to add
just
the
1000 difference to cap the FUTA at 7000.

anyone out there that knows what the formula would be, please help.