Tax Calculator

[JonConc]

Hi, I'm trying to create a tax calculator in Excel. The calculator would calculate tax based on a 'purchase price' and the %'age rate on each band. Any assistance would be gratefully appreciated. Below is an image of the draft I have created.

 

[MARK858]

Welcome to the board, please see the post below...

Spreadsheet to calculate Scottish income tax

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...

www.mrexcel.com

 

[Dave Patton]

Alternative 1 names the Brackets and Rate Differences
Name Manager
aB ={180000;250000;400000;750000;1500000}
aR ={0.035;0.015;0.025;0.025;0.02}

Alternative 2 references ranges from the table
N.B. I have no knowledge of your tax system. Please review and test carefully.
Use the alternative that you prefer.

T202009c.xlsm
	A	B	C	D	E	F	G	H
1		Taxable amount	1,500,000.00			Bracket	Rate	Tax calculation
2		Tax Alternative 1	111,200.00			0	0
3		Tax Alternative 2	111,200.00			180,000	3.5%	0.00
4						250,000	5.0%	2,450.00
5						400,000	7.5%	7,500.00
6						750,000	10.0%	26,250.00
7						1,500,000	12.0%	75,000.00
8						1E+12		0.00
9								111,200.00
10
2cc

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(C1>aB),C1-aB,aR)
C3	C3	=SUMPRODUCT(--(C1>F3:F7),C1-F3:F7,G3:G7-G2:G6)
F8	F8	=10^12
H3:H8	H3	=MAX(0,(MIN($C$1,F3)-F2)*G2)
H9	H9	=SUM(H3:H8)

 

[JonConc]

Alternative 1 names the Brackets and Rate Differences
Name Manager
aB ={180000;250000;400000;750000;1500000}
aR ={0.035;0.015;0.025;0.025;0.02}

Alternative 2 references ranges from the table
N.B. I have no knowledge of your tax system. Please review and test carefully.
Use the alternative that you prefer.

T202009c.xlsm
	A	B	C	D	E	F	G	H
1		Taxable amount	1,500,000.00			Bracket	Rate	Tax calculation
2		Tax Alternative 1	111,200.00			0	0
3		Tax Alternative 2	111,200.00			180,000	3.5%	0.00
4						250,000	5.0%	2,450.00
5						400,000	7.5%	7,500.00
6						750,000	10.0%	26,250.00
7						1,500,000	12.0%	75,000.00
8						1E+12		0.00
9								111,200.00
10
2cc

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(C1>aB),C1-aB,aR)
C3	C3	=SUMPRODUCT(--(C1>F3:F7),C1-F3:F7,G3:G7-G2:G6)
F8	F8	=10^12
H3:H8	H3	=MAX(0,(MIN($C$1,F3)-F2)*G2)
H9	H9	=SUM(H3:H8)

Many thanks for your reply and examples, much appreciated. Just wondering if you could explain why no tax is calculated in the 180,000 bracket (H3)?

 

[JonConc]

Many thanks for your reply and examples, much appreciated. Just wondering if you could explain why no tax is calculated in the 180,000 bracket (H3)?

Please ignore, managed to figure it out. Thanks

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Tax Calculator (Based on Bands)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Dave Patton]

The relevant ranges are named in Alternative 1. Brackets are named "aB" and Rates are named "aR_1" and "aR_2".
In Name Manager name the Brackets aB ={0;180000;250000;400000;750000;1500000}
Highlight F3:F8 in C4 and press F9; you will see the array shown above.
In Name Manager, name the Rate Differentials aR_1 and aR_2.
aR_1 ={0;0.035;0.015;0.025;0.025;0.02}
Highlight G3:G8-G2:G7 in C4 and press F9; you will see the array shown above.
aR_2 ={0.03;0.035;0.015;0.025;0.025;0.02}
The Rate Differentials are not the gross rates per Table; they are like G3:G8-G2:G7 for example see C4.
You could calculate the rate differential in another column; see Column L.
The formula is then =SUMPRODUCT(--(C1>F3:F8),C1-F3:F8,L3:L8)
With the information provided, the brackets are the same for both sets of rates.
F2:K2 is intentionally blank; see the calculation J3:J8-J2:J7 in cell D4. Text in J2 would invalidate the calculation.

T202009c.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Taxable amount	1,500,000.00			Bracket [aB]	Rate =aR_1	Tax		Rate [aR_2]	Tax
2		Standard Rate [1]	Higher Rate [2]
3	Tax Alternative 1	111,200.00	156,200.00		0	0	0.00		3.0%	0.00	3.0%
4	Tax Alternative 2	111,200.00	156,200.00		180,000	3.5%	0.00		6.5%	5,400.00	3.5%
5					250,000	5.0%	2,450.00		8.0%	4,550.00	1.5%
6	Tax Alternative 2		156,200.00		400,000	7.5%	7,500.00		10.5%	12,000.00	2.5%
7					750,000	10.0%	26,250.00		13.0%	36,750.00	2.5%
8					1,500,000	12.0%	75,000.00		15.0%	97,500.00	2.0%
9					1E+12		0.00			0.00
10							111,200.00			156,200.00
2cc

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(--(C1>aB),C1-aB,aR_1)
D3	D3	=SUMPRODUCT(--(C1>aB),C1-aB,aR_2)
C4	C4	=SUMPRODUCT(--(C1>F3:F8),C1-F3:F8,G3:G8-G2:G7)
D4	D4	=SUMPRODUCT(--(C1>F3:F8),C1-F3:F8,J3:J8-J2:J7)
D6	D6	=SUMPRODUCT(--(C1>F3:F8),C1-F3:F8,L3:L8)
L3:L8	L3	=J3-N(J2)
F9	F9	=10^12
H3:H8	H3	=MAX(0,(MIN($C$1,F3)-F2)*G2)
H9,K3:K9	H9	=MAX(0,(MIN($C$1,$F9)-$F8)*G8)
H10,K10	H10	=SUM(H4:H9)

 

[Dave Patton]

T202009c.xlsm
	B	C	D	E	F	G	H
1	Taxable amount	1,500,000.00			Bracket [aB]	Rate =aR_1	Rate [aR_2]
2		Standard Rate [1]	Higher Rate [2]
3	Tax Alternative 1	111,200.00	156,200.00		0	0	3.0%
4	Tax Alternative 2	111,200.00	156,200.00		180,000	3.5%	6.5%
5					250,000	5.0%	8.0%
6					400,000	7.5%	10.5%
7					750,000	10.0%	13.0%
8					1,500,000	12.0%	15.0%
2cc

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(--(C1>aB),C1-aB,aR_1)
D3	D3	=SUMPRODUCT(--(C1>aB),C1-aB,aR_2)
C4	C4	=SUMPRODUCT(--(C1>F3:F8),C1-F3:F8,G3:G8-G2:G7)
D4	D4	=SUMPRODUCT(--(C1>F3:F8),C1-F3:F8,H3:H8-H2:H7)