Reverse calculate commission and tax on commission

[simondb]

Hi, I have a situation where I know a final price and the percentage for commission & tax on the commission only. I need to reverse calculate this to get the starting price.

Example:
If the commission is 15% and the tax on the commission only is 20% and the final value is 118.00 then I need a formula to be able to calculate the original value (in this case it would be 100.00)
The commission and tax percentages are not fixed values.

Please help, I cannot seem to be able to do this.
Thank you

 

[kevin9999]

Your reasoning seems to be that the "tax on the commission only" is added to the original value plus the commission. If that's the case, then this gives you what you want:

test.xlsx
	B	C	D
1
2	Final Value =	$118.00
3
4	Commission =	15.0%
5
6	Tax on Commission =	20.0%
7
8	Original Value =	$100.00
9
Sheet3

Cell Formulas
Range	Formula
C8	C8	=C2/(1+(C4*(1+C6)))

 

[simondb]

Your reasoning seems to be that the "tax on the commission only" is added to the original value plus the commission. If that's the case, then this gives you what you want:

test.xlsx
	B	C	D
1
2	Final Value =	$118.00
3
4	Commission =	15.0%
5
6	Tax on Commission =	20.0%
7
8	Original Value =	$100.00
9
Sheet3

Cell Formulas
Range	Formula
C8	C8	=C2/(1+(C4*(1+C6)))

Perfect, thank you

 

[kevin9999]

Perfect, thank you

Glad to help and thanks for the feedback

 

[alansidman]

Here is the Mcode. I imported your file to Power Query. Highlighted the two columns and clicked on Remove Rows-->Remove Duplicates.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source)
in
    #"Removed Duplicates"