Tax Brackets

[jl3287]

I've been trying to create a sheet that would give me the tax breakdown of an income. There are quite a few variables...

Hypothetically speaking,

-You make $35000 Gross Income

-You fall under 5 different brackets,
0-10000, 10001-15000, 15001-25000, 25001-50000, 50001-100000

-The percentage for each increases by 1 starting from 2%,
(2%, 3%, 4%, 5%, 6%)

And, since you make 35000,
you count
10000 (at 2% = 200) for being in the 0-10000 bracket
5000 (3% = 150) for being in 10001-15000
10000 (4% = 400) for being in 15001-25000 &
10000 (5% = 500) for being in 25001-50000
0 (6% = 0) for being 50001-100000

Equaling: 1250

So, I need a formula that can do that.

Can figure out your income is to high for a bracket, in the middle of a bracket, to low for a bracket, and if it fits somewhere in the middle of a bracket can count only the amount it's made in that bracket...




Any ideas? And, thank you in advance for any answers!

 

[Papi]

Give this a try. I am sure there could be better but see if it is a starting point for you!

Gross Income.xlsm
	A	B	C	D
7	60,000	Percent	Rate
8	10,000	2%	200
9	5,000	3%	150
10	10,000	4%	400
11	10,000	5%	500
12	-	6%	600
13		Total	1,850
Sheet1

 

[Papi]

I forgot to mention that the gross income cell is named "Gross_Income"

 

[Marcelo Branco]

Other possible solution

http://www.mcgimpsey.com/excel/variablerate.html

M.

 

[jl3287]

Ended up doing something like this

=IF(D18<6001,0, IF(AND(D18>6000,D18<=30001),(D18-6000)*0.15, IF(AND(D18>30001,D18<75001),3600+(D18-30000)*0.3, IF(AND(D18>=75001,D18<150001),17100+(D18-75000)*0.4, IF(D18>=150001,47100+(D18-150000)*0.45)))))

 

[Papi]

Great. I was just going over my formulas and I missed one complete step.

 

[jl3287]

God, the McGimpsey one is nice!

=sumproduct, you're my only friend!

Thank you kind sirs!