Calculate Commission after tax

[happy77]

I need to calculate commission. We split the net profit 50/50. Normally, I'd just calculate it like:
Commission=Revenue - expense = profit x commission rate of 50%
=1845-900=945x.5 = 472.50 commission
A B C D
<TABLE style="WIDTH: 272pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=360 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" span=3 width=86><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 height=17 width=86>Revenue</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 width=86>Expense</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 width=86>Profit</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 width=102>Commission $</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 height=17 x:num>1845</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 x:num>900</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 x:num x:fmla="=A2-B2">945</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 x:num x:fmla="=C2*0.5">472.5</TD></TR></TBODY></TABLE>

However, my boss wants to pay commission after tax rate of 18%. Since the amount of commission will be part of expense, how can I calculate this in excel?

 

[jbeaucaire]

Like so:

Excel Workbook
	A	B	C	D	E
1	Revenue	Expense	Tax	Profit	Commission
2	1845.00	900.00	332.10	612.90	306.45
3	3200.00	1150.00	576.00	1474.00	737.00
Sheet2

 

[happy77]

The tax is corporate tax, not sales tax, so not based on revenue, rather should be based on =Revenue - Expense - Commission.

Profit should be =Revenue - Expense - Commission - Tax.

Seems like an equation with two variables, but I can't figure out how to solve.

 

[happy77]

What I want to do to calculate tax is this, but it is a circular reference.
=ROUND((A2-B2-E2)*0.18,2)

 

[jbeaucaire]

Just need more columns...

Excel Workbook
	A	B	C	D	E	F
1	Revenue	Expense	Profit	Commission	Tax	Final Comm.
2	1845.00	900.00	945.00	472.50	85.05	387.45
3	3200.00	1150.00	2050.00	1025.00	184.50	840.50
Sheet2

 

[ozborni]

Hi there,

I need to set something up quite similar to what this poster has requested.

With these figures the commission due is $274.79? (using iteration) I was hoping there was a way I could do this without a circular formula.

Refer below

a Revenue 1845.00
b Expense 900.00
d Commission 50% 274.79 (f*0.5)
c Profit Before Tax 670.21 (a-b-d)
e Tax Rate 18% 120.64 (c*.18)
f Profit After Tax 549.57 (f-e)

Any help would be appreciated

Cheers

 

[jbeaucaire]

The only thing that makes sense to me... pay commission on the Net Profit.

Excel Workbook
	A	B	C	D	E	F
1	Revenue	Expense	Gross Profit	Tax	Net Profit	Commission
2	$1,845.00	$ 900.00	$ 945.00	$ 170.10	$ 774.90	$ 387.45
Sheet1

 

[MrVillareal]

Hi,

Is this what you want? Computing Commission based on Profit After tax and After Commission?

Excel Workbook
	A	B	C	D	E	F
1	Revenue	Expenses	Profit Before Tax and Before Commission	Commission Base	Commission Rate After Tax	Commission
2	1845	900	945	670.21	41%	275
3	Supporting note		141%	100%
4
5
6	Note:
7	Commission Rate		50%
8	Tax Rate		18%
9	Commission Rate after tax		41%
Sheet13

Excel 2010

Cell Formulas
Range	Formula
C2	=A2-B2
C9	=C7*(1-C8)
D2	=C2/(1.41)
F2	=ROUND(D2*E2,0)

 

[ozborni]

That is exactly what I was after.

Thank you very much.

 

[MrVillareal]

You are so welcome. Im glad that I get the result you want