Looking for a way to distribute left over cash flows over other loans

[MattRoux]

Hi,

I'm looking for a way to distribute left over cash flows to other loan repayments.
At the moment I have a percentage input of the free cash flows to be distributed over 5 different loans.

Those free cash flows are annual and the percentages are fixed.
At some point in time, 1 of those loans is repaid. From this point onwards, I want to distribute the percentage of the free cash flows over the repayment of the other loans in the weights used to distribute the free cash flows. I'm trying to get this to happen too in the case 2,3 and 4 loans are paid off.

Next to that I'm trying to find a way to distribute a portion of the reserved free cash flow in case the available portion is bigger than the left over debt for that loan. (E.g. I have 1.2 available for a loan, the debt outstanding for that loan is only 0.7, in that case I would like to distribute the left over 0.5 over the remaining loans in a similar fashing as mentioned before.)

In the attachments you can find the file I'm working in. The highlighted blue cells are the cells I think the function(s) need to be inserted.

I would really appreciate it if someone can help me out with this!


Kind regards,

Matt

<strike></strike>
​