Calculate Average Income Tax Rate Based On Net Income?

[olimits7]

Hi,

I'm trying to automatically calculate my business average income tax based on entering my business net income; for example $300K.

So based on the table below using the $300K example; $18,150 out of the $300K would be taxed at 10%, $73,800 out of the $300K would be taxed at 15%, and so on...

And since $300K falls under the 33% tax rate the last portion of taxable income would be $73,500 ($300K - $226,850) at 33%.

Married filing jointly Tax rate
$- $18,150.00 10.0%
$18,151.00 $73,800.00 15.0%
$73,801.00 $148,850.00 25.0%
$148,851.00 $226,850.00 28.0%
$226,851.00 $405,100.00 33.0%
$405,101.00 $457,600.00 35.0%
$457,601.00 $100,000,000.00 39.6%

So I'm having a hard time to figure out how to automate this so if I enter $450K it would then calculate properly at this tax rate too.

Thank you!

 

[VBA Geek]

Try:

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">MATCH(<font color="Green">D1,B2:B8</font>)>=(<font color="Green">ROW(<font color="Purple">B2:B8</font>)-1</font>)</font>),C2:C8,B2:B8</font>)+(<font color="Blue">D1-LOOKUP(<font color="Red">D1,B2:B8</font>)</font>)*LOOKUP(<font color="Blue">D1,A2:C8</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013
	A	B	C	D	E
1	Married	filing	Tax Rate	100,000.00
2	-	18,150.00	10.00%
3	18,151.00	73,800.00	15.00%		19,435.00
4	73,801.00	148,850.00	25.00%
5	148,851.00	226,850.00	28.00%
6	226,851.00	405,100.00	33.00%
7	405,101.00	457,600.00	35.00%
8	457,601.00	100,000,000.00	39.60%
Sheet4

 

[Vidar]

Hi
It will be a long explanation to solve this.

Take a look at this video from ExcelIsFun that cover your exact problem.
Come back if you have problems.

 

[joeu2004]

I'm trying to automatically calculate my business average income tax based on entering my business net income; for example $300K.

Set up the table as shown in A4:C11. Explained below.

[TABLE="class: grid, width: 690"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Taxl inc
[/TD]
[TD="align: right"]300,000[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Tax[/TD]
[TD="align: right"]74,904.50[/TD]
[TD]
[/TD]
[TD]=ROUND(SUMPRODUCT((B1>$A$5:$A$11)*(B1-$A$5:$A$11),$C$5:$C$11),2)[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]Over--[/TD]
[TD="align: right"]Tax Rate[/TD]
[TD="align: right"]Diff Rate[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]10.0%[/TD]
[TD]=B5[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]18,150[/TD]
[TD="align: right"]15.0%[/TD]
[TD="align: right"]5.0%[/TD]
[TD]=B6-B5[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]73,800[/TD]
[TD="align: right"]25.0%[/TD]
[TD="align: right"]10.0%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]148,850[/TD]
[TD="align: right"]28.0%[/TD]
[TD="align: right"]3.0%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD="align: right"]226,850[/TD]
[TD="align: right"]33.0%[/TD]
[TD="align: right"]5.0%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD="align: right"]405,100[/TD]
[TD="align: right"]35.0%[/TD]
[TD="align: right"]2.0%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD="align: right"]457,600[/TD]
[TD="align: right"]39.6%[/TD]
[TD="align: right"]4.6%[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Columns A and B come from the tax rate schedule.

Column C has the formulas shown in column D. Copy C6 and paste into C7:C11.

The formula in B2 calculates the tax on the amount in B1. The formula is shown in D2.

[EDIT] As a double-check, note that the IRS table indicates the tax is 50,765 plus 33%*(300,000 - 226,850), which is indeed 74,904.50.

 

[olimits7]

Thank you for all the replies!

VBA Geek, I tested your formula too and it seemed to work but for some reason when I typed in $149,000 it returned a very small amount back which didn't seem right.

joeu2004, I tested what you wrote and it seems to work properly for all different amounts!

Thank you...

 

[VBA Geek]

my formula returns 50,139.50 when you input 149,000 which also appears to be the correct value considering the description in your original post.
[TABLE="width: 183"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

Thank you for all the replies!

VBA Geek, I tested your formula too and it seemed to work but for some reason when I typed in $149,000 it returned a very small amount back which didn't seem right.

joeu2004, I tested what you wrote and it seems to work properly for all different amounts!

Thank you...

 

[joeu2004]

my formula returns 50,139.50 when you input 149,000 which also appears to be the correct value considering the description in your original post.

A basic understanding of how marginal rate tax tables work in general and how US tax rate schedules work in particular would be helpful.

You are referring to olimits7's statement: "$73,800 out of the $300K would be taxed at 15%".

That is literally incorrect. Olimits7 should have written: the amount between 18,150 and 73,800 is taxed at 15%. So the additional tax for that tier is 8,347.50.

See my next response to olimits7 for further explanation.

 

[joeu2004]

VBA Geek, I tested your formula too and it seemed to work

I'm surprised by that statement since VBA Geek's own example is incorrect. The tax on 100,000 is 16,712.50, not 19,435.

My number is based on the tax rate schedule found in the 2014 Form 1040-ES at www.irs.gov.

The tax on 100,000 is 10,162.50 plus 25% of the amount over 73,800. Thus, 10162.50 + 25%*(100000 - 73800) = 10162.50 + 6550.00 = 16,712.50.

joeu2004, I tested what you wrote and it seems to work properly for all different amounts! Thank you.

You're welcome. But the SUMPRODUCT formula is confusing to many. If you prefer, the following is an alternative formula that follows the IRS instructions more literally.

[TABLE="class: grid, width: 650"]
<tbody>[TR]
[TD="align: right"]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="colspan: 10, align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Taxl inc
[/TD]
[TD="align: right"]100,000.00
[/TD]
[TD]
[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Tax[/TD]
[TD="align: right"]16,712.50[/TD]
[TD]
[/TD]
[TD="colspan: 10"]=ROUND(VLOOKUP(B1,$A$5:$C$11,2)
+ VLOOKUP(B1,$A$5:$C$11,3)*(B1-VLOOKUP(B1,$A$5:$C$11,1)),2)[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="align: right"]

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

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

Tax is--[/TD]
[TD="align: right"]Plus
Excess
Times--[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]10.00%[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]18,150[/TD]
[TD="align: right"]1,815.00[/TD]
[TD="align: right"]15.00%[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]73,800[/TD]
[TD="align: right"]10,162.50[/TD]
[TD="align: right"]25.00%[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]148,850[/TD]
[TD="align: right"]28,925.00[/TD]
[TD="align: right"]28.00%[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD="align: right"]226,850[/TD]
[TD="align: right"]50,765.00[/TD]
[TD="align: right"]33.00%[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD="align: right"]405,100[/TD]
[TD="align: right"]109,587.50[/TD]
[TD="align: right"]35.00%[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD="align: right"]457,600[/TD]
[TD="align: right"]127,962.50[/TD]
[TD="align: right"]39.60%[/TD]
[TD="colspan: 10"]
[/TD]
[/TR]
</tbody>[/TABLE]

The numbers in the table in A5:C11 are taken directly from the IRS tax rate schedule in the 2014 Form 1040-ES.

The formula in B2 is shown in D2.

Note: If the taxable income were 148,850, for example, the VLOOKUP formula would use the numbers in row 8. Technically, the numbers in row 7 apply, since 148,850 is not "over 148,850". However, the difference should not matter in a properly-constructed marginal rate schedule, since the amount in B8 should be the tax we would compute based on row 7.

(Admittedly, a few years ago, I encountered a state marginal tax rate schedule that was not "properly constructed". However, it was obviously a mistake, and the state corrected it some time later.)

 

[VBA Geek]

joeu,

i base my formula on what the OP is asking not on the official way the tax is calculated, and his question seemd quite clear to me. I do know the way the tax is calculated, but if he asks to calculate it in such a way then I assume he has his own good reasons.

btw i think this modification of my formula should work with your interpretation

=SUMPRODUCT(--(MATCH(D1,B2:B8)>=(ROW(B2:B8)-1)),C2:C8,B2:B8-A2:A8)+(D1-LOOKUP(D1,B2:B8))*LOOKUP(D1,A2:C8)

A basic understanding of how marginal rate tax tables work in general and how US tax rate schedules work in particular would be helpful.

You are referring to olimits7's statement: "$73,800 out of the $300K would be taxed at 15%".

That is literally incorrect. Olimits7 should have written: the amount between 18,150 and 73,800 is taxed at 15%. So the additional tax for that tier is 8,347.50.

See my next response to olimits7 for further explanation.

 

[olimits7]

Sorry for the confusion...I went back to my original post and I see I made a mistake in writing this part "$73,800 out of the $300K would be taxed at 15%" this should have been "$55,650 out of the $300K would be taxed at 15%".

VBA Geek; ok, I'll try out the new formula you wrote above. Will this one work based on the official way tax is calculated excluding my mistake in the original post?

joeu2004; ok, I'll try out the VLOOKUP formula method too. The formula you provided me in your 1st post seemed to work fine in Excel, however, when I tried to open this spreadsheet in "Numbers" app on my iPhone I would get this error "All arguments to SUMPRODUCT must be the same length." but maybe with the VLOOKUP method it won't show this on the "Numbers" app.

Thank you!