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

 

[natsand]

The £93,500 is taxable income after the PA and has this formula in:
=IF(J15>12,570,J15-(12570-(AND(J15>=100000,J15<+125140)*(J15-100000)/2+(J15>125140)*12570)),J15)

So i just need formulas that will work out taxable income in each tax band so that is the taxable income changes, the numbers in these cells with automatically change. If that makes sense?

I didn't create the above formula and so not familiar with how I would need to write the IF formulas for this.

 

[natsand]

Sorry, the £37,500 was the 20/21 rate.21/22 rates can be used

 

[MARK858]

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

Based on the 37500...

Book2
	A	B	C	D
1			Taxable Income after deducton of personal allowance
2			45000	93500
3	Tax due
4	Tax at 20%	37500	7500	7500
5	Tax at 40%	150000	3000	22400
6	Tax at 45%		0	0
Sheet1

Cell Formulas
Range	Formula
C4:D4	C4	=IF(C2>$B$4,$B$4*0.2,C2*0.2)
C5:D5	C5	=IF(AND(C2>$B$4,C2<=$B$5),(C2-$B$4)*0.4,0)
C6:D6	C6	=IF(C2>$B$5,(C2-$B$5)*0.45,0)

Based on 50270

Book2
	A	B	C	D
1			Taxable Income after deducton of personal allowance
2			45000	93500
3	Tax due
4	Tax at 20%	50270	9000	10054
5	Tax at 40%	150000	0	17292
6	Tax at 45%		0	0
Sheet1

Cell Formulas
Range	Formula
C4:D4	C4	=IF(C2>$B$4,$B$4*0.2,C2*0.2)
C5:D5	C5	=IF(AND(C2>$B$4,C2<=$B$5),(C2-$B$4)*0.4,0)
C6:D6	C6	=IF(C2>$B$5,(C2-$B$5)*0.45,0)

Change the Cell ranges to match yours.

 

[natsand]

Thank you for this
This works but then when I say increase taxable income to £200,000, it says £0 is owed at 40% tax. Do you know what I need to change in the formula so that it works it out accurately no matter how much taxable income is?
Many thanks
Natalie

 

[MARK858]

Book2
	A	B	C	D
1			Taxable Income after deducton of personal allowance
2			200000	93500
3	Tax due
4	Tax at 20%	50270	10054	10054
5	Tax at 40%	150000	39892	17292
6	Tax at 45%		22500	0
Sheet1

Cell Formulas
Range	Formula
C4:D4	C4	=IF(C2>$B$4,$B$4*0.2,C2*0.2)
C5:D5	C5	=IF(AND(C2>$B$4,C2<=$B$5),(C2-$B$4)*0.4,0)+IF(C2>150000,39892,0)
C6:D6	C6	=IF(C2>$B$5,(C2-$B$5)*0.45,0)

Book5
	A	B	C	D
1			Taxable Income after deducton of personal allowance
2			200000	93500
3	Tax due
4	Tax at 20%	37500	7500	7500
5	Tax at 40%	150000	45000	22400
6	Tax at 45%		22500	0
Sheet1

Cell Formulas
Range	Formula
C4:D4	C4	=IF(C2>$B$4,$B$4*0.2,C2*0.2)
C5:D5	C5	=IF(AND(C2>$B$4,C2<=$B$5),(C2-$B$4)*0.4,0)+IF(C2>150000,45000,0)
C6:D6	C6	=IF(C2>$B$5,(C2-$B$5)*0.45,0)

 

[Dave Patton]

Why do you want the amount by bracket?

The rates in column A are custom formatted to show Tax at rate

Tax 2021.xlsm
	A	B	C	D
1			Taxable Income after deducton of personal allowance
2			45,000.00	200,000.00
3		Total	9,000.00	72,446.00
4	Tax at 20%
5	Tax at 40%	50270	9,000.00	10,054.00
6	Tax at 45%	150000	0.00	39,892.00
7			0.00	22,500.00
8			9,000.00	72,446.00
9
1d

Cell Formulas
Range	Formula
C3:D3	C3	=SUMPRODUCT(--(C2>{0;50270;150000}),C2-{0;50270;150000},{0.2;0.2;0.05})
C5:D7	C5	=MAX(0,MIN(C$2,$B5)-$B4)*$A4
C8:D8	C8	=SUM(C5:C7)

 

[etaf]

Just an Idea, here is an online calculator - which shows Tax bands and the NI , But also the tampered Personal Allowance, which i mentioned
You can also allow for any Pension Contributions (allowances) & put in an Actual Tax Code if different

ListenToTaxman - UK Tax Calculator Salary Wages PAYE Income Taxes 2023 - 2024

listentotaxman.com

Just an idea,

 

[natsand]

Thanks everyone for your help.

Thanks Etaf - was more looking for a spreadsheet where I can change the salary etc amounts and it works it all out for me

 

[Dave Patton]

Did you try the suggestion in post #16 ????