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]

Welcome to the MrExcel board!

Your table does not include a rate for 400M. Please clarify whether that is 40 or 30.

Could you gives us two actual examples, the results you calculate manually and explain the actual calculations?

BTW, the best way to give us sample data is with XL2BB
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[StephenCrump]

Yes, welcome @CJF22

One way you could do this:

	A	B
1	AUM ($M)	Fee (bps)
2	0	40
3	400	30
4	600	20
5
6	AUM ($)	500,000,000
7	Fee ($)	1,900,000
Sheet1

Cell Formulas
Range	Formula
B7	B7	=LET(AUM,A2:A4*1000000,SUM((B6>AUM)*(B6-AUM)*(B2:B4-N(+B1:B3)))/10000)

 

[Holger]

here is another option:

Multiple Fee Tiers.xlsx
	A	B	C	D	E	F	G	H
1	Low	High	Rate	Base Fee		AUM
2	0	400,000,000	0.40%			500,000,000
3	400,000,000	600,000,000	0.30%	1,600,000
4	600,000,000	10,000,000,000	0.20%	2,200,000		Result as table
5
6						AUM	Rate	Fee
7						400,000,000	0.40%	1,600,000
8						100,000,000	0.30%	300,000
9						0	0.20%	0
10						500,000,000		1,900,000.00
Sheet1

Cell Formulas
Range	Formula
D3:D4	D3	=SUMPRODUCT(B$2:B2-A$2:A2+1,C$2:C2)
F6:H10	F6	=LET( aum,F2, brackets,A2:C4, bracketStart,INDEX(brackets,0,1), bracketEnd,INDEX(brackets,0,2), feeRate,INDEX(brackets,0,3), aum_by_bracket,IF(aum<=bracketStart,0, IF(aum>bracketEnd,bracketEnd-bracketStart,aum-bracketStart)), fee_by_bracket,aum_by_bracket*feeRate, VSTACK( HSTACK("AUM","Rate","Fee"), HSTACK(aum_by_bracket,feeRate,fee_by_bracket), HSTACK(SUM(aum_by_bracket),"",SUM(fee_by_bracket) )))
Dynamic array formulas.

 

[Dave Patton]

I hesitate to post when there isn't a clear example complete with expected results.
If you require a tiered formula, consider the following.

T202502a.xlsm
	A	B
1	Amount
2	1,000,000,000	80
4a

Cell Formulas
Range	Formula
B2	B2	=AUM_Calc(A2)

Lambda Functions
Name	Formula
AUM_Calc	=LAMBDA(Amount,LET(i,Amount,b,{400000000;600000000;1000000000},r,{0.0000001;0.00000005;-0.0000001},SUM((i>b)*(i-b)*r)))

The same assumptions with brackets and rates is shown with the formulas that follow. The formulas are all the same these formulas show the rate table.

T202502a.xlsm
	A	B	C	D	E
1	Amount			Aum
2	1,000,000,000	80		400,000,000	0.000010%
3		80		600,000,000	0.000015%
4		80		1,000,000,000	0.000005%
5
4a

Cell Formulas
Range	Formula
B2	B2	=AUM_Calc(A2)
B3	B3	=SUM((A2>D2:D4)*(A2-D2:D4)*(E2:E4-E1:E3))
B4	B4	=SUM((A2>{400000000;600000000;1000000000})*(A2-{400000000;600000000;1000000000})*({0.0000001;0.00000005;-0.0000001}))
E2	E2	=40/D2
E3	E3	=30/(D3-D2)
E4	E4	=20/(D4-D3)

Lambda Functions
Name	Formula
AUM_Calc	=LAMBDA(Amount,LET(i,Amount,b,{400000000;600000000;1000000000},r,{0.0000001;0.00000005;-0.0000001},SUM((i>b)*(i-b)*r)))

If you work with a number of rate tables, the Lambda can be modified to pull the bracket information from your worksheet.

 

[kvsrinivasamurthy]

Example:
In B2 Copied Down.

=SUMPRODUCT(VALUE(TEXT(A2-$E$4:$E$8,"0;\0;0"))*($G$4:$G$8-SUMIF(OFFSET($G$3,ROW($G$4:$G$8)-ROW($G$4),0),"<>0")))

Tired calculation For different slabs % calculation.xlsx
	A	B	C	D	E	F	G	H
1	AUM	Amount
2	800	26000			Free Tires AUM
3	1000	30000			FROM	TO	Rate
4	1200	33000			0	399	40
5	5000	90000			400	599	30
6	8000	120005			600	999	20
7	10000	140005			1000	4999	15
8					5001	9.13517E+17	10
9
Sheet1 (2)

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

 

[CJF22]

Welcome to the MrExcel board!

Your table does not include a rate for 400M. Please clarify whether that is 40 or 30.

Could you gives us two actual examples, the results you calculate manually and explain the actual calculations?

BTW, the best way to give us sample data is with XL2BB
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Apologies, it should read <=400m. As such, 400m is charged at 40.

Example 1 - AUM 500M

400M*0.40% = $1,600,000
100M*0.30% = $300,000

Total Fee: $1,900,000

Example 2 - AUM 1bn

400M*0.40% = $1,600,000
200M*0.30% = $600,000
400M*0.20% = $800,000

Total Fee: $3,000,000

 

[CJF22]

Example:
In B2 Copied Down.

=SUMPRODUCT(VALUE(TEXT(A2-$E$4:$E$8,"0;\0;0"))*($G$4:$G$8-SUMIF(OFFSET($G$3,ROW($G$4:$G$8)-ROW($G$4),0),"<>0")))

Tired calculation For different slabs % calculation.xlsx
	A	B	C	D	E	F	G	H
1	AUM	Amount
2	800	26000			Free Tires AUM
3	1000	30000			FROM	TO	Rate
4	1200	33000			0	399	40
5	5000	90000			400	599	30
6	8000	120005			600	999	20
7	10000	140005			1000	4999	15
8					5001	9.13517E+17	10
9
Sheet1 (2)

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

Thank you for this! I can't say I follow exactly how it works but I have run a few scenarios and it works!

 

[Dave Patton]

T202502a.xlsm
	A	B	C	D	E
1	Amount
2	1,000,000,000	3,000,000		0	0.40%
3		3,000,000		400,000,000	0.30%
4		3,000,000		600,000,000	0.20%
5
6
7
8	200,000,000	800,000
9	500,000,000	1,900,000
10	2,000,000,000	5,000,000
4a

Cell Formulas
Range	Formula
A2	A2	=10^9
B2,B8:B10	B2	=AUM_Calc(A2)
B3	B3	=SUM((A2>D2:D4)*(A2-D2:D4)*(E2:E4-E1:E3))
B4	B4	=LET(i,A2,b,{0;400000000;600000000},r,{0.004;-0.001;-0.001},SUM((i>b)*(i-b)*r))

Lambda Functions
Name	Formula
AUM_Calc	=LAMBDA(Amount,LET(i,Amount,b,{0;400000000;600000000},r,{0.004;-0.001;-0.001},SUM((i>b)*(i-b)*r)))

 

[Dave Patton]

Post #6 includes new rates. Please provide a complete rate table.
If you do not want to use sum, use sumproduct.