# Redundancy payout calculation formula

#### pan_kaj

##### Board Regular
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.

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
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
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....

#### mortgageman

##### Well-known Member
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

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
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

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
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
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
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

Replies
3
Views
83
Replies
2
Views
227
Replies
0
Views
196
Replies
6
Views
760
Replies
1
Views
51

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.

### Which adblocker are you using?

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

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