UK Tax spreadsheet

[maix27]

Hi guys.

Newbie here looking for some help with excel!

I'm begining to love this program but i'm still rubish with it!

I work in a job where my salary goes up and down every month and i want to be able to put my salary into one cell and it tell me hom much tax and NI (i'm uk based) will be taken out of my salary.

I have no idea as to where to start...

I think i need to creat a table with all the relevent brackets but i wouldn't know how to the calculate from this.

Can anyone help me? Has anyone got any examples they could show me?

Thanks for any help in advance!

[corinereyes]

Hi maix27,

Maybe this link will help you.

http://www.mcgimpsey.com/excel/variablerate.html

Hope it will help you get started.

[maix27]

that's cool, but it doesn't seem to be yielding the right figures... can you see what i'm doing wrong?

[EdMac]

Hi maix27,

Unfortunately it's not as simple as you want it to be.

The amount deducted depends on both what you are being paid in a month as well as your total earning for the year to date, the number of months left in the year and your tax free amount.

The Inland Revenue produce a big set of PAYE tables to enable employers to calculate this.

Without reproducing and using the tables, you won't be able to self calculate.

Ed

[daddylonglegs]

You have a couple of problems here.

As it stands you are calculating 10% of all earnings from 2230 to 34600 when it should be 22% in that bracket:

You need to make J17 = 0, J18 = 2230 and J19 = 34600

Also, you already have the differential rates in column K so you need to use these figures not column L, you don't need those.

Finally, you need to deduct the personal allowance ( which is £5,225 in 2007/2008) from the total earnings so you end up with this calculation

=SUMPRODUCT(--(H17-G13>{0;2230;34600}), H17-G13-{0;2230;34600}, {0.1;0.12;0.18})

I've shown the values but obviously you can just use the ranges J17:J19 and K17:K19.

If you pay into a company pension scheme this amount would also be tax free and should be deducted too

[daddylonglegs]

Just saw Ed's comments above.

What he says is true but you can get pretty accurate figures with the above calculations, assuming your pay is similar for every month of the year.

If not then you can also take into account your earnings and tax paid so far this tax year and calculate accordingly. You can't get an exact figure without the tax tables but I manage to calculate my earnings correctly to within a pound or two each month (including a calculation for NI)