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!

 

[olimits7]

@joeu2004, I'm trying to see how the "Tax is" column values are being calculated but I can't seem to figure out the formula that they used on the 1040 to get these numbers. Do you how, for example, they got $10,162.50 for the 2nd tier?

Thank you!

 

[joeu2004]

@joeu2004, I'm trying to see how the "Tax is" column values are being calculated but I can't seem to figure out the formula that they used on the 1040 to get these numbers. Do you how, for example, they got $10,162.50 for the 2nd tier?

That's the 3rd tier ($73,800). The 1st tier is $0 to $18,150.

My point was: you can take that as a given, since the IRS calculated it for you.

But if you want to understand how the IRS made the calculation, step through my SUMPRODUCT formula manually, calculating the tax on each amount in column A.

For taxable income of $73,800 to $148,850, the base tax is:
  1,815.00 = 10% of 18,150 (18150 - 0)
+ 8,347.50 = 15% of 55,650 (73800 - 18150)
---------
 10,162.50
Add 25% of taxable income minus 73,800.

For taxable income of $148,850 to $226,850, the base tax is:
  1,815.00 = 10% of 18,150 (18150 - 0)
+ 8,347.50 = 15% of 55,650 (73800 - 18150)
+18,762.50 = 25% of 75,050 (148850 - 73800)
----------
 28,925.00
Add 28% of taxable income minus 148,850.

 

[joeu2004]

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."

I am not familiar with any iPhone apps, much less the "Numbers" app.

However, the error message "all arguments ... must be the same length" sounds like a data entry error.

They are the same length in my original formula, namely:
=ROUND(SUMPRODUCT((B1>$A$5:$A$11)*(B1-$A$5:$A$11),$C$5:$C$11),2)

But sometimes, apps give the wrong message for a condition they are not designed to understand.

Terms like (B1>$A$5:$A$11) are not the way SUMPRODUCT was originally intended to be used. So it would not surprise me if a phone app implementation (scaled-down due to limited memory) does not handle it.

My VLOOKUP formula uses VLOOKUP in a conventional way. So yes, it might work better in a scaled-down phone app implementation.

 

[joeu2004]

Errata (to late to edit)....

if you want to understand how the IRS made the calculation, step through my SUMPRODUCT formula manually, calculating the tax on each amount in column A.

Although you could do that, what I demonstrated in the "code" block is not how the SUMPRODUCT formula works. It is, in fact, how the IRS makes the calculation.

The SUMPRODUCT formula is not, although it does do the job another equivalent way.

 

[olimits7]

That's the 3rd tier ($73,800). The 1st tier is $0 to $18,150.

My point was: you can take that as a given, since the IRS calculated it for you.

But if you want to understand how the IRS made the calculation, step through my SUMPRODUCT formula manually, calculating the tax on each amount in column A.

For taxable income of $73,800 to $148,850, the base tax is:
  1,815.00 = 10% of 18,150 (18150 - 0)
+ 8,347.50 = 15% of 55,650 (73800 - 18150)
---------
 10,162.50
Add 25% of taxable income minus 73,800.

For taxable income of $148,850 to $226,850, the base tax is:
  1,815.00 = 10% of 18,150 (18150 - 0)
+ 8,347.50 = 15% of 55,650 (73800 - 18150)
+18,762.50 = 25% of 75,050 (148850 - 73800)
----------
 28,925.00
Add 28% of taxable income minus 148,850.

Ok, great...thank you! This makes more sense on how the IRS gets to those figures now.

 

[olimits7]

I am not familiar with any iPhone apps, much less the "Numbers" app.

However, the error message "all arguments ... must be the same length" sounds like a data entry error.

They are the same length in my original formula, namely:
=ROUND(SUMPRODUCT((B1>$A$5:$A$11)*(B1-$A$5:$A$11),$C$5:$C$11),2)

But sometimes, apps give the wrong message for a condition they are not designed to understand.

Terms like (B1>$A$5:$A$11) are not the way SUMPRODUCT was originally intended to be used. So it would not surprise me if a phone app implementation (scaled-down due to limited memory) does not handle it.

My VLOOKUP formula uses VLOOKUP in a conventional way. So yes, it might work better in a scaled-down phone app implementation.

Yes, I'm guessing it's an iPhone app limitation; I'll test VLOOKUP to see if it works on the iPhone app properly.