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.