Calculating tiered percentages

I am hoping this may be something simple.

I want to create a calculator to calculate fee percentages based on settlement value.

The criteria is 20% of $0 to $5,000, 15% of the next $5,000 ($5,001 - $10,000), and 10% of anything above $10,000. I am looking for somebody to input the total settlement in the first box and then have formulas automatically calculate out the amounts.

The formulas I used were as follows with N/A signifying the settlement was not within that threshold:

=IF(C3<=5000,C3*0.2,5000*0.2)
=IF(C3>5000,((C3-5000)*0.15),"N/A")
=IF(C3>10000,(C3-10000)*0.1,"N/A")

I've attached the sample workbook as well.

Thank you!

First, your manual calculation is incorrect. For a total of 12500 in C3, the fee for the "second 5000" is 5000*15% = 750, not 1125. So the total fee is 2000, not 2375.

One way is to enter the following formula in C11:

=MIN(5000,C3)*20% + MAX(0,MIN(5000,C3-5000)*15%) + MAX(0,(C3-10000)*10%)

Another way is to create the following table in G3:I6:

	G	H	I
3	Over...	Fee	Diff
4	0	20%	=H4
5	5000	15%	=H5-H4
6	10000	10%	=H6-H5

Then enter the following formula into C11:

=SUMPRODUCT((C3>$G$4:$G$6)*(C3-$G$4:$G$6),$I$4:$I$6)

I agree with joeu2004, it should be 2000.

Try this formula

=SUMPRODUCT(--(C3>{10000;5000;0}),(C3-{10000;5000;0}),{-0.05;-0.05;0.2})

Thank you both! That was part of the problem I was running into, the "second $5,000" just kept calculating and wouldn't cap where I needed it to. @Joeu2004, that took care of it perfectly.