Match formula calculation for Excel

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

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)

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

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?

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

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

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.

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.

=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