0

So stock exchanges like super trade or options express have thresholds for the way they price trades on their platform. So for instance, say for less than 100 trades it costs $17 per trade, and then after 100 trades it costs $14. When I think about modeling this for the number of trades greater than 100 in excel, I would initially think the broker fees being in the form of $ 17*99+(#-99)*14 $ where # is obviously the number of trades, but when I did research I found it was $ 99-# $ instead, which doesn't make sense to me because that suggests somehow the broker fees decrease and eventually go negative after a certain point. Neither of these models actually seems right, so how does one explain this?

Also, why aren't the latex tags working here?

Improve this question
4
  • 2
    " why aren't the latex tags working here? " - money.meta.stackexchange.com/questions/426/…
    – D Stanley
    Oct 25, 2017 at 19:46
  • Well that doesn't make much sense why only some sites would report it, they're all the same format. But what about the topic at hand that actually matters?
    – james
    Oct 25, 2017 at 19:49
  • $ 17*99+(#-99)*14 seems perfectly correct to me - what problem were you having with that formula?
    – D Stanley
    Oct 25, 2017 at 19:49
  • At the first trade, it will cost $17, but the model says it's $328. Maybe that's just because it works exclusively for trades greater than 99, but it seems odd to me that there couldn't be a single equation to account for both circumstances.
    – james
    Oct 25, 2017 at 19:57

2 Answers 2

Reset to default
2 
=IF(A1<100,17*A1,1700+(A1-100)*14)

where A1 is the number of trades.

you may have to change the number 100 to 99 depending on how the 100th trade is charged.

The idea is to use the if statement to determine the price of the trades. Once you are over the threshold the price is 14*number over threshold.

Improve this answer
3

Assuming cell A1 contains the number of trades:

=MIN(a1,100)*17+MAX(0,a1-100)*14

will price up to A1=100 at 17 each, and the rest at 14 each.

The key is the MAX and MIN. They keep an item from being counted twice. If X would end up negative, MAX(0,x) clamps it to 0. By extension, if X-100 would be negative, MAX(0, X-100) would be 0 -- ie: that number doesn't increase til X>100.

When A1=99, MIN(a1,100) == 99, and MAX(0,a1-100) == 0.
When A1=100, MIN(a1,100) == 100, and MAX(0,a1-100) == 0.
When A1=101, MIN(a1,100) == 100, and MAX(0,a1-100) == 1.

Of course, if the 100th item should be $14, then change the 100s to 99s.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.