Applying bonus payments but avoiding circular reference

Hi, all. Been a while since I've been here.

Anyway, I'm working on a debt optimizer spreadsheet. I have a version that takes debts and figures out the effective interest rate (taking things like tax considerations, annual fees, etc., into account) for all debts you put in. The idea is you put in a figure, such as $50, that you can put in above and beyond the minimum payments. The spreadsheet applies that payment automatically to the payment with the highest effective interest rate. When one debt is paid off, the minimum payment for that debt and the excess payment are added up and applied to the debt with the next highest effective interest rate, and so on. Thus, your payments from the first month until the last (or more likely, the one before the last) will be the same in total; it's a matter of allocating these appropriately.

I'm including an example of what I mean. C1 has the additional payment. Columns B, G, L, and Q have the interest rates and C, H, M, and R have the payments. Since the highest interest rate here is in the fourth table, the $50 additional payment would be applied to that debt first until it's done. In doing this, the debt goes away at Row 24 with a payment of $220.42. This means that there is an additional $29.58 to be applied to the next highest debt, the .007%. Then, the next month, the full $250 in additional money would be applied to that debt.

My problem is that I keep running into circular references when I try to do formulas. I also have 23 debts (4 mortgages, 4 student loans, and 15 other debts) that the sheet can handle, so the formula needs to be expandable to encompass all of these.

Any help is much appreciated. Ideally, I'd like to be able to enter the formula where the payments already are, but I'm open to additional columns if that's the only way.

As an additional difficulty, the effective interest rates may change over time. For example, a credit card may charge an annual fee. I include that in effective interest rate. If your balance is $10,000, a $50 annual charge is very little in terms of interest rate. If you get the balance down to $500, it becomes significant and may overtake another account. This means that simply asking the people to enter their debts in from highest interest to lowest will not work.