Tiered If statement

[CROWAN]

Having trouble calculating fees based on these tiers:

First $2 bil in net assets - 1 bps
Next $8 bil in net assets - .75 bps
In excess of $10 bil in net assets - .5 bps

Any ideas? Thank you

 

[Ron Coderre]

I think this does what you want...
With
A1: a number

This regular formula calculates the number of bps (whatever that is) based on your rules
B1: =SUM((A2/10^9>{0,2,10})*(A2/10^9-{0,2,10})*({1,-0.25,-0.5}))

These are some sample values and results

NetAssets      BPS
 1,000,000,000 1.00
 2,000,000,000 2.00
 3,000,000,000 2.75
 4,000,000,000 3.50
 5,000,000,000 4.25
 6,000,000,000 5.00
 7,000,000,000 5.75
 8,000,000,000 6.50
 9,000,000,000 7.25
10,000,000,000 8.00
11,000,000,000 8.25

Is that something you can work with?

 

[shg]

Mornin', Ron,

I think ...*({1,-0.25,-0.25}))

 

[CROWAN]

BPS are basis points. In this example, 1 bps = .0001; .75 bps = .000075 and .5 bps = .00005. I have the net assets and the basis points. I need to calculate the fees. The calculation should be (assets*bps/calendar days in a year(366 for 2012)*calendar days in a month(31 for July). So if the client had 15 billion in avg assets as of 7/31 (I wish), the calc should be (2 bill*.0001/366*31)+(8 bil*.000075/366*31)+(5 bil*.00005/366*31) = $86,065.57. I'd like to use one IF Formula if possible. If I have to use 3 I can. I'm just having trouble with the middle tier of > 2bil but < 10 bil.

 

[Ron Coderre]

Mornin', Ron,

I think ...*({1,-0.25,-0.25}))

I believe you're right...the deductions are cumulative. thanks for catching that.

 

[CROWAN]

I think this does what you want...
With
A1: a number

This regular formula calculates the number of bps (whatever that is) based on your rules
B1: =SUM((A2/10^9>{0,2,10})*(A2/10^9-{0,2,10})*({1,-0.25,-0.5}))

These are some sample values and results

NetAssets      BPS
 1,000,000,000 1.00
 2,000,000,000 2.00
 3,000,000,000 2.75
 4,000,000,000 3.50
 5,000,000,000 4.25
 6,000,000,000 5.00
 7,000,000,000 5.75
 8,000,000,000 6.50
 9,000,000,000 7.25
10,000,000,000 8.00
11,000,000,000 8.25

Is that something you can work with?

That actually wouldn't work. the simple math breakdown is:

If I had exactly 2 bil in assets -

=2000000000*0.0001/366*31 = 16,939.89 <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col width="589"></colgroup><tbody>
</tbody>

If I had exactly 10 bil in assets-

=(2000000000*0.0001/366*31)+(8000000000*0.000075/366*31) = 67,759.56 <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col width="589"></colgroup><tbody>
</tbody>

If I had exactly 15 bil in assets -

=(2000000000*0.0001/366*31)+(8000000000*0.000075/366*31)+(5000000000*0.00005/366*31) = 88,934.43 <colgroup><col></colgroup><tbody> </tbody> Does that help explain my end goal?

<colgroup><col width="589"></colgroup><tbody>
</tbody>

 

[CROWAN]

I just figured it out. Here is the formula.

=IF(B4>10000000000,((B4-10000000000)*0.00005/366*C4)+(8000000000*0.000075/366*C4)+(2000000000*0.0001/366*C4),IF(B4>2000000000,((B4-2000000000)*0.000075/366*C4)+(2000000000*0.0001/366*C4),B4*0.0001/366*C4)) It's not very efficient, but it works. Thanks guys

<colgroup><col width="98"></colgroup><tbody>
</tbody>

 

[Ron Coderre]

In that case...try this regular formula
=SUM((B4/10^9>{0,2,10})*(B4/10^9-{0,2,10})*({1,-0.25,-0.25}))/366*C4*100000

Does that help?