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

[FatFreddy]

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>

 

[FatFreddy]

Thank you Asad. I think this will explain better (especially as I now understand how to post an example here on the board).

Here is my calculation of the example with a 58,000 net income:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Tax Brackets			Taxable income in bracket	Marginal Rate	Tax in Bracket	Tax Paid	Net Income
2	$0	to	$10,320	$10,320	0.00%	$0	$0	$10,320
3	$10,321	to	$11,394	$1,074	15.00%	$161	$161	$11,233
4	$11,395	to	$15,000	$3,606	23.79%	$858	$1,019	$13,981
5	$15,001	to	$21,000	$6,000	28.79%	$1,727	$2,746	$18,254
6	$21,001	to	$29,590	$8,590	23.79%	$2,044	$4,790	$24,800
7	$29,591	to	$40,726	$11,136	29.95%	$3,335	$8,125	$32,601
8	$40,727	to	$59,180	$18,454	36.95%	$6,819	$14,944	$44,236
9	$59,181	to	$80,661	$21,481	38.67%	$8,307	$23,251	$57,410
10	$80,662	to	$81,452	$791	40.34%	$319	$23,570	$57,882
11	$81,453	to	$81,663	$211	44.34%	$94	$23,663	$58,000
Sheet1

With this additional information, is your idea for calculating backwards to arrive at the gross (when all I know is the net and the tax brackets) still possible?

 

[FatFreddy]

Hi

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

Thanks Wigi,

I freely admit my limited ability in this area, but what I did get from that page all seemed to be dealing with the forward conversion of calculating Net Income and Tax Paid when starting with Gross Income and Tax Brackets.

The challenge I am having is getting it turned around to calculate the Gross Income starting only with the Net Income and the known Tax Brackets...

 

[wigi]

Thanks Wigi,

I freely admit my limited ability in this area, but what I did get from that page all seemed to be dealing with the forward conversion of calculating Net Income and Tax Paid when starting with Gross Income and Tax Brackets.

The challenge I am having is getting it turned around to calculate the Gross Income starting only with the Net Income and the known Tax Brackets...

True, the article does not address this question I'm afraid.

 

[FatFreddy]

I have it!

Entering the Net Income after tax in cell D18, gives the correct Gross Income in Cell E18.

Thanks for your thoughts - they helped me get my head around this question to solve it myself.

Now that I have this done, I wonder if there is an easier, more compact way to write it out in my completed sheet. I will start another topic with that question.

Thanks Again!

FF

Excel Workbook
	A	B	C	D	E	F
1	Threshold	Rate	*	Cummulative Tax	Cummulative Net	Gross Sums
2	*	*	*	*	$0.00	FALSE
3	$0	0.00%	*	$0.00	$10,320.00	FALSE
4	$10,320	15.00%	*	$1,011.15	$16,049.85	FALSE
5	$17,061	20.60%	*	$1,057.29	$16,227.71	FALSE
6	$17,285	23.26%	*	$3,884.78	$25,556.22	FALSE
7	$29,441	20.06%	*	$5,143.54	$30,572.46	FALSE
8	$35,716	22.70%	*	$6,280.81	$34,445.19	$56,230.71
9	$40,726	29.70%	*	$15,400.79	$56,032.21	FALSE
10	$71,433	32.50%	*	$18,656.97	$62,795.03	FALSE
11	$81,452	36.50%	*	$18,862.10	$63,151.90	FALSE
12	$82,014	38.29%	*	$25,591.18	$73,996.82	FALSE
13	$99,588	40.70%	*	$36,448.32	$89,815.68	FALSE
14	$126,264	43.70%	*	$2,166,270.95	$2,833,729.05	FALSE
15	$5,000,000	*	*	*	*	*
16	*	*	*	*	*	*
17	*	*	*	Net (Entered):	Gross (calculated):	*
18	*	*	*	$45,345.00	$56,230.71	*
Sheet1

 

[FatFreddy]

For continuity, here is the new thread I started: http://www.mrexcel.com/forum/showthread.php?p=2068006

 

[asad]

Hello Freddy,

I was looking at your formula and I tested it along side my formula. They give the same results. I don't know where is the difference. If you insert 58000 in your file, it gives exactly the same answer as mine one. Have a look

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Threshold	Net income	Rate	|||	dRate		$ 58,000.00		Net Income Calculated
2
3	$10,320	$ 10,320.00	0%	|||	0.00%		$ 10,320.00		$ 10,320.00
4	$17,061	$ 16,049.85	15.00%	|||	15.00%		$ 17,061.00		$ 5,729.85
5	$17,285	$ 16,227.71	20.60%	|||	5.60%		$ 17,285.00		$ 177.86
6	$29,441	$ 25,556.22	23.26%	|||	2.66%		$ 29,441.00		$ 9,328.51
7	$35,716	$ 30,572.46	20.06%	|||	-3.20%		$ 35,716.00		$ 5,016.24
8	$40,726	$ 34,445.19	22.70%	|||	2.64%		$ 40,726.00		$ 3,872.73
9	$71,433	$ 56,032.21	29.70%	|||	7.00%		$ 71,433.00		$ 21,587.02
10	$81,452	$ 62,795.03	32.50%	|||	2.80%		$ 74,348.25		$ 1,967.79
11	$82,014	$ 63,151.90	36.50%	|||	4.00%
12	$99,588	$ 73,996.82	38.29%		1.79%
13	$126,264	$ 89,815.68	40.70%		2.41%
14			43.70%		3.00%
15
16			Gross Income if Net income is 58000 =				$ 74,348.25		$ 58,000.00
17			.
18
19
20
21	*	A	B	C	D	E	F
22	1	Threshold	Rate	*	Cummulative Tax	Cummulative Net	Gross Sums
23	2	*	*	*	*	$0.00
24	3	$0	0.00%	*	$0.00	$10,320.00
25	4	$10,320	15.00%	*	$1,011.15	$16,049.85
26	5	$17,061	20.60%	*	$1,057.29	$16,227.71
27	6	$17,285	23.26%	*	$3,884.78	$25,556.22
28	7	$29,441	20.06%	*	$5,143.54	$30,572.46
29	8	$35,716	22.70%	*	$6,280.81	$34,445.19
30	9	$40,726	29.70%	*	$15,400.79	$56,032.21	$ 74,348.24
31	10	$71,433	32.50%	*	$18,656.97	$62,795.03
32	11	$81,452	36.50%	*	$18,862.10	$63,151.90
33	12	$82,014	38.29%	*	$25,591.18	$73,996.82
34	13	$99,588	40.70%	*	$36,448.32	$89,815.68
35	14	$126,264	43.70%	*	$2,166,270.95	$2,833,729.05
36	15	$5,000,000	*	*
37	16	*	*	*
38	17	*	*	*	Net (Entered):	Gross (calculated):
39	18	*	*	*	$58,000.00	$ 74,348.24	*
Sheet1

 

[FatFreddy]

Well, crap. You are absolutely right. I was working off the complete spreadsheet, rather than just the part I had posted here, and inadvertently had toggled to a different set of tax brackets when I came back with the answer that did not match. I was moving too fast and not checking enough along the way - my apologies for the extra postings you had to make to get me to realize you were right in the first place!

I will go back now and have a more thorough look at your original posting

FF

Hello Freddy,

I was looking at your formula and I tested it along side my formula. They give the same results. I don't know where is the difference. If you insert 58000 in your file, it gives exactly the same answer as mine one. Have a look

 

[asad]

No problem Freddy. We all are here to learn and if one makes mistake it is no big deal. The main point is to get to the right answer even if it after a few mistakes/failures.

Anyway, the answer given by another person in your second thread is also very good and gives the answer.

 

[FatFreddy]

Thank you, Asad, that is most gracious of you. I truly appreciate you volunteering your time to assist me, and hope to pass the favour along to someone else in the future -though I may have a lot to learn before I will do it on this board i think!