Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Social Security Annual Payroll Tax Formula
One method.
=IF(A194000,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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
last four digits of a social security number | Excel Worksheet Functions | |||
Social Security numbers | New Users to Excel | |||
Social Security Number | Excel Discussion (Misc queries) | |||
social security numbers | Excel Worksheet Functions | |||
social security sorting | Excel Worksheet Functions |