Creating Airtable formulas to calculate auction house commission rates

Topic Labels: Formulas
773 0
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

I am looking for some help please to bring a formula from Excel into Airtable in order to calculate Auction fees and commission based on certain variables.

First, I will explain how this example auction works:

The Hammer Price is the final amount someone bids on an item. The auction adds a “Buyers Premium” based on the final amount to be paid by the buyer. Here is how it is calculated.

X Auction charges a Buyer’s Premium calculated on the Hammer Price as follows: a rate of twenty-five percent (25%) of the Hammer Price of the Lot up to and including $25,000; plus twenty percent (20%) on the part of the Hammer Price over $25,000 and up to and including $5,000,000; plus fifteen percent (15%) on the part of the Hammer Price over $5,000,000.

The eventual buyer pays the Hammer Price + the Buyer’s Premium.

In addition to the above charges, Auction X also charges the seller a fixed Selling Commission of 10% on the Hammer Price. This is amount is subtracted from the Hammer Price before the proceeds are remitted to the seller. Therefore, the total auction commission is the Selling Commission + Buyer’s Premium.

If I know the Hammer Price, I can easily calculate the Buyer’s Premium with the attached Array Formula in Excel. I am looking for some help please to bring this formula into Airtable.

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(S4>S7:S10,(IF(S4<S8:S11,S4,S8:S11)-S7:S10)*T8:T11,0))), 1, 1)
Screen Shot 2020-12-06 at 2.00.54 PM

If I don’t know the Hammer Price, and perhaps only what the buyer paid, I would like to be able to reverse calculate the Hammer Price and what the seller received using the same variables described above.

I would really appreciate some help producing the various formulas in Airtable.

Thank you!

0 Replies 0