Formula for calculating eBay selling price
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Formula for calculating eBay selling price

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Formula for calculating eBay selling price

    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

  2. #2
    Board Regular ParamRay's Avatar
    Join Date
    Aug 2014
    Location
    England, UK
    Posts
    1,192
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for calculating eBay selling price

    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 by ParamRay; Jul 21st, 2019 at 12:15 PM.
    Windows 10, Excel 365

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for calculating eBay selling price

    That’s great, but is it possible to use this as a formula, as I need to apply it to 55,000 products.

  4. #4
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,314
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula for calculating eBay selling price

    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.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,345
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula for calculating eBay selling price

    See if this helps you out:

    Excel 2012
    A B C D E
    1 Profit Shipping eBay fees PayPal % PayPay fix
    2 15% 0.87 10% 2.90% 0.2
    3
    4 Cost Break even price Profit price
    5 1.72 3.203215 3.683697
    6
    7 Cost 1.72
    8 Shipping 0.87
    9 eBay fees 0.320321
    10 Paypal % 0.092893
    11 paypal Fixed 0.2
    12
    13 Total 3.203215
    Sheet2

    Worksheet Formulas
    Cell Formula
    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)

    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 by Eric W; Jul 21st, 2019 at 01:34 PM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  6. #6
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,314
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula for calculating eBay selling price

    @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

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  7. #7
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,314
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula for calculating eBay selling price

    That was a typo, a 3.68 Selling Price leaves a 13.95% Gross Margin. Sorry about the confusion...
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  8. #8
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,376
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula for calculating eBay selling price

    @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.


    A B C D E F
    1 item cost 1.72
    2 ship 0.87
    3 ebay% 10.00%
    4 paypal% 2.90%
    5 pp fixed 0.20
    6 prof% 15.00%
    7
    8 profit on item cost profit on all costs
    9 Exact Rounded Exact Rounded
    10 price 3.5517 3.56 3.8696 3.87
    11 ship -0.8700 -0.87 -0.8700 -0.87
    12 ebay -0.3552 -0.36 -0.3870 -0.39
    13 paypal -0.3030 -0.30 -0.3122 -0.31
    14 net rev 2.0235 2.03 2.3004 2.30
    15 prof% 15.00% 15.27% 15.00% 14.99%

    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.


    Quote Originally Posted by ParamRay View Post
    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.


    Quote Originally Posted by igold View Post
    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.


    Quote Originally Posted by Eric W View Post
    4 Break even price Profit price
    5 3.203215 3.683697
    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 by joeu2004; Jul 21st, 2019 at 05:35 PM.

  9. #9
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,314
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula for calculating eBay selling price

    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 by igold; Jul 21st, 2019 at 06:58 PM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,345
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula for calculating eBay selling price

    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.

    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •