PAYE Tax Calculator

evilmouse

Hi,

I am trying to make a little PAYE tax calculator in excel where I can log my overtime and for it to basically list each line on my payslip.

It's working so far apart from the tax deduction.

My Tax code is 1106L meaning the first £11,060 of my is tax free?
After this (up to 45k) should be 20%, after that it will be 40% (up to a figure i won't earn)

My total earning for last month is £3,089.06.
The tax I was charged on this was £439.60.

My calculator is calculating the tax on this to be £433.47

(£3089.06-(£11060/12))*0.2 = £433.48 (£6.12 less than my payslip)

I can see on my payslip that I have a medical benefit that I think I remember people saying you get taxed on.

This is shown as £31.42, if tax on this is 20% then this would be £6.28, i'm not sure why there would be 16p difference on this.
It doesn't sound like much but I want something as accurate as I can get.


Also another question, I have quite a bit of overtime coming up, with my calculator I should be able to work our how much over the higher rate tax bracket I would get.
Would you guys suggest I pay in more to my pension to avoid getting any salary over £45k charged at 40% tax? Would it work out paying no extra tax on that if I change it?

With a calculator like how I want I will be able to work out exactly how much I should be taking home and will be able to update my pension % each month to avoid higher rate tax.

Dan

Kynthia

Paying extra into a pension on earnings in the higher rate tax bracket is often a good idea. Would you be doing this through your employers scheme or into a private pension/SIPP? Will your employer match any increased contributions?

Dazed_and_confused

PAYE tax deductions are always calculated after an element of rounding so in your example you have
Salary 3089.06 + Medical benefit 31.42 = 3120.48
Less tax free amount for April £922.43 (based on tax code 1106L, by March 2018 this will work out to be around £11069 not £11060)
Amount to be taxed = £2198.05
Amount actually taxed = £2198 x 20% = £439.60

Pension is often seen as very tax effective once you get into the 40% band. If it is a work pension deducted at source then it simply reduces the amount of taxable pay each month.

If private pension/sipp you get basic rate tax relief from the pension provider i.e. you pay £80 which becomes £100 in your pension fund and you then claim the extra tax relief from HMRC which in this example would be £20 you get, it doesn't go into your pension fund. You end up with £100 in your pension for an overall outlay of £60 (assuming you pay enough 40% tax).

getmore4less

As well as the rounding as above tou also have the issue there are two ways to work out PAYE some use the tables.

anamenottaken

evilmouse wrote: »

Hi,

Also another question, I have quite a bit of overtime coming up, with my calculator I should be able to work our how much over the higher rate tax bracket I would get.
Would you guys suggest I pay in more to my pension to avoid getting any salary over £45k charged at 40% tax? Would it work out paying no extra tax on that if I change it?

With a calculator like how I want I will be able to work out exactly how much I should be taking home and will be able to update my pension % each month to avoid higher rate tax.

Dan

So you expect to earn more than £7,500 as overtime (or a possible pay rise?) during this tax year?

Is it for budgeting reasons that you want to make monthly contribution adjustments?

BarneyJ87

Dazed_and_confused wrote: »

PAYE tax deductions are always calculated after an element of rounding so in your example you have
Salary 3089.06 + Medical benefit 31.42 = 3120.48
Less tax free amount for April £922.43 (based on tax code 1106L, by March 2018 this will work out to be around £11069 not £11060)
Amount to be taxed = £2198.05
Amount actually taxed = £2198 x 20% = £439.60

Pension is often seen as very tax effective once you get into the 40% band. If it is a work pension deducted at source then it simply reduces the amount of taxable pay each month.

If private pension/sipp you get basic rate tax relief from the pension provider i.e. you pay £80 which becomes £100 in your pension fund and you then claim the extra tax relief from HMRC which in this example would be £20 you get, it doesn't go into your pension fund. You end up with £100 in your pension for an overall outlay of £60 (assuming you pay enough 40% tax).

Awesome answer, really helped me out with my PAYE calculations, thanks! Any idea why in some months I seem to get a 20p anomaly? Using the information you've given, and my tax code of 1185L, for the last few months I've noticed a few anomalies ;

April
Taxable Pay; £2927.20
Calculated PAYE; £387.60
Actual PAYE; £387.60
Difference; Nil

May
Taxable Pay; £1927.82
Calculated PAYE; £385.40
Actual PAYE; £385.60
Difference; +20p

Jun
Taxable Pay; £1832.50
Calculated PAYE; £366.40
Actual PAYE; £366.60
Difference; +20p/B]

Jul
Taxable Pay; £1785.50
Calculated PAYE; £357.00
Actual PAYE; £357.00
Difference; NilB]

Aug
Taxable Pay; £1785.50
Calculated PAYE; £357.00
Actual PAYE; £357.20
Difference; +20p

Sep
Taxable Pay; £2692.09
Calculated PAYE; £538.40
Actual PAYE; £538.40
Difference; Nil

Oct
Taxable Pay; £2063.88
Calculated PAYE; £412.60
Actual PAYE; £412.80
Difference; +20p

Nov
Taxable Pay; £2371.36
Calculated PAYE; £474.20
Actual PAYE; £474.20
Difference; Nil

Dec
Taxable Pay; £1799.48
Calculated PAYE; £359.80
Actual PAYE; £359.80
Difference; Nil

Jan
Taxable Pay; £1799.48
Calculated PAYE; £359.80
Actual PAYE; £356.00
Difference; +20p

I appreciate 20p difference is very unimportant, I'm just curious as to why there is a different from what I calculated, especially when for example in Jul & Aug, and Dec & Jan when I was paid the same amount, but taxed differently?

I'm just a bit of an excel nerd who doesn't like lose ends.... Any ideas?

chrisbur

getmore4less wrote: »

As well as the rounding as above tou also have the issue there are two ways to work out PAYE some use the tables.

Both methods give exactly the same answer. All payroll programs use the same PAYE figures as the manual tables.

National insurance does have two methods one an exact calculation and the other using tables which have bands of earnings and the NI is based on a calculation of the mid-point of these bands. These days very few employers use these tables.

chrisbur

BarneyJ87 wrote: »

I appreciate 20p difference is very unimportant, I'm just curious as to why there is a different from what I calculated, especially when for example in Jul & Aug, and Dec & Jan when I was paid the same amount, but taxed differently?

I'm just a bit of an excel nerd who doesn't like lose ends.... Any ideas?

As explained the amount that you get tax free comes from a set of tables which use a figure for every block of 10 tax codes. So 1150L
covers everyone from £11500 allowance to £11509, it is then adjusted a bit more so that you have two allowances one for weekly which can be divided by 52 and one for monthly which can be divided by 12 to give a weekly and monthly figure that does not need rounding. For example monthly figure for 1150L is £11509.08 which when divided by 12 gives £959.09.
When tax is calculated for month 1 the tax allowance (1/12 of your annual figure) is deducted from taxable pay and what is left is what you pay tax on. Except that tax is only deducted from full pounds eg taxable element of pay is £1500.99 you only pay tax on £1500.00
When month 2 is done taxable earnings for months 1 and 2 are added together (so although you only paid tax on £1500 the full £1500.99 is carried forward) from this the tax free allowance for month 2 (2/12 of annual allowance) is deducted and again tax worked out on the full pounds. Tax paid in month 1 is deducted from this to give tax due for month 2.
This continues for each month, your tax is not just calculated on that months earnings it is calculated on your total earnings to date less tax allowance to date to give a tax due to date figure from which is deducted a tax paid so far figure to give tax due this month.

It is these odd pence that are not taxed each month but they are carried forward that every so often build up to a full pound and you pay 20% on that full pound so you see an extra 20P tax.
That is also why you might notice that if you only pay tax at 20% your tax that you pay is always divisible by 20P

Dazed_and_confused

Not much I could add to chrisbur's explanation!

BarneyJ87

chrisbur wrote: »

As explained the amount that you get tax free comes from a set of tables which use a figure for every block of 10 tax codes. So 1150L
covers everyone from £11500 allowance to £11509, it is then adjusted a bit more so that you have two allowances one for weekly which can be divided by 52 and one for monthly which can be divided by 12 to give a weekly and monthly figure that does not need rounding. For example monthly figure for 1150L is £11509.08 which when divided by 12 gives £959.09.
When tax is calculated for month 1 the tax allowance (1/12 of your annual figure) is deducted from taxable pay and what is left is what you pay tax on. Except that tax is only deducted from full pounds eg taxable element of pay is £1500.99 you only pay tax on £1500.00
When month 2 is done taxable earnings for months 1 and 2 are added together (so although you only paid tax on £1500 the full £1500.99 is carried forward) from this the tax free allowance for month 2 (2/12 of annual allowance) is deducted and again tax worked out on the full pounds. Tax paid in month 1 is deducted from this to give tax due for month 2.
This continues for each month, your tax is not just calculated on that months earnings it is calculated on your total earnings to date less tax allowance to date to give a tax due to date figure from which is deducted a tax paid so far figure to give tax due this month.

It is these odd pence that are not taxed each month but they are carried forward that every so often build up to a full pound and you pay 20% on that full pound so you see an extra 20P tax.
That is also why you might notice that if you only pay tax at 20% your tax that you pay is always divisible by 20P

Thanks chrisbur, that really helped tie up the loose ends. From what you explained, I concluded that on my tax code of 1185L, the actual amount for calculations is £11,859.12, and with the new tax code I got earlier this month of 1304M, the figure is £13049.04. This seems to satisfy all of my calculations.

Thanks again for your help! :beer:

chrisbur

BarneyJ87 wrote: »

Thanks chrisbur, that really helped tie up the loose ends. From what you explained, I concluded that on my tax code of 1185L, the actual amount for calculations is £11,859.12, and with the new tax code I got earlier this month of 1304M, the figure is £13049.04. This seems to satisfy all of my calculations.

Thanks again for your help! :beer:

Actual figure for 1304M is £13049.16