Tiered Fee

[Graemea]

Hi,

i would like like to calculate a composite fee rate based on the following sample tiers:

1st $225m charged at 0.50%
Up to $450m charged at 0.45%
Above $$450m charged at 0.40%

So the fee on $1bn would be 0.50% on the first $225m, 0.45% on the next $225m and 0.40% on the remaining $550m.

I need to calculate the effective fee on the $1bn. I would expect it to be around 0.44%.

Can someone please suggest a formula to do this. It’s possible with IF statements but this becomes unwieldy when you introduce more tiers to the fee structure.

thanks!

 

[tyija1995]

Hello, I have done it with an IF statement (as you say it becomes unwieldy) but for now it is OK I suppose? I am sure someone will come up with something more elaborate

IF(A1<=225000000,0.005*A1,IF(A1<=450000000,(0.005*225000000)+0.0045*(A1-225000000),(0.005*225000000)+(0.0045*225000000)+0.004*(A1-450000000)))/A1

Where A1 has the $ amount

If A1 is $1bn, then this formula returns 0.43375% for example

 

[Graemea]

Thanks!

I’ve seen a similar problem solved with SUMPRODUCT.

Does anyone know how to adapt this to my question?

thank you!

 

[Graemea]

Tiered Fee with SUMPRODUCT?

Hi,

Sorry for bumping this up but is anyone please able to suggest a solution that doesn't use lots of nested IF functions as I'd like to extend the number of tiers and the formula becomes very unwieldy?

thanks!

 

[Dave Patton]

Re: Tiered Fee with SUMPRODUCT?

Excel 2010
	A	B	C	D	E	F
1	Amount	1,000,000,000		Brackets	Rate	Rate_Diff
2	Fee	4,337,500.00		0	0.50%	0.50%
3		0.0043375		225,000,000	0.45%	-0.05%
4				450,000,000	0.40%	-0.05%
1d

Cell Formulas
Range	Formula
F2	=E2-N(E1)
B2	=SUMPRODUCT(--(B1>D2:D4),B1-D2:D4,F2:F4)
B3	=B2/B1

 

[Graemea]

Thanks very much, Dave!!!