fee schedule - multiply % based on investment

[Mr Philip]

Hello Excel Gurus,

I'm trying to create a spreadsheet that will calculate fees based on the total

The schedule is:

[TABLE="width: 223"]
<TBODY>[TR]
[TD]First $1 million </SPAN>[/TD]
[TD="align: right"]1.00%</SPAN>[/TD]
[/TR]
[TR]
[TD]Next $4 million</SPAN>[/TD]
[TD="align: right"]0.60%</SPAN>[/TD]
[/TR]
[TR]
[TD]Next $5 million </SPAN>[/TD]
[TD="align: right"]0.50%</SPAN>[/TD]
[/TR]
[TR]
[TD]Next $10 million</SPAN>[/TD]
[TD="align: right"]0.40%</SPAN>[/TD]
[/TR]
[TR]
[TD]Next $30 million</SPAN>[/TD]
[TD="align: right"]0.30%</SPAN>[/TD]
[/TR]
[TR]
[TD]Balance over $50 million</SPAN>[/TD]
[TD="align: right"]0.25%</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]


So if someone invests $1 million, they will be charged the 1% only. If they invest 7 million, they would be charged 1% for the 1st million, .6% for everything above $1 million up to $5 million, and end charged .5% based on the remaining 2 million.

The starting number will always be at $1 million, so I got the first one down. It will always be 10,000. After that, I'm stumped on how to make only the specific range of what's left over be multipled by the corresponding % and so on.

Ideally, I would have a column for each range.

so if cell A1 had the investment,
cell B1 would have the fee for any number $1,000,001 - $5,000,000
cell C1 would have the fee for any number $5,000,000 - $10,000,000

And so on.

This forum has been a reference for me on so many other Excel questions and am very grateful you guys volunteer your advice. Any guidance or help would be tremendously appreciated!!!!!!!!

- Novice with a lot to learn

 

[Mr Philip]

This is what I have written out:

=IF(A2>0,(A2<=4000000)*0.006,"")

Where A2 is the number, minus 1 million, and am trying to multiply the portion of the number up to 4 million by .6

 

[Mr Philip]

How do I add the spreadsheet? Not sure if I worded the above correctly. Didn't see an attachment button on the advanced reply