401k Match formula

[Ginger 1775]

Hi! I'm trying to build a formula for calculating 401k match based on the following logic:

Employer will match 50% of the combined Pre-tax and Roth deferrals up to a maximum of 6%. Match begins on the 1st of the month following 6 months of service.

My payroll sheet contains the following data columns to reference:

Total Gross Earnings (Column T)
Eligibility/Match Date (Column V)
3% of Gross Earnings (Column W)
Pre Tax Deferral Amount (Column Y)
Roth Deferral Amount (Column AA)
and I want the formula to produce in ER Match (Column AD)

Thank you!!

 

[Joe4]

Welcome to the Board!

How does the date come into play?
What is the date in column V being compared to?

Can you show us a few rows of dummy data and expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

The Match calculation (without any regards to the date), would look something like this:

=MIN((Y10+AA10)*0.5,T10*0.06)

If you tell us a little more about the date piece (are we comparing the date in column V to the current date, or something else), we can help you work in that part too.
If it was just comparing it to the current date, it might look something like:

=IF(V10<=TODAY(),MIN((Y10+AA10)*0.5,T10*0.06),0)

 

[Ginger 1775]

Thank you! Below is an example of outcomes. The "Match Date" is being used to essentially restrict the Employer/ER Match column as the match isn't effective until someone has reached their Match Date. I can also manually update/remove those contributions if that's too cumbersome to build into the formula.

Those that have not yet met their match date may contribute, but the match is inactive (2nd and 3rd line example)
Those contributing a combined or total 6% of gross earnings will earn the full 3% employer match. (4th line example)
Those contributing at a rate higher than 6% are capped at the 3% maximum (1st and 5th line examples)
Those contributing below 6% are matched at 50% of their contribution rate (6th line example)

 

[Joe4]

Did you see my 2nd post, that has some formulas for you and a question regarding dates?
The formula you need may already be there.

 

[Ginger 1775]

Did you see my 2nd post, that has some formulas for you and a question regarding dates?
The formula you need may already be there.

Hi Joe, I did see your formula, but it didn't appear to be producing the right total when I tried it on my end.

i.e. The formula produces $411 as the match for this participant, but it should be capping them at the maximum 3% ( = up to 50% of a max deferral of 6%)
So should my formula be =MIN((Y10+AA10)*0.5, T10*.03) instead?

The match date is a manual input from me of the date that their match should "turn on". So if it is a future date, then the ER match would be '$0" but I would still be capturing their employee contributions from column Y & column AA

 

[Joe4]

So should my formula be =MIN((Y10+AA10)*0.5, T10*.03) instead?

Yes, sorry I thought you meant a maximum match of 6%. So your update to it should be correct.
Does it work right if you make that change?