Jump to content
Sign in to follow this  
Mr Bagwell

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
44 minutes ago, XTitan said:

If Cell A1 has the EE contribution, try

=MIN(MIN(A1,0.04)+0.3334*MIN(MAX(0,A1-0.04),0.03),0.05)

XTitan,

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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

=IF(+E1/D1>=0.07,1/3*(0.03*D1)+0.04*D1,IF(+E1/D1>0.04,1/3*(+E1/D1-0.04)*D1+0.04*D1,+E1)) 

Would also work, not quite as elegant but gets same results. Would probably add ROUND function to it as well

Share this post


Link to post
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
Sign in to follow this  

×
×
  • Create New...