Statutory Redundancy Pay Out

Hi All,

I have a spreadsheet containing over 2000 rows of data. I need to calculate UK statutory redundancy pay based on following rules.

The current rules state the maximum that can be paid caps out at 20 years’ service and a max payout of £14,670 (£15,000 for Northern Ireland).
The weekly pay is capped at £489 per week (for Northern Ireland its £500 per week)

The rules also count on following age basis:

0.5 weeks’ pay for each full year of service when under age 22
1.0 weeks’ pay for each full year of service between age 22 – 41
1.5 weeks’ pay for each full year of service from age 41 or older

In addition to above we should count full years so if someone has 3.5 years’ service, only 3 years should be count. There is no rounding up.

Could someone help please?

I tried to attach spreadsheet but for some reason it wouldn't work for me. I will try again but I have pasted below the table that I am looking to use

Name Age Annual Pay Stat Weekly Rate Redundancy Effective Date Redundancy Payment
O 57 22260 428 31/12/2016
P 61 22382 430 31/12/2016
Q 54 22740 437 31/12/2016
R 52 23090 444 31/12/2016
S 57 23304 448 31/12/2016
T 38 29740 572 31/12/2016

When you copy and paste above data into spreadsheet it will appear in columns. I know its not ideal to paste data here I am unable to my spreadsheet.


Thanks for your help.

Shiraz

Hi gak

Thanks for getting back. Apologies but I missed crucial piece of info there. Below is the revised table. Also is it possible to work out this formula as you described above as well as base it on the age of redundancy?

Name Date of Birth Age Yrs. In Service Annual Pay Stat Weekly Rate Redundancy Effective Date Location Redundancy Payment
O 18/02/1960 57 32 22260 428 31/12/2016 England
P 24/05/1956 61 27 22382 430 31/12/2016 Northern Ireland
Q 21/12/1962 54 27 22740 437 31/12/2016 Northern Ireland
R 19/08/1964 52 32 23090 444 31/12/2016 Northern Ireland
S 27/10/1959 57 32 23304 448 31/12/2016 England
T 30/05/1963 54 38 29740 572 31/12/2016 England
U 08/11/1963 53 30 30000 577 31/12/2016 England
V 03/02/1959 58 37 30210 581 31/12/2016 England
W 03/03/1962 55 32 30220 581 31/12/2016 England
X 14/07/1963 53 35 30250 582 31/12/2016 England
Y 10/05/1969 48 27 31270 601 31/12/2016 England
Z 27/11/1968 48 30 31290 602 31/12/2016 Northern Ireland

As mentioned above, if you copy and paste this table into Excel it will convert in to relevant columns.

Thanks for your help.

S

Hi

Thanks for looking into this. The good news is that formula worked and the bad news is that I am not getting desired answer which in case of individual 'O' should be £11984 based on govt. website https://www.gov.uk/calculate-your-redundancy-pay .
I am using this website to confirm the answer. do you mind checking this formula again so we can get same answers as per the govt. website?

Thanks again

Attached full workings for you... complete with VLOOKUP tables.

Thanks BW

I have reviewed the formula and it seems to be working. The formula should calculate pay out in three tranches i.e. under 22, between 22 - 41 and 41 +. I have checked each of your calcs on .gov website some match and seems ok but large majority are not. I have again tried to attach the spreadsheet but its not letting me attach it. Not sure if someone can help with this issue. But I have listed down desired answers for each of them. All of the examples that I sent through seems to cap at 20yrs. service so to provide little bit of variety, I have added in few more individuals below (B downwards) whose yrs. in services is less than 20 yrs. Do you mind taking a look again please?

Name DOB Age Yrs. In Srv. Annual Salary Weekly Stat. Pay Redundancy Date Country Sum payout (with Caps) Desired Answer
O 18/02/1960 57 32 22260 428 31/12/2016 England 15,000.00 Ok
P 24/05/1956 61 27 22382 430 31/12/2016 Northern Ireland 15,000.00 Ok
Q 21/12/1962 54 27 22740 437 31/12/2016 Northern Ireland 15,000.00 Ok
R 19/08/1964 52 32 23090 444 31/12/2016 Northern Ireland 14,670.00 Ok
S 27/10/1959 57 32 23304 448 31/12/2016 England 14,670.00 Ok
T 30/05/1963 54 38 29740 572 31/12/2016 England 14,670.00 12,693
U 08/11/1963 53 30 30000 577 31/12/2016 England 14,670.00 12,454
V 03/02/1959 58 37 30210 581 31/12/2016 England 14,670.00 13,652
W 03/03/1962 55 32 30220 581 31/12/2016 England 14,670.00 12,933
X 14/07/1963 53 35 30250 582 31/12/2016 England 14,670.00 12,454
Y 10/05/1969 48 27 31270 601 31/12/2016 England 15,000.00 11,257
Z 27/11/1968 48 30 31290 602 31/12/2016 Northern Ireland 14,670.00 Ok
B 01/04/1979 38 5 6950 134 31/12/2016 England 14,670.00 670
C 14/12/1981 35 4 9230 178 31/12/2016 England 14,670.00 712
D 03/08/1963 53 8 9900 190 31/12/2016 England 14,670.00 2,280
E 20/04/1986 31 10 10200 196 31/12/2016 England 14,670.00 1,862
F 29/05/1975 42 4 11030 212 31/12/2016 England 14,670.00 954
G 08/08/1975 41 7 12124 233 31/12/2016 England - 1,631

Thanks again

Thank you Josephteh

Your spreadsheet looks really good and seems to be working great. However, I discovered earlier on that pay out needs to be calculated in three tranches. I have provided below how it should be calculated:

0.5 weeks’ pay for each full year of service when under age 22
1.0 weeks’ pay for each full year of service between age 22 – 41
1.5 weeks’ pay for each full year of service from age 41 or older

I have provided below the individual cases and desired answers. If you don't mind giving it another stab:

Name DOB Age Yrs. In Srv. Annual Salary Weekly Stat. Pay Redundancy Date Country Desired Answer
O 18/02/1960 57 32 22260 428 31/12/2016 England 15,000
P 24/05/1956 61 27 22382 430 31/12/2016 Northern Ireland 15,000
Q 21/12/1962 54 27 22740 437 31/12/2016 Northern Ireland 15,000
R 19/08/1964 52 32 23090 444 31/12/2016 Northern Ireland 14,670
S 27/10/1959 57 32 23304 448 31/12/2016 England 14,670
T 30/05/1963 54 38 29740 572 31/12/2016 England 12,693
U 08/11/1963 53 30 30000 577 31/12/2016 England 12,454
V 03/02/1959 58 37 30210 581 31/12/2016 England 13,652
W 03/03/1962 55 32 30220 581 31/12/2016 England 12,933
X 14/07/1963 53 35 30250 582 31/12/2016 England 12,454
Y 10/05/1969 48 27 31270 601 31/12/2016 England 11,257
Z 27/11/1968 48 30 31290 602 31/12/2016 Northern Ireland 14,670
B 01/04/1979 38 5 6950 134 31/12/2016 England 670
C 14/12/1981 35 4 9230 178 31/12/2016 England 712
D 03/08/1963 53 8 9900 190 31/12/2016 England 2,280
E 20/04/1986 31 10 10200 196 31/12/2016 England 1,862
F 29/05/1975 42 4 11030 212 31/12/2016 England 954
G 08/08/1975 41 7 12124 233 31/12/2016 England 1,631

Thanks again

can anyone else help?

Thank you Shg, I am reviewing this

Thank you Joseph,

This looks promising but doing some sense checks, will get back to you with feedback. thanks again for finding time to do this for me

Thanks Joseph

I think of Northern Ireland cap is making the formula too complex then let's take out this cap. I will review revised spreadsheet as soon as I get into office and let you know.

Hi Joseph

I have now incorporated your formula in my main spreadsheet and I am pleased to confirm that everything is working well. Thank you so much for your help and everyone else who came to rescue.

Thanks once again

i will mark this thread as resolved.

Shiraz

Resolved.

Glad to be of help.

To mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

Hi Joseph

Just before we close the thread, I have discovered something. The individual below 1 year of service should have 0 as their final pay out. As you can see below, using your spreadsheet, its working out the pay out figure. According to the rule, if someone's service is less than 2 years, they are not entitled to redundancy pay.

Employee DOB Age YoS Ann$ W$ EOD Location Entitled YoS W Entitled (0.5, 1,1.5) W$ (with Caps) Total payout (with Caps)
H 25/05/1984 32 1 12540 241.00 31/12/2016 England 1 1 241.00 241.00
I 06/10/1996 20 1 15200 292.00 31/12/2016 England 1 0.5 292.00 146.00
J 07/01/1991 25 1 16160 311.00 31/12/2016 England 1 1 311.00 311.00
K 16/03/1998 18 1 16460 317.00 31/12/2016 England 1 0.5 317.00 158.50
L 26/11/1990 26 1 16690 321.00 31/12/2016 England 1 1 321.00 321.00
M 23/07/1992 24 1 17190 331.00 31/12/2016 England 1 1 331.00 331.00
N 30/05/1994 22 1 17220 331.00 31/12/2016 England 1 0.5 331.00 165.50

Is it possible to build this into the formula within the final payout?

Thanks

Shiraz

Hi Ali,

Thanks for picking this up. I have tried this but the formula gives me an error. My final formula looks like this:

=IF($H21="Northern Ireland",MIN(J21*K21,15000),MIN(J21*K21,14670))

H = Location
J = Week's relevant for calculation
K = Weekly pay out (caped)

I have tried to do it using IF AND but again, i receive an error.

Any suggestions?

Thanks

That's brilliant, it worked. Thank you so much

Shiraz

Just a little change of formula in cell M5 will do... M5 = MAX(MIN($D5-22-$N5,$J5,J5-N5),0).

Hi -Please can you send me the attached file as I can't open it