401k Employer Match limit

[GTMoo]

I need a formula that calculates the companies matching of 401K contributions. The company will auto enroll everyone at 2% unless they opt out and match $0.25 of every $1.00 up to $500 max per year.

Lets say I have a participant who made $100,000 and contributed $15,000 to the plan, I'm not sure how to recalc the employer match and cap it at $500.

 

[Toadstool]

Re: 401k Employer Match limit - help please!

It's probably best if you put some worked examples together and run it passed your HR/Accounts/Legal staff to make sure you're correctly interpreting as there's usually some complexities (e.g. what if an Employee is Opted In for a part of a year?).

Here's my draft calculation with the Cap just being the MIN statement:

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Employee[/TD]
[TD="align: center"]Opted In=1,
Out=0
[/TD]
[TD="align: center"]Salary[/TD]
[TD="align: center"]Percent[/TD]
[TD="align: center"]Employee Contribution[/TD]
[TD="align: center"]401K Matched[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Fred[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$20,000[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$200[/TD]
[TD="align: center"]$50[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Susan[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$100,000[/TD]
[TD="align: center"]2%[/TD]
[TD="align: center"]$2,000[/TD]
[TD="align: center"]$500[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Charlie[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$25,000[/TD]
[TD="align: center"]3%[/TD]
[TD="align: center"]$750[/TD]
[TD="align: center"]$188[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Vanessa[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$2,000,000[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]$100,000[/TD]
[TD="align: center"]$500[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Toad[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]$50,000[/TD]
[TD="align: center"]2%[/TD]
[TD="align: center"]$1,000[/TD]
[TD="align: center"]$0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=D2*C2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2
[/TH]
[TD="align: left"]=(MIN((D2*C2*0.25),500))*B2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[GTMoo]

Re: 401k Employer Match limit - help please!

Thanks for the reply Toadstool. Your calculation doesn't quit work with my data set. I have the total compensation for the year, total contribution, everyone that participated is a flat 2% match, up to a max of $500 per year. Most participants received the max $500, but a few did not contribute enough during the year and have various figures like 233, 431 etc. I I don't think the column B reference applies for my data set as I am only looking at the participants during the whole year.

It's probably best if you put some worked examples together and run it passed your HR/Accounts/Legal staff to make sure you're correctly interpreting as there's usually some complexities (e.g. what if an Employee is Opted In for a part of a year?).

Here's my draft calculation with the Cap just being the MIN statement:

	A	B	C	D	E	F

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Employee[/TD]
[TD="align: center"]Opted In=1,
Out=0[/TD]
[TD="align: center"]Salary[/TD]
[TD="align: center"]Percent[/TD]
[TD="align: center"]Employee Contribution[/TD]
[TD="align: center"]401K Matched[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Fred[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$20,000[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$200[/TD]
[TD="align: center"]$50[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Susan[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$100,000[/TD]
[TD="align: center"]2%[/TD]
[TD="align: center"]$2,000[/TD]
[TD="align: center"]$500[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Charlie[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$25,000[/TD]
[TD="align: center"]3%[/TD]
[TD="align: center"]$750[/TD]
[TD="align: center"]$188[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Vanessa[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$2,000,000[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]$100,000[/TD]
[TD="align: center"]$500[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Toad[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]$50,000[/TD]
[TD="align: center"]2%[/TD]
[TD="align: center"]$1,000[/TD]
[TD="align: center"]$0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=D2*C2[/TD]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=(MIN((D2*C2*0.25),500))*B2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Toadstool]

Re: 401k Employer Match limit - help please!

Please supply some worked examples of several employees. Just copy the cells and paste here without the employee name.