We help IT Professionals succeed at work.

calculating employer match of 401k

In the attached sheet currently is calculating a 3% match for employee 401K contributions in cell F5, I need to change this calculation to a 3% match if column CYOS is 5 years or less, a 6% match if column CYOS is between 5 to 10 years, and a 9%  match if column CYOS is 10 years or more
Book1.xlsx
Comment
Watch Question

CERTIFIED EXPERT
=IF((B5*0.03)>C5,C5,(B5*CHOOSE(MIN(3,INT((E5-0.0001)/5)+1),3,6,9)/100))
Rob HensonFinance Analyst
CERTIFIED EXPERT
Couple of options, nested IF statement to choose the % rate or lookup on small table of years and relevant rate.

See attached with both.

VLOOKUP:
=IF((B5*VLOOKUP($E$5,$I$1:$J$3,2))>C5,C5,(B5*VLOOKUP($E$5,$I$1:$J$3,2)))   where I1 to J3 has table:

0      3%
5      6%
10      9%

Nested IF:
=IF((B10*IF(E10>10,0.09,IF(E10>5,0.06,0.03)))>C10,C10,(B10*IF(E10>10,0.09,IF(E10>5,0.06,0.03))))
Book1--3-.xlsx
Rob HensonFinance Analyst
CERTIFIED EXPERT
Did both occurrences of the 3% in the original formula need changing to variable rate? My suggestion did that but can easily change it so that it doesn't.
CERTIFIED EXPERT
Rob, your two formulas give different values at 5 and 10.
Rob HensonFinance Analyst
CERTIFIED EXPERT
Nested IF might need tweaking to be >= rather than just >

=IF((B10*IF(E10>=10,0.09,IF(E10>=5,0.06,0.03)))>C10,C10,(B10*IF(E10>=10,0.09,IF(E10>=5,0.06,0.03))))

Can change the lookup table to adjust accordingly.
CERTIFIED EXPERT
Actually, the nested if is giving results as per the question. The vlookup is different.
Rob HensonFinance Analyst
CERTIFIED EXPERT
Maybe need some clarity from Tom as to what happens when exactly 5  or 10 years. Question says "5 or less" and "between 5 and 10" and "10 or more".
The occurrence of us having an employee at exactly 5 or 10 years the day we run this calculation would be high unlikely, additionally it is done quarterly after the fact so I think it's relevant.  Both solutions work for what I am doing thank you both
CERTIFIED EXPERT
However low the probability is, there always exists the possibility that such an instance exists where this happens exactly. So you must know your criteria otherwise you might have to face some noise.
Rob HensonFinance Analyst
CERTIFIED EXPERT
If the criteria is
>0 = 3%
>5 = 6%
>10 = 9%

The original nested IF matches those criteria. For the lookup change the left hand column to
0
5.0000001
10.000001

The decimal fraction is less than 1 day 1/365 = 0.00274 so will cover the difference that one day could make to the calcs.

If the criteria is:
<5 = 3%
5 -9.999 = 6%
10+ = 9%

Original lookup table is correct, formula with >= is correct.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.