MrExcel Publishing
Your One Stop for Excel Tips & Solutions

close to commissions problem


Posted by Barry on January 11, 2002 8:30 AM

I am trying to write a formula for a fee which I do by hand, example:

portfolio value $877,392.00 (known)

first $100,000 at a rate of 1.2% annually =
Next $150,000 at a rate of .6% annually =
Next $250,000 at a rate of .48% annually =
Next $500,000 at a rate of .36% annually =

total fee =
I have been using IF statement but get a negative number that is not what I want.

Barry


Posted by Mark W. on January 11, 2002 9:09 AM

Try this...

=IF(A1<100000,A1*0.012,VLOOKUP(A1,{100000,1200;250000,2100;500000,3300;1000000,5100},2)+MAX(0,({100000,250000,500000,1000000}-A1)*{0.012,0.006,0.48,0.0036}))

Posted by Mark W. on January 11, 2002 9:16 AM

Ahh! This won't work! Back to the drawing board (nt)

Posted by Aladin Akyurek on January 11, 2002 10:34 AM

Barry --

What should be the (total) fee for, say $20,000.00?

And, What is the total, hand-calculated, fee for the portfolio value $877,392.00 that you gave as an example?

Finally, What is the total fee for the value of $1,000,000.00?

Aladin

=========