401(k) Safe Habor Calculations/Formulas

[zzapapa]

Hi everyone - thank you for reading.

I am looking to create a simplified formula to plug into employer census data to work out the cost to implement Safe Harbor 401(k) plans.

At present I am using the following formulas:

Traditional - 100% match on first 3% of deferred salary; then 50% match on next 2% of deferred salary.

Accelerated - dollar for dollar match up to 4% of deferred salary.

QACA -100% match on first 1% of deferred salary; then 50% match on next 5% of deferred salary.

Currently I am using a couple of different formulas for each Safe Harbor design. Ideally, I would like to try and replace with a single formula for each.

Any assistance will be greatly appreciated.

Thanks!

 

[Joe4]

Welcome to the Board!

The more specific you are, the better we can tailor a response to your exact scenario.

What values are you working with, and where are they located (i.e what columns are the percentages and dollar amounts in)?

 

[zzapapa]

Welcome to the Board!

The more specific you are, the better we can tailor a response to your exact scenario.

What values are you working with, and where are they located (i.e what columns are the percentages and dollar amounts in)?

Thank you very much for the warm welcome, Joe.

I am more looking for a formula that is possible to tailor to different spreadsheets. Different people will place these details in different columns and I would of course adjust accordingly.

Having said that typically salary data would be in column C/D and deferral % would be in column D/E.

Does that make sense? Sometimes difficult to explain this kind of stuff! Thanks.

 

[Joe4]

OK. Let's say that your salary is in cell C2 and your Deferral Percentage is in cell D2.
For each equation, I am listing two different formulas:
Formula1: If you deferral percentage is listed as a whole number, i.e. 8
Formula2: If you deferral percentage is listed as a percentage or decimal, i.e. 8% or .08

Traditional - 100% match on first 3% of deferred salary; then 50% match on next 2% of deferred salary.
=((MIN(D2,3)*C2)+IF(D2>3,MIN(D2-3,2)*0.5*C2))/100
=(MIN(D2,3%)*C2)+IF(D2>3%,MIN(D2-3%,2%)*0.5*C2)

Accelerated - dollar for dollar match up to 4% of deferred salary.
=(MIN(D2,4)*C2)/100
=MIN(D2,4%)*C2

QACA -100% match on first 1% of deferred salary; then 50% match on next 5% of deferred salary.
=((MIN(D2,1)*C2)+IF(D2>1,MIN(D2-1,5)*0.5*C2))/100
=(MIN(D2,1%)*C2)+IF(D2>1%,MIN(D2-1%,5%)*0.5*C2)

 

[zzapapa]

This looks amazing. I'm going through it now in an attempt to reverse engineer what you have done.

Out of curiosity, why use =min ? It isn't a command I have ever come across.

Thanks!

 

[Joe4]

Microsoft actually has done a great job with documenting their functions. They have full descriptions and examples.
The MIN function returns the smallest value (see: https://support.office.com/en-us/article/MIN-function-61635d12-920f-4ce2-a70f-96f202dcc152)

So, when we are just comparing two values, it is taking the lesser one.
So if we have:
MIN(D2,3%)
it is saying take the lesser of what is in cell D2 and 3%.
That is perfect when we only want to match up to the first 3%.