Share this content

Spreadsheet for CJRS claim

Care to share?

Didn't find your answer?

HMRC have produced a CJRS calculator here:

https://www.tax.service.gov.uk/job-retention-scheme-calculator/

Unfortunately it is slow, cumbersome, only works for one employee at a time & doesn't work for more than the simplest scenario and, if an employer 'tops up' you get:

We’re unable to calculate claims when pay is topped up

This will be available in a future version.

Somebody on here kindly offered to share a spreadsheet they had developed for CJRS claims, which will, no doubt, be more sophistictaed than the HMRC version. Unfortunatley I can't seem to find that post at present.

Wondering if anyone is prepared to share with the community? Maybe via a dropbox link so it doesn't involve them in the hassle of responding to PMs, e-mails etc.

No doubt we could all, in a constructive way, suggest & share tweaks as we find more complicated scenarios.

 

Replies (13)

Please login or register to join the discussion.

By SteveHa
22nd Apr 2020 09:35

I recall the post but no idea by whom. However, I'm currently working on a spreadsheet which I'm happy to share once I know it's working properly.

Thanks (2)
Replying to SteveHa:
avatar
By Wanderer
22nd Apr 2020 09:39

Fantastic!

Thanks (0)
avatar
By Paul Hawes
22nd Apr 2020 11:59

I've made a very basic one if anyone needs it https://www.dropbox.com/s/njsgbkk23t4tw3m/Furlough%20calculator.xlsx?dl=0

Just type in the correct days and gross pay, remove top up if not needed and should be good to go. I didn't bother doing restriction to nil on NI and pension so if it comes up with negative figures you can just ignore.

Thanks (3)
Replying to Paul Hawes:
A Putey FACA
By Arthur Putey
22nd Apr 2020 13:42

Trouble with home made calculators (like mine) is they take some refining. So while I'm not criticising you for trying, is the NI claim amount correct? Your sheet gives 295.04 (based on 20/21 ST of 732 not the 19-20 of 719 that relates to the Feb base pay) but the HMRC calculator gives this result (I assumed 15 days furlough from 16 April)

What you can claim for this employee
£1250.10
Amount paid to this furloughed employee
£173.74
Employer National Insurance contributions
£29.70
Employer minimum pension contributions

Thanks (0)
Replying to Arthur Putey:
avatar
By Paul Hawes
22nd Apr 2020 13:55

I wouldn't be able to tell you without knowing what period you are trying to claim for and what amount is being paid for their working days/usual gross and if there is top up. This isn't to be used to cover a period longer than a month.

Thanks (0)
Replying to Paul Hawes:
A Putey FACA
By Arthur Putey
22nd Apr 2020 14:16

Actually your calc is correct, its just a matter of interpretation, I put £4000 in the HMRC calculator not £8000. But it shows the risk of using other people's solutions I suppose!

Thanks (0)
Replying to Paul Hawes:
A Putey FACA
By Arthur Putey
22nd Apr 2020 13:51

Also if you change the top up to 0 the NI claim goes up to 311.74

Thanks (0)
Replying to Arthur Putey:
A Putey FACA
By Arthur Putey
22nd Apr 2020 14:17

Again ignore that its just counter intuitive.

Thanks (0)
avatar
By paulconnon
23rd Apr 2020 11:20

Don't forget to take into account any Employment Allowance claimed, which in many cases will cancel out the Employer's NI. I think a lot of people will forget about this. The HMRC calculator doesn't even ask the question "are you claiming EA" even though it is in the guidance.

Thanks (0)
avatar
By AGreen84
23rd Apr 2020 11:50

I just did out a basic spreadsheet to figure out the 80% of wages. Then put this figure into my payroll system, got it to calculate the deductions for pension and NI. Note down the figures I get. I then amend the payroll if top-ups are being paid. But as mentioned by someone, most of my clients are getting the employment allowance so there's no need to calculate the Employers NI anyway.

Thanks (0)
By SteveHa
23rd Apr 2020 13:51

I have to be honest, whilst I'm using my own, I find I'm having to tweak it for each company. It is far from the nice simple "punch in some numbers and you get the answer" spreadsheet that I was hoping for.

Thanks (0)
avatar
By [email protected]
23rd Apr 2020 14:42

OK Heres mine. "as is" no warranties implied or expressed
https://app.box.com/s/6c73dg23ucoz7gu24x9y0lp0qcr0wpj9

You'll still need to work out your initial reference pay, and do one for each employee.
It will work out furlough, NI apportionment and pension apportionment.
I did it for monthly employees with or without topup
If you need other pay periods feel free to adapt it
Inputs in Yellow, Outputs in Orange. There are no hidden cells!

Thanks (0)
Replying to [email protected]:
avatar
By Great1
01st May 2020 00:11

Thank you Paul. This is the best calculator I have seen yet!

I wasn't able to convert it for use in a weekly payroll scheme. Do you have another version for weekly payroll which operates in a similar way? I would be eternally grateful!

Kind regards and stay safe.

Thanks (0)
Share this content