Using cel 2013 functions for tiered rates schedule

[LeeMan88]

Hello

I am hoping that someone can help me create a formula for the following table containing tiered rates.



Taxable income Tax on this income
0 – $18,200 Nil
$18,201 – $37,000 19c for each $1 over $18,200
$37,001 – $80,000 $3,572 plus 32.5c for each $1 over $37,000
$80,001 – $180,000 $17,547 plus 37c for each $1 over $80,000
$180,001 and over $54,547 plus 45c for each $1 over $180,000


Thanks

 

[DRSteele]

Check out this link in which I helped with an Income Tax formula for Marginal Tax rates. Income Tax Formula | Excel University Scroll to the bottom of the article and you can download a file that does what you need.

Also, see here:
[TABLE="width: 605"]
<tbody>[TR]
[TD][TABLE="width: 602"]
<tbody>[TR]
[TD]http://www.mcgimpsey.com/excel/variablerate.html[/TD]
[/TR]
[TR]
[TD]ftp://ftp.irs.gov/pub/irs-drop/rp-13-35.pdf
[TABLE="width: 592"]
<tbody>[TR]
[TD]http://www.cra-arc.gc.ca/tx/ndvdls/fq/txrts-eng.html[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	200,000	63,547.00		Bracket	Rate	Rate_Delta
2				0.00	0.000	0.00
3				18,200.00	0.190	0.19
4				37,000.00	0.325	0.14
5				80,000.00	0.370	0.05
6				180,000.00	0.450	0.08
7
1

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(--(A1>D2:D6),A1-D2:D6,F2:F6)
F2	=E2-N(E1)

 

[LeeMan88]

Thanks - this is a real help. This forum never fails me thanks to people like you.

 

[LeeMan88]

2nd great way to get this to work - thanks heaps.

 

[DRSteele]

You're most welcome. Dave Patton's solution is clean and simple.

 

[Dave Patton]

Thanks for the feedback.

Dave