Mr Bagwell Posted February 19, 2018 Share Posted February 19, 2018 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 More sharing options...
XTitan Posted February 19, 2018 Share Posted February 19, 2018 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) - There are two types of people in the world: those who can extrapolate from incomplete data sets... Link to comment Share on other sites More sharing options...
NJ Mike Posted February 19, 2018 Share Posted February 19, 2018 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 More sharing options...
Mr Bagwell Posted February 19, 2018 Author Share Posted February 19, 2018 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? Link to comment Share on other sites More sharing options...
Mr Bagwell Posted February 19, 2018 Author Share Posted February 19, 2018 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 More sharing options...
XTitan Posted February 19, 2018 Share Posted February 19, 2018 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 More sharing options...
Bri Posted February 20, 2018 Share Posted February 20, 2018 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 More sharing options...
Mr Bagwell Posted February 20, 2018 Author Share Posted February 20, 2018 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 More sharing options...
Kac1214 Posted February 20, 2018 Share Posted February 20, 2018 =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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now