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.

​

 

[CJF22]

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!

 

[Holger]

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!

View attachment 122352

Hi,

not sure how you calculated this but my view would be:

* 1st 400m attracts 0.04% fees, which is 160m
* next 200m (diff 600-400) attracts 0.03% fees, which is 60m
* out of 8b 600m already attracted fees, which leaves 8b-600m = 7.4b @ 0.02% fees = 1.48b

all of the above should result in 1.48b+160m+60m = 1.7b in fees?

Cheers

 

[Dave Patton]

I will repeat my suggestions one last time. [I do not know why.]
You can try the alternative that you prefer.
If you try the Lamba, you can name the function to a name that you prefer and you can name the input parameter/prompt to something that is appropriate.
The second Lambda lets one just reference an appropriately structured rate table.

T202502a.xlsm
	A	B	C	D	E	F	G
1	Amount
2	8,000,000,000	1,600,000		0	0.04%
3		1,700,000		400,000,000	0.03%
4		1,700,000		600,000,000	0.02%
5
6
7
8	200,000,000	40,000
9	500,000,000	100,000
10	2,000,000,000	400,000
11	8,000,000,000	1,600,000		Bracket	Rate
12
13				0	0.04%
14				400	0.03%
15				600	0.02%
16
17	Million=	1,000,000	named "m"
18	Million
19	200	80,000	80,000
20	500	190,000	190,000
21	1,000	300,000	300,000
22	2,000	500,000	500,000
23	8,000	1,700,000	1,700,000		Tiers
24					From	To	Rate
25					0	400	0.04%
26					400	600	0.03%
27					600	1E+308	0.02%
28
29
30	Million
31	200	80,000
32	500	190,000
33	1,000	300,000
34	2,000	500,000
35	8,000	1,700,000
4aaa

Cell Formulas
Range	Formula
B2,B8:B11	B2	=CALC_X(A2)
B3	B3	=SUM((A2>$D$2:$D$4)*(A2-$D$2:$D$4)*($E$2:$E$4-$E$1:$E$3))
B4	B4	=LET(i,A2,b,{0;400000000;600000000},r,{0.0004;-0.0001;-0.0001},SUM((i>b)*(i-b)*r))
D14:D15	D14	=D3/m
B19:B23	B19	=SUM((A19>{0;400;600})*(A19-{0;400;600})*({0.0004;-0.0001;-0.0001}))*m
C19:C23	C19	=CALC_X(A19)*m
F27	F27	=BigNum
B31:B35	B31	=CALC_L($E$25:$G$27,A31)*m

Named Ranges
Name	Refers To	Cells
m	='4aaa'!$B$17	D14:D15, B19:C23, B31:B35

Lambda Functions
Name	Formula
CALC_L	=LAMBDA(rngRateData,AUS_AMT,LET(rng,rngRateData,i,AUS_AMT,b,INDEX(rng,,1),r,INDEX(rng,,3),ro,VSTACK(0,DROP(r,-1)),SUM((i>b)*(i-b)*(r-ro))))
CALC_X	=LAMBDA(Amount,LET(i,Amount,b,{0;400;600},r,{0.0004;-0.0001;-0.0001},SUM((i>b)*(i-b)*r)))

 

[Peter_SSs]

I just ran it again for $8bn ... and my manual calcs are matching. $8bn listed below.

Then you must be using a different formula to the one listed in post #6. Here is that formula used with your table and it does not match the manual calculation that you have shown in post #13.

CJF22.xlsm
	A	B	C	D	E	F	G
1	AUM	Amount
2	8,000,000,000.00	1,700,000.00			Free Tires AUM
3					FROM	TO	Rate
4						400,000,000.00	0.04%
5					400,000,001.00	600,000,000.00	0.03%
6					600,000,001.00	8,000,000,000.00	0.02%
Sheet2

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(VALUE(TEXT(A2-$E$4:$E$6,"0;\0;0"))*($G$4:$G$6-SUMIF(OFFSET($G$3,ROW($G$4:$G$6)-ROW($G$4),0),"<>0")))

Can you explain just how you manually calculated 1,645,474.89 in the third row of your manual check in post #13?