Spreadsheet to calculate Scottish income tax

[DavidGee]

Hi, I'm trying to create a spreadsheet to calculate income tax payable for a given level of income. I know the tax bands (here), but I just can't work out what the formula should be to calculate how much of the total income would fall into each band, for any given level of income. Can anyone help with this, please? Thank you.

 

[Dave Patton]

N.B. I have no knowledge of your tax system.
Please review and test carefully.
I show the formula with named ranges or self contained; use the format that you prefer.

T202009c.xlsm
	A	B	C	D	E	F	G
1	Taxable amount	150,000.00	50,041.57		Bracket [rB]	Rate	Rate Diff [rR]
2			50,041.57		12,500	19%	19%
3			50,041.57		14,585	20%	1%
4					25,158	21%	1%
5					43,430	41%	20%
6					150,000	46%	5%
7
2c

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(B1>rB),B1-rB,rR)
C2	C2	=SUMPRODUCT(--(B1>{12500;14585;25158;43430;150000}),B1-{12500;14585;25158;43430;150000},{0.19;0.01;0.01;0.2;0.05})
C3	C3	=SUMPRODUCT(--(B1>E2:E6),B1-E2:E6,G2:G6)
G2:G6	G2	=F2-N(F1)

Named Ranges
Name	Refers To	Cells
rB	='2c'!$E$2:$E$6	C3, C1
rR	='2c'!$G$2:$G$6	C3, C1

T202009c.xlsm
	B	C
5	20,000.00	1,479.15
6
2c

Cell Formulas
Range	Formula
C5	C5	=SUMPRODUCT(--(B5>rB),B5-rB,rR)

 

[drsarao]

Try this:

	A	B	C	D	E	F
1​				Bands	Rate	Amount
2​	Income	1,00,000​		0​	0​	0.00​
3​	Tax	29,541.57​		12500​	19​	0.00​
4​				14585​	20​	396.15​
5​				25158​	21​	2,510.75​
6​				43430​	41​	6,347.87​
7​				150000​	26​	50,041.57​

Formula in B3: =VLOOKUP(C3,E3:G8,3)+(C3-VLOOKUP(C3,E3:G8,1))*VLOOKUP(C3,E3:G8,2)%

 

[drsarao]

Sorry.
Formula in C3: =VLOOKUP(C3,E3:G8,3)+(C3-VLOOKUP(C3,E3:G8,1))*VLOOKUP(C3,E3:G8,2)%

A	B	C	D	E	F	G
1​				Bands	Rate	Amount
2​	Income	1,00,000​		0​	0​	0.00​
3​	Tax	29,541.57​		12500​	19​	0.00​
4​				14585​	20​	396.15​
5​				25158​	21​	2,510.75​
6​				43430​	41​	6,347.87​
7​				150000​	26​	50,041.57​

 

[DavidGee]

Sorry.
Formula in C3: =VLOOKUP(C3,E3:G8,3)+(C3-VLOOKUP(C3,E3:G8,1))*VLOOKUP(C3,E3:G8,2)%

A	B	C	D	E	F	G
1​				Bands	Rate	Amount
2​	Income	1,00,000​		0​	0​	0.00​
3​	Tax	29,541.57​		12500​	19​	0.00​
4​				14585​	20​	396.15​
5​				25158​	21​	2,510.75​
6​				43430​	41​	6,347.87​
7​				150000​	26​	50,041.57​

Hi, Thanks. But a couple of questions: 1. Can I download the sheet you've created, or do I need to copy everything? 2. How are the values on column G calculated? Thanks again.

 

[DavidGee]

N.B. I have no knowledge of your tax system.
Please review and test carefully.
I show the formula with named ranges or self contained; use the format that you prefer.

T202009c.xlsm
	A	B	C	D	E	F	G
1	Taxable amount	150,000.00	50,041.57		Bracket [rB]	Rate	Rate Diff [rR]
2			50,041.57		12,500	19%	19%
3			50,041.57		14,585	20%	1%
4					25,158	21%	1%
5					43,430	41%	20%
6					150,000	46%	5%
7
2c

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(B1>rB),B1-rB,rR)
C2	C2	=SUMPRODUCT(--(B1>{12500;14585;25158;43430;150000}),B1-{12500;14585;25158;43430;150000},{0.19;0.01;0.01;0.2;0.05})
C3	C3	=SUMPRODUCT(--(B1>E2:E6),B1-E2:E6,G2:G6)
G2:G6	G2	=F2-N(F1)

Named Ranges
Name	Refers To	Cells
rB	='2c'!$E$2:$E$6	C3, C1
rR	='2c'!$G$2:$G$6	C3, C1

T202009c.xlsm
	B	C
5	20,000.00	1,479.15
6
2c

Cell Formulas
Range	Formula
C5	C5	=SUMPRODUCT(--(B5>rB),B5-rB,rR)

Thank you very much for this. It works!

 

[Dave Patton]

I added the Vlookup alternative to the suggestions.
aB and aR are named ranges for Bracket and Rate Diff information.

T202009c.xlsm
	B	C	D	E	F	G
1	200,000.00	73,041.57		Bracket	Rate	Scottish Tax
2				0	0
3		73,041.57		12,500	19%	0.00
4		73,041.57		14,585	20%	396.15
5				25,158	21%	2,510.75
6				43,430	41%	6,347.87
7				150,000	46%	50,041.57
8				1E+308		73,041.57
2c

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
C3	C3	=SUMPRODUCT(--(B1>E3:E7),B1-E3:E7,F3:F7-F2:F6)
C4	C4	=VLOOKUP(B1,E2:G8,3)+(B1-VLOOKUP(B1,E2:G8,1))*VLOOKUP(B1,E2:G8,2)
E8	E8	=BigNum
G3:G8	G3	=MAX(0,(MIN($B$1,E3)-E2)*F2)+G2

 

[DavidGee]

I added the Vlookup alternative to the suggestions.
aB and aR are named ranges for Bracket and Rate Diff information.

T202009c.xlsm
	B	C	D	E	F	G
1	200,000.00	73,041.57		Bracket	Rate	Scottish Tax
2				0	0
3		73,041.57		12,500	19%	0.00
4		73,041.57		14,585	20%	396.15
5				25,158	21%	2,510.75
6				43,430	41%	6,347.87
7				150,000	46%	50,041.57
8				1E+308		73,041.57
2c

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
C3	C3	=SUMPRODUCT(--(B1>E3:E7),B1-E3:E7,F3:F7-F2:F6)
C4	C4	=VLOOKUP(B1,E2:G8,3)+(B1-VLOOKUP(B1,E2:G8,1))*VLOOKUP(B1,E2:G8,2)
E8	E8	=BigNum
G3:G8	G3	=MAX(0,(MIN($B$1,E3)-E2)*F2)+G2

Thanks again. Can you just confirm what the range names are in this version, please?

 

[Dave Patton]

The names include the same data that is used in each example.
aB ={12500;14585;25158;43430;150000}
aR={0.19;0.01;0.01;0.2;0.05}
Example C2 highlight E3:E7 press F9 and you see {12500;14585;25158;43430;150000}

T202009c.xlsm
	A	B	C
1	Taxable amount	200,000.00	73,041.57
2c

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(B1>aB),B1-aB,aR)

The self-contained formulas do not require the table of Brackets and Rates.

 

[DavidGee]

OK, thanks, but I can't get this example to work. You say aR={0.19;0.01;0.01;0.2;0.05}, but aR appears to name the range in column F, which holds the rates, not the differential rates. And you'll see below that my copy isn't working (including the formulae in E8 and G8.
But the formula =SUMPRODUCT(--(J32>{12500;14585;25158;43430;150000}),J32-{12500;14585;25158;43430;150000},{0.19;0.01;0.01;0.2;0.05}) in your previous example works, and I've been able to copy it into a spreadsheet I'm using to calculate my pension income after tax, so thanks again for your help and work on this.

Taxable amount	200000​	196618.5​		Bracket	Rate	Scottish Tax
				0​	0%​
		73041.57​		12500​	19%​	0​
		73041.57​		14585​	20%​	396.15​
				25158​	21%​	2510.75​
				43430​	41%​	6347.87​
				150000​	46%​	50041.57​
				#NAME?​		#NAME?​