Tax and NIC calculation formulas

[natsand]

Hi all, I am a newbie to complex formulas in excel and I am trying to put together a tax and NIC calculator for 2020/21. I have searched formulas on different threads and most seem to give formulas based on calculating all of the tax due in one formula, rather than splitting it out into the different bands.

I am looking to put formulas into the below x cells:

Total taxable income Cell B6 (for example)

Tax at 20% x (to calculate the amount if income taxable at 20% - up to £37500) £ (tax due would then be calculated by multiplying x by 20%)
Tax at 40% x (to calculate the amount if income taxable at 40% - between £37501 and £150000) £ (tax due would then be calculated by multiplying x by 40%)
Tax at 45% x (to calculate the amount if income taxable at 45% - Income over £150000) £ (tax due would then be calculated by multiplying x by 45%)

Then the same for NIC using the NIC bands.

Is someone able to help me with the formula I need to put in to calculate the taxable/NICable income?

Thank you
Natalie

 

[etaf]

are you UK based

i dont know the NIC bands
Should there be an allowance before tax deducted, Which also the allowance changes at different salary levels

BUT you could setup with some IF()

Personal Allowance Up to £12,570 0%
Basic rate £12,571 to £50,270 20%
Higher rate £50,271 to £150,000 40%
Additional rate over £150,000 45%

Under £100,000 £12,570
£100,000 to £125,000 Decreases from £12,570 by £1 for every £2 you earn above £100,000, until it reaches £0
Over £125,000 £0

If you know the NI Bands can you paste - also do you take into account the allowances ?

 

[natsand]

Yeah I am in the UK and the 20/21 NIC rates are:

Up to £9500 - 0%
Between £9501-£50,000 - 12%
Over £50,000 - 2%

20/21 tax rates are:
First £37,500 - 20%
£37,501-£150,000 - 40%
Over £150,000 - 45%

I already have a formula to calculate taxable income after the PA has been deducted but not sure how to set up the formulas to determine how much income is taxable in each band.

Which allowances are you referring to?

Thank you

 

[MARK858]

Which allowances are you referring to?

The personal tax allowance which is the amount that you earn before you pay tax. In the UK the standard rate is £12,570 but there are variations. Please see the links below.

Income Tax rates and Personal Allowances

Tax codes

What tax codes are, how they're worked out, and what to do if you think your code is wrong.

www.gov.uk

For the tax part with an allowance of 12570 it would be as per the table below

Book1
	A	B	C	D	E
1	Breakpoint	Tax	Diifferential	Income	Tax Amount
2	12570	20%	20%	112422	34954.80
3	37500	40%	20%	48037	9200.80
4	150000	45%	5%	100	0.00
5				74482	19778.80
6				68543	17403.20
7				160000	54486.00
8				81066	22412.40
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2
C3:C4	C3	=B3-B2
E2:E8	E2	=SUMPRODUCT(--(D2>$A$2:$A$4),(D2-$A$2:$A$4), $C$2:$C$4)

 

[MARK858]

@natsand Figures in the table in the previous post is based on your figures with the breakpoint at 37,500 whereas the breakpoint in the UK is actually at 50,270 which gives the results below up to 100000 (after which the tax allowance goes down which I haven't taken account of in the table) and you can check at the link below.

Income tax calculator 2024-25, 2023-24 and 2022-23 - Which?

Use Which?'s income tax calculator to work out your take-home pay

www.which.co.uk

Book1
	A	B	C	D	E	F	G
1	Breakpoint	Tax	Diifferential	Income	Tax Amount		As a single Formula
2	12570	20%	20%	112422	32400.80		32400.80
3	50270	40%	20%	48037	7093.40		7093.40
4	150000	45%	5%	100	0.00		0.00
5				74482	17224.80		17224.80
6				68543	14849.20		14849.20
7				160000	51932.00		51932.00
8				81066	19858.40		19858.40
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2
C3:C4	C3	=B3-B2
E2:E8	E2	=SUMPRODUCT(--(D2>$A$2:$A$4),(D2-$A$2:$A$4), $C$2:$C$4)
G2:G8	G2	=SUMPRODUCT(--(D2>{12570;50270;150000}),(D2-{12570;50270;150000}), {0.2;0.2;0.05})

 

[natsand]

Thanks for your help. I have uploaded a picture of the spreadsheet I have set up and looking at setting up formulas in the blue cells to calculate the taxable/NICable income in each band so that if the income of £93,500 changes, the formulas automatically change the amounts.

Would they not need IF formulas, eg if taxable income is less than £37,700 the amount taxed at 20% would equal the amount of taxable income and the other cells would be £0 but if for example taxable income is £45,000, the formula would determine that £37.700 is taxable at 20%, £7,300 is taxable at 40% and £0 taxed at 45%?

Problem is i don't know how to use IF formulas. I am actually a tax accountant but never have to use them as I am only ever looking at one scenario at once rather than setting up a template to be used where the taxable income changes.

I am not sure if your formulas would work in this case?

Thanks
Natalie

 

[natsand]

Thanks for your help. I have uploaded a picture of the spreadsheet I have set up and looking at setting up formulas in the blue cells to calculate the taxable/NICable income in each band so that if the income of £93,500 changes, the formulas automatically change the amounts.

Would they not need IF formulas, eg if taxable income is less than £37,700 the amount taxed at 20% would equal the amount of taxable income and the other cells would be £0 but if for example taxable income is £45,000, the formula would determine that £37.700 is taxable at 20%, £7,300 is taxable at 40% and £0 taxed at 45%?

Problem is i don't know how to use IF formulas. I am actually a tax accountant but never have to use them as I am only ever looking at one scenario at once rather than setting up a template to be used where the taxable income changes.

I am not sure if your formulas would work in this case?

Thanks
Natalie

Sorry, please use this pic, hadn't added class 1 2% NIC and can ignore self employment income for this

 

[MARK858]

the formula would determine that £37.700 is taxable at 20%, £7,300 is taxable at 40% and £0 taxed at 45%

That is not correct for the UK tax rules as I stated in my previous post for an employee, are talking about self-employed?

 

[natsand]

I know its £50,270 if you are factoring in the PA of £12,570 but this has already been considered in the taxable income of £93,500 so I am just looking at a formula that will determine taxable income in each of the 20%, 40% and 45% bands

 

[etaf]

so is it JUST for total income of 93,500 - a ONE OFF calculation
IF() should do as mentioned
Otherwise the Personal Allowance changes - As i did put all the info , in my original replay about the TAX band Thresholds & also the personal Allowance rules ?

Just trying to be complete and accurate to give the correct response, and make sure it is the one you need , as it was not clear the allowance had been taken into account

Tax at 20% x (to calculate the amount if income taxable at 20% - up to £37500) £ (tax due would then be calculated by multiplying x by 20%)

50270 - 12570 = 37770 , you have 37500 - is that because of NIC ?
Which you wanted also separate