Tiered Fee Formula

[Niny]

Hello,

I'm in a bit of trouble as I am excel challenged!

I am having trouble coming up with a formula to effectively produce a fee on an investment amount. I would love assistance with creating a formula:

Tier structure is as follows:

$0 - $90587 = $750 (minimum fee) otherwise it's 0.90% of the first $90587 if the investment amount is higher ....
$90587 - $299,999 = 0.90%
$300,000 - $599,999 = 0.65%
$600,000 - $999,999 = 0.55%
$1,000,000 - $1,999,999 = 0.55% on the first $1,000,000 and 0.10% thereafter
$2,000,000 +++ = 0.325% on the first $2,000,000 and 0% thereafter (i.e. there is a cap at $6500).

So for example, if the investment amount was $800,000 it would be 0.90% of the first $299,999 ($2,700) + 0.65% of the next $299,999 being the amount between $300,000 and $599,000 ($1,950) + 0.55% on the next $200,001 ($1,105). The total fee payable on $800,000 would be $2,700 + $1,950 + $1,105 = $5,755.

If the investment amount is over 2,000,000, then the fee is capped at $6,500.

I would really appreciate any help. I'm very stuck!!!

Thanks in advance.

 

[PaddyD]

Hi. welcome to the board.

Here's the 'teach 'em to fish' version of an answer:

http://www.mcgimpsey.com/excel/variablerate.html

...post back if you can't get it working.

 

[Niny]

Hi PaddyD,
I can't seem to get it to work.
I think I must be doing things in the wrong order or not using brackets and commas properly.
Any help would be so appreciated.