Help with formula

[Pink cupcake]

Hello,

I need to audit our safe harbor match and need assistance with creating an Excel formula. Our match is 100% match on first 3% of deferred salary; then 50% match on next 2% of deferred salary. I currently have this formula =(MIN(D3,3%)*C3)+IF(D3>3%,MIN(D3-3%,2%)*0.5*C3), but it does not factor in if an EE defers 5% or more. 

100% X 3% = 3% 50% X 2% = 1% -------------------- Total Match = 4% of employees deferring 5% or more. If employees are deferring 4%, the match would be: 100% X 3% = 3% 50% X 1% = 0.50% ------------------------ Total Match = 3.50 % If the employees deferral is 3% or less, the employer match would be the same percent as the employee deferral, since it is matched at 100% up to 3%. For example, ee match =1%, ER Match = 1% Please help!  Thank you!

[C. B. Zeller]

Compensation is in B2, deferral amount is in C2

Put the deferral rate in D2: =ROUND(C2/B2,4)

The calculated match rate in E2: =IF(D2<.03,D2,IF(D2>.05,.04,.03+.5*(D2-.03)))

The match in dollars in F2: =ROUND(B2*E2,2)

[BG5150]

Here is a basic SHM spreadsheet I came with a while ago.

Feel free to use it.

No warranties or guarantees of any kind implied or given.  Though, if you look at the examples in there, the calculations fur under 3%, between 3-5% and over 5% come out correct.

Safe Harbor Match Calc.xlsx

[Calavera]

3 hours ago, Pink cupcake said:

=(MIN(D3,3%)*C3)+IF(D3>3%,MIN(D3-3%,2%)*0.5*C3)

=(MIN(D3,3%)*C3)+IF(D3>3%,MIN(MIN(D3,5%)-3%,2%)*0.5*C3)

[Bri]

no ifs needs:

let A1 = pay, let B1 = deferrals

match = min(A1*.025, B1*.5) + min(A1*.015, B1*.5)

use a round function on it, but that's messier to type out here.

You essentially have 2 matches layered on top of each other.  50 percent on the first 5, plus 50 percent on the first 3.

[Bill Presson]

1 hour ago, Bri said:

no ifs needs:

let A1 = pay, let B1 = deferrals

match = min(A1*.025, B1*.5) + min(A1*.015, B1*.5)

use a round function on it, but that's messier to type out here.

You essentially have 2 matches layered on top of each other.  50 percent on the first 5, plus 50 percent on the first 3.

That's a fascinating way to look at it.

[Bri]

I shook my head at how easy it was when I figured it out that way.  (I had the uglier formulas written down somewhere for years, too.)

And it can do a QACA, too: 

match = min(A1 * .005, B1 * .5) + min(A1 * .03, B1 * .5)