Tax Bands

Try this
Enter in A4

Formula:

Please Login or Register  to view this content.

v	A	B	C	D
2
3	Income	834,000
4	12%	From	To	Rate
5		-	145,000	0%
6		145,000	250,000	2%
7		250,000	325,000	5%
8		325,000	750,000	10%
9		Over	750,000	12%

Originally Posted by AlKey

=LOOKUP(B$3,{0,145000,250000,325000,750000},D$5:D$9)

Originally Posted by alive555

thanks solved

But incorrectly?

Arguably, it is unclear what alive555 means by "calc tax on different tax bands". AlKey's formula returns the max tax rate, not the tax [sic], for 834,000.

Usually, such tables reflect marginal rates (aka progressive rates). That is, the first 145,000 to 250,000 is taxed at 2%, the next 250,000 to 325,000 is taxed at 5%, etc.

Alive555's table appears to be for the Scotland LBTT (Land and Buildings Transaction Tax). The calculator at https://www.savills.co.uk/resources-...alculator.aspx confirms that the total tax on 834,000 should be 58,430, to wit: 0% of the first 145,000; 2% of the next 105,000 (250,000 - 145,000); 5% of the next 75,000 (325,000 - 250,000); 10% of the next 425,000 (750,000 - 325,000); and 12% of the last 84,000 (834,000 - 750,000).

Change the table design as follows, and enter the following formula into B2:

=SUMPRODUCT((B3>C5:C8)*(B3-C5:C8), E6:E9)

v	A	B	C	D	E
2	Tax	58,430
3	Income	834,000
4		From	To	Rate	Diff Rate
5		-	145,000	0%	0%
6		145,000	250,000	2%	2%
7		250,000	325,000	5%	3%
8		325,000	750,000	10%	5%
9		Over	750,000	12%	2%

Enter the following formula into E6, formatted as Percentage with zero decimal places:

=D6-D5

Copy E6 into E7:E9.

Originally Posted by Sulprobil

=MAX(B3%*{0,2,2,10}-{0,2900,10400,26650})
How can you derive this?

Why bother? It returns the wrong answer for 834,000 in B3: 56,750 instead of 58,430.

It returns the wrong answer for 250,001 through 324,999 as well as 750,001 or greater.

The following is correct [2]:

=MAX(B3*{0,2,5,10,12}%-{0,2900,10400,26650,41650})

Also note the position of "%". Although the two positions are equivalent algebraically, I think it makes more sense to treat {0,2,5,10,12} as percentages, since they are (klunk!), rather than treat the income (B3) as a percentage (huh?!).

It might be noted that in Excel, "%" is an operator that is shorthand for "/100" (divide by 100).

In any case, I don't believe that formula is as flexible as the SUMPRODUCT formula [1]. It is difficult to adapt to another table of percentages and break points, IMHO. (Which is also why I suggested the minor table redesign in post #6.)


-----
[1] Adapted from http://www.mcgimpsey.com/excel/variablerate.html

[2] I did not use http://www.sulprobil.com/Get_it_done...rpolation.html

Originally Posted by Sulprobil

=MAX(B3%*{0,2,2,10}-{0,2900,10400,26650})

How can you derive this?
[deleted]

Regards,
Bernd

It is not acceptable to use a post to drive traffic to your own web site.