Excel Formula for Calculating eBay Listing and Final Value Fees
May-2008
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
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
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))
=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.
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+…..
There is a tool called 3-Clicks that adds a lot of functions to Excel which can then talk directly to eBay:
https://www.prime2s.com/3-Clicks/
https://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:
https://www.auctionfeecalculator.com/
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))))
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)))
great stuff, but what is e3 (sell through rate) i dont get it.
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.
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
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