Tiered tax table - refined solution?

[JamesonMH]

Hi all,

I'm trying to refine this formula that calculates the total taxes off of a tiered table. Assume $10,000 income. Notice the 2nd row has to be blank for formula to work, which I don't like.

E.g.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Bracket Min $ (cell A1)[/TD]
[TD]Bracket Max $ (cell B1)[/TD]
[TD]Tax % (cell C1)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$0[/TD]
[TD]$4,740[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]4,740.01[/TD]
[TD]6,067[/TD]
[TD]20.00%[/TD]
[/TR]
[TR]
[TD]6,067.01[/TD]
[TD]100,000[/TD]
[TD]36.00%[/TD]
[/TR]
</tbody>[/TABLE]

I use array formula = {SUM(--((C3:C5-C2:C4)*
(10,000-A3:A5)*N(10,000>A3:A5))}
=$1,681.28 in taxes

Is there a clever way I can tweak the formula so I don't have to have a blank row above the data? Right now the blank row facilitates the first part of the formula (calculating the difference in tax rate at each tier.

Thanks for your time!

James

 

[Eric W]

You could add another column with the difference in the tax rate:

Excel 2012

	A	B	C	D	E	F
Bracket Min $	Bracket Max $	Tax %	Delta	Taxes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]$0 [/TD]
[TD="align: right"]$4,740 [/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1681.276[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4,740.01[/TD]
[TD="align: right"]6,067[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6,067.01[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]36.00%[/TD]
[TD="align: right"]16.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=C2-N(C1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMPRODUCT(D2:D4,(10000-A2:A4),--(10000>A2:A4))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JamesonMH]

Thanks for your reply Eric. I like your solution better b/c it makes it clear how the tax differential drives the solution. It also avoids the blank row which was what I was after.

With the original formula it of course needs that blank row since you can't subtract text from a %. Are there any other workarounds to turn a text into say, zero so I could avoid the extra column? E.g. subtract this range from this range and treat any text values in either range to a zero...thanks again.

 

[MARK858]

You could (much as it is more difficult to manage) hard code the differences in the formula (then again if manageability is an issue I would also put the total earnings in a cell).
Btw you should lose the .01 in the bands as the sumproduct is doing a greater than.

Excel Workbook
	A	B
1	Bracket Min $
2	0	1681.28
3	4740
4	6067
Sheet1

 

[Eric W]

You could try this array formula:

=SUM(IFERROR(C2:C4-C1:C3,0)*(10000-A2:A4)*(10000>A2:A4))

confirmed with Control+Shift+Enter.

 

[JamesonMH]

Thanks Eric for your help. I just tried with the IFERROR but unfortunately the formula won't work if there is ever a tax rate in the 1st bracket.

Right now I show 0% in my example for the first bracket, but if it were say 10% from $0 - $4,740 it would still return $0 b/c of the IFERROR.

 

[JamesonMH]

You could (much as it is more difficult to manage) hard code the differences in the formula (then again if manageability is an issue I would also put the total earnings in a cell).
Btw you should lose the .01 in the bands as the sumproduct is doing a greater than.

Sheet1

	A	B
Bracket Min $

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:103px;"><col style="width:93px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1681.28[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]4740[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]6067[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =SUMPRODUCT({0;0.2;0.16},(10000-$A$2:$A$4),--(10000>$A$2:$A$4)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks Mark, I didn't even realize using that array within SUMPRODUCT was an option! I do agree on linking of the income number - I was just hard coding it for my question as I couldn't figure out how to insert a real looking spreadsheet like many of you do on here.

Is there any way to make those hard-coded numbers (0, 0.2, 0.16) dynamic to avoid the hard-coding?

And noted on the $0.01.

Thanks for your help, much appreciated!

James

 

[Eric W]

Try this one instead:

=SUM((C2:C4-IFERROR(C1:C3+0,0))*(10000-A2:A4)*(10000>A2:A4))

with Control+Shift+Enter.

And there's no way to make a hard-coded array dynamic - you need to put those values in cells to do that.

 

[JamesonMH]

Try this one instead:

=SUM((C2:C4-IFERROR(C1:C3+0,0))*(10000-A2:A4)*(10000>A2:A4))

with Control+Shift+Enter.

And there's no way to make a hard-coded array dynamic - you need to put those values in cells to do that.

Eric - amazing as usual! You wouldn't believe how many other variations I tried that were much longer. I've never seen that trick before where you add zero to the end of a range.

As for the hard-coded array, what I meant was can you link those values inside the curly brackets within the SUMPRODUCT, somehow so you don't have to hard code? Excel just gives you a pop-up window when you try to link so I couldn't figure it out. But I'm good either way now that I have this solution of yours. Thanks again Eric!

James