LinkBack URL
About LinkBacks
Good evening,
I was hoping someone could help me with a formula for the following Tiered pricing.
1st Million = 0.13%
1mm-5mm = 0.11%
5mm-10mm=0.09%
10mm+=6bps
Good evening,
I was hoping someone could help me with a formula for the following Tiered pricing.
1st Million = 0.13%
1mm-5mm = 0.11%
5mm-10mm=0.09%
10mm+=6bps
With your amount in A1
=MIN(A1, 1000000) * 0.13% + MIN(MAX(A1-1000000,0), 5000000)* 0.11% + MIN(MAX(A1-5000000), 10000000) * 0.09% + MAX(A1-10000000,0) * 0.06%
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thank you very much for replying!
I'm unfortunately getting a negative number. For instance, if my value is $548,935 i am getting ($3,292.34). Strangely, $12,322,000 gives me positive $14,783.81.
Can you give an example that you know to be correct?
BTW, what's a "bps?"
Entia non sunt multiplicanda sine necessitate
ah sorry... bps equals basis points. 50bps=0.5%. Sorry for the vernacular.
So:
$1.5mm account
>>>if an account is worth less than $1mm than they get charged 0.13%
>>>if an account is worth $1.5mm they are charged as follows:
the first $1 million is charged 0.13% and the next $500,000 is charged 0.11%. The total cost on this account would be
$1,000,000*0.13%=$1,300
$500,000*0.11%=$550
Total blended fee would be ($1,300+$550)/$1,500,000=0.123%
Sorry to be so complicated...
C2: =B2-N(B1) B8: = SUMPRODUCT((A8 > $A$2:$A$5) * (A8 - $A$2:$A$5) * $C$2:$C$5) C8: =B8/A8
this rocked it - thanks!
You're welcome.
Sorry, typo in my original post
=MIN(IF(ISNUMBER(A1),A1,0), 1000000) * 0.13% + MIN(MAX(A1-1000000,0), 5000000)* 0.11% + MIN(MAX(A1-5000000,0), 10000000) * 0.09% + MAX(A1-10000000,0) * 0.06%
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks