Formula for calculating eBay selling price

eaxlns

New Member
Joined
Apr 18, 2019
Messages
10
Hi folks,
I'm not sure if this has been posted before or if there is a guide on how to do this on this forum, so apologies if there is.
I want a formula which I can use to work out my selling price on eBay, assuming I want to make a 15% profit of the selling price, and the following are my costs:

Item cost £1.72
Shipping £0.87
eBay fees 10% of final selling price
PayPal fees 2.9% + £0.20 of final selling price

Many thanks
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
Can use the goal seek for this problem.
Data --> Forecast --> What-If Analysis --> Goal Seek

With your example values, it gives £3.77 as the selling price.
 
Last edited:

eaxlns

New Member
Joined
Apr 18, 2019
Messages
10
That’s great, but is it possible to use this as a formula, as I need to apply it to 55,000 products.
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
I can give you the logic but you will have to fill the formula to match your sheet layout.


The first thing you have to do is get the selling price of your item that generates a 15% Gross Margin.


In you example your total cost, is the item cost plus the shipping: 1.72 + .87 = 2.59. To get a Gross Margin of 15% for item that costs 2.59, you must divide the cost by the reciprocal of the GM.


So that is 2.59/(1-15%) = 3.05. Now we take care of the fees. First you add in the .20 Paypal Fee = 3.25. Now you have to get another GM of 12.9% which is the total of the Ebay 10% and the Paypal 2.9%.


Same as before you divide by the reciprocal. Now it is 3.25/(1-12.9%) = 3.73 which is your FINAL SELL Price. If you go backwards now you will see that


3.73 * 10% Ebay fee = .37
3.73 * 2.9% PPal fee = .11
also subtract the .20


These 3 add up to .68 subtract that from your sell of 3.73 and you have 3.05 which was your original sell price at a 15% margin.

If you layout all the base figures on a row you can generate a easy formula...

I hope this helps.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,910
See if this helps you out:

Excel 2012
ABCDE
1ProfitShippingeBay feesPayPal %PayPay fix
215%0.8710%2.90%0.2
3
4CostBreak even priceProfit price
51.723.2032153.683697
6
7Cost1.72
8Shipping0.87
9eBay fees0.320321
10Paypal %0.092893
11paypal Fixed0.2
12
13Total3.203215

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
C5=(A5+$B$2+$E$2)/(1-$C$2-$D$2)
E5=C5*(1+$A$2)
C7=A5
C8=B2
C9=C5*C2
C10=C5*$D$2
C11=$E$2
C13=SUM(C7:C11)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Everything from row 7 down is just for a check. You can just use the C5 and E5 formulas (or combine them) to get your answer.
 
Last edited:

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
@Eric

If you use a 3.68 sell price, after you deduct the fixed fee of .20, the 10% Ebay = .37 and the PPal of 2.9% = .11 you are left with 3.01. If the item costs 2.59 (cost + shipping) that leaves a Gross Margin % = 11.41%.
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
That was a typo, a 3.68 Selling Price leaves a 13.95% Gross Margin. Sorry about the confusion...
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,744
Office Version
2010
Platform
Windows
@eaxlns.... Hmm, 3 responses with 3 different answers. How can that be? Which should you use?

In part, that hinges on terminology and what you want profit to be based on: just the item cost, simply passing along the "operating costs" (shipping, ebay fees and paypal fees); or all of your costs.

Personally, I don't believe you should make a profit on shipping and fees. But that's entirely up to you.

The following demonstrates the formulas for the two different methods.


ABCDEF
1item cost1.72
2ship0.87
3ebay%10.00%
4paypal%2.90%
5pp fixed0.20
6prof%15.00%
7
8profit onitem costprofit onall costs
9ExactRoundedExactRounded
10price3.55173.563.86963.87
11ship-0.8700-0.87-0.8700-0.87
12ebay-0.3552-0.36-0.3870-0.39
13paypal-0.3030-0.30-0.3122-0.31
14net rev2.02352.032.30042.30
15prof%15.00%15.27%15.00%14.99%

<tbody>
</tbody>

Rich (BB code):
Formulas:
B10: =(B1/(1-B6)+B2+B5)/(1-B3-B4)
B11: =-B2
B12: =-B10*B3
B13: =-(B10*B4+B5)
B14: =SUM(B10:B13)
B15: =(B14-B1)/B14

C10: =ROUNDUP((B1/(1-B6)+B2+B5)/(1-B3-B4),2)
C11: =-B2
C12: =-ROUND(C10*B3,2)
C13: =-ROUND(C10*B4+B5,2)
C14: =SUM(C10:C13)
C15: =(C14-B1)/C14

E10: =(B1+B2+B5)/(1-B6-B3-B4)
E11: =-B2
E12: =-E10*B3
E13: =-(E10*B4+B5)
E14: =SUM(E10:E13)
E15: =(E10-B1+SUM(E11:E13))/E10

F10: =ROUNDUP((B1+B2+B5)/(1-B6-B3-B4),2)
F11: =-B2
F12: =-ROUND(F10*B3,2)
F13: =-ROUND(F10*B4+B5,2)
F14: =SUM(F10:F13)
F15: =(F10-B1+SUM(F11:F13))/F10

I highlighted the primary formulas.

The calculations in columns B and E are provided as a proof of concept. They demonstrate that exact profit margin is indeed calculated.

The calculations in columns C and F are the correct calculations, given the real-world requirement to round to unit of currency. This necessarily causes a difference in the profit margin. But it should be "close".

Note that if the profit is applied only to the item cost, excluding operating costs, the profit margin is really a percentage of net revenue (price minus operating costs), not the full price.

Since you specified a profit margin as a percentage of price per se, you would want the profit applied to all costs.

Note that my numbers different somewhat from the other 3 responses. This is explained in the comments below.


Can use the goal seek for this problem.
[....]
With your example values, it gives 3.77 as the selling price.
Comparable to my 3.87, I suspect. But since ParamRay did not show his formulas, we do not what the cause of the discrepancy is.


In you example your total cost, is the item cost plus the shipping: 1.72 + .87 = 2.59. To get a Gross Margin of 15% for item that costs 2.59, you must divide the cost by the reciprocal of the GM. So that is 2.59/(1-15%) = 3.05. [....] 3.73 which is your FINAL SELL Price.
My comparable numbers are 2.03 and 3.56. The difference is: I do not include shipping costs in the amount that the profit margin is based on.

If we exclude ebay and paypal operating costs, I see no reason not to also exclude the shipping cost. It is just another operating cost; that is, it is not a cost of goods sold.

After thought.... That depends on an interpretation of "shipping" in the OP. I interpret it to mean: the cost of shipping to the customer. Arguably, igold might interpret it to mean: the cost of shipping to the seller. Both interpretations are equally valid, IMHO. If igold's interpretation is correct, I would agree with his inclusion of shipping with cost of goods sold. In that case, change the B1/(1-B6)+B2 to (B1+B2)/(1-B6) in B10 and C10. Corresponding changes are also needed in the "proof of concept" formulas in 11:15. Let know if details are required.

In any case, note that igold's profit margin is based on the net revenue (price minus operating costs), not on the price, as eaxlns specified.


4Break even priceProfit price
53.2032153.683697

<tbody>
</tbody>
Your mistake is in simply multiplying the break-even price by (1+15%).

That modified price would change the ebay and paypay components in the derivation of the price, which is not reflected correctly.
 
Last edited:

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
I did read the shipping cost to be the cost of shipping to the seller. Therefore, I say that the true cost of the item is the "Landed Cost" which would include the shipping. Additionally, I took the OP to mean the Selling Price of the item before fees. Gross Margin is defined as the amount of money remaining after deducting the Cost of Good Sold. You would pay your operating costs out of your Gross Margin. The fees would be treated no different than the expense of keeping the lights on.
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,910
I chose my final formula of

=(Break-even price)*(1+percent profit)

because the OP said "I want to make a 15% profit of the selling price". However, I really didn't do a complete analysis. Multiplying the break-even price by the desired profit ended up with higher fees, so it ended up as a 13% profit on the sale price, or a 24% profit on the cost. Neither one is a desired answer. :oops:

It appears joeu2004 has the most complete and accurate analysis, so I'd stick with that.


On a related note, I spent a lot of time once working out the same type of analysis for another eBay seller. His setup was much more complicated, with different types of products getting different fees and tax rates. I finally concluded in that case that the only solution was to create a UDF that would start at a base sale price, probably the cost times a percentage, then would increment by 1, checking all the fees and taxes, and quit when all constraints were met. There just were no fixed formulas that covered all situations for him.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,378
Messages
5,486,520
Members
407,550
Latest member
LucasBordure

This Week's Hot Topics

Top