Calculate Multiple Fee Tiers in One Formula, while capturing dollars in the prior tier(s) at their fee rates

[CJF22]

Hi - I am having coming up with a formula that will calculate multiple fee tiers as AUM scales. Essentially at a certain level, the fee scales down, but the assets from the previous fee tier are charged on the prior level. The Tiers are posted below. In this hypothetical, I need to be able to model out all ranges of AUM. So if my AUM figure is $1bn, I need to calculate the 40 bps on the first $400m, 30 bps on the next $200m and 20 bps thereafter. Additionally, if it is $500m, I need to capture only that last $100m receiving the 30 bps rate, etc. The closest I've come is a long IFS statement, but that gets messy and has not been successful yet. Any help appreciated!!

 

[Peter_SSs]

but I have run a few scenarios and it works!

Are you sure?
For the sample data in post #6, doing a manual calculation check I get different results for rows 6 & 7

 

[Dave Patton]

Peter_SSs stated " I get different results for rows 6 & 7"; I agree.

I requested a complete and clear rate table but my post and request was ignored.

​