uk tax calculator

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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)
 
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)
 
B1: tax allowance (e.g. 4895)
Click to expand...

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

--


Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) 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
 
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
 
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)
 
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
 
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.
 
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.
 
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?
 
DarkNight said:
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?
Click to expand...

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
 
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
 
Back
Top