formula to calculate a 401K company match?

[Trish]

The company I work for will match employees 401K with the following: The
first 3% the company matches 100%, the 4th and 5th % the company will match
50%. Does anyone know a formula that will calculate this, I need to figure
this semi-monthly.

 

[intruder9]

Can't you just multiply the amount invested by .04 ?

 

[Bernie Deitrick]

Trish,

With the salary in cell A2, and the percentage 401K constribution in B2, use this formula in C2:

=IF(B2>=5%,A2*4%,IF(B2<=3%,A2*B2,A2*3%+A2*(B2-3%)/2))

HTH,
Bernie
MS Excel MVP

 

[joeu2004]

The company I work for will match employees 401K with the
following: The first 3% the company matches 100%, the 4th
and 5th % the company will match 50%. Does anyone know
a formula that will calculate this, I need to figure this
semi-monthly.

I presume you mean that anything above 3% and less than or
equal to 5% is matched at 50%. If the salary is A2 and the
percentage contribution is in B2:

=A2*MIN(3%,B2) + 50%*A2*MAX(0,MIN(2%,B2-3%))

or equivalently:

=A2*(MIN(3%,B2) + 50%*MAX(0,MIN(2%,B2-3%)))

You probably want to put all that inside ROUNDDOWN(...,0)
or ROUNDDOWN(...,2) to round down to dollars or cents.

However, if you mean that anything under 4% is matched
100% and anything between 4% and 5% inclusive is matched
at 50%, that is harder. Post again if this is your intent.