Banded Fee Structure Calculation

[David Evans]

Evening All ...

I've been playing with a project to calculate a "banded" fee structure for investment holdings. I currently use a UDF for each Type of holding, but I *think* there may be a better way of doing it .... although I can't quite put my finger on it ...

There's no consistent pattern to the number of "bands" - sometimes there are 5 of them, sometimes as little as 2. They are incre-mental, until the final band which essentially is anything over the sum of the prior bands ...

I'm wrestling with using some kind of array formula for the calculation - anyone got any bright ideas along these lines? As always, all thoughts positively received and appreciated.

Come back with any further questions, thoughts, avenues of investigation.

 

[NARAYANK991]

Hi ,

I think your structure is not clear. For type 1 , which of the following is it ?

1.

0 - 5000
5001 - 10000
10001 - 20000
20001 - 35000
35001 - 99999999

2.

0 - 4999
5000 - 9999
10000 - 19999
20000 - 34999
35000 - 99999999

3.

Any other ?

You have shown 4 bands , which I am not able to fit into either of 1 or 2 ; can you clarify ?

Narayan

 

[David Evans]

Hi ,

I think your structure is not clear. For type 1 , which of the following is it ?

1.

0 - 5000
5001 - 10000
10001 - 20000
20001 - 35000
35001 - 99999999

2.

0 - 4999
5000 - 9999
10000 - 19999
20000 - 34999
35000 - 99999999

3.

Any other ?

You have shown 4 bands , which I am not able to fit into either of 1 or 2 ; can you clarify ?

Narayan

Thanks for responding.
Insofar as your above question, let's go with 1. The principle is the same irrespective of the actual breakpoint. As i noted, some of the fee structures have 2 bands, some have 3, some 4 and there are even a few 5 bands!
I have been working on a sumproduct solution, "looking up" the bands from a table listing type and bands - I shall post it later, when I get a break in my day.
Again, thanks for your interest and thoughts.