calculating redundancy pay

[davidwylie]

does anyone have a formula to help calculate UK redundancy pay

rules are as follows:

-0.5 weeks pay for each full year under 22
-1 week pay for each full year they were 22 or older but under 41
-1.5 week pay for each full year they were 41 or older
-length of service capped at 20 years
-weekly pay capped at £500
-total max stat pay capped at £15,000


assume cell a1 for employee age, cell b1 for years of service, cell c1 for average weekly pay

thanks for your help in advance

 

[oldbrewer]

does anyone have a formula to help calculate UK redundancy pay
rules are as follows:
-0.5 weeks pay for each full year under 22								mytable
-1 week pay for each full year they were 22 or older but under 41
-1.5 week pay for each full year they were 41 or older
-length of service capped at 20 years								16	0.5
-weekly pay capped at £500									22	1
-total max stat pay capped at £15,000								41	1.5
											65	1.5
assume cell a1 for employee age, cell b1 for years of service, cell c1 for average weekly pay
thanks for your help in advance
age	years	pay
17	1	300	150	#####
18	2	300	300
19	3	300	450
20	4	300	600
21	5	300	750
22	6	300	1800
25	7	300	2100
40	21	800	15000
41	9	300	4050
45	10	300	4500
60	20	900	15000
		#####
		=IF(VLOOKUP(A21,mytable,2)*B21*C21>15000,15000,VLOOKUP(A21,mytable,2)*B21*C21)

<colgroup><col span="15"></colgroup><tbody>
</tbody>

 

[dmt32]

Did one for my daughter back in 2009 but unable to locate copy.

Rather than start from scratch, Suggest you google "excel redundancy calculator" & download any examples.

Dave

 

[davidwylie]

I cant find any good ones that help with doing 40 employees at once in order to work out estimated total redundancy pay. has any one else any ideas

 

[oldbrewer]

dont u like mine then....

 

[Dave Patton]

I am not sure we have the full information for the calculation.
A few examples would have been useful.

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Annual max	500						Redundancy
2	Maximum	20		Brackets -->	41	21	0	Rate
3	Age	Worked		Factor -->	1.5	1.0	0.5		Ver1	Ver2
4	50	32	20	Accum	9	11	0	600	12,250	12,250
5	42	22	20	Accum	1	19	0	550	10,250	10,250
6	24	4	4	Accum	0	3	1	450	1,575	1,575
7	70	50	20	Accum	20	0	0	700	15,000	15,000
8	21	4	4	Accum	0	0	4	400	800	800
4a

Cell Formulas
Range	Formula
C4	=MIN($B$2,B4)
E4	=MAX(0,MIN($C4-SUM($D4:D4),$A4-E$2))
F4	=MAX(0,MIN($C4-SUM($D4:E4),$A4-F$2))
G4	=MAX(0,MIN($C4-SUM($D4:F4),$A4-G$2))
I4	=SUMPRODUCT($E$3:$G$3,E4:G4)*MIN($B$1,H4)
J4	=(E4*E$3+F4*F$3+G4*G$3)*MIN($B$1,H4)

 

[davidwylie]

for more infor this is the design of the table I have

Employee PAYE Start Date Service Contracted Hours Av Weekly Pay Today Date DOB Age Age Commenced Redundancy Pay
Jack HP services 01/10/1990 27.02 15 125.09 07/10/2017 26/06/1937 80 53
Peter HP services 01/11/1993 23.93 16 145.11 07/10/2017 20/05/1943 74 50
Emily HP services 01/11/1999 17.93 12.5 114.06 07/10/2017 27/04/1949 68 50
Harry HP services 01/12/2001 15.85 32 398.96 07/10/2017 15/08/1954 63 47

 

[davidwylie]

thanks Dave. your model seems to be working. I assume ver2 is just a validation check?