Calculating Product Price with % + variable commission costs

[V_Netherlands]

Hi,

I want to calculate product prices on a large scale, however the formula that I have been using does not work for selling on a certain platform. This is because the platform in question does not only want a commission in the form of a % of the product price, but also a variable price depended on the product price.

The commission fees are as follows:
-Product price is lower than €10,- -> commission fee of 10% + €0,2
-Product price is between €10,- and €20,- -> commission fee of 10% + €0,4
-Product price is higher than €20,- -> commission fee of 10% + €0,8

For now I had the formula for price = costs/1-(commission + margin). Where price is the product price excl. vat, costs are the total costs of the product, commission is the % of commission and margin is the % of margin. Now I need to find a way to incooperate the changing 0,2/0,4/0,8 which is dependend on the output price.

Any help is greatly appreciated!

 

[etaf]

=IF ( Cost > 20 , (cost*0.1)+0.8 , IF( Cost > - 10 , (cost*0.1)+0.4 , (cost*0.1)+0.2 ))

 

[V_Netherlands]

=IF ( Cost > 20 , (cost*0.1)+0.8 , IF( Cost > - 10 , (cost*0.1)+0.4 , (cost*0.1)+0.2 ))

How would this work? Because the %margin and the %commission have to include the costs of the 0,2/0,4/0,8?

Can you give an example in Excel?

 

[etaf]

sorry, i miss-read and thought it was a commision calculation

 

[Dave Patton]

You did not provide examples of the expected results.
The following using Goal Seek.

Commission2020.xlsm
	A	B	C	D	E
4	Margin	15%
5	Cost	Commission	Margin	Price	Margin
6	$30.00	4.90	6.14	41.05	15%
5b

Cell Formulas
Range	Formula
B6	B6	=D6*0.1+LOOKUP(D6,{0,0.2;10,0.4;20.01,0.8})
C6	C6	=D6-(SUM(A6:B6))
E6	E6	=C6/D6

Named Ranges
Name	Refers To	Cells
Cost	='5b'!$A$6	C6