RANKCUMULATE Tranches with a "Not to Exceed" allocation

Options
rhightower
rhightower
in Modeling

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! 

 

rhightower_0-1655772894133.png

 

Tagged:

Best Answers

  • anikdas
    anikdas
    Answer ✓
    Options

    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.

  • rhightower
    rhightower
    Answer ✓
    Options

    I ran into some issues and prompted an initial response with more questions, but I think the issue is tied up in the Ranking of non-allocated amounts in Step 2. I need to figure out how to apply my Ranking by Tranche (A,M,B,[...]) for each individual deal. It keeps trying to rank each number across three lists and I suspect time as well.

     

Answers

  • rhightower
    rhightower
    Options
    Bingo - just had to build out a separate system module with limited dimension. Appreciate the help.

Categories