Formula to return a fee

[jcdarling]

I have a tiered fee schedule and I want to create a formula to return the correct fee - why is my formula not working?

Book2
	A	B	C	D	E	F
1	Start	End	%
2	$ -	$ 500,000.00	1.00%
3	$ 500,000.00	$ 1,000,000.00	0.75%
4	$ 1,000,000.00	$ 2,500,000.00	0.50%
5	$ 2,500,000.00	$ -	0.25%
6
7
8	Balance	Fee should be		Formula
9	$ 104,916.96	$ 1,049.16		($5,200.83)		=SUMPRODUCT(($A9<=$B$2:$B$5)*($A9>$A$2:$A$5)*($A9-$A$2:$A$5)*$C$2:$C$5)+SUMPRODUCT((($A9>$B$2:$B$5)*($B$2:$B$5-$A$2:$A$5))*$C$2:$C$5)
10	$ 2,780,964.40	$ 16,952.40		$10,000.00
Sheet1

Cell Formulas
Range	Formula
D9:D10	D9	=SUMPRODUCT(($A9<=$B$2:$B$5)*($A9>$A$2:$A$5)*($A9-$A$2:$A$5)*$C$2:$C$5)+SUMPRODUCT((($A9>$B$2:$B$5)*($B$2:$B$5-$A$2:$A$5))*$C$2:$C$5)

 

[johnny51981]

Would this work in place of your SUMPRODUCT?

=A9*VLOOKUP(A9,A2:C5,3,TRUE)

 

[jcdarling]

Would this work in place of your SUMPRODUCT?

=A9*VLOOKUP(A9,A2:C5,3,TRUE)

it doesn't factor in for anything over $500K

 

[johnny51981]

That is due to your Finish values matching your following Start values. If you add a penny to the following Start value, it should do just fine.

 

[jcdarling]

That is due to your Finish values matching your following Start values. If you add a penny to the following Start value, it should do just fine.
View attachment 98343

I guess I didn't explain correctly - the first $500K is charged at the 1% rate; the next $500K is charged at .75% and so forth

 

[Tetra201]

@ jcdarling: Just enter, say, $100,000,000.00 in cell B5 instead of a zero, and your formula will work.

 

[johnny51981]

Ohhhhhhh. Like U.S. Tax brackets. Got it. Sorry I misunderstood.

 

[Phuoc]

Try this

Book1
	A	B	C
1	Start	End	%
2	0	500000	1.00%
3	500000	1000000	0.75%
4	1000000	2500000	0.50%
5	2500000	0	0.25%
6
7
8	Balance	Fee should be
9	104916.96	1049.16	1049.170
10	2780964.4	16952.4	16952.411
Sheet1

Cell Formulas
Range	Formula
C9:C10	C9	=SUMPRODUCT((A9>$A$2:$A$5)*(A9-$A$2:$A$5)*($C$2:$C$5-N(+$C$1:$C$4)))

 

[jcdarling]

Try this

Book1
	A	B	C
1	Start	End	%
2	0	500000	1.00%
3	500000	1000000	0.75%
4	1000000	2500000	0.50%
5	2500000	0	0.25%
6
7
8	Balance	Fee should be
9	104916.96	1049.16	1049.170
10	2780964.4	16952.4	16952.411
Sheet1

Cell Formulas
Range	Formula
C9:C10	C9	=SUMPRODUCT((A9>$A$2:$A$5)*(A9-$A$2:$A$5)*($C$2:$C$5-N(+$C$1:$C$4)))

Thanks - works!