If statement next for tiered fee schedules

dsforbes

Board Regular
Joined
Mar 29, 2005
Messages
58
I have to calculate a fee based on the following

.006 on first 2MM
.005 on next 2MM
.004 on next 25MM
.003 on next 50MM
.002 on remaining assets

The market value will vary anywhere from 2MM to 100MM. I'd like the result in one cell.
How do I write an if statement that does this without having to write a formula for each fee break. thanks.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Here is an example:

I have an account that has a market value of 75,458,000
first 25,000,000 is billed at .0075 (187,500)
second 25,000,000 is billed at .006 (150,000)
third 50,000,000 is billed at .0055 (remaining 25,458,000 = 140,019)
for a total fee of 477,519

there could be up to six different tiers.

I can't figure out a nested or lookup without getting negative values in the last tiers...
 
Upvote 0
Believe me nobody will ever accuse me of being a mathmetician. Without seeing all of your data here is what I have come up with so far. I'm sure there are plenty of people on this board who can crack this in a second, but do you think this is something you can work with. Does the answer have to be in one cell because it seems it will take a lot of error checking.

Sheet7

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 71px"><COL style="WIDTH: 64px"><COL style="WIDTH: 71px"><COL style="WIDTH: 79px"><COL style="WIDTH: 71px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD>Bal</TD><TD>Rate</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: right">75,458,000</TD><TD style="TEXT-ALIGN: right">0.0075</TD><TD style="TEXT-ALIGN: right">25000000</TD><TD style="TEXT-ALIGN: right">187,500.00 </TD><TD style="TEXT-ALIGN: right">50,458,000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.006</TD><TD style="TEXT-ALIGN: right">25000000</TD><TD style="TEXT-ALIGN: right">150,000.00 </TD><TD style="TEXT-ALIGN: right">25,458,000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.0055</TD><TD> </TD><TD style="TEXT-ALIGN: right">140,019.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">477,519.00 </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H27</TD><TD>=IF(I27>G27,G27*F27)</TD></TR><TR><TD>I27</TD><TD>=E27-G27</TD></TR><TR><TD>H28</TD><TD>=IF(I27>G28,G28*F28)</TD></TR><TR><TD>I28</TD><TD>=I27-G28</TD></TR><TR><TD>H29</TD><TD>=IF(I28>0,I28*F29)</TD></TR><TR><TD>H30</TD><TD>=SUM(H27:H29)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
I can work with this. looks like I had my operator backwards (less than vs. greater than)
thanks.

I was hoping to do in a table where users would type the market value in and see the result, but I can hide the formulas and protect the sheet.

appreciate your help.
 
Upvote 0
Code:
75,458,000	 477,519	477,519	    477,519	477,519

xxxxxxxxxx	Range Name	xxxxxxxxxxx		
		
Brackets	 rB		$K$3:$K$8		
Rates1	 	rRates1		$L$3:$L$8		
Rates2	 	rRates2		$L$2:$L$7	

Your information is not complete or clear exactly what brackets and rates you want to use.

You could use Vlookup or numerous variations of Sumproduct.

One example with 75,458,000 in A1.

=SUMPRODUCT((A1 > rB)*(A1-rB)*(rRates1-rRates2))

N.B. Cell L2 is blank.
 
Upvote 0
I'm sort of getting it, hope you help me the rest of the way.

Let's me try and explain a simple example and then a more complex one.

I have a 10MM account and the fee schedule is
.0075 on the first 25MM - this account would have a fee of .0075*10MM = $75,000

I have a 75.5MM account and the fee schedule is
.0075 on the first 25MM = .0075*25
.0065 on the next 25MM = .0065*25
.0050 on the next 50MM = .0050*25.5
.0040 on next
.0030 on next
.0020 on next
I need to be able to go to 6 tiers
 
Upvote 0
Did you try the suggestion that I made?

How often do the brackets and rates change?
 
Upvote 0
I understand the sumproduct function (kind of), it multiples the values and then adds them together, I just don't know how to set up the underlying data to use your formula.

I can set up a spreadsheet with my data in it and send it, don't know how to post that onhere. Thanks for your hlep.
 
Upvote 0
I documented the range names above.

The rates and brackets you mentioned are not consistent or complete.
For the calculation that I completed and a guess of complete inputs see below:
Code:
0.000	0.75%
25,000,000	0.60%
50,000,000	0.55%
100,000,000	0.50%
150,000,000	0.45%
200,000,000,000	0.40%


It is probably easier to edit the brackets and rates in a table but if you do not want a table edit the following with the correct inputs.


=SUMPRODUCT((A1>{0;25000000;50000000;100000000;150000000;200000000000})*(A1-{0;25000000;50000000;100000000;150000000;200000000000})*({0.0075;0.006;0.0055;0.005;0.0045;0.004}-{0;0.0075;0.006;0.0055;0.005;0.0045}))

or can you complete the arithemetic on the rate part and the formula is like the following

=SUMPRODUCT((A1>{0;25000000;50000000;100000000;150000000;200000000000})*(A1-{0;25000000;50000000;100000000;150000000;200000000000})*({0.0075;-0.0015;-0.0005;-0.0005;-0.0005;-0.0005}))
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top