RANKCUMULATE Tranches with a "Not to Exceed" allocation

rhightower

Module contains Investment Tranches maintained as a list as well as multiple investments as a separate list (Whole Dollar). A fixed $ Credit is input in a separate module, but then should be allocated to the lowest tranches first - any excess credit is carried into the next tranche up and so on until the credit is exhausted.

 

Using the RANKCUMULATE and two "touch free" formulas, I've managed to perform the allocation of the lowest tranches which are completely exhausted, however, I'm struggling to limit the "cut-off" tranche, where the amount of that tranche exceeds the remaining credit to be allocated. 

 

I'm adding additional line items, however, my formulas are not "best practice" - in order to force the cutoff, the formulas are very specific on my current parameters. 

 

Looking for anyone with a smarter approach. Example in the image below- yellow is the intended output (with Tranche in a list and Investments are also in a list, which will have new investments added). 

 

Any insight is appreciated! 

 

 

anikdas

Hi @rhightower ,

Have solved a similar problem in past using the concept of full allocation and partial allocation:

1. In the first step, use the rankcumulate values to compare against the overall value and only allocate to those items where rankcumulate is less than the overall value - this is full allocation
2. In the next step, rank the items which did not get allocated
3. Calculate the remaining part to be allocated (Overall Value minus Full Allocation) - should be without the dimension of the items.
4. For the rank 1 item, assign the value from step 3 - this is partial allocation.
5. Final allocation should be sum of step 1 and step 4

Let me know how this works for you.