Jump to content

Match formula calculation for Excel


Recommended Posts

Could someone write me a match formula for the following? 

Match formula is as follows:

100% of first 4, 33.34% of next 3.  So if you put in 7% deferrals, you get 5% match

The last 3%.... payroll calcs it 33.34% for 5, 33.34% for 6, 33.34 for 7%... I know, a little different than what you might expect.

I'm proficient in excel, just not that proficient on the if/then statements.

Thanks

Link to comment
Share on other sites

Here is mine you can try where D1 is the compensation and E! is the deferral:

=IF(E1/D1<0.04,E1/D1*D1,IF(E1/D1<0.07,(((E1/D1-0.04)*0.333333333+0.04)*D1),0.05*D1))

It is from a few years ago and may be more complicated then is has to be.

Mike

Link to comment
Share on other sites

40 minutes ago, NJ Mike said:

Here is mine you can try where D1 is the compensation and E! is the deferral:

=IF(E1/D1<0.04,E1/D1*D1,IF(E1/D1<0.07,(((E1/D1-0.04)*0.333333333+0.04)*D1),0.05*D1))

It is from a few years ago and may be more complicated then is has to be.

Mike

Mike,

This gets me close enough for a game of horseshoes.  Just rounding differences from my very long process.

Thanks

Link to comment
Share on other sites

1 hour ago, Mr Bagwell said:

XTitan,

Thanks for the info.  The formula returns 5%.  Should this be comparing against compensation?

You may have a misplaced parenthesis.  I get

Deferral Percentage Match Percentage
0.01
0.01
0.02
0.02
0.03 0.03
0.04 0.04
0.05 0.043334
0.06 0.046668
0.07 0.05
0.08 0.05
0.09 0.05
0.1 0.05

 - There are two types of people in the world: those who can extrapolate from incomplete data sets...

Link to comment
Share on other sites

I would have done this, with A1 = deferrals and B1 = pay

=MIN(A1*0.3334,B1*0.023338)+MIN(A1*0.6666,B1*0.026664)

Similar to how I code basic safe harbor match calculations.  Here, it's a 33.34% match on the first 7% deferred, plus a 66.66% match on the first 4% deferred.

No if/then needed.

Link to comment
Share on other sites

5 hours ago, Bri said:

I would have done this, with A1 = deferrals and B1 = pay

=MIN(A1*0.3334,B1*0.023338)+MIN(A1*0.6666,B1*0.026664)

Similar to how I code basic safe harbor match calculations.  Here, it's a 33.34% match on the first 7% deferred, plus a 66.66% match on the first 4% deferred.

No if/then needed.

Thanks Bri.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...