Redundancy Pay Calculations

Hi

Im trying to calculate the redundancy pay for 200+ staff


The formula im struggle with which seems to round up some of the week entitlements is

=IF(E2<2,0,SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:"&MIN(E2,20)))+C2-MIN(E2,20),{0,22,41;0.5,1,1.5})))


Header are: and its the redundancy entitlement that is is out by 0.5 of a week on some staff (not consistant)


Start Date Years of Service Annual Pay Weekly Pay Weekly Pay (Statutory Cap) Termination Date Redundancy Entitlement Redundancy Pay (Cap £16,140) Statutory Notice Period (Cap 12 weeks)

attached copy of spreadsheet.

thanks in advance

Hi



Hi

Hi

Hi


The government website states 27 weeks; the spreadsheet shows 27.5 week

Thanks




Skip to main content
GOV.UK
Search
Coronavirus (COVID-19)
Guidance and support
Calculate your employee's statutory redundancy pay
Statutory redundancy payment: £9,346.86 (£9,346.86 in Northern Ireland).
How it’s worked out
Your employee’s entitlement is 27.0 weeks.

They get:

0.5 week’s pay for each full year they were under 22
1 week’s pay for each full year they were 22 or older, but under 41
1.5 week’s pay for each full year they were 41 or older
Length of service is capped at 20 years and weekly pay at £538 (£560 in Northern Ireland). Years of service or earnings over these amounts are not included in the calculation.

The maximum amount of statutory redundancy pay is £16,140 (£16,800 in Northern Ireland).

Redundancy pay under £30,000 is not taxable.

Next steps
Read the guide to Making staff redundant

Previous answers
Start again

Question Answer Change answer
What date was your employee made redundant? 1 September 2020 Changeanswer to "What date was your employee made redundant?"
How old was your employee on the date they were made redundant? 55 Changeanswer to "How old was your employee on the date they were made redundant?"
Number of years they’ve worked for you 20.0 Changeanswer to "Number of years they’ve worked for you"
What is their weekly pay before tax and any other deductions? £346.18 Changeanswer to "What is their weekly pay before tax and any other deductions?"

The formula im struggle with which seems to round up some of the week entitlements is...
=IF(E2<2,0,SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:"&MIN(E2,20)))+C2-MIN(E2,20),{0,22,41;0.5,1,1.5})))
Header are: and its the redundancy entitlement that is is out by 0.5 of a week on some staff (not consistant)

FWIW, all of your results look aligned to the gov logic, at least to me
if you extrapolate your data per row, and calculate each year manually, and aggregate, you will get the same results; which do you think are wrong?

Above said, you could 'simplify' your current formula a little (and remove the volatility) with:

Formula:

Please Login or Register  to view this content.

as per your existing calculation, the above creates an array of ages, 1 for each year of service (capped at 20 years), applies the relevant allowance for each of those values, and aggregates the results.

Hi,

In J2:
=if(e2<2,0,sumproduct(lookup(row(indirect("1:"&min(e2,20)))+(c2-1)-min(e2,20),{0,22,41;0.5,1,1.5})))

Hi

Sorry its still out, even removing the volatility out as suggested

HMRC Calculates line 3 as 27. The spreadsheet calculates it to 27.5 although line 2 calculates correctly at 29. If I change the formula to suggested it makes line 2 incorrect.


It should calculate (HMRC):

Line 2

1x1.0 - 19x1.5


Line 3

6x1.0 - 14x1.5


Thanks

Hi, the only way I can get it to match is to assume that the HMRC wording is ambiguous, to extent that the upper 'full year' boundary starts after you turn 41 (i.e. from age 42+)

If you modify the LOOKUP to use 42 as upper boundary, as opposed to 41, you will get the same results as HMRC

Given the ambiguity, and limited sample size, I would suggest running across some more varied dates in / around that age bracket just to prove out the logic.

Note: the calculation is not an Array so it does not require the CSE entry -- just Enter.

Thanks everyone for your help.

I have used the formula that you recommended, however some of my results appears to be skewed, could someone take a look and let me know if I am missing something...thanks in advance