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

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

Excel File.xlsx

Hi Matt and welcome to the forum,

To solve it reasonably easy with formulas, you need to sort your debts in order of expected date of repaiment (shortest to longest).
The easy way to check it is using ratio
=E2/H2
so as you write it in for instance I2 and copy down, you can sort B2:I6 on I ascending.

now headings in 2 next tables:
B13 and B2:
=B2
and copy down

Now for the quickest to pay one (L13) formula would be quite obvious:

Formula:

Please Login or Register  to view this content.

so the smaller of two numbers: calculated payment and whole remaining part of loan.
(orange cell and copied right)

The other will be calculated as the smaller of remaining payment, and ratio of "% of FCF for debt used for paying off that specific loan" to sum of "% of FCF for debt used for paying off that specific loan and all loans below" multiplied by "remaining money" = difference of money available and used already for paying loans above current one.
Putting it into formula for L14 (green cell and copy down and right) we have:

Formula:

Please Login or Register  to view this content.

In real life, one would probably need some rounding and/or displaying more decimal places.
especially if rounding is used, I'd change formula for last (lowest in sheet) payment to calculate the difference of available funds and used above for all other loans).