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

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

"Steve" wrote:

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

On Mar 3, 11:08 pm, Steve wrote:
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 -----

On Mar 3, 11:08*pm, Steve wrote:
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!!

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



On Mar 4, 10:48*am, joeu2004 wrote:
On Mar 3, 11:08 pm, Steve wrote:

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

On Mar 3, 11:08*pm, Steve wrote:



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 -

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

Thanks!


On Mar 4, 8:50*pm, Steve wrote:
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!!!

On Mar 4, 10:48*am, joeu2004 wrote:



On Mar 3, 11:08 pm, Steve wrote:

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

On Mar 3, 11:08*pm, Steve wrote:

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 -- Hide quoted text -

- Show quoted text -

On Mar 4, 7:50 pm, Steve wrote:
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 -----

On Mar 4, 7:50*pm, Steve wrote:
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!!!

On Mar 4, 10:48*am, joeu2004 wrote:



On Mar 3, 11:08 pm, Steve wrote:

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

On Mar 3, 11:08*pm, Steve wrote:

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

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!


On Mar 4, 9:36*pm, joeu2004 wrote:
On Mar 4, 7:50 pm, Steve wrote:

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

On Mar 4, 7:50*pm, Steve wrote:



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

On Mar 4, 10:48*am, joeu2004 wrote:

On Mar 3, 11:08 pm, Steve wrote:

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

On Mar 3, 11:08*pm, Steve wrote:

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 -

On Mar 4, 9:08 pm, Steve wrote:
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 -----

On Mar 4, 9:08*pm, Steve wrote:
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!

On Mar 4, 9:36*pm, joeu2004 wrote:



On Mar 4, 7:50 pm, Steve wrote:

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

On Mar 4, 7:50*pm, Steve wrote:

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

On Mar 4, 10:48*am, joeu2004 wrote:

On Mar 3, 11:08 pm, Steve wrote:

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

On Mar 3, 11:08*pm, Steve wrote:

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