|
||||||||||||||||||||
|
|||||||||||||||||||||
|
#1
03-22-2007, 12:17 PM
|
|||
|
|||
multiple IF function
Hi,
I am an Accountant trying to make a model in excel that calculate net income after deducting tax and national insurance contributions from gross salary . The trouble I am facing is I don't know how to use multiple IF functions. For e.g A person earning £40000 per year will be charges income tax like this £40000 Less: 5035 ( Non Taxable Income for year) ____________ 34965 - Taxable income Income Tax will be deducted like this Income less than £2150 tax is NIL From £2151 tax is 10% If Income is from £2151 - £33,300 Tax is 22% Anything above £33,301 will be charged at 40% National Insurance Contributions is about 11 % on Gross Income which is easy but the real trouble is built of all these IF and AND functions . Could someone please help me build this function of Income tax in one line please ? There are other complications as well that but if you could only solve income tax that will be great. Thanks Sarvesh 
|
|
#2
03-22-2007, 12:58 PM
|
||||
|
||||
|
Income less than £2150 tax is NIL
From £2151 tax is 10% If Income is from £2151 - £33,300 Tax is 22% Something is wrong here, or am I not seeing straight?
__________________
oldchippy -------------   Click here >>> Top Excel links for beginners to Experts Forum Rules >>>Please don't forget to read these
|
|
#3
03-22-2007, 01:22 PM
|
|||
|
|||
|
Reply
Sincere apologies mate .
Taxable Bands table Taxable Bands Allowances 2006-07 (£) Starting rate 10% 0 - 2,150 Basic rate 22% 2,151 - 33,300 Higher rate 40% over 33,300 Hope this helps. There are personal allowances like a single person under the age of 65 will get £5035 per yr. Could you now assist ? Thanks Sarvesh
|
|
#4
03-22-2007, 05:54 PM
|
||||
|
||||
|
You can do this without If functions.
If you have annual salary in A1 this will give total income tax payable in the year =SUMPRODUCT(--(A1>{5035,7185,38335}),A1-{5035,7185,38335},{0.1,0.12,0.18})
|
|
#5
03-23-2007, 07:37 AM
|
|||
|
|||
|
Reply
Mate
Thanks for your suggestion but the answer is not coming correct . Could you let me what would a person pay in income tax in 2006-07 year on a salary of £38220 per year . Also I wanted to ask you what is the logic behind this function and could you explain it to me please ? Thanks Sarvesh
|
|
#6
03-23-2007, 08:25 AM
|
||||
|
||||
|
If you earn £38,820 in the UK in this tax year (2006/7) you pay 0% on the first £5,035, 10% on the next £2,150 (which equals £215) and then 22% on the rest (22% * £31,635 = £6,959.70). In total this is £7,174.70 which is the result you’ll get if you use the formula above.
Note: 40% doesn’t kick in until you reach £5,035 + £33,300 = £38,335 The formula uses the logic shown in this link http://www.mcgimpsey.com/excel/variablerate.html An alternative formula is =MAX(0,A1-5035)*10%+MAX(0,A1-7185)*(22%-10%)+MAX(0,A1-38335)*(40%-22%) Of course the bands will change slightly next month for the 2007/8 tax year
|
|
#7
03-23-2007, 08:39 AM
|
|||
|
|||
|
Quote:
You can find 3 methods about calculation income tax. .. Last edited by sgm020; 03-23-2007 at 08:43 AM.
|
|
#8
03-23-2007, 02:32 PM
|
|||
|
|||
|
Excellent
Mate you have been excellent . Could you please guide me on how I can build on my excel skills as I do financial modelling and sometimes I come across situations where I cannot build a model due to complexities. I am trying to learn modelling myself and there are lot of IF , AND functions come in situations which I find it hard to use.
Thanks very much. Sarvesh
|
|
#9
03-23-2007, 06:32 PM
|
||||
|
||||
|
__________________
oldchippy ------------- Click here >>> Top Excel links for beginners to Experts Forum Rules >>>Please don't forget to read these
|
 |
| Bookmarks |
New topics in F1 Get the most out of Excel Formulas & Functions
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Linear Mode
