Spreadsheet for 401K

[Guest Liosis]

I've attached a spreadsheet that I designed and would like to gather your opinions on why a formula such as this isn't used more often in connection with payroll software. I could be completely off base, but it seems as though this would be a very effective way of administering a 401k plan if the software was programmed appropriately. This specific spreadsheet is set up for the basic safe harbor match of 100% of the first 3% and 50% of the next 2%, and is for a company making semi-monthly payrolls. The design is one of a running true-up, so each employee would receive a match every payroll based on their YTD deferrals and YTD earnings. Would this work for a plan in which the match was made every payroll, but calculated on an annual basis? I'm actually on the payroll side of things and would love to hear some feedback about how this approach would jive with 401k rules, because I have yet to see a plan that provides for a match every payroll that doesn't have some tradeoffs. If I'm not mistaken, this spreadsheet will always end up calculating the appropriate match with no need for a normal year end true-up. I challenge you to find a scenario that can stump it given the previously mentioned parameters.

You can change the pay, election %, or flat amount for each pay period and the annual deferral limit to be 16500 or 22000. For each period, you should only have filled in an election % or flat amount, but not both. It's not the prettiest, and there is more formatting that can be done, but please give it a go and let me know what you think.

Thanks.

401k_SH.xls

[masteff]

Personally speaking, a YTD match is an elegant solution. It gives each employee the maximum possible match w/out the employee having to worry about maxing out too early in the year. And it removes the administrative burden and hassle of a year-end true-up.

The sole problem of what you propose is the programming of the payroll software itself. The real problem is lack of foresight on the part of the software developers. Most of them go for the 90% solution... it works for 90% of possible customers (who happen to have plain vanilla match) and they don't spend the time to program functionality for the 10%. You're more likely to find more flexibility/features in a stand alone payroll package than in one that's bundled in an accounting program. Sadly, 401(k) deductions and match are a poor step-child in most payroll softwares; it appears hard enough just finding one that properly handles tiered match.

I worked for 8 years in 401(k) administration at a major oil and gas company. One of their plans had a YTD match like you propose (the match formula was different but that's not relevant to my comments). We used SAP and had to pay programmers to build custom code to go into the database and get YTD deferral and YTD eligible earnings from the prior payperiod. Sure, the software goes and looks those same things up when it updates the totals, but, in the developers' linear thinking, that occurs at the end of the payroll process so the numbers needed aren't available in the middle of the process.

So to directly answer your request for comments on how it jives w/ 401(k) rules... it works perfectly well w/ the rules but the devil is in the implementation of it.

[masteff]

Found a link showing that eBay implemented this type of YTD match for their plan in 2009. https://www.schwabplan.com/download/misc/EBY_FAQs.pdf http://www.schwabplan.com/images/misc/edu/...the_True-Up.pdf

[GMK]

This is cool. I like it.

Hmm. I think I'll ask our people if they can work it into our payroll software.

[EBDI]

Impressive spreadsheet.

[Guest Liosis]

Thanks for all the feedback thus far...it reassures me that I am on the right track.

Masteff- I hear what you're saying loud and clear. Implementation is severely, if not completely limited with most payroll systems.... but as a result there has got to be a business opportunity for whoever implements a streamlined solution. This provides a win-win for all parties involved and once implemented properly, would make a lot of peoples' lives a lot easier. You're absolutely correct about the 90% solution, but the problem is that the 10% it doesn't work for accounts for at least 90% of the headache!!

[Guest Liosis]

Masteff- and thanks for those links, they perfectly illustrate the entire point of this spreadsheet

[GMK]

Liosis - Just wanted to pop back here and thank you for starting this thread.

Without very little prodding on my part, our IT people upgraded our (tiered) match calculator with your excellent suggestion.

They think it's really cool, too, and it wasn't hard to do.

Thanks, again.

[austin3515]

GMK, just make sure this new enhancement complies with your document. The way most "payroll to payroll" matches are written, you ONLY look at the deferralsd and comp in that specific pay-period.

[GMK]

Thanks, Austin. That's an important reminder.

Yes, as we're frequently reminded on these boards, our Step One is to see what the plan document says.