Blended Fee Calculator

[pbolli1]

Hi Im trying to build a calculator that calculates a blended fee based on the constraints below. For example, if X client has $10 million to invest - his fee would be:?[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 324"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Investable Assets[/TD]
[TD] $ 10,000,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blended Fee[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]1.00%[/TD]
[/TR]
[TR]
[TD="align: right"]500001[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]0.90%[/TD]
[/TR]
[TR]
[TD="align: right"]1000001[/TD]
[TD="align: right"]3000000[/TD]
[TD="align: right"]0.75%[/TD]
[/TR]
[TR]
[TD="align: right"]3000001[/TD]
[TD="align: right"]5000000[/TD]
[TD="align: right"]0.65%[/TD]
[/TR]
[TR]
[TD="align: right"]5000001[/TD]
[TD="align: right"]10000000[/TD]
[TD="align: right"]0.50%[/TD]
[/TR]
[TR]
[TD="align: right"]10000001[/TD]
[TD="align: right"]20000000[/TD]
[TD="align: right"]0.40%[/TD]
[/TR]
[TR]
[TD="align: right"]20000001[/TD]
[TD="align: right"]30000000[/TD]
[TD="align: right"]0.30%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Looks like just a VLOOKUP:

Book1
	A	B	C	D	E	F
1	Amount to Invest	Rate		From	To	Rate
2	$ 3,456,789.00	0.65%		$ -	$ 500,000.00	1.00%
3				$ 500,001.00	$ 1,000,000.00	0.90%
4				$ 1,000,001.00	$ 3,000,000.00	0.75%
5				$ 3,000,001.00	$ 5,000,000.00	0.65%
6				$ 5,000,001.00	$ 10,000,000.00	0.50%
7				$ 10,000,001.00	$ 20,000,000.00	0.40%
8				$ 20,000,001.00	$ 30,000,000.00	0.30%
Sheet7

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,D2:F8,3)

 

[pbolli1]

Hi Eric, thanks for the reply! I forgot to mention that the fee should be a blended rate. For example: for a 10 million dollar investment, the first $500k would be charged 1% the second 500k would be charged .9% and so on. So the blended rate for $10 million should come out to about .59%. Any help would be greatly appreciated.

 

[Eric W]

OK, try this:

Book1
	A	B	C	D	E	F	G
1	Amount to Invest	Fee		From	To	Rate	Differential Rate
2	$ 10,000,000.00	$ 62,500.00		$ -	$ 500,000.00	1.00%	1.00%
3				$ 500,000.00	$ 1,000,000.00	0.90%	-0.10%
4		Blended Rate		$ 1,000,000.00	$ 3,000,000.00	0.75%	-0.15%
5		0.625%		$ 3,000,000.00	$ 5,000,000.00	0.65%	-0.10%
6				$ 5,000,000.00	$ 10,000,000.00	0.50%	-0.15%
7				$ 10,000,000.00	$ 20,000,000.00	0.40%	-0.10%
8				$ 20,000,000.00	$ 30,000,000.00	0.30%	-0.10%
Sheet7

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((A2-D2:D8),--(A2>D2:D8),G2:G8)
B5	=B2/A2
G2	=F2-N(F1)

You need to add the Differential Rate column to your table. Put the G2 formula in and drag down to G8. I also changed the D column to be the exact amount of the tier, not 1 dollar over, since the B2 formula expects it that way. Then the B2 formula figures out the fee, and the B5 formula gives the "blended" rate. Hope this helps!

 

[pbolli1]

Hi Eric,

I think were almost there! I am just getting an error on the B2 Formula =SUMPRODUCT((A2-D2:D8),--(A2>D2:D8),G2:G8)

This is exactly what I have input.

 

[Eric W]

What error are you getting? If that's the exact formula, then what do you have in the table? Are all the values numeric? Do the columns and ranges all match up to the sample sheet? Is A2 numeric? Are you dragging the formula down, if so you need to add the absolute reference indicators:

=SUMPRODUCT((A2-$D$2:$D$8),--(A2>$D$2:$D$8),$G$2:$G$8)

 

[Dave Patton]

try

Excel 2010
	A	B	C	D	E	F	G
1	Amount to Invest	Fee		Brackets		Rate	Differential Rate
2	$10,000,000.00	$62,500.00		0.00		1.00%	1.00%
3				500,000.00		0.90%	-0.10%
4		Blended Rate		1,000,000.00		0.75%	-0.15%
5		0.63%		3,000,000.00		0.65%	-0.10%
6				5,000,000.00		0.50%	-0.15%
7				10,000,000.00		0.40%	-0.10%
8				20,000,000.00		0.30%	-0.10%
3a

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(A2>$D$2:$D$8),A2-$D$2:$D$8,$G$2:$G$8)
B5	=B2/A2