Redundancy payout calculation formula

[pan_kaj]

I would like a formula which would help me calculate payout based on the following business rules.

from the age 20 to 21(both included) - 0.5 week’s pay for each completed year of service
22 - 40 (both included) years of age - 1 week’s pay for each completed year of service
41 and above years of age - 1.5 weeks’ pay for each completed year of service

A2 has my present age = 52
B2 has my yrs of completed service = 19
C2 has my one weeks pay = 290

Can any body help ? The calculation is step based so the period between 22-40 will be calculated at 1 weeks pay and the years beyond (so in my case 12 yrs fall in 41+ category and 7 yrs in the 22-40 category.

Please help me ..

THanks Pankaj

 

[mortgageman]

Not fully tested, but this seems right - it should work for anyone who reaches the age of 200, but no more

=LOOKUP(A2,{20,22,41,200},{0.5,1,1.5})*B2*C2


Gene, "The Mortgage Man", Klein

 

[pan_kaj]

Sorry this doesnt work somehow... It comes up with only 1 week each time...

I need to make sure that the intermediate time frames are calculated as mentioned....

Thanks for your help though...

 

[mortgageman]

Your welcome for the help, but I'm not sure what you mean. What value are you putting in cell A2?

 

[pan_kaj]

Age service Weeks pay Desired Answer
52 8 290 3480
35 14 290 3915
46 20 290 6525
24 5 290 1015

 

[barry houdini]

Try this

=(B2*1.5-(MEDIAN(0,B2-A2+40,B2)+MEDIAN(0,B2-A2+22,B2))/2)*C2

I don't think it gives the correct result for your example at age 46 but I don't think you are consistent here. If 52 gets 12 weeks at 1.5 then 46 should get 6 weeks at 1.5 and 14 at 1 and therefore give 23 weeks at 290 = 6670

 

[mortgageman]

If I understand your rules, if the age is 35, then it is 1 weeks pay for each year of service. By your table that would be 1x14x290 which equals 4,060 (what my formula gets) not 3915 (what you say is the desired answer). Are there other rules that you did not disclose?

 

[barry houdini]

Hi Gene,

I think the idea is that the first year comes within the age 20-22 category and therefore only qualifies for 0.5 weeks pay, the other 13 get a full week.

pan_kaj

I think there are still inconsistencies. 1015 represents 3.5 weeks pay which presumably is 2 years at 1 week and 3 years at 0.5 but you say that 0.5 is payable for only 2 years 20-21.

My formula above will fall down in certain circumstances. I've revised it to the following but, as I say, it may not give you the desired results in al circumstances, I think you need to make sure your conditions ae consistent

=IF(A2<20,0,(B2*1.5-(MEDIAN(0,B2-A2+40,B2)+MEDIAN(0,B2-A2+22,B2)+MAX(0,19-A2+B2))/2)*C2)

 

[mortgageman]

Hi Gene,

I think the idea is that the first year comes within the age 20-22 category and therefore only qualifies for 0.5 weeks pay, the other 13 get a full week.

pan_kaj

I think there are still inconsistencies. 1015 represents 3.5 weeks pay which presumably is 2 years at 1 week and 3 years at 0.5 but you say that 0.5 is payable for only 2 years 20-21.

My formula above will fall down in certain circumstances. I've revised it to the following but, as I say, it may not give you the desired results in al circumstances, I think you need to make sure your conditions ae consistent

=IF(A2<20,0,(B2*1.5-(MEDIAN(0,B2-A2+40,B2)+MEDIAN(0,B2-A2+22,B2)+MAX(0,19-A2+B2))/2)*C2)

Barry - still not sure I follow. If you are right about the rules (and I do wish the OP had made that clearer) I don't think the third row follows them. The employee started at age 26 (46-20 years of service). So that means he has 14 years of 1 times and 6 at 1.5 times. (Stop me if I'm wrong)

Now 14x1x290 +6x1.5x290=6670, not 6525. Or do I need my first cup of coffee?

 

[pan_kaj]

Hey Guys...
Thanks for your inputs... I did put together your replies and have arrived at a workable formula...

To reply to the 46 (20 yrs) question....

if one starts at the age of 26, he would work for 15 yrs in the 26-40 band and 5 yrs in the 41-46 band. (on his 46th birthday he wd complete 20 yrs service assuming he started on his 26th birthday) and hence the calculation - 15*1*290 + 5*1.5*290 which wd be 6525

Thanks
Pankaj