iA


Excel Formula for Calculating eBay Listing and Final Value Fees

by trevor. Average Reading Time: less than a minute.

Excel is very much the battle axe of the information age. White shirted warriors slog it out across the battle field of cells and pivot tables.

Anyway I am getting overexcited.  Here is formulas for calculating eBay final value and listing fees

Listing fee

=IF(D3<0.99, 0.1, IF(D3<4.99,0.15,IF(D3<14.99,0.25,IF(D3<29.99,0.5,
IF(D3<99.99,1.30)))))/E3

Where D3 is the eBay starting or fixed price and E3 is the sell through rate. 

Final Value fee

=IF(D3<29.99, D3*0.075, IF(D3<599.99, 2.24+(D3-29.99)
*0.045,2.24+25.65+ (D3-599.99)*0.019))

Where again D3 is the eBay final price, be it auction or fixed price

11 comments on ‘Excel Formula for Calculating eBay Listing and Final Value Fees’

  1. Scott Merrill says:

    I desperately want to buy you a coffee …and I was *almost* there – but I’m running into more frustration, maybe you can help!

    In the formula you listed:
    =IF(D3<29.99, D3*0.075, IF(D3<599.99, 2.24+(D3-29.99)
    *0.045,2.24+25.65+ (D3-599.99)*0.019))

    (assuming D3 is final sales price)

    a) the correct first part of that, based on new eBay pricing in Feb 2008 should be =IF(D3<25.00, D3*.0875….)

    That part alone saved me a ton of work, as many of my auctions that I track in Excel are less than $25.00. However, I do have a bunch that are over $25.00 and that is where I ran into a problem with the rest of the formula.

    I’m not an Excel geek by any means …I can pick apart most things and make them work for me, but the second half of that expression is lost to me.

    Here is the new eBay fee structure, and maybe you could help a brother out?:

    $.01 – $25.00
    8.75% of closing value

    $25.01 – $1,000.00
    8.75% of the initial $25.00 ($2.19), plus 3.5% of the remaining balance from $25.01 to $1000.00

    $1,000.00 and over
    8.75% of the initial $25.00 ($2.19), plus 3.5% of the remaining balance from $25.01 to $1000.00 ($34.12), plus 1.5% of the remaining closing value over $1,000.01.

    I tried hacking it apart and rewriting it, and just made a mess of it. Would love your help!

    Scott

  2. trevor says:

    Here is the formula for US sellers

    =IF(D3<=25, D3*0.085, IF(D3<=1000, 2.19+(D3-25) *0.035,2.19+34.12+ (D3-1000)*0.015))

  3. Scott Merrill says:

    =IF(D3<=25, D3*0.085, IF(D3<=1000, 2.19+(D3-25)
    *0.035,2.19+34.12+ (D3-1000)*0.015))

    Thanks for posting this. I have a question though…

    The way I read the formula, the first part and the second part are “semi” redundant.

    On the “IF(D3<=1000 part, how does the formula know which IF to apply?

    Meaning, a $12 final value meets both the first and second condition.

    Shouldn’t the part that reads IF(D325.01, D3<1000, 2.19+…..etc?

    Seems that way, you allow for items between 25.01 and 1000.00

    Did I make any sense just now, or should I just trust that you’re right? Again, I’m not an Excel guru, but the logic doesn’t seem to account for that middle part.

  4. Scott Merrill says:

    I think I backspaced over part of my post. What I meant to say was:

    Shouldn’t the part that reads IF(D325.01, D3<1000, 2.19+…..etc? instead read:

    IF(D3<=25.01, D3<1000, 2.19+…..

  5. Andy Geldman says:

    There is a tool called 3-Clicks that adds a lot of functions to Excel which can then talk directly to eBay:

    http://www.prime2s.com/3-Clicks/
    http://www.prime2s.com/3-Clicks/functions.html

    If you do the functions yourself you do need to fully understand them because you will need to update them when the fees change!

    You could use one of the online fee calculators to test your functions. For example:

    http://www.auctionfeecalculator.com/

  6. Skywalkman-Geforce says:

    All these do not work correctly
    This formular is perfect for the Technology fees as long as you sell items under 299.98, if you sell higher just add another IF and put in the correct fees for sales above 299.99

    =IF(B29<29.99,B29*0.0525,IF(B29<99.99,(B29-29.99)*0.03+1.57,IF(B29<199.99,(B29-99.99)*0.025+3.67,IF(B29<299,(B29-199.99)*0.02+6.17))))

  7. Tom Lewter says:

    I found this on the web:

    Listing:
    =IF($C12<1,0.15,IF($C12<10,0.35,IF($C12<25,0.55,IF($C12<50,1,IF($C12<200,2,IF($C12<500,3,4))))))

    Final Value Fees:
    =IF(D12<25.01,0.0875*D12,IF(D12<1000.01,(2.19+((D12-25)*0.035)),36.31+((D12-1000)*0.015)))

  8. amin says:

    great stuff, but what is e3 (sell through rate) i dont get it.

  9. Custom_ice says:

    There appear to be a few errors in that formula, but it may just be my brain. There need to be more brackets isolating the previous tierd fee from being included in the next percentage multiplication, and there appears to be an IF staement missing from the end tier.

  10. swift says:

    Hi could you please create me a fomula for working out the below fees

    Final Sale Price

    Final Value Fee

    Item not sold

    No Fee

    £0.99 – £29.99

    5.25% of the final selling price, up to £29.99

    £30.00 – £99.99

    5.25% of the initial £29.99 (£1.57) plus 3.00% of the remaining balance of the final selling price

    £100.00 – £199.99

    5.25% of the initial £29.99 (£1.57) plus 3.00% of the initial £30.00 – £99.99 (£2.10) plus 2.50% of the remaining balance of the final selling price

    £200.00 – £299.99

    5.25% of the initial £29.99 (£1.57), plus 3.00% of the initial £30.00 – £99.99 (£2.10), plus 2.50% of the initial £100.00 – £199.99 (£2.50) plus 2.00% of the remaining balance of the final selling price

    £300.00 – £599.99

    5.25% of the initial £29.99 (£1.57), plus 3.00% of the initial £30.00 – £99.99 (£2.10), plus 2.50% of the initial £100.00 – £199.99 (£2.50) 2.00% of the initial £200.00 – £299.99 (£2.00) plus 1.50% of the remaining balance of the final selling price

    £600.00 or more

    5.25% of the initial £29.99 (£1.57), plus 3.00% of the initial £30.00 – £99.99 (£2.10), plus 2.50% of the initial £100.00 – £199.99 (£2.50) plus 2.00% of the initial £200.00 – £299.99 (£2.00) plus 1.50% of the initial £300.00 – £599.99 (£4.50) plus 1.00% of the remaining balance of the final selling price

  11. Martin Ladd says:

    Hi – the formula is very helpful thank you but do you one that uses the currect final fee percentage charges as at June 2011? Thank you, Martin

Leave a Reply