FICA Formula

[Steve]

Hi all. I am putting together a financial model that spans over 5
years by month. I am tyring to create a FICA formula that will
calculate FICA tax 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 paid starts over
beginning with the new calendar year whether or not the the max has
been reached?

Thanks!!

 

[Joel]

How do you seperate YEARS? You only show 3 months for one year.

 

[joeu2004]

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 the formula
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 -----

 

[Steve]

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 the formula
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 a FICA formula that will
calculate FICA tax 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 paid starts over
beginning with the new calendar year whether or not the the max has
been reached?

Thanks!!- Hide quoted text -

- Show quoted text -

 

[Steve]

One more thing to note from my last post...the dates and income
amounts go ACROSS the columns rather than down the rows.

Thanks!

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.

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 the formula
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 -----

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[joeu2004]

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.

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 the formula
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 -----

 

[Steve]

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 the formula
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.  Butit
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 a FICA formula that will
calculate FICA tax 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 paid starts over
beginning with the new calendar year whether or not the the max has
been reached?
Thanks!!- Hide quoted text -

- Show quoted text -

 

[joeu2004]

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 this one
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!

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 the formula
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 a FICA formula that will
calculate FICA tax 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 cancopy
across all years, keeping in mind the cumulative tax paid starts over
beginning with the new calendar year whether or not the the max has
been reached?
Thanks!!

 

[Steve]

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 this one
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 lookat
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 the formula
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 paid starts over
beginning with the new calendar year whether or not the the maxhas
been reached?
Thanks!!- Hide quoted text -

- Show quoted text -

 

[joeu2004]

For example, the results look like this:
[....]
930 1,860 2,790 3,720 4,650 5,580 [....]

I was hoping for the formula to produce the incremental
tax due each month...something like this:
[....]
930 930 930 930 930 930 [....]

Yes, the latter is what I, too, was expecting. You fell victim to my
failure to test the formula. (At least I had said it was untested.)
The following works as you (and I) expect:

=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($A$1:A1))*$A$3:A3))),2)

The change is the removal of the N() function in the last two terms.
This means that A1 and A3 must be empty. The N() function was needed
to permit A1 and A3 to contain titles. But because of the N()
function, the last two terms failed to generate arrays for SUMPRODUCT
to iterate over. In effect, the second SUMPRODUCT always returned
zero, which is why the formula returned cumulative instead of
incremental figures.

If A1 and A3 must contain titles, I believe we must resort to an array
formula. That's okay; I just try to avoid them when I can.

Let me know if A1 and A3 cannot be empty.


----- original posting -----

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!

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 this one
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.  Forexample,
$A$1:A1 will change to $A$1:A2, $A$1:A3 etc as you copy the formula
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 spansover 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 Ican copy
across all years, keeping in mind the cumulative tax paid starts over
beginning with the new calendar year whether or not the the max has
been reached?
Thanks!!

 

[joeu2004]

PS....

Let me know if A1 and A3 cannot be empty.

Let me rephrase. The formula requires empty cells (no formula or
value) before B1 and B3.

If that is not normally the case, insert a column before column B. If
you do not want to see that empty column in your worksheet, hide the
added column.

If you insert a column, your data will then start in column C. That
will require adjustments to the formula, to wit:

=ROUND(6.2% *
(MIN(VLOOKUP(YEAR(C1),$BB$1:$BC$5,2,0),
SUMPRODUCT((YEAR(C1)=YEAR($C$1:C1))*$C$3:C3)) -
MIN(VLOOKUP(YEAR(C1),$BB$1:$BC$5,2,0),
SUMPRODUCT((YEAR(C1)=YEAR($B$1:B1))*$B$3:B3))),2)


----- original posting -----

For example, the results look like this:
[....]
 930     1,860   2,790   3,720   4,650   5,580 [....]

I was hoping for the formula to produce the incremental
tax due each month...something like this:
[....]
 930     930     930     930     930     930     [....]

Yes, the latter is what I, too, was expecting.  You fell victim to my
failure to test the formula.  (At least I had said it was untested.)
The following works as you (and I) expect:

=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($A$1:A1))*$A$3:A3))),2)

The change is the removal of the N() function in the last two terms.
This means that A1 and A3 must be empty.  The N() function was needed
to permit A1 and A3 to contain titles.  But because of the N()
function, the last two terms failed to generate arrays for SUMPRODUCT
to iterate over.  In effect, the second SUMPRODUCT always returned
zero, which is why the formula returned cumulative instead of
incremental figures.

If A1 and A3 must contain titles, I believe we must resort to an array
formula.  That's okay; I just try to avoid them when I can.

Let me know if A1 and A3 cannot be empty.

----- original posting -----

Ah, I see what you changes in the last two ranges!  So to answer you
question:

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 this one
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 subjectto 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 the formula
down.
The formula assumes that A1 and B1 are empty, or they containtext
(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) thatI can copy
across all years, keeping in mind the cumulative tax paid starts over
beginning with the new calendar year whether or not the themax has
been reached?
Thanks!!

 

[Steve]

For the sake of avoiding array formulas, I am COMPLETE in agreement
with adding a blank column!! And the formula worked like a charm!!!
You're a gentleman and a scholar! Thank you so much for your
continued support...it is greatly appreciated!!

PS....

Let me know if A1 and A3 cannot be empty.

Let me rephrase.  The formula requires empty cells (no formula or
value) before B1 and B3.

If that is not normally the case, insert a column before column B.  If
you do not want to see that empty column in your worksheet, hide the
added column.

If you insert a column, your data will then start in column C.  That
will require adjustments to the formula, to wit:

=ROUND(6.2% *
 (MIN(VLOOKUP(YEAR(C1),$BB$1:$BC$5,2,0),
      SUMPRODUCT((YEAR(C1)=YEAR($C$1:C1))*$C$3:C3)) -
  MIN(VLOOKUP(YEAR(C1),$BB$1:$BC$5,2,0),
      SUMPRODUCT((YEAR(C1)=YEAR($B$1:B1))*$B$3:B3))),2)

----- original posting -----

For example, the results look like this:
[....]
 930     1,860   2,790   3,720   4,650   5,580 [....]
I was hoping for the formula to produce the incremental
tax due each month...something like this:
[....]
 930     930     930     930     930     930     [....]

Yes, the latter is what I, too, was expecting.  You fell victim to my
failure to test the formula.  (At least I had said it was untested.)
The following works as you (and I) expect:

=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($A$1:A1))*$A$3:A3))),2)

The change is the removal of the N() function in the last two terms.
This means that A1 and A3 must be empty.  The N() function was needed
to permit A1 and A3 to contain titles.  But because of the N()
function, the last two terms failed to generate arrays for SUMPRODUCT
to iterate over.  In effect, the second SUMPRODUCT always returned
zero, which is why the formula returned cumulative instead of
incremental figures.

If A1 and A3 must contain titles, I believe we must resort to an array
formula.  That's okay; I just try to avoid them when I can.

Let me know if A1 and A3 cannot be empty.

----- original posting -----

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 -

- Show quoted text -