Working out UK tax

ive set up a spreadsheet to calculate my income per week. I now would like
to make my tax and national insurance boxes to be calculated automatically.
meaning all im required to do is type in my hours worked for that week.

The problem is finding a formula that can do this. The UK tax and national
insurance is directly proportional to how much you earn. Not one fixed
percentage.

Let me show us some numbers: -

Payslip 1

Total Pay = 297.62
Tax = 40.65 = 13.7%
N.I = 22.73 = 7.6%

Payslip 2

Total Pay = 242.63
Tax = 28.56 = 11.77%
N.I = 16.67 = 6.87%

as you can see the percentage increases with the wage so id like to know how
to get around this. all i want to be able to do is type in my hours worked
for that week. then that and my hourly rate shou,d do the rest of the work

i dont know if ive expplained this too well or if you may need more
information to possibly help me. but id really appreciate any feedback.

I can email my spreadsheet if anybody is willing to take a look at it. give
me a few pointers on how to do it differently

take care all

rob day

hawkeye uk wrote:
|| ive set up a spreadsheet to calculate my income per week. I now
|| would like to make my tax and national insurance boxes to be
|| calculated automatically. meaning all im required to do is type in
|| my hours worked for that week.
||
|| The problem is finding a formula that can do this. The UK tax and
|| national insurance is directly proportional to how much you earn.
|| Not one fixed percentage.
||
|| Let me show us some numbers: -
||
|| Payslip 1
||
|| Total Pay = 297.62
|| Tax = 40.65 = 13.7%
|| N.I = 22.73 = 7.6%
||
|| Payslip 2
||
|| Total Pay = 242.63
|| Tax = 28.56 = 11.77%
|| N.I = 16.67 = 6.87%
||
|| as you can see the percentage increases with the wage so id like to
|| know how to get around this. all i want to be able to do is type in
|| my hours worked for that week. then that and my hourly rate shou,d
|| do the rest of the work
||
|| i dont know if ive expplained this too well or if you may need more
|| information to possibly help me. but id really appreciate any
|| feedback.
||
|| I can email my spreadsheet if anybody is willing to take a look at
|| it. give me a few pointers on how to do it differently
||
|| take care all
||
|| rob day

It's not quite as straight forward as that.
As tax due is calculated on a cumulative basis throughout the tax year, the
process is as follows:
First you need to calculate your taxable pay for the year to date. This is
done by taking the Free Pay for the current period as defined by your tax
code, from the total pay to date including the current period. You then
calculate the tax due on that amount. Subtract the tax you have paid up to
and including the last period, and that is the tax due for this current
period.

National Insurance IS a straight forward percentage of the period gross pay,
but you need to split it between Emploees Contribution and Employers
Contribution.

Have you got a set of Tax Tables?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

> It's not quite as straight forward as that.
> As tax due is calculated on a cumulative basis throughout the tax year,
> the process is as follows:
> First you need to calculate your taxable pay for the year to date. This is
> done by taking the Free Pay for the current period as defined by your tax
> code, from the total pay to date including the current period. You then
> calculate the tax due on that amount. Subtract the tax you have paid up to
> and including the last period, and that is the tax due for this current
> period.
>
> National Insurance IS a straight forward percentage of the period gross
> pay, but you need to split it between Emploees Contribution and Employers
> Contribution.
>
> Have you got a set of Tax Tables?
>

Right, thats useful. Cheers. No I dont have any tables. Do you have a link
as to where to obtain one?

thanks for the reply

hawkeye uk wrote:
||| It's not quite as straight forward as that.
||| As tax due is calculated on a cumulative basis throughout the tax
||| year, the process is as follows:
||| First you need to calculate your taxable pay for the year to date.
||| This is done by taking the Free Pay for the current period as
||| defined by your tax code, from the total pay to date including the
||| current period. You then calculate the tax due on that amount.
||| Subtract the tax you have paid up to and including the last period,
||| and that is the tax due for this current period.
|||
||| National Insurance IS a straight forward percentage of the period
||| gross pay, but you need to split it between Emploees Contribution
||| and Employers Contribution.
|||
||| Have you got a set of Tax Tables?
|||
||
|| Right, thats useful. Cheers. No I dont have any tables. Do you have
|| a link as to where to obtain one?
||
|| thanks for the reply

Try your local Tax Office. I'm not actually sure whether they give out tax
tables to people who are not registered with them as an Employer but they
might! Good Luck!

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

http://www.mypersonalfinances.co.uk/data/taxtables/

"hawkeye uk" <[email protected]> wrote in message
news:[email protected]...
:
:
: > It's not quite as straight forward as that.
: > As tax due is calculated on a cumulative basis throughout the tax year,
: > the process is as follows:
: > First you need to calculate your taxable pay for the year to date. This is
: > done by taking the Free Pay for the current period as defined by your tax
: > code, from the total pay to date including the current period. You then
: > calculate the tax due on that amount. Subtract the tax you have paid up to
: > and including the last period, and that is the tax due for this current
: > period.
: >
: > National Insurance IS a straight forward percentage of the period gross
: > pay, but you need to split it between Emploees Contribution and Employers
: > Contribution.
: >
: > Have you got a set of Tax Tables?
: >
:
: Right, thats useful. Cheers. No I dont have any tables. Do you have a link
: as to where to obtain one?
:
: thanks for the reply
:
:

Lady Layla wrote:
|| http://www.mypersonalfinances.co.uk/data/taxtables/
||

Thank you VERY much for that link!


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Gordon wrote:
|| Lady Layla wrote:
|||| http://www.mypersonalfinances.co.uk/data/taxtables/
||||
||
|| Thank you VERY much for that link!
||

Having said that, there doesn't appear to be any Free Pay tables there,
unless I've missed them.


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

In message <#[email protected]>, Gordon
<[email protected]> writes
>Gordon wrote:
>|| Lady Layla wrote:
>|||| http://www.mypersonalfinances.co.uk/data/taxtables/
>||||
>||
>|| Thank you VERY much for that link!
>||
>
>Having said that, there doesn't appear to be any Free Pay tables there,
>unless I've missed them.

<http://www.inlandrevenue.gov.uk/rates/it.htm>

Easy..

If tax code is 474L that equates to £4745 pa divided by 52 = £91.25 pw
In other words multiply the tax code by 10 and add 5 = free pay

So if you were calculating tax on say £200 pw as at week 10 in the
current tax year the Free Pay would be 4745/52 x 10 = 912.50

Gross pay say 200 pw x 10 wks 2000.00
Free pay (as above) 912.50
Taxable pay 1087.50
less tax at 10% on 2020 pro rata ytd = 2020/52 x 10 = 388.46 = 38.84
less tax at 22% on balance (up to 31400 pa) = 699.04 = 153.79
Tax payable ytd week 10 192.63
deduct whatever tax was payable cumulative to the previous week
balance = tax payable this week

Note that tax codes ending in W1 or M1 (week 1 or month 1) are not
cumulative.


--
squire