uk tax calculator

hello i'm trying to make a wage calculator in excel that works out your tax
and NI ( UK ) i think i need to work a formula out on my topline but not
sure how to do this as my wages are diffrent each week.
please help

A UK tax calculator is quite complex, because as well as knowing all of the
tax bands and the rates, you have to take into account any tax free
deductions (such as pension payments), but worst of all, as I understand it
your tax is calculated based upon the assumption that whatever you earn this
month/week will be earnt every month/week for the rest of the year, so you
need tax paid to date, the number of months/weeks to go etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" <[email protected]> wrote in message
news:[email protected]...
> hello i'm trying to make a wage calculator in excel that works out your
tax
> and NI ( UK ) i think i need to work a formula out on my topline but not
> sure how to do this as my wages are diffrent each week.
> please help

I pay an accountant to do it !!

Pete

But I bet you only do it once a year, this guy wants it once a week <g>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pete_UK" <[email protected]> wrote in message
news:[email protected]...
> I pay an accountant to do it !!
>
> Pete
>

thanks guys for taking the time to respond to this post.
think i'm getting somewhere...
when i calculate my gross earnings for a week i then divide by a % this
however changes with diffrent amounts of pay.. but would like it to auto
calculate.
p.s. it dosent have to be 100% right just a rough estamate would be nice.
i've even been to the inland revenue site and there calculator gets me
somewhere near old payslips.

If approximate is close enough then do this

A1: earning for this month
B1: tax allowance (e.g. 4895)
C1: 10%
B2: 2090
C2: 22%
B3: 32400
C3: 40%
A2: =MAX(($A$1-SUM($B$1:B1)/12),0)*C1
A3: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
A4: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)

and the answer in A5

=ROUND($A$1-SUM(A2:A4),2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" <[email protected]> wrote in message
news:[email protected]...
> thanks guys for taking the time to respond to this post.
> think i'm getting somewhere...
> when i calculate my gross earnings for a week i then divide by a % this
> however changes with diffrent amounts of pay.. but would like it to auto
> calculate.
> p.s. it dosent have to be 100% right just a rough estamate would be nice.
> i've even been to the inland revenue site and there calculator gets me
> somewhere near old payslips.
>

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...

> B1: tax allowance (e.g. 4895)

Children grown up and left home then Bob? <g>

--


Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk

I never got a tax allowance for my girls!

Actually, I had no idea what the number was, had to look it up on the IR
site <G>

Bob

"Sandy Mann" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
>
> > B1: tax allowance (e.g. 4895)
>
> Children grown up and left home then Bob? <g>
>
> --
>
>
> Sandy
> In Perth, the ancient capital of Scotland
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
>

Thanks Bob it works out very well within £4.96 to £8.45 so thats great. Thank
You Very Much.
just 1 little thing A3 and A4 are the same is that suposed to be correct( it
works all the same )
and what are the values in b2 and b3
sorry to be a pain

"Bob Phillips" wrote:

> If approximate is close enough then do this
>
> A1: earning for this month
> B1: tax allowance (e.g. 4895)
> C1: 10%
> B2: 2090
> C2: 22%
> B3: 32400
> C3: 40%
> A2: =MAX(($A$1-SUM($B$1:B1)/12),0)*C1
> A3: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
> A4: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
>
> and the answer in A5
>
> =ROUND($A$1-SUM(A2:A4),2)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "DarkNight" <[email protected]> wrote in message
> news:[email protected]...
> > thanks guys for taking the time to respond to this post.
> > think i'm getting somewhere...
> > when i calculate my gross earnings for a week i then divide by a % this
> > however changes with diffrent amounts of pay.. but would like it to auto
> > calculate.
> > p.s. it dosent have to be 100% right just a rough estamate would be nice.
> > i've even been to the inland revenue site and there calculator gets me
> > somewhere near old payslips.
> >
>
>
>

No, sorry, A4 should be

=MAX($A$1-SUM($B$1,B3)/12,0)*(C3-C2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob it works out very well within £4.96 to £8.45 so thats great.
Thank
> You Very Much.
> just 1 little thing A3 and A4 are the same is that suposed to be
correct( it
> works all the same )
> and what are the values in b2 and b3
> sorry to be a pain
>
> "Bob Phillips" wrote:
>
> > If approximate is close enough then do this
> >
> > A1: earning for this month
> > B1: tax allowance (e.g. 4895)
> > C1: 10%
> > B2: 2090
> > C2: 22%
> > B3: 32400
> > C3: 40%
> > A2: =MAX(($A$1-SUM($B$1:B1)/12),0)*C1
> > A3: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
> > A4: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
> >
> > and the answer in A5
> >
> > =ROUND($A$1-SUM(A2:A4),2)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "DarkNight" <[email protected]> wrote in message
> > news:[email protected]...
> > > thanks guys for taking the time to respond to this post.
> > > think i'm getting somewhere...
> > > when i calculate my gross earnings for a week i then divide by a %
this
> > > however changes with diffrent amounts of pay.. but would like it to
auto
> > > calculate.
> > > p.s. it dosent have to be 100% right just a rough estamate would be
nice.
> > > i've even been to the inland revenue site and there calculator gets me
> > > somewhere near old payslips.
> > >
> >
> >
> >

Thanks for your time and help bob, just gonaa play about abit more with my
calculations then might even find out where i can post the finished version.
thanks again with all your help

"Bob Phillips" wrote:

> No, sorry, A4 should be
>
> =MAX($A$1-SUM($B$1,B3)/12,0)*(C3-C2)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "DarkNight" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Bob it works out very well within £4.96 to £8.45 so thats great.
> Thank
> > You Very Much.
> > just 1 little thing A3 and A4 are the same is that suposed to be
> correct( it
> > works all the same )
> > and what are the values in b2 and b3
> > sorry to be a pain
> >
> > "Bob Phillips" wrote:
> >
> > > If approximate is close enough then do this
> > >
> > > A1: earning for this month
> > > B1: tax allowance (e.g. 4895)
> > > C1: 10%
> > > B2: 2090
> > > C2: 22%
> > > B3: 32400
> > > C3: 40%
> > > A2: =MAX(($A$1-SUM($B$1:B1)/12),0)*C1
> > > A3: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
> > > A4: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
> > >
> > > and the answer in A5
> > >
> > > =ROUND($A$1-SUM(A2:A4),2)
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "DarkNight" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > thanks guys for taking the time to respond to this post.
> > > > think i'm getting somewhere...
> > > > when i calculate my gross earnings for a week i then divide by a %
> this
> > > > however changes with diffrent amounts of pay.. but would like it to
> auto
> > > > calculate.
> > > > p.s. it dosent have to be 100% right just a rough estamate would be
> nice.
> > > > i've even been to the inland revenue site and there calculator gets me
> > > > somewhere near old payslips.
> > > >
> > >
> > >
> > >
>
>
>

Hi Dark Night,

I note that from the start of this tax year the income tax bands have changed to the following

untaxed standard allowance first £5035
10% band next £2150
22% band next £31150

this means that to the nearest £ the weekly rates are as follows

£0-£97 0%
£97-£138 10%
£138-£735 22%
£735+ 40%

National insurance (assuming you're not in a company pension scheme) works like this

£0-£97 0%
£97-£645 11%
£645+ 1%

As Bob says, there are complications with the way Income Tax is calculated but given these rates, if your gross earnings are in cell A1 this formula gives your approximate Income Tax

=SUMPRODUCT(--(A1>{0,97,138,735}),A1-{0,97,138,735},{0,0.1,0.12,0.18})

and this your National Insurance

=SUMPRODUCT(--(A1>{0,97,645}),A1-{0,97,645},{0,0.11,-0.1})

Clearly if you deduct these two amounts from your gross pay you should get
your net pay

...or you can combine the above two formulas in one to give your total deductions

=SUMPRODUCT(--(A1>{0,97,138,645,735}),A1-{0,97,138,645,735},{0,0.21,0.12,-0.1,0.18})

Thanks daddylonglegs, i've adjusted the tax bands.
and tryed the income tax and national insurance but for some reason its way
out £35, which is a real shame.
if i use Bobs example and add together cells a2:a4 it gets somewhere near
tax and cell a2 gets me somewhere near Ni.
also would it be possible for you to post the site where you got the tax
bands, i've been looking and non of it makes any sence.


"daddylonglegs" wrote:

>
> Hi Dark Night,
>
> I note that from the start of this tax year the income tax bands have
> changed to the following
>
> untaxed standard allowance first £5035
> 10% band next £2150
> 22% band next £31150
>
> this means that to the nearest £ the weekly rates are as follows
>
> £0-£97 0%
> £97-£138 10%
> £138-£735 22%
> £735+ 40%
>
> National insurance (assuming you're not in a company pension scheme)
> works like this
>
> £0-£97 0%
> £97-£645 11%
> £645+ 1%
>
> As Bob says, there are complications with the way Income Tax is
> calculated but given these rates, if your gross earnings are in cell A1
> this formula gives your approximate Income Tax
>
> =SUMPRODUCT(--(A1>{0,97,138,735}),A1-{0,97,138,735},{0,0.1,0.12,0.18})
>
> and this your National Insurance
>
> =SUMPRODUCT(--(A1>{0,97,645}),A1-{0,97,645},{0,0.11,-0.1})
>
> Clearly if you deduct these two amounts from your gross pay you should
> get
> your net pay
>
> ...or you can combine the above two formulas in one to give your total
> deductions
>
> =SUMPRODUCT(--(A1>{0,97,138,645,735}),A1-{0,97,138,645,735},{0,0.21,0.12,-0.1,0.18})
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=537126
>
>

Hi Dark Night,

I think you mentioned earnings for the week, so my formulas are based on you inputting your gross weekly earnings whereas Bob's approach assumes monthly earnings.

Assuming you enter the weekly rate my formulas should work give or take a few pence, they certainly work for my earnings, otherwise you could adjust for monthly pay (i.e. 12 times a year) giving this formula for NI

=SUMPRODUCT(--(A1>{0,422,2804}),A1-{0,422,2804},{0,0.11,-0.1})

although the rates are slightly different if you are in a company pension scheme.

This site gives details of bands and allowances for both UK Income Tax and NI

http://www.hmrc.gov.uk/rates/it.htm

sorry to be a pain,
a1=gross pay
b2=payment frequency (52 weekly, 13 montly 26 fortnightly)
how would i implament your formula to work this out
just trying to make the calculator very universal so it can follow me to my
next job which may be montly pay or weekly, at the moment i'm on fortnightly.
and thanks again for taking the time to respond.


"daddylonglegs" wrote:

>
> Hi Dark Night,
>
> I think you mentioned earnings for the week, so my formulas are based
> on you inputting your gross weekly earnings whereas Bob's approach
> assumes monthly earnings.
>
> Assuming you enter the weekly rate my formulas should work give or take
> a few pence, they certainly work for my earnings, otherwise you could
> adjust for monthly pay (i.e. 12 times a year) giving this formula for
> NI
>
> =SUMPRODUCT(--(A1>{0,422,2804}),A1-{0,422,2804},{0,0.11,-0.1})
>
> although the rates are slightly different if you are in a company
> pension scheme.
>
> This site gives details of bands and allowances for both UK Income Tax
> and NI
>
> http://www.hmrc.gov.uk/rates/it.htm
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=537126
>
>

Assuming B2 just contains a number try this for NI

=SUMPRODUCT(--(A1>{0,5044,33540}/B2),A1-{0,5044,33540}/B2,{0,0.11,-0.1})

and this for income tax

=SUMPRODUCT(--(A1>{0,5035,7185,38335}/B2),A1-{0,5035,7185,38335}/B2,{0,0.1,0.12,0.18})

If you wish you could include the figures in a table on your worksheet and amend the formulas to reference these, then you only have to amend the rates and bands each time they cahnge, without altering the formulas.

that works a treat, thanks for you hard work.
1 little daft question the bands in your refering to are they 5035, 7185,
38335 NI
and 5044, 33540 for income tax ?
if so how come there diffrent?

"daddylonglegs" wrote:

>
> Assuming B2 just contains a number try this for NI
>
> =SUMPRODUCT(--(A1>{0,5044,33540}/B2),A1-{0,5044,33540}/B2,{0,0.11,-0.1})
>
> and this for income tax
>
> =SUMPRODUCT(--(A1>{0,5035,7185,38335}/B2),A1-{0,5035,7185,38335}/B2,{0,0.1,0.12,0.18})
>
> If you wish you could include the figures in a table on your worksheet
> and amend the formulas to reference these, then you only have to amend
> the rates and bands each time they cahnge, without altering the
> formulas.
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=537126
>
>

Originally Posted by DarkNight

that works a treat, thanks for you hard work.
1 little daft question the bands in your refering to are they 5035, 7185,
38335 NI
and 5044, 33540 for income tax ?
if so how come there diffrent?

Yes, those bands, although you have them the wrong way round, 5044 and 33540 are for NI.

Possibly the 5044 and 5035 should be the same but it depends how you calculate the number of weeks in a year! I'm not quite sure how the Inland Revenue do that.....

I don't believe the bands have ever been co-ordinated as such, as they currently stand there's a discrepancy in that the combined rate of NI and income tax actually drops (at £33540 per year) to 23% and then goes back up again when 40% band kicks in

thanks again for you help daddylonglegs, i've made a table like you surgested
so i'm not changing the formulas, but getting an error message
the formula you typed contains an error....
any ideas how i can solve this problem please
i'm draging information from another sheet called "user settings"
p.s i can find all the bands on the web site you gave me apart from 7185


"daddylonglegs" wrote:

>
> DarkNight Wrote:
> > that works a treat, thanks for you hard work.
> > 1 little daft question the bands in your refering to are they 5035,
> > 7185,
> > 38335 NI
> > and 5044, 33540 for income tax ?
> > if so how come there diffrent?
> >
> >
>
> Yes, those bands, although you have them the wrong way round, 5044 and
> 33540 are for NI.
>
> Possibly the 5044 and 5035 should be the same but it depends how you
> calculate the number of weeks in a year! I'm not quite sure how the
> Inland Revenue do that.....
>
> I don't believe the bands have ever been co-ordinated as such, as they
> currently stand there's a discrepancy in that the combined rate of NI
> and income tax actually drops (at £33540 per year) to 23% and then goes
> back up again when 40% band kicks in
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=537126
>
>

Hi Dark Night

Which formula gives an error?

If you put your NI bands and percentages in a table like this:

band percentage

0 0%
5044 11%
33540 1%

[top left of table, "band" is in C1 - bottom right "1%" in D5

you can use the formula

=SUMPRODUCT(--(A1>C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)

you can accomplish something similar using a table for Income Tax. Note C2 & D2 should be blank

Income tax is paid at 10% for the first £2150 of taxable earnings but because the first £5035 is tax free this means that 10% is paid for all earnings between £5035 and (£5035+£2150=£7185) hence the £7185 band

thanks for getting back to me,
still having problems getting #Div/0!
think its the way i've constructed the table?
A1 = Gross earnings
B2 = frequency of pay ( in my case 26 for fortnightly)
C1 = tax band 5035
C3 = 0
C4 = 5044
C5 = 33540
D3 = 0%
D4 = 11%
D5 = 1%
A7 = =SUMPRODUCT(--(A1>C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)

just incase i get same problem with income tax can you help with that aswell

p.s. i can now see how 7185 band is worked out thank you very much.

when all is working i'd like you to have a look a the calculator if possible
is there any way i can send you it?

then you can see what i'm trying to do, and would welcome any feed back on it
theres probably an easer way to do what i've done but just learning, thats
why all the questions.

"daddylonglegs" wrote:

>
> Hi Dark Night
>
> Which formula gives an error?
>
> If you put your NI bands and percentages in a table like this:
>
> band percentage
>
> 0 0%
> 5044 11%
> 33540 1%
>
> [top left of table, "band" is in C1 - bottom right "1%" in D5
>
> you can use the formula
>
> =SUMPRODUCT(--(A1>C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)
>
> you can accomplish something similar using a table for Income Tax. Note
> C2 & D2 should be blank
>
> Income tax is paid at 10% for the first £2150 of taxable earnings but
> because the first £5035 is tax free this means that 10% is paid for all
> earnings between £5035 and (£5035+£2150=£7185) hence the £7185 band
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=537126
>
>

Thanks daddylonglegs I’ve found my problem now...
A great job done by you and Bob Phillips for helping me on this task.
Much to your relief i now consider the calculator working 100%


"DarkNight" wrote:

> thanks for getting back to me,
> still having problems getting #Div/0!
> think its the way i've constructed the table?
> A1 = Gross earnings
> B2 = frequency of pay ( in my case 26 for fortnightly)
> C1 = tax band 5035
> C3 = 0
> C4 = 5044
> C5 = 33540
> D3 = 0%
> D4 = 11%
> D5 = 1%
> A7 = =SUMPRODUCT(--(A1>C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)
>
> just incase i get same problem with income tax can you help with that aswell
>
> p.s. i can now see how 7185 band is worked out thank you very much.
>
> when all is working i'd like you to have a look a the calculator if possible
> is there any way i can send you it?
>
> then you can see what i'm trying to do, and would welcome any feed back on it
> theres probably an easer way to do what i've done but just learning, thats
> why all the questions.
>
> "daddylonglegs" wrote:
>
> >
> > Hi Dark Night
> >
> > Which formula gives an error?
> >
> > If you put your NI bands and percentages in a table like this:
> >
> > band percentage
> >
> > 0 0%
> > 5044 11%
> > 33540 1%
> >
> > [top left of table, "band" is in C1 - bottom right "1%" in D5
> >
> > you can use the formula
> >
> > =SUMPRODUCT(--(A1>C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)
> >
> > you can accomplish something similar using a table for Income Tax. Note
> > C2 & D2 should be blank
> >
> > Income tax is paid at 10% for the first £2150 of taxable earnings but
> > because the first £5035 is tax free this means that 10% is paid for all
> > earnings between £5035 and (£5035+£2150=£7185) hence the £7185 band
> >
> >
> > --
> > daddylonglegs
> > ------------------------------------------------------------------------
> > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> > View this thread: http://www.excelforum.com/showthread...hreadid=537126
> >
> >