401K matching formula

[daddybird]

Okay - I'm fried and this one's confusing the heck out of me. It's for determining the company matching amount of an employees 401K defferal amount.

The legalese says this about calculating the company match:

(Employer shall match 100% of each participants deferral contribution, not exceeding 3% of the participants compensation, plus 50% of each participants deferral contributions in excess of 3%, but not in excess of 5% of the participants compensation).

So, I don't easily have access to the participants compensation number, but I do have the participants deferral percentage and deferral amount.

Please help me calculate this so I can save my job and my sanity.

 

[Gerald Higgins]

Hi daddybird - alot of people on this board are not in the USA, and so don't know exactly what a 401k is - myself included.

From what you say, it looks as if the calculation is do-able in theory, but if you don't have the "participants compensation number" (by this I assume you mean their salary, right ?) then I don't see how you can work all of this out, as some of it seems to depend on that.

 

[daddybird]

Apologies to our international readers

A 401K is a US government sponsored tax provision so that employees can "defer" amounts of their salary into a special investment savings account with tax benefits, and receive the money when they reach retirement age.

The employer gets tax benefits to "match" a portion of those "deferrals", using the guidelines above so confusingly written by a lawyer I'm sure .

So, each employee can elect a percentage of their salary to "defer" into this account, I need to calculate the employer's matching portion.

Does that help?

Since I know the employee's deferral percentage and the employees deferral amount, perhaps I can reverse engineer their salary for that portion of the data?

 

[Gerald Higgins]

Ah, sorry, yes indeed you should be able to "reverse engineer" their salary.

Assuming employee's deferral percentage is in A1, and employee's deferral amount is in B1, salary is found by

=b1/a1

 

[daddybird]

Thanks, Gerald. I got that part down LOL.

What I'm confused about is the wording of determining the employer match:

(Employer shall match 100% of each participants deferral contribution, not exceeding 3% of the participants compensation, plus 50% of each participants deferral contributions in excess of 3%, but not in excess of 5% of the participants compensation).

 

[Gerald Higgins]

deleted

 

[Gerald Higgins]

OK. So, let's say all of the figures are in their own cell somewhere.

You want the employer match contribution.
Let's say participant's deferral contribution is in A1
Participant's compensation is in B1
For the sake of simplicity, let's work out the 3% level and put it in C1, with this formula

=B1*.03

and let's do the same for the 5% level, and put it in D1, with this formula

=B1*.05

Contribution is (I think) given by this formula

=if(a1<=c1,a1,c1+((d1-c1)/2))

Note - I haven't been able to check this.

 

[daddybird]

Thanks, Gerald. I'm at the end of my day wit wise so I'll give it a try in the morning and post a reply.

Thanks again!


Ian

 

[gwkenny]

Can't get more correct than Gerald's formula.

g-
gwkenny@yahoo.com
___________________________________

I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

 

[JuliaLeighF]

I just had the exact same issue with the exact same 401(k) match legalese. Gerald's formula worked for all my employees who defer either less than 3% of their income, or more than 5%, but not those employees in the 3-5% range, and there just so happens to be one such employee.

So, this is what I did. I hope it's right.

Where the participant's deferral contribution is in A1 and the participant's salary is in B1...

I start by calculating 3% of salary by entering into C1:

=B1*.03

And I calculate 5% of salary by entering into D1:

=B1*.05

And use this logical formula to come up with my match:

"=IF(A1<=C1,C1,IF(A1<D1,C1+(A1-C1)/2,C1+(D1-C1)/2))"

<d1,c1+(a1-c1) 2,c1+(d1-c1)="" 2))[="" code]
<d1,c1+(a1-c1) 2,c1+(d1-c1)="" 2))<d1,c1+(a1-c1)="" 2))
<d1,c1+(a1-c1) 2,c1+(d1-c1)="" 2))[="" quote]
<d1,c1+(a1-c1) 2,c1+(d1-c1)="" 2))"[="" html]

This formula produces the same results as my manual calculation for 5 deferring employees.</d1,c1+(a1-c1)></d1,c1+(a1-c1)></d1,c1+(a1-c1)></d1,c1+(a1-c1)>