Please Help Qtr Mtch

[KristiM]

I was provided with the following formula to calculate qtrly match:
=IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y")<21,0,G2*MIN(D2,3%)+50%*MAX(0,MIN(G2*3%,D2*2%))).
The problem is that this formula is not calculating the correct amount for
anything over 4%. Match is 100% of the first 3% and 50% of the second 2%, so
if contribute 5% we only match 4% (3%(100%) + 1%( 50%- 2%)
Example: John contributes 10% 1st qtr amt $1514.43= $60.58. The formula
shown above calcs $45.43. What am I doing wrong?

 

[Bernie Deitrick]

Kristi,

=IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y")<21,0,G2*(MIN(D2,3%)+50%*MAX(0,MIN((D2-3%),2%))))

HTH,
Bernie
MS Excel MVP

 

[KristiM]

Thank you so much that works! Do you mind explaining why placing the 2%
outside the parenthesis worked.

 

[Bernie Deitrick]

What you were doing originally had this:

MIN(G2*3%,D2*2%)

but D2 is a percent - and 2% of that is irrelevant.

Anyway, the 2% is not 'outside' the parens. Here's the calc:

G2*(MIN(D2,3%)+50%*MAX(0,MIN((D2-3%),2%))))

The key is evaluating this:

MIN((D2-3%),2%)

That compares the amount above 3% but limits it to 5%

If D2 is 4%, it evaluates to 1%
If D2 is 5% or higher, it evaluates to 2%

BUT, if D2 is below 3%, it evaluates to a negative%
So, this part prevents that:

MAX(0,MIN((D2-3%),2%)))

MAX(0,Neg%)

evaluates to 0 for cases where D2 is less than 3%.


HTH,
Bernie
MS Excel MVP

 

[KristiM]

Trying to calculate Qtr 401k mtch. Mtch is 100% of the first 3% of deferred
compensation and 50% of the next 2%. D: Defer %, E: Salary, F: Deferred
Amount

=IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y")<21,0,IF(D2<=0.03,D2*E2,MIN(0.03*E2+((D2-0.03)*E2)/2,0.03*E2+(0.02*E2)/2)))

Example: Ann elects 1% total deducted is 180.02. Qtr Match is 180.02

Pleas help!

 

[Bernie Deitrick]

Kristi,

????? I thought we had come up with a formula that works:

=IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y")<21,0,G2*(MIN(D2,3%)+50%*MAX(0,MIN((D2-3%),2%))))

HTH,
Bernie
MS Excel MVP

 

[KristiM]

I place the calculation in and result= 1.80 I think I need to remove the E:
Salary from the formula. If cell D is less than 3% then cell F is 100%, if
cell D is 4%then cell F calculates 100% of 3% and .5%, if cell D is 5% or
more then cell F calculates 100 of 3% and 2%/2.