Fee formula for private sellers items over £300

[jimthing]

Can anyone do a spreadsheet formula to get the pre-fee price, which includes both the up to £300 at 4% tier, but also the £300 to £4000 at 2% tier? 

Note the formula should include the top £4000 amount too, so would be something that incorporates 300 to 4000 at 2%, along with no fee at all above 4000 tier, per the guidelines here: 

The Buyer Protection fee is calculated as:

• A flat fee of up to £0.75 per item, and
• 4% of the item price up to £300, and
• 2% of any portion of the item price from £300 to £4,000
• Any portion of the item price over £4,000 won't incur any additional fee.

I've worked out a formula for items up to 300, but not one for those above that. <300 formula would be: 

({cell with final price}-0.75)÷1.04

(NB. The 0.75 figure in the above formula can be anywhere from 0.70 to 0.75, depending on the final figure. So you have to manually adjust that per item.) 

I tried asking a couple of AI's and even they got it wrong... help! 

[snappyfish]

Try this 

https://ebaybpfcalculator.netlify.app/

[jimthing]

That's not a formula. 

[snappyfish]

ok , thought it might help 😁

[jimthing]

---

@snappyfish wrote:

ok , thought it might help 😁

---

Thanks, but while it gets you a figure, really need something for Numbers spreadsheet use. 

EDIT: I notice this is  your own tool, nice one. Any chance you could share the formula you used or something? Thanks in advance. 

[snappyfish]

Hi Sorry, not something I can share right now. 

[honeymonster007]

Looks like a useful tool 👍

[jimthing · Accepted solution]

Looks like we have an answer: 

=IF(A1<=300,ROUND((A1-0.72)/1.04,2),ROUND((((A1-312)-0.72)/1.02)+300,2))

NOTE: When using this formula, you may have to change the figure in red above to be anywhere from 0.70 to 0.75 to be accurate with what Ebay shows.