Looking for formula/VBA to determine where to best put extra funds

[trishcollins]

I have a budget workbook for income and expenses, with each worksheet being a fiscal year, divided out by months. If there is a balance left for any month after deducting expenses from income, I want to apply the additional funds to my debt based on the set priority and/or if a mortgage is maturing that month. For each loan, I have the summarized the information in my budget workbook in a table called "Loan_Info".
That information is sourced from another workbook for loan amortizations, with the exception of the TFSA, which will always be the lowest priority regardless of how many other mortgages and lines of credit I have. Note as the loans become due and are either paid in full or the balance owing is transferred to another mortgage, the priorities automatically readjust.

The "Extra" (funds) can be found in row 112 in table "Income_Expenses_2025" below. The "Difference" in row 113 is what is left after I apply extra payments, and that amount is the opening balance for the next month.

Here is the "Extra_Payments_2025" table I need to populate with formulas for the best use of any extra funds. Note that I have shown the "Maturity Date" and "Priority" using xlookup on the "Loan_Info" table. That is just for display purposes. The formulas should always reference the "Income_Expenses_2025" table above for the extra funds table and the "Loan_Info" table for all the loan information, to keep it simple.

Things to keep in mind when creating the formulas, include the "Max" -- maximum annual extra amount that can put on a mortgage per year, and maximum annual TFSA contributions (amount includes previous years where no contribution has been made). LoC maximum are the actual credit limit. Also, when considering applying the extra payments, I need to deduct the standard monthly payments from the balance, to ensure I don't overpay. The standard monthly payments would be in the "Expenses_2025" table below.

Here is my pseudo code for the formula:
If extra funds > 0 then
If maturity date of the any loan type mortgage is due this month, then
use any extra funds to put towards the balance of the maturing mortgage (any additional balance owing will be transferred to Mortgage 4, which will be a new mortgage)
if the extra funds EXCEED the mortgage balance owing at the date of maturity, use the extra funds towards the other debt then
goto debtpaydown
Endif
Endif
Else
goto debtpaydown
Endif

debtpaydown:
If Priority 1 loan type is LoC OR (Priority 1 loan type is mortgage AND year-to-date extra payments has not exceeded maximum extra funds allowed) then
use any extra funds to pay down the Priority 1 debt
If the Priority 1 debt has no balance OR the extra funds EXCEED the balance owing less the standard monthly payment(s) in the Priority 1 debt AND
If Priority 2 loan type is LoC OR (Priority 2 loan type is mortgage AND year-to-date extra payments has not exceeded maximum extra funds allowed) then
use the extra funds to pay down the Priority 2 debt
If the Priority 2 debt has no balance OR the extra funds EXCEED the balance owing less the standard monthly payment(s) on the Priority 2 debt AND
If Priority 3 loan type is LoC OR (Priority 3 loan type is mortgage AND year-to-date extra payments has not exceeded maximum extra funds allowed) then
use the extra funds to pay down the priority 3 debt
If the Priority 3 debt has no balance OR the extra funds EXCEED the balance owing less the standard monthly payment(s) on the priority 3 debt AND
If Priority 4 loan type is LoC OR (Priority 4 loan type is mortgage AND year to date extra payments has not exceeded maximum extra funds allowed) then
use the extra funds to pay down the priority 4 debt
If the Priority 4 debt has no balance OR the extra funds EXCEED the balance owing less the standard monthly payment(s)on the Priority 4 debt AND
If Priority 5 loan type is LoC OR (Priority 5 loan type is mortgage AND year to date extra payments has not exceeded maximum extra funds then allowed)
use the extra funds to pay down the priority 5 debt
If the Priority 5 debt has no balance OR the extra funds EXCEED the balance owing less the standard monthly payment(s)on the Priority 5 then debt
put the extra funds into the savings account
Endif
Endif
Endif
Endif
Endif
Endif
Endif
Endif
Endif
Endif

Because I want the extra payment number to populate the correct row and column in the for each loan, I am not sure how to do that with this logic. Any suggestions would be appreciated.

Trish

 

[trishcollins]

FYI, if I do this in VBA that's fine, but I would want it to run everytime one of the "extra" funds cell amount changes on ANY worksheet.

Trish