Payroll calcualtions - nested IF?

**bmz** · 02-12-2010, 05:04 PM

I've been stuck on this excel problem for a bit now and could use some help.

In the worksheet I'm trying to keep an accurate track of all payroll expenses by employee, by month, by location. I've got the root information all figured out but am stuck on the formula for figuring out Unemployment and L&I costs.

Unemployment is .57% for the first $35700 of a persons wages.

L&I is .3474% per hour worked for Yale, .3445 per hour worked for Mercer

Here is the formula I have thus far for the unemployment field - =IF(AND(B2="m",C2:N2<35700),C2*B16,0) - but this only totals for one employee - not all of them who worked at Mercer for January- as well as will only work if they make less than $35700 not if they made more.

Any help or insight anyone can offer would be greatly appreciated.

**zbor** · 02-12-2010, 05:24 PM

Is this it?

**bmz** · 02-12-2010, 05:45 PM

Close - the only part that is missing is that we should only be calculating the first $37500 of John's (or anyone who makes more than $37500) and not anything over that.

Your help is much appreciated.

**zbor** · 02-13-2010, 04:59 AM

How about this?

**DonkeyOte** · 02-13-2010, 05:12 AM

Might help if you outlined the expected results (calculated manually)

Is the Unemployment cost front loaded ?
eg: 0.57% of monthly salary up to a cap of cumulative earnings of 37.5K

or, is the 0.57% applied to the lesser of 37.5K / annual salary and apportioned over months employed ?

I'm guessing the former but given no expected results outlined it's hard to be sure.

On an aside, I was always under the impression that FICA was both capped and calculated on an individual basis ?

The maximum FICA tax for the year 2010 remains the same as the 2009 FICA tax: 7.65% on each employee’s first $106,800 of taxable earnings (such as wages, salaries, bonuses, etc.) plus 1.45% of any employee’s taxable earnings in excess of $106,800.
The FICA tax is withheld from each employee’s earnings and it is also matched by the employer. This means that the employer is required to remit 15.3% (employee’s 7.65% + employer’s 7.65%) of each employee’s first $106,800 of earnings in 2010 plus 2.9% (employee’s 1.45% + employer’s 1.45%) of any employee’s earnings greater than $106,800.
The FICA tax is really two taxes:
1. The Social Security tax of 6.2% which applies only to the first $106,800 of an employee’s taxable earnings (in year 2010 and in year 2009), and
2. The Medicare tax of 1.45% on every dollar of every employee’s taxable earnings.

taken from: http://blog.accountingcoach.com/maximum-fica-tax-2010/

(your file implies 2009 but I think 2009 and 2010 limits were the same)

Obviously the above may not be what you're doing at all... just wanted to check

**bmz** · 02-15-2010, 01:08 PM

Still not quite it. The result for total Unemployment costs for Mercer should be $487.35.

The calculation should be .57% of a persons salary costed each month up to $37500. So if a person never reaches 37.5k then the cost is .57% per month of their gross payroll. However, as soon as am employee goes over 37.5k there is no more due so the maximum for any one employee would be $213.75 per year but is broken down by month.

there is a cap for FICA - you are correct - but we don't have any people in our business earning over 100k. And while 09' and 10' were the same for FICA they are dramatically different for Unemployment and L&I.

I really, really appreciate everyones help on this and I'm open to any suggestions.