formula to calculate a 401K company match?

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.

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

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


"Trish" <Trish@discussions.microsoft.com> wrote in message
news:22168812-BB26-4E0C-8EB8-771F0A728BDD@microsoft.com...
> 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.

"Trish" wrote:
> 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.