Excel Formula for Calculating eBay Listing and Final Value Fees
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
If you found this post useful, why not buy me a coffee!
June 12th, 2008 at 11:56 pm
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
June 14th, 2008 at 10:07 pm
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))
June 14th, 2008 at 10:55 pm
=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.
June 14th, 2008 at 10:57 pm
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+…..
June 19th, 2008 at 10:49 am
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/