Ah, I see what you changes in the last two ranges! So to answer you
question:
It was intended to calculate the monthly Soc Sec tax. I assume that
is what you want.
Yes, I do want it to calculate the monthly Soc Sec tax. The formula
you provided does that, but on a cumulative basis. For example, the
results look like this:
1/1/09 2/1/09 3/1/09 4/1/09 5/1/09 6/1/09 7/1/09 8/1/09 9/1/09 10/1/09
11/1/09 12/1/09
930 1,860 2,790 3,720 4,650 5,580 6,510 6,696 6,696
6,696 6,696 6,696
I was hoping for the formula to produce the incremental tax due each
month...something like this:
1/1/09 2/1/09 3/1/09 4/1/09 5/1/09 6/1/09 7/1/09 8/1/09 9/1/09 10/1/09
11/1/09 12/1/09
930 930 930 930 930 930 930 186 - - -
-
Thanks!
One final pain in the rump question - the Social
Security Tax that is calculated is on a cumulative
basis. Is there a way to make it incremental?
It was intended to calculate the monthly Soc Sec tax. I assume that
is what you want.
But I introduced a defect when I modified the formula. Does thisone
work?
=round(6.2% *
(min(vlookup(year(B1),$BB$1:$BC$5,2,0),
sumproduct((year(B1)=year($B$1:B1))*$B$3:B3)) -
min(vlookup(year(B1),$BB$1:$BC$5,2,0),
sumproduct((year(B1)=year(n($A$1:A1)))*n($A$3:A3)))),2)
The correction is in the last two ranges.
----- original posting -----
Sweet! Thanks!!
One final pain in the rump question - the Social Security Tax that is
calculated is on a cumulative basis. Is there a way to make it
incremental? I did the subtraction month to month to calc an
incremental tax, but the formula gives bad results in January if an
employee reaches his max in a given year, and therefore Dec was zero.
Again, much appreciated!
Beginning with B1, I have dates by month (1/1/09, 2/1/09,3/1/09...)
Beginning with B3, I have monthly income subject to Social Security
tax.
So based on that, how do I adjust your formula? I did add years
(2009...2013) in BB1:BB5 and the Social Security Limits in BC1:BC5
Try (untested):
=round(6.2% *
(min(vlookup(year(B1),$BB$1:$BC$5,2,0),
sumproduct((year(B1)=year($B$1:B1))*$B$3:B3)) -
min(vlookup(year(B1),$BB$1:$BC$5,2,0),
sumproduct((year(B1)=year(n($A$1:B1)))*n($A$3:B3)))),2)
----- original posting -----
Thanks so much for the response! To answer your first question, YES
this is only income subject to Social Securety tax. After I look at
my original post, I realized I really messed up how the data is
organized. So:
Beginning with B1, I have dates by month (1/1/09, 2/1/09,3/1/09...)
Beginning with B3, I have monthly income subject to Social Security
tax.
So based on that, how do I adjust your formula? I did add years
(2009...2013) in BB1:BB5 and the Social Security Limits in BC1:BC5
Thanks again for your help!!!
Beginning with A2 I have the dates by month [....]
Beginning with B2 I have the monthly incvome of an employee
I assume you mean that B2 contains the monthly income subject to Soc
Sec tax. Not all wage income is.
Does anyone have a formula that will calc the tax based
on 6.2% with a cap of $108,000 (the actual TAX cap is
6.2% x 108,000) that I can copy across all years, keeping
in mind the cumulative tax paid starts over beginning with
the new calendar year
Try (untested):
=round(6.2% *
(min(vlookup(year(A2),$Y$1:$Z$5,2,0),
sumproduct((year(A2)=year($A$2:A2))*$B$2:B2))-
min(vlookup(year(A2),$Y$1:$Z$5,2,0),
sumproduct((year(A2)=year(N($A$1:A1)))*N($B$1:B1)))),2)
where Y1:Y5 contains each year (2009, 2010 etc) and Z1:Z5 contains the
Soc Security income limit for each year (e.g. 106,800 for 2009).
That says: the Soc Sec tax this month is 6.2% of the difference
between year-to-date income up to the cap and the previous-month year-
to-date income up to the cap.
Pay close attention to absolute and relative references. For example,
$A$1:A1 will change to $A$1:A2, $A$1:A3 etc as you copy theformula
down.
The formula assumes that A1 and B1 are empty, or they contain text
(e.g. column titles).
PS: Technically, it would be prudent to look up the Soc Sec tax rate
(6.2%) with VLOOKUP. It has not change in oh-so-many years. But it
might change in the near future.
----- original posting -----
Hi all. I am putting together a financial model that spans over 5
years by month. I am tyring to create aFICAformula that will
calculateFICAtax by month based on salary paid to an individual.
Beginning with A2 I have the dates by month (1/1/09, 2/1/09,
3/1/09...)
Beginning with B2 I have the monthly incvome of an employee
Does anyone have a formula that will calc the tax based on 6.2% with a
cap of $108,000 (the actual TAX cap is 6.2% x 108,000) that I can copy
across all years, keeping in mind the cumulative tax paidstarts over
beginning with the new calendar year whether or not the the max has
been reached?
Thanks!!- Hide quoted text -