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.
Related resources
Replies (13)
Please login or register to join the discussion.
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.
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.
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
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.
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!
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.
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.
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.
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!
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.