Calculation question

[Schorn]

What would be the best way to calculate a total based using criteria like the
following:

I want to take varying totals of investments under management and calculate
costs related using .45% of first 100,000, .30% of next 400,000, .20% of next
500,000 and .10% of anything over 1,000,000.

 

[Stephane Quenson]

If A1 contains your total investment, you can use a formula like:
=MIN(MAX(0,A1),100000)*0.45% + MIN(MAX(0,A1-100000),400000)*0.3% +
MIN(MAX(0,A1-500000),500000)*0.2% + MAX(0,A1-1000000)*0.1%

Stephane.

 

[joeu2004]

What would be the best way to calculate a total based using criteria like the
following:
I want to take varying totals of investments under management and calculate
costs related using .45% of first 100,000, .30% of next 400,000, .20% of next
500,000 and .10% of anything over 1,000,000.

"Best" way? Well, that's debatable. There are many ways to
accomplish this.

Arguably, the simplest formula might be (if the investment total is in
A1):

=45%*min(A1,100000) + max(0,30%*min(A1-100000,400000)) +
max(0,20%*min(A1-500000,500000)) + max(0,10%*(A1-1000000))

Arguably, a formula that is easier to change as needed (including
adding additional breakpoints) would rely on a table. The formula
might be:

=(A1-vlookup(A1,table,1))*vlookup(A1,table,2) + vlookup(A1,table,3)

where "table" is the following in A2:C5 :

A2: 0 B2: 45% C2: 0
A3: 100000 B3: 30% C3: =C2 + B2*(A3-A2)
A4: =A3+400000 B4: 20% C4: =C3 + B3*(A4-A3)
A5: =A4+500000 B5: 10% C5: =C4 + B4*(A5-A4)

 

[Stephane Quenson]

Joeu2004,

Sorry to inform you that your formula returns a wrong result. Test it with
A1 = 1 for example, you get a negative amount (I corrected the first
percentage to 0.45%). It took me a while to write a proper formula, as you
have to combine MIN and MAX, look at my post above yours in this thread. Your
table approach is very good, again check your formulas in column C as they
return negative values for amounts lower than the threshold. Use MAX(0, your
formula) to solve it.

Stephane

 

[joeu2004]

Sorry to inform you that your formula returns a wrong result.
Test it with A1 = 1 for example, you get a negative amount

I get 0.45, as expected. The logic of the first term is: use the
smaller of A1 or 100000. Since 1 is smaller, min(...) should return
1, which is then multiplied by 45%. The other min(...) terms will
return negative numbers, but they are nullified because max(0,...)
will return 0 in those cases.

I do notice one potentially undesirable behavior. The formula works
fine if A1 is an explicit 0. But if A1 is blank, min(A1,100000)
returns 100000(!). This can be fixed by replacing A1 with n(A1) in
the first term. That is:

=45%*min(n(A1),100000) + ....

N() is not required in the other terms because Excel treats a blank
cell as zero in an arithmetic expression (e.g. A1-100000). On the
other hand, if the cell __appears__ blank, but it actually contains a
formula that returns the null string ("") sometimes, it might be
prudent to replace A1 with N(A1) in all instances. Isn't Excel
grand? (Rhetorical.)

PS: I did not bother to bullet-proof the formula for A1<0. KISS.
The OP says that A1 (in my example) represents the "total of
investments under management". Presumably, that is non-negative. But
if negative A1 is a possibility, then yes, replacing A1 in the first
term with max(0,A1) fixes both problems. That is:

=45%*min(max(0,A1),100000) + ....

Again, max(0,A1) is required only in the first term because the
max(0,min(...)) takes care of the problem in the other terms.

Your
table approach is very good, again check your formulas in column C as they
return negative values for amounts lower than the threshold. Use MAX(0, your
formula) to solve it.

Again, I did not feel the need to bullet-proof against having a
negative "total of investments under management". But if that is
desirable, I would simply do:

=if(A1<0, 0, (A1-vlookup(A1,table,1))*....)

 

[joeu2004]

Errata....

I want to take varying totals of investments under management and calculate
costs related using .45% of first 100,000, .30% of next 400,000, .20% of next
500,000 and .10% of anything over 1,000,000.

[....]
=45%*min(A1,100000) + max(0,30%*min(A1-100000,400000)) +
max(0,20%*min(A1-500000,500000)) + max(0,10%*(A1-1000000))
[....]
where "table" is the following in A2:C5 :
A2: 0 B2: 45% C2: 0
A3: 100000 B3: 30% C3: =C2 + B2*(A3-A2)
A4: =A3+400000 B4: 20% C4: =C3 + B3*(A4-A3)
A5: =A4+500000 B5: 10% C5: =C4 + B4*(A5-A4)

Oops: obviously, all percentages should be of the form 0.45% instead
of 45%. I didn't see the itsy-bitsy period before all the percentages
in the OP's posting.

Note to OP: That is why is always best to write 0.45 instead of .45
-- that is, with the "superfluous" leading zero.

 

[joeu2004]

Errata....

On Jul 21, 12:32 pm, Stephane Quenson
I corrected the first percentage to 0.45%

.... And all the other percentage accordingly. Good pick up! My bad!

I get 0.45, as expected.

Make that 0.0045. Whether or not the OP wants to round and how are
unspecified.

In any case, my "off by 0." error only changes the magnitude of the
result of my formulas, not their correct operation otherwise, I
believe.

 

[Stephane Quenson]

Sorry joeu2004, I was wrong. Reason is that I am French and we use the comma
as decimal separator and I thought that on the formula max(0,10%... you
actually meant 0.10% (what Schorn wanted), and therefore your max function
had only one argument for me, returning a negative value.

 

[Dana DeLouis]

Perhaps another option:

=MIN(0.1%*A1+1650,0.2%*A1+650,0.3%*A1+150,0.45%*A1)

 

[joeu2004]

Perhaps another option:
=MIN(0.1%*A1+1650, 0.2%*A1+650, 0.3%*A1+150, 0.45%*A1)

Excellent! I would only suggest an explanation of the "magic"
constants. I leave that to you to have the last word on the
subject ;-).

 

[Dana DeLouis]

Hi. I have a program that does this for me automatically.
If we were to plot these types of problems, what we would have are 4
straight line intervals. The problem is to calculate the equation of each
line (y = a*x+b).
We are given the slopes of each line (the 'a). We need to calculate the
y-intercepts 'b.

As we start from zero, the first one is easy. The intercept is zero.
=.0045+a1

For the second line, we need two points to calculate a straint line.
Therefore, let's pick two easy x-points. 100000, and 100001
1) (x,y) = (100000,100000*.0045) ->(100000,450)
2) (x,y) = (100001,450+.003)

Hence:

=INTERCEPT({450,450.003},{100000;100001})
returns:
150

Second equation is:
0.3%*A1+150

Keep going for each one.

Because the slopes are decreasing, we take the "Min."
Some problems, like commissions, are increasing, so we would want to take
the "Max" for those problems.
Hope this helps. :>)

=Min(0.0045*A1,150+0.003*A1,650+0.002*A1,1650+0.001*A1)

 

[joeu2004]

Hi. I have a program that does this for me automatically.
If we were to plot these types of problems, what we would have are 4
straight line intervals. The problem is to calculate the equation of each
line (y = a*x+b).

Interesting! But I think that is unnecessarily complex. The "magic"
numbers can be determined simply by reducing the following expression
of your formula:

=min(0.1%*(A1-1000000) + 0.2%*500000 + 0.3%*400000 + 0.45%*100000,
0.2%*(A1-500000) + 0.3%*400000 + 0.45%*100000,
0.3%*(A1-100000) + 0.45%*100000,
0.45%*A1)

Note that 400000 is 500000-100000; that is, the __size__ of the
bracket, not its limit. Expanding and rearranging terms, we get:

=min(0.1%*A1 - 1000 + 1000 + 1200 + 450,
0.2%*A1 - 1000 + 1200 + 450,
0.3%*A1 - 300 + 450,
0.45%*A1)

which is easily reduced to your concise expression.