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

trishcollins

Board Regular
Joined
Jan 7, 2006
Messages
71
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.

1720798877783.png



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.

1720798387630.png


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.

1720798457898.png


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.

1720800416929.png


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 ;)
 

Attachments

  • 1720797790856.png
    1720797790856.png
    39.3 KB · Views: 24
  • 1720797849859.png
    1720797849859.png
    135.3 KB · Views: 28
  • 1720798088120.png
    1720798088120.png
    93.5 KB · Views: 25
Last edited:
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 ;)
 
Upvote 0
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).

1737905482445.png


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.

1737905861416.png
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,827
Messages
6,193,189
Members
453,778
Latest member
RDJones45

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top