uk tax formula

[Adrian1]

This is a bit of a stab in the dark but does anyone have a formula that would work out UK Tax and NI deductions based on salary top line. I want the formula to be able to calculate my approx earnings after deductions based on how many hours I work, but it needs to also take into consideration extra earnings and higher tax as a result of working overtime. My tax code is 466L. Everything else on my spreadsheet is fine, I just need this formula as I can only get my gross at the moment and want net! (take home pay)

Cheers

 

[PaddyD]

have a look at chip person's site for "bracket pricing':

www.cpearson.com

...post back with more details if you can't get it working

 

[Richie(UK)]

Hi Adrian,

In addition to Chip's page, also have a look here:

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

This should enable you to come up with something like this:

Book1
	A	B	C	D	E	F	G
1	IncomeTax				NIC
2	Lower	Upper	Rate	Marg.Rate	Lower	Upper	Rate
3	0	2,020	10%	10%	0	4,823	0%
4	2,021	31,400	22%	12%	4,824	32,330	11%
5	31,401		40%	18%	32,331		1%
6
7
8	Salary		20,000.00
9	IncomeTax		-3,131.18
10	NationalInsurance		-1,669.36
11	Net		15,199.46
12
13	PersonalAllowance		4,665
Sheet3

HTH

 

[Adrian1]

Excellent. Thank you both, I have now added a table with tax margins into my worksheet which I can calculate my tax on. Now I need to find out what the margins are!!!!!! Then look at National Insurance.

Cheers guys!!! ray:

 

[Richie(UK)]

Now I need to find out what the margins are!!!!!! Then look at National Insurance.

Adrian - what about the rates I used in the example! You can double-check them here:

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

 

[JGHunter]

Hello, I'm new here but I have been working out budgets for me and my wife and, being the lover of detail that I am, I wanted to know just how much out of our wages is tax and NI, however it appears to be a bit of a hidden piece of formula. Those that I did find, I found confusing, so hopefully even if this is oversimplified, it works for me and hopefully people Googling will discover this too!

If anyone is looking for the calculations for 2012/13 tax year, I've managed to make them:

I'm going to pretend A1 is the cell you have your gross salary in. Let's use a nice round number 25,000. Tax allowance of 8105 means your taxable amount (TA) is

=SUM(A1-8105) obviously. You then get your taxation (TX) as =SUM("TA"*0.2). Your NI contributions are 12% of your weekly wage beyond £146 so your NI is =SUM(((((A1/52)-146)/100)*12)*52) Your total deductions are now =SUM("NI"+"TX"). If you want to work out your net monthly take home from your gross salary, you need

=SUM((A1-(((A1-8105)*0.2)+(((((A1/52)-146)/100)*12)*52)))/12)

If 25000 is in A1 and you put this in B1, you will get £1,627.67

I hope this is helpful.

 

[damianshy]

I am very new in this forum and i believe i can get solution to Nigeria tax computaion in excel spread sheet here. I want a fomular that will return the payable tax value in a cell once the taxable income is inserted without having to create many colums,

The tax computaion is such that the payable Tax (PT) is is charged at different rate for a particulat Taxable Income (TI) e.g

Assuming Mr, A Taxable Income (TI) is 700K

1st 30k is charged at 7%
next 30k is charged at 11%
next 50k is charged at 15%
next 60k is charged at 21% e.t.c

Please assist ASAP.

 

[PaddyD]

Hi. Welcome to the board.

1) In general, you should start a new thread for a new topic, not 'hijack' an old one. If a previous thread is relevant, simply include a link to it.

2) This thread details a number of ways to accomplish what you've asked about. What have you tried?

 

[Fivecases]

Hi internet,

Found this thread when looking for a formula, not quite what I wanted so I've made my own.

Where cell A1 equals annual salary and using 2016-2017 tax bands, I have the following:

Income tax: =MAX(MAX(A1-150000,0)*0.45+MIN(MAX(A1-43000,0),150000-43000)*0.4+MIN(A1-11000,43000-11000)*0.2+MAX((MIN(A1,122000)-100000)/2,0)*0.4,0)

nb. the 122000 and 100000 reflect the erosion of the £11,000 personal allowance by £1 for every £2 over £100,000

Class 1 E'er NIC: =MAX(MAX(A1-(827*52),0)*0.02+MIN(MAX(A1-(155*52),0),(827-155)*52)*0.12,0)

Monthly Take Home: =(A1-MAX(MAX(A1-150000,0)*0.45+MIN(MAX(A1-43000,0),150000-43000)*0.4+MIN(A1-11000,43000-11000)*0.2+MAX((MIN(A1,122000)-100000)/2,0)*0.4,0)-MAX(MAX(A1-(827*52),0)*0.02+MIN(MAX(A1-(155*52),0),(827-155)*52)*0.12,0))/12

Boom!

Hope it helps
RCT

 

[treatsmarket]

Hi internet,

Found this thread when looking for a formula, not quite what I wanted so I've made my own.

Where cell A1 equals annual salary and using 2016-2017 tax bands, I have the following:

Income tax: =MAX(MAX(A1-150000,0)*0.45+MIN(MAX(A1-43000,0),150000-43000)*0.4+MIN(A1-11000,43000-11000)*0.2+MAX((MIN(A1,122000)-100000)/2,0)*0.4,0)

nb. the 122000 and 100000 reflect the erosion of the £11,000 personal allowance by £1 for every £2 over £100,000

Class 1 E'er NIC: =MAX(MAX(A1-(827*52),0)*0.02+MIN(MAX(A1-(155*52),0),(827-155)*52)*0.12,0)

Monthly Take Home: =(A1-MAX(MAX(A1-150000,0)*0.45+MIN(MAX(A1-43000,0),150000-43000)*0.4+MIN(A1-11000,43000-11000)*0.2+MAX((MIN(A1,122000)-100000)/2,0)*0.4,0)-MAX(MAX(A1-(827*52),0)*0.02+MIN(MAX(A1-(155*52),0),(827-155)*52)*0.12,0))/12

Boom!

Hope it helps
RCT

Can somebody do this formula for the year 19-20

Thanks