Any suggestions to make this calculation more compact? (Gross Pre-tax Income derived from Net Income after tax)

[FatFreddy]

I have created a small spreadsheet to derive the Gross Pre-tax Income when all that is known is the Net Income, and the Tax Brackets and Rates.

In the example that follows, when the known Net Income is entered in D18, E18 displays the correct Gross Income.

The Tax Brackets and Rates are not static, but are in turn drawn from a set of 13 different Provincial rate groups using an INDIRECT function.

This was the best solution I could think of and it is accurate, but if possible, I'd like to condense this a bit more if there is a simple way to do it that I have overlooked and am very receptive to any ideas that may be obvious to others with more experience.

Thank you for your consideration

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	*
, FF. Sheet1

 

[MorganO]

You can use the following formula in Cell E18 and eliminate Column F entirely:

=INDEX(E3:E14,MATCH(D18,E3:E14,1))+INDEX(D3:D14,MATCH(D18,E3:E14,1))+(($D$18-INDEX(E3:E14,MATCH(D18,E3:E14,1)))/(1-INDEX(B3:B14,MATCH(D18,E3:E14,1)+1)))

Hopefully this helps point you in an alternate direction.

Take care,

Owen

 

[asad]

You can use the following formula in Cell E18 and eliminate Column F entirely:

=INDEX(E3:E14,MATCH(D18,E3:E14,1))+INDEX(D3:D14,MATCH(D18,E3:E14,1))+(($D$18-INDEX(E3:E14,MATCH(D18,E3:E14,1)))/(1-INDEX(B3:B14,MATCH(D18,E3:E14,1)+1)))

Hopefully this helps point you in an alternate direction.

Take care,

Owen

Even this one gives the same answer as mine

Excel Workbook
	A	B	C	D	E	F	G
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	*
40
41						$ 74,348.24
Sheet1

 

[FatFreddy]

Thank you MorganO.

That is exactly what I was thinking before Asad pointed out he had already answered my question completely in another thread!

I will take what you added to my solution and place it by his solution and see what I can learn from the comparison of the two responses to perhaps use in the future. I have just discovered this forum - what a great resource this is, thank you!