Redundancy payout calculation formula

pan_kaj

Board Regular
Joined
Dec 28, 2004
Messages
80
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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

Board Regular
Joined
Dec 28, 2004
Messages
80
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

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Your welcome for the help, but I'm not sure what you mean. What value are you putting in cell A2?
 

pan_kaj

Board Regular
Joined
Dec 28, 2004
Messages
80

ADVERTISEMENT

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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

Board Regular
Joined
Dec 28, 2004
Messages
80
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
 

Forum statistics

Threads
1,136,266
Messages
5,674,728
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top