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

CJF22

New Member
Joined
Feb 11, 2025
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!!

1739323013690.png
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.

 
Upvote 0

Forum statistics

Threads
1,226,515
Messages
6,191,495
Members
453,659
Latest member
thomji1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top