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

 

[FredDvorak]

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

Hi, Based on the logic which you outline, it seems that you want excess cash to "fall" into the next category of payment or fund. This can be achieved simply with formulas, rather than VBA. There are 2 typical choices:
1) Using nested IF statements;
2) Using nested MIN and MAX (which is comparable to IF statements, but more efficient and not as clear for most people how want to understand what formulas do). Nesting MIN and MAX is favoured in financial models such as yours because it is compact; it is use in waterfall calculations (which is what you are attempting to do).

In your case, you will need to add OR and IF in order to deal with dates and other constraints.

What you will need to facilitate this implementation is to separate each account in the waterfall and create an opening balance, a payment row and a closing balance. Below each account block, you need a row that records the cash available that can be used by the next account block. The cash available after a account block "X" is the positive difference between the previous row of cash available (above account block "X") and the cash used in account block "X", and its ultimate minimum can only be 0 (0 means all the excess cash has been used in the previous steps).

The payment row in each block uses nested MIN and MAX, with OR and IF to deal with dates and other constraints.

That said, in the 2nd screenshot that you have shared, a priority order is shown (rows 115 to 121). If this priority does not change during the year, you should reorder the accounts by order of priority (0 at the top, and then, 1, 2, 4, etc.). If you have 2 accounts with the same priority, you must determine either (1) which on is processed first (which creates a priority) and (2) how to apportion excess cash between them if they must be satisfied at the same time.

I've tried to attached the worksheet which I have screenshot and displayed above (I use it for demonstration for my clients), for which I must install an add-in to be able to upload Mini-sheets but can't do that for security reasons. As a solution, I'm showing below the formulas for the first year (2025(E)), which can be copied to the right for each subsequent year. Note: I've neither audited nor tested this model for some time, so use it knowing that it has limitations.

Note re. optional repayments:
1) Enter any number which set the optional repayment if excess cash is available.
2) Entering 0 means that the cash sweep is 0 (no sweep).
3) A blank cell implies that all the cash available after mandated repayment is used by the cash sweep.

I hope it helps.

 

[FredDvorak]

Hi, Based on the logic which you outline, it seems that you want excess cash to "fall" into the next category of payment or fund. This can be achieved simply with formulas, rather than VBA. There are 2 typical choices:
1) Using nested IF statements;
2) Using nested MIN and MAX (which is comparable to IF statements, but more efficient and not as clear for most people how want to understand what formulas do). Nesting MIN and MAX is favoured in financial models such as yours because it is compact; it is use in waterfall calculations (which is what you are attempting to do).

In your case, you will need to add OR and IF in order to deal with dates and other constraints.

What you will need to facilitate this implementation is to separate each account in the waterfall and create an opening balance, a payment row and a closing balance. Below each account block, you need a row that records the cash available that can be used by the next account block. The cash available after a account block "X" is the positive difference between the previous row of cash available (above account block "X") and the cash used in account block "X", and its ultimate minimum can only be 0 (0 means all the excess cash has been used in the previous steps).

View attachment 121664

The payment row in each block uses nested MIN and MAX, with OR and IF to deal with dates and other constraints.

That said, in the 2nd screenshot that you have shared, a priority order is shown (rows 115 to 121). If this priority does not change during the year, you should reorder the accounts by order of priority (0 at the top, and then, 1, 2, 4, etc.). If you have 2 accounts with the same priority, you must determine either (1) which on is processed first (which creates a priority) and (2) how to apportion excess cash between them if they must be satisfied at the same time.

I've tried to attached the worksheet which I have screenshot and displayed above (I use it for demonstration for my clients), for which I must install an add-in to be able to upload Mini-sheets but can't do that for security reasons. As a solution, I'm showing below the formulas for the first year (2025(E)), which can be copied to the right for each subsequent year. Note: I've neither audited nor tested this model for some time, so use it knowing that it has limitations.

Note re. optional repayments:
1) Enter any number which set the optional repayment if excess cash is available.
2) Entering 0 means that the cash sweep is 0 (no sweep).
3) A blank cell implies that all the cash available after mandated repayment is used by the cash sweep.

I hope it helps.

View attachment 121665

I've improved the formulas and corrected an error. Use the following formulas instead:

Hi, Based on the logic which you outline, it seems that you want excess cash to "fall" into the next category of payment or fund. This can be achieved simply with formulas, rather than VBA. There are 2 typical choices:
1) Using nested IF statements;
2) Using nested MIN and MAX (which is comparable to IF statements, but more efficient and not as clear for most people how want to understand what formulas do). Nesting MIN and MAX is favoured in financial models such as yours because it is compact; it is use in waterfall calculations (which is what you are attempting to do).

In your case, you will need to add OR and IF in order to deal with dates and other constraints.

What you will need to facilitate this implementation is to separate each account in the waterfall and create an opening balance, a payment row and a closing balance. Below each account block, you need a row that records the cash available that can be used by the next account block. The cash available after a account block "X" is the positive difference between the previous row of cash available (above account block "X") and the cash used in account block "X", and its ultimate minimum can only be 0 (0 means all the excess cash has been used in the previous steps).

View attachment 121664

The payment row in each block uses nested MIN and MAX, with OR and IF to deal with dates and other constraints.

That said, in the 2nd screenshot that you have shared, a priority order is shown (rows 115 to 121). If this priority does not change during the year, you should reorder the accounts by order of priority (0 at the top, and then, 1, 2, 4, etc.). If you have 2 accounts with the same priority, you must determine either (1) which on is processed first (which creates a priority) and (2) how to apportion excess cash between them if they must be satisfied at the same time.

I've tried to attached the worksheet which I have screenshot and displayed above (I use it for demonstration for my clients), for which I must install an add-in to be able to upload Mini-sheets but can't do that for security reasons. As a solution, I'm showing below the formulas for the first year (2025(E)), which can be copied to the right for each subsequent year. Note: I've neither audited nor tested this model for some time, so use it knowing that it has limitations.

Note re. optional repayments:
1) Enter any number which set the optional repayment if excess cash is available.
2) Entering 0 means that the cash sweep is 0 (no sweep).
3) A blank cell implies that all the cash available after mandated repayment is used by the cash sweep.

I hope it helps.

View attachment 121665

Warning: I've noticed an error in my formulas. I'll correct it and resend you the formulas.