RANKCUMULATE Tranches with a "Not to Exceed" allocation
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!