formula for a pension
"barbarat" wrote:
I need to calculate (in Column I) the match for a 401(k) as follows:
Column G is compensation, H is employee contribution, and B is Date
of birth (as numbers...ie, 01011986).
The match is $1 for each dollar invested up to 3% of compensation,
and $.75 for each dollar invested over 3%, but not greater than 6%,
of compensation, and $.50 match for each dollar between 7 and 10%.
No match over 10%. Also no match if employee is under 18 yrs of
age at 12/31/04.
I have tried IF...and keep gettong the wrong results.
I assume you mean "not greater than 7%" or "between
6 and 10%". Otherwise, answer: what is the match for
the amount between 6% and 7% of compensation?
Ostensibly:
IF(DATEDIF(B1, "12/31/2004", "y") < 18, 0,
INT(MIN(H1, G1*3%))
+ 0.75*MAX(0, INT(MIN(H1-G1*3%, G1*6%)))
+ 0.50*MAX(0, INT(MIN(H1-G1*6%, G1*10%))))
This assumes that no birthday is after 12/31/2004. It also
assumes that B1 is the form of mm/dd/yyyy. Yours is in
the form mmddyyyy. Ideally, change the format in B1.
Otherwise, replace B1 above with the following formula
or a reference to a helper cell with it:
DATEVALUE(INT(B1/1000000)
& "/" & INT(MOD(B1,1000000)/10000)
& "/" & MOD(B1,10000))
|