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
4
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
:confused: Are you sure?
For the sample data in post #6, doing a manual calculation check I get different results for rows 6 & 7
Yes - I just ran it again for $8bn and $10bn and my manual calcs are matching. $8bn listed below. Appreciate both of your attention to this, but unless there is something I am missing I think I am all set here!

1739478904522.png
 
Upvote 0

Forum statistics

Threads
1,226,527
Messages
6,191,571
Members
453,665
Latest member
WaterWorks

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