Need a formula to calculate gross income when I know net income

FatFreddy · Sep 21, 2009

I need a formula for excel that would allow me to calculate the gross income required to produce a certain net income. I will know the net income and the marginal brackets and rates.

For example, here are a set of marginal rates (there is no tax below $10,320), and suppose my net income was $58,432. I know how to manually figure this out, but how can I phrase it so that it will be easy to deal with in a spreadsheet with multiple sets of tax rates and widely varying net incomes?

<table x:str="" style="border-collapse: collapse; width: 240pt;" border="0" cellpadding="0" cellspacing="0" width="320"><col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">Threshold</td> <td class="xl25" style="border-left: medium none; width: 48pt;" width="64">|||</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">Rate</td> <td class="xl25" style="border-left: medium none; width: 48pt;" width="64">|||</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">dRate</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" x:num="10320" height="17">$10,320</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.15">15.00%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.15">15.00%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" x:num="17061" height="17">$17,061</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.20599999999999999">20.60%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="5.5999999999999994E-2">5.60%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" x:num="17285" height="17">$17,285</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.2326">23.26%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="2.6600000000000013E-2">2.66%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" x:num="29441" height="17">$29,441</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.2006">20.06%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="-3.2000000000000001E-2">-3.20%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" x:num="35716" height="17">$35,716</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.22700000000000001">22.70%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="2.6400000000000007E-2">2.64%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" x:num="40726" height="17">$40,726</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.29699999999999999">29.70%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.07">7.00%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" x:num="71433" height="17">$71,433</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.32500000000000001">32.50%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="2.8000000000000025E-2">2.80%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" x:num="81452" height="17">$81,452</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.36499999999999999">36.50%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.04">4.00%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" x:num="82014" height="17">$82,014</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.38290000000000002">38.29%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="1.7900000000000027E-2">1.79%</td> </tr> </tbody></table>

wigi · Sep 21, 2009

And how would you do this manually?

Does the rate apply on the amount between to thresholds, or is it a kind of average rate on the full amount?

asad · Sep 21, 2009

You think this would be some where close to the solution?

Excel Workbook

A

B

C

D

E

F

G

1

Threshold

|||

Rate

|||

dRate

58000

2

3

$10,320

|||

15.00%

|||

15.00%

12141.18

4

$17,061

|||

20.60%

|||

5.60%

8489.924

5

$17,285

|||

23.26%

|||

2.66%

291.8947

6

$29,441

|||

20.06%

|||

-3.20%

15206.4

7

$35,716

|||

22.70%

|||

2.64%

8117.723

8

$40,726

|||

29.70%

|||

7.00%

7126.6

9

$71,433

|||

32.50%

|||

2.80%

25591.11

10

$81,452

|||

36.50%

|||

4.00%

11

$82,014

|||

38.29%

|||

1.79%

12

13

Gross Income if Net income is 58000 =

76964.83

Sheet1

asad · Sep 21, 2009

I make the same mistake in posting almost every time. Some people just don't learn

.
Anyway, here is the correct formula but be CAREFUL, you will have to remove spaces from either side of "<" and ">" symbols.

Code:

=IF($G$1 < A2,"",IF($G$1 > A3,((A3-A2)/(1-C3)),(($G$1-A2)/(1-C3))))

FatFreddy · Sep 21, 2009

I think you have something there, but I have explained the tax rates poorly.

Here is an expanded tax rate chart. The first rate of 0% applies from $0 to $10,320, then 15% from $10,321 to $17,061, etc...

<table x:str="" style="border-collapse: collapse; width: 240pt;" border="0" cellpadding="0" cellspacing="0" width="320"><col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" x:num="0" width="64" height="17">$0</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">to</td> <td class="xl24" style="border-left: medium none; width: 48pt;" x:num="10320" width="64">$10,320</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">|||</td> <td class="xl25" style="border-left: medium none; width: 48pt;" x:num="0" width="64">0.00%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="10321" height="17">$10,321</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="17061">$17,061</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.15">15.00%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="17062" height="17">$17,062</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="17285">$17,285</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.20599999999999999">20.60%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="17286" height="17">$17,286</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="29441">$29,441</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.2326">23.26%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="29442" height="17">$29,442</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="35716">$35,716</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.2006">20.06%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="35717" height="17">$35,717</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="40726">$40,726</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.22700000000000001">22.70%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="40727" height="17">$40,727</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="71433">$71,433</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.29699999999999999">29.70%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="71434" height="17">$71,434</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="81452">$81,452</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.32500000000000001">32.50%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="81453" height="17">$81,453</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="82014">$82,014</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.36499999999999999">36.50%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="82015" height="17">$82,015</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="99588">$99,588</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.38290000000000002">38.29%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="99589" height="17">$99,589</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">to</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="126264">$126,264</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.40699999999999997">40.70%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" x:num="126265" height="17">$126,265</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">plus</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">|||</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.437">43.70%</td> </tr> </tbody></table>

I apologize for not knowing my way around this board yet - if I'd known I'd get a reply so quickly, I would have taken the time to figure out how to post properly first!

Asad, can I post the actual file where I am trying to apply this calculation? Or send it to you directly for your thoughts?

wigi · Sep 21, 2009

Hi

There are working examples on this page: http://www.ozgrid.com/Excel/sliding-bracket.htm, together with some guidance.

asad · Sep 21, 2009

FatFreddy said:
I think you have something there, but I have explained the tax rates poorly.

Here is an expanded tax rate chart. The first rate of 0% applies from $0 to $10,320, then 15% from $10,321 to $17,061, etc...

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320 x:str=""><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt" class=xl24 height=17 width=64 x:num="0">$0</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt" class=xl24 width=64>to</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt" class=xl24 width=64 x:num="10320">$10,320</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt" class=xl24 width=64>|||</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt" class=xl25 width=64 x:num="0">0.00%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="10321">$10,321</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="17061">$17,061</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.15">15.00%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="17062">$17,062</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="17285">$17,285</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.20599999999999999">20.60%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="17286">$17,286</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="29441">$29,441</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.2326">23.26%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="29442">$29,442</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="35716">$35,716</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.2006">20.06%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="35717">$35,717</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="40726">$40,726</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.22700000000000001">22.70%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="40727">$40,727</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="71433">$71,433</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.29699999999999999">29.70%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="71434">$71,434</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="81452">$81,452</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.32500000000000001">32.50%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="81453">$81,453</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="82014">$82,014</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.36499999999999999">36.50%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="82015">$82,015</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="99588">$99,588</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.38290000000000002">38.29%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="99589">$99,589</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>to</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24 x:num="126264">$126,264</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.40699999999999997">40.70%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl24 height=17 x:num="126265">$126,265</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>plus</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>

</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl24>|||</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl25 x:num="0.437">43.70%</TD></TR></TBODY></TABLE>

I apologize for not knowing my way around this board yet - if I'd known I'd get a reply so quickly, I would have taken the time to figure out how to post properly first!

Asad, can I post the actual file where I am trying to apply this calculation? Or send it to you directly for your thoughts?

Freddy,
I think my formula already works around this new table of yours. It does take into consideration that the rate is only for the amount greater than previous slab and less than or equal to current slab.

What would your answer be if you calculated for 58000 net income?
Asad

FatFreddy · Sep 21, 2009

Hmmm... When I calculate forwards from $76964.83, I get tax of $21821.31 and a net of $55143.52

To get a net of $58,000, I need a gross of $81663.48, for tax of $23,663.48

I thought the tax brackets were being picked up incorrectly in your first post - the tax rates are based on ranges that are gross of tax. So the gross is being shown in that table but not the net. Does that explain better?

(I want to post an example here, but I haven't yet found the explanation of how to post a bit of a spreadsheet other than as a link to an external picture)