Price calculation

[agust]

I'm stuck with a formula for a price calculation where the service fee and the sales tax both increase with the other, if you catch my drift.

I sell an item for which I want to earn 100$ after sales tax and service fee. The sales tax is 10% and the service fee is 5%. How do I calculate the price? What do I multiply or divide 100 with to reach the correct price?

For example, if I do: 100 * (1 + 0.10 + 0.05) = 115, I will then lose 5.75 in service fee and 11.50 in sales tax, leaving me with 97.75. And If I try: (100 * 1.10) * 0.05 = 115.50, I then lose 5.78 in service fee and 11.55 in sales tax, leaving me with 98.17.

I can't seem to wrap my head around it... Very thankful for help!

 

[dreid1011]

For example, if I do: 100 * (1 + 0.10 + 0.05) = 115, I will then lose 5.75 in service fee and 11.50 in sales tax, leaving me with 97.75. And If I try: (100 * 1.10) * 0.05 = 115.50, I then lose 5.78 in service fee and 11.55 in sales tax, leaving me with 98.17.

I think you're calculations are off after you figure in the 10% and 5%. If I am not mistaken, you are calculating your lost % on the total after you added the fees. This will of course return a number smaller than your starting value of $100 when you subtract.

ie:
100 * 10% = 10
100 * 5% = 5
100 + 10 + 5 = 115

Your calculation of your losses:
115 * 10% = 11.50
115 * 5% = 5.75
115 - 11.50 - 5.75 = 97.75

Shouldn't it be:
115 - 10 - 5 = 100?

 

[PeteWright]

I think I got it if I understood your calculations:

Your selling price should be X / 0.855
Hence the selling price for a win of 100$ would be 116.959$

The explanation:
100$ = (X - X * 0.1) - (X - X * 0.01) * 0.05

So if you want to get 100$ you calculate like this:
Tax = X * 10% = 116.959 * 0.1 = 11.696$
leftover = X - Tax = 116.959$ - 11.696$ = 105.263$
Fee = leftover * 5% = 105.263$ * 0.05 = 5.263$
final leftover = leftover - Fee = 105.263$ - 5.263$ = 100$

Does that formula provide the result you are looking for?

 

[agust]

Your calculation of your losses:
115 * 10% = 11.50
115 * 5% = 5.75
115 - 11.50 - 5.75 = 97.75

Shouldn't it be:
115 - 10 - 5 = 100?

I pay 5% service fee on the total amount that I bill, plus also 10% tax on the same total amount. The amount that I bill must cover what I pay in tax and service fee - in order for me to end up with the 100$. I'm trying to figure out how to calculate what amount to bill, when I only know the amount that I need to end up with. Does that make sense?

 

[agust]

I think I got it if I understood your calculations:

Your selling price should be X / 0.855
Hence the selling price for a win of 100$ would be 116.959$

The explanation:
100$ = (X - X * 0.1) - (X - X * 0.01) * 0.05

So if you want to get 100$ you calculate like this:
Tax = X * 10% = 116.959 * 0.1 = 11.696$
leftover = X - Tax = 116.959$ - 11.696$ = 105.263$
Fee = leftover * 5% = 105.263$ * 0.05 = 5.263$
final leftover = leftover - Fee = 105.263$ - 5.263$ = 100$

Does that formula provide the result you are looking for?

Thank you for the thorough reply! I'm afraid it's not what I'm looking for... The service fee is charged on the total amount including tax (in your example 116.959$). So tax and service fee would rack up to 17,54385$ in total on 116.959$. Any idea how to solve that calculation..? Thanks again.

 

[dreid1011]

I pay 5% service fee on the total amount that I bill, plus also 10% tax on the same total amount. The amount that I bill must cover what I pay in tax and service fee - in order for me to end up with the 100$. I'm trying to figure out how to calculate what amount to bill, when I only know the amount that I need to end up with. Does that make sense?

Okay, how about this. With the following formula in B2, use Goal Seek with the following settings. You can see the end price of $117.65 satisfies the desired $100 target:

Book1
	A	B	C	D
1	End Price	Goal	10%	5%
2	$117.65	$100.00	$11.76	$5.88
Sheet2

Cell Formulas
Range	Formula
B2	B2	=A2-(A2*0.1)-(A2*0.05)
C2	C2	=A2*0.1
D2	D2	=A2*0.05

After running goal seek:

 

[dreid1011]

Your responses in posts #4 and #5 are contradicting. In #4, you say the 10% is calculated on the amount you bill, then the 5% is also calculated on the amount you bill. But in #5 you say the 5% is calculated on the amount you bill including the 10%. Please clarify which calculation is correct:

X = amount billed

A: (X+10%)+(X+5%)= total billed

OR

B: ((X+10%)+5%) = total billed

 

[agust]

Okay, how about this. With the following formula in B2, use Goal Seek with the following settings. You can see the end price of $117.65 satisfies the desired $100 target:

Book1
	A	B	C	D
1	End Price	Goal	10%	5%
2	$117.65	$100.00	$11.76	$5.88
Sheet2

Cell Formulas
Range	Formula
B2	B2	=A2-(A2*0.1)-(A2*0.05)
C2	C2	=A2*0.1
D2	D2	=A2*0.05

View attachment 101712

After running goal seek:
View attachment 101713

Thank you. I have now figured out that the calculation is as follows: PRODUCT(100/SUM(1-0,1-0,05))

 

[dreid1011]

Glad you found a solution.