A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Social Security Annual Payroll Tax Formula



 
 
Thread Tools Display Modes
  #1  
Old March 18th 07, 04:59 PM posted to microsoft.public.excel.misc
Toppers
external usenet poster
 
Posts: 4,340
Default Social Security Annual Payroll Tax Formula

=min(a1,94000)*0.0765

OR

=min(a1,94000)*B1

Where b1 is formatted as % with 7.65

"Jen" wrote:

> Hello-
>
> I am in need of some help, which may be as simple as an IF function, but I
> am currently in brain freeze mode because I am thinking about this one too
> hard.
>
> I am putting together a budget for a fictitious start-up company for class
> and need to account for any social security taxes paid from an employer
> perspective. The rate is 7.65%, however, I want this formula to take 7.65%
> of UP TO 94,000. Anything over the 94,000 is no longer taxed. So an
> employee that I have budgeted to make $120,000 for the year will only have
> SS taxes paid up to $94,000 of that $120,000 amount ($94,000 x 7.65% =
> $7,191).
>
> How would I write this formula in excel? I have 750 employees and
> approximately 25 make over the $94,000 limit.
>
> Thanks in advance-
> Jennifer
>
>
>

Ads
  #2  
Old March 18th 07, 05:08 PM posted to microsoft.public.excel.misc
Jen[_2_]
external usenet poster
 
Posts: 1
Default Social Security Annual Payroll Tax Formula

Hello-

I am in need of some help, which may be as simple as an IF function, but I
am currently in brain freeze mode because I am thinking about this one too
hard.

I am putting together a budget for a fictitious start-up company for class
and need to account for any social security taxes paid from an employer
perspective. The rate is 7.65%, however, I want this formula to take 7.65%
of UP TO 94,000. Anything over the 94,000 is no longer taxed. So an
employee that I have budgeted to make $120,000 for the year will only have
SS taxes paid up to $94,000 of that $120,000 amount ($94,000 x 7.65% =
$7,191).

How would I write this formula in excel? I have 750 employees and
approximately 25 make over the $94,000 limit.

Thanks in advance-
Jennifer


  #3  
Old March 18th 07, 05:17 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,911
Default Social Security Annual Payroll Tax Formula

One method.

=IF(A1>94000,94000*0.0765,A1*0.0765)


Gord Dibben MS Excel MVP


On Sun, 18 Mar 2007 12:08:30 -0500, "Jen" > wrote:

>Hello-
>
>I am in need of some help, which may be as simple as an IF function, but I
>am currently in brain freeze mode because I am thinking about this one too
>hard.
>
>I am putting together a budget for a fictitious start-up company for class
>and need to account for any social security taxes paid from an employer
>perspective. The rate is 7.65%, however, I want this formula to take 7.65%
>of UP TO 94,000. Anything over the 94,000 is no longer taxed. So an
>employee that I have budgeted to make $120,000 for the year will only have
>SS taxes paid up to $94,000 of that $120,000 amount ($94,000 x 7.65% =
>$7,191).
>
>How would I write this formula in excel? I have 750 employees and
>approximately 25 make over the $94,000 limit.
>
>Thanks in advance-
>Jennifer
>


  #4  
Old March 18th 07, 05:20 PM posted to microsoft.public.excel.misc
joeu2004
external usenet poster
 
Posts: 2,061
Default Social Security Annual Payroll Tax Formula

On Mar 18, 9:08 am, "Jen" > wrote:
> I am putting together a budget for a fictitious start-up company for class
> and need to account for any social security taxes paid from an employer
> perspective. The rate is 7.65%, however, I want this formula to take 7.65%
> of UP TO 94,000. [....] How would I write this formula in excel?


Well, if your facts were correct -- they are not, but perhaps those
are the facts that your instructor wants you to work with -- you could
write:

=round(7.65%*max(94000,A1), 2)

where A1 is the employee's cumulative wages subject to FICA tax. If
you want a formula that works for each pay period, you could write:

=if(A1 > 94000, 0, round(7.65%*A2, 2))

where A2 is the employee's periodic wages subject to FICA tax.

A couple of details that you may or may not want to take into account,
depending on the assignment....

1. At best, that is FICA tax from the __employee's__ perspective, not
the employer's. That is, it is the amount withheld from the
employee's wages. From the __employer's__ point of view, FICA is paid
at the rate of 15.3% -- applying the same over-simplification that you
are. Typically, this is accomplished by taking half from the
employee's wages and contributing half from the employer's cash.

2. Actually, FICA tax is composed of two taxes: Soc Sec and
Medicare. It is only Soc Sec that is limited. Medicare tax at 2.9%
(1.45% from the employee) is assessed on all wages subject to Medicare
tax. Soc Sec tax at 12.4% (6.2% from the employee) is assessed on the
first $94,200 (in 2006; or $97,500 in 2007) of each employee's wages
subject to Soc Sec tax.

Taking #2 into account, the total FICA tax is one of the following
(see the above choices), reverting to the incorrect Soc Sec limit that
you mentioned:

=round(1.45%*A1, 2) + round(6.2%*max(94000,A1), 2)

=round(1.45%*A2, 2) + if(A1 > 94000, 0, round(6.2%*A2, 2))

  #5  
Old March 18th 07, 05:21 PM posted to microsoft.public.excel.misc
joeu2004
external usenet poster
 
Posts: 2,061
Default Social Security Annual Payroll Tax Formula

Errata....

On Mar 18, 9:20 am, "joeu2004" > wrote:
> =round(7.65%*max(94000,A1), 2)
> [....]
> =round(1.45%*A1, 2) + round(6.2%*max(94000,A1), 2)


Arrgghh! Of course, that should MIN(...), not MAX(...).

  #6  
Old March 18th 07, 10:09 PM posted to microsoft.public.excel.misc
joeu2004
external usenet poster
 
Posts: 2,061
Default Social Security Annual Payroll Tax Formula

On Mar 18, 9:20 am, "joeu2004" > wrote:
> 1. At best, that is FICA tax from the __employee's__ perspective, not
> the employer's. That is, it is the amount withheld from the
> employee's wages. From the __employer's__ point of view, FICA is paid
> at the rate of 15.3% -- applying the same over-simplification that you
> are. Typically, this is accomplished by taking half from the
> employee's wages and contributing half from the employer's cash.


In email, Jennifer points out that my point above is a bit of a
nitpick. After all, isn't "half" of 15.3% simply 7.65%?
(Rhetorical.) Technically, not exactly, if only due to rounding the
employee's deduction to a penny. But I probably should not have
mentioned it all. It's a "hot button" for me recently because of an
issue I have been having with someone who wants to bend the IRS rules
by underwithholding the employee's FICA tax deduction. Much ado about
nothing. Mea culpa!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
last four digits of a social security number elli Excel Worksheet Functions 2 October 25th 05 09:01 PM
Social Security numbers Hernan New Users to Excel 5 August 11th 05 08:14 PM
Social Security Number montagu Excel Discussion (Misc queries) 3 June 27th 05 05:09 PM
social security numbers Jean Excel Worksheet Functions 4 March 7th 05 07:37 PM
social security sorting Precious Pearl Excel Worksheet Functions 4 January 25th 05 02:37 PM


All times are GMT +1. The time now is 09:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.