Excel formula for Payslip

truescot

Not sure if this is the right place to post or if anyone can help.

I work shifts in the NHS and pay is different every month. Decided it's time to keep track of my projected monthly salary each month, and also make sure everything is in order from my employer.

I have devised an Excel Worksheet which is identical to my Payslip except I cant get the right formula for how much Tax/National Insurance I pay. I have been on HMRC website and I know the thresholds and allowances and I know my tax code and what that means etc, but does anyone know the basic formula to put in the "tax" and "National Insurance" columns?

I figured if anyone else does this, people on this board do!!

fengirl_2

Not sure if this is possible with a spreadsheet, but you need to take your code number and add a 9 to the end, eg code 543L, makes 5439. Divide this by 12 and you get the tax free pay each month.
Assuming you are a basic rate taxpayer, the remainder is taxed at 20%. PAYE is cumulative however, so you always need to take into account your pay and tax deducted back to 6 April.
For NIC, you deduct 420 per month from your pay and the rest is charged at 11%. This is not cumulative.

CLAPTON

if you're a member of the NHS pension fund you will be contracted out of the second state pension fund so your NI deductions will be 9.4% of everything over 453 per month and under 3337 then its 1%

Altarf

A handy website I have used a few times to work out tax and NI that allows you to input your wages by year, month, week, day, etc, is http://listentotaxman.com/

It might help you to 'tweak' your spreadsheet.

truescot

jimmo wrote: »

I just think you are barking up the wrong tree if you think that the deductions are wrong. If anything does go wrong its more likely to be an incorrect input of gross pay.

I have no doubt that the tax/NI that my employer calculates is correct, the point is more that I wanted to be able to make projections of my future monthly wages so I can predict things.So knowing how to calculate them myself would be usefuil because my gross pay can vary enormously.

I've managed to get it fairly accurate tho so thanks for your help everyone.

mrposhman

I've got one of these and I can get the tax to work out everytime but the NI I seem to struggle with. I use the 9.4% but this always comes out slightly wrong (its only pence but its one of those things that drives me crazy).

Anyway, like someone has said it depends how good you are with excel.

If you managed it then thats ok but if you still need a hand let me know and I will give you some formulae. It pretty much just uses If statements rather than lookup tables though.

It also depends on whether you pay pension contributions as if your gross pay regularly changes then I think your pension contributions do too. Do you know at what rate your pension contributions are calculated?

truescot

mrposhman wrote: »

I use the 9.4% but this always comes out slightly wrong

If you managed it then thats ok but if you still need a hand let me know and I will give you some formulae.

Do you know at what rate your pension contributions are calculated?

I also use the 9.4% for NI and like you it 's always pence out and this drives me mad! Why is it not accurate?

I can get my pension exact as it is 6.5% and I also get an Annual leave Supplement which is related to my unsocial hours enhancements, and these are always exactly right.

My problem is with the tax. Do you have the formula you use to get the tax right? My tax code is 551L (although I know this will change in September).:rolleyes:

meester

truescot wrote: »

I also use the 9.4% for NI and like you it 's always pence out and this drives me mad! Why is it not accurate?

I can get my pension exact as it is 6.5% and I also get an Annual leave Supplement which is related to my unsocial hours enhancements, and these are always exactly right.

My problem is with the tax. Do you have the formula you use to get the tax right? My tax code is 551L (although I know this will change in September).:rolleyes:

There are NIC tables. It's absolutely bonkers. They usually don't just say £1500 salary minus £450 allowance * 9.4%, they use a complicated set of tables.

Info here

http://www.businesslink.gov.uk/bdotg/action/detail?type=RESOURCES&itemId=1073791182