If statement next for tiered fee schedules

dsforbes

Board Regular
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

dsforbes

Board Regular
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...

jeffreybrown

Well-known Member
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

dsforbes

Board Regular
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.

Dave Patton

Well-known Member
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.``````

dsforbes

Board Regular
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

Dave Patton

Well-known Member
Did you try the suggestion that I made?

How often do the brackets and rates change?

dsforbes

Board Regular
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.

Dave Patton

Well-known Member
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}))

Replies
5
Views
713
Replies
2
Views
598

1,195,623
Messages
6,010,749
Members
441,567
Latest member
Flitbee

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.

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

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