Calculating income tax in reverse

[Aqusmacro]

Hi hopefully someone can help me out, been on this all weekend.

I am trying other ways to do a reverse income tax calculation. I was almost there but can’t figure out the last part.

I column D if I enter the value of $70k it calculates to a net income of $585K. If I enter $585 in D16 it calculates to the original amount of $70K, so calculates well. So far great.

In column C If I enter 70K and apply the Refundable abatement and personal deductions It gives me $62347.23. If enter this amount in C16 I need to get to the original $70k but getting $70809. What do I need to modify so it calculates all in reverse?

The parts in green I don’t need just using to test my yellow calculations.

Thanks

880340-progressive-tax-calculation-formula-in-reverse.xlsx
	A	B	C	D	E	F	G	H
1
2
3
4		CalculateNet from Gross
5
6		Amount	$ 70,000.00	70000	Tax Rates
7		Tax	11415	11415	From	TO	Rate	Base
8		Net	58585	58585	0	0	0.0%	0
9		Refundable quebec abatement	$ 1,512.23		1	53359	15.0%	0
10		Personal deductions:	$ 2,250.00		53360	106717	20.5%	8004
11			$ 7,652.78		106718	235675	26.0%	18942
12			$ 62,347.23
13
14		Calculate Gross from Net after Tax
15
16		Net	62347.23	58585.00	Net to Taxable
17		Tax	12385	11415	From	TO	Rate	Base
18		Gross	74732.23	70000	0	0	0%	0
19		Refundable quebec abatement	$ 1,672.28		1	45355	15.0%	0
20		Personal deductions:	$ 2,250.00		45356	87775	20.5%	8004
21			$ 8,462.73		87776	235675	26.0%	18942
22		Gross	$ 70,809.96
23
24	Refundable quebec abatement	$ 1,672.28
25	Personal deductions:	$ 2,250.00
26
27
28	Federal Tax (Quebec residents)
29	Quebec abatement (% of federal tax)	16.50%
30	Present-day Federal basic personal deduction:	$ 15,000.00
31	Federal base rate	15.00%
Sheet1

Cell Formulas
Range	Formula
D6	D6	=C6
D7	D7	=ROUND((D6-LOOKUP(D6,E8:E12,E8:E12))*LOOKUP(D6,E8:E12,G8:G12)+LOOKUP(D6,E8:F12,H8:H12),0)
C8:D8	D8	=D6-D7
E9:E11,E19:E21	E9	=F8+1
H9:H11	H9	=ROUND((F8-E8)*(G8),0)+H8
C7	C7	=ROUND((C6-LOOKUP(C6,E8:E12,E8:E12))*LOOKUP(C6,E8:E12,G8:G12)+LOOKUP(C6,E8:E12,H8:H12),0)
C9	C9	=((C7-C10)*B29)
C10,C20	C10	=($B$30*$B$31)
C11,C21	C11	=C7-C9-C10
C12	C12	=C6-C11
D17	D17	=ROUND((D16-LOOKUP(D16,E18:E22,E18:E22))*LOOKUP(D16,E18:E22,G18:G22)/(1-LOOKUP(D16,E18:E22,G18:G22))+LOOKUP(D16,E18:E22,H18:H22),0)
C18:D18	D18	=D16+D17
E18:H18,G19:H21	E18	=E8
F19:F21	F19	=F9-H10
C17	C17	=ROUND((C16-LOOKUP(C16,E18:E22,E18:E22))*LOOKUP(C16,E18:E22,G18:G22)/(1-LOOKUP(C16,E18:E22,G18:G22))+LOOKUP(C16,E18:E22,H18:H22),0)
C19	C19	=((C17-B25)*B29)
C22	C22	=C16+C21
B24	B24	=((C17-B25)*B29)
B25	B25	=(B30*B31)

 

[Dave Patton]

Please check your tax calculation.

Income Tax Cdn.xlsx
	A	B	C	D	E	F
1
2
3				Canada	Tax Rates
4		CalculateNet from Gross			Brackets	Rates
5
6		Amount	70,000.00		0	15.0%
7		CA Tax net	9,165.26		53,359	20.5%
8		Net	60,834.75		106,717	26.0%
9		Refundable quebec abatement	1512.27		165,430	29.0%
10					235,675	33.0%
11			7,652.99
12			62,347.01
Sheet2

Cell Formulas
Range	Formula
C7	C7	=CA_Tax23(E6:F10,C6,B30)
C8	C8	=C6-C7
C9	C9	=C7*B29
C11	C11	=C7-C9
C12	C12	=C6-C11

Lambda Functions
Name	Formula
CA_Tax23	=LAMBDA(rngRateData,TaxableIncome,TaxCredits,LET(rng,rngRateData,s,TaxableIncome,cr,TaxCredits,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((s>b)*(s-b)*(r-ro))-cr*0.15))

Income Tax Cdn.xlsx
	B	C
16	Net	62,347.01
17	Tax	9,165.26
18	Gross
19	Refundable quebec abatement	-1,512.27
20
21
22	Gross	70,000.00
Sheet2

Cell Formulas
Range	Formula
C17	C17	=C7
C22	C22	=SUM(C16:C20)

 

[Aqusmacro]

Thanks but I was probably not clear. I need the opposite. Input net and get the gross. My gross to net is working.

 

[Aqusmacro]

Not sure what I am doing wrong. First time I use Lamba functions. I read-up and created a name but getting errors.
Also I see from your sheet from net to gross you are referencing values in the top part of the gross to net. That can not work because I do not know those values unless I put the gross value which I don't know. Where I am having trouble is the Que abatement.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3				Canada	Tax Rates
4		CalculateNet from Gross			Brackets	Rates					CA_Tax23	#CALC!
5
6		Amount	70000		0	15.0%
7		CA Tax net	#REF!		53359	20.5%
8		Net	#REF!		106717	26.0%
9		Refundable quebec abatement	#REF!		165430	29.0%
10					235675	33.0%
11			#REF!
12			#REF!
13
14
15
16		Net	62347.01
17		Tax	#REF!
18		Gross
19		Refundable quebec abatement	-1512.27
20
21
22		Gross	#REF!
23
24	Refundable quebec abatement	#REF!
25	Personal deductions:	$ 2,250.00
26
27
28
29		16.50%
30		$ 15,000.00
31		15.00%
Sheet1

Cell Formulas
Range	Formula
L4	L4	=LAMBDA(rngRateData,TaxableIncome,TaxCredits,LET(rng,rngRateData,s,TaxableIncome,cr,TaxCredits,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((s>b)*(s-b)*(r-ro))-cr*0.15))
C7	C7	=CA_Tax23(E6:F10,C6,B30)
C8	C8	=C6-C7
C9	C9	=C7*B29
C11	C11	=C7-C9
C12	C12	=C6-C11
C17	C17	=C7
C22	C22	=SUM(C16:C20)
B24	B24	=((C17-B25)*B29)
B25	B25	=(B30*B31)

Named Ranges
Name	Refers To	Cells
CA_Tax23	=Sheet1!$L$4	C7

 

[Dave Patton]

Lambda requires Excel 365.

To calculate gross from net, try Goal Seek.

I started with with a base calculation income of 50000
I used goal seek and specified the required amount net and Goal Seek changing the gross number

Initial

Income Tax Cdn.xlsx
	I	J
9	Gross	50,000.00
10	Federal	5,250.00
11	Quebec ref	16.5%
12	Quebec ref	866.25
13	Net	45,616.25
Sheet2

Cell Formulas
Range	Formula
J10	J10	=SUM((J9>{0;53359;106717;165430;235675})*(J9-{0;53359;106717;165430;235675})*({0.15;0.055;0.055;0.03;0.04}))-B33*0.15
J12	J12	=J10*J11
J13	J13	=J9-J10+J12

Income Tax Cdn.xlsx
	I	J	K
8			or
9	Gross	70,000.00	70000.00
10	Federal	9,165.25	9165.25
11	Quebec ref	16.5%	16.5%
12	Quebec ref	1512.27	1512.27
13	Net	62,347.01	62,347.01
14
Sheet2

Cell Formulas
Range	Formula
J10	J10	=SUM((J9>{0;53359;106717;165430;235675})*(J9-{0;53359;106717;165430;235675})*({0.15;0.055;0.055;0.03;0.04}))-B33*0.15
K10	K10	=CA_Tax23(E9:F13,K9,B33)
J12:K12	J12	=J10*J11
J13:K13	J13	=J9-J10+J12

Lambda Functions
Name	Formula
CA_Tax23	=LAMBDA(rngRateData,TaxableIncome,TaxCredits,LET(rng,rngRateData,s,TaxableIncome,cr,TaxCredits,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((s>b)*(s-b)*(r-ro))-cr*0.15))

 

[Aqusmacro]

I am on office 365. Not sure why it is not working. Maybe think I see in your formula you have named ranges like Taxableincome, taxcredits etc. If that is the case which areas are named?
I also tried goal seek but the reason I wanted a formula on a spreadsheet is that what I posted is only part of it. After that I need to apply to many other values and goal seek would be too long for every one and if I change a value I got to start over

 

[Dave Patton]

"I am on office 365. Maybe think I see in your formula you have named ranges like Taxableincome, taxcredits etc. "

No , these names are prompts for the input required. Check that you entered the Lambda value as specified; ensure that there is an "=" and just one "=".

 

[Dave Patton]

Ensure that you recorded the Lambda function.

With name manager,
specify the name new CA_Tax23
in value area enter the formula =LAMBDA(rngRateData,TaxableIncome,TaxCredits,LET(rng,rngRateData,s,TaxableIncome,cr,TaxCredits,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((s>b)*(s-b)*(r-ro))-cr*0.15))

Save

When you enter the formula in a cell like =Ca_Tax23( Excel will show the prompts that are built into the function such rngRateDate ...

 

[Aqusmacro]

okmy error was when I named the labda function in refers to I put the cell I put the function instead of the actual function. So working great thanks

 

[Dave Patton]

Thanks for the feedback.