(NOTE: Although this question refers to a spreadsheet to solve the problem, I am also open to using PHP or Javascript)
I have a spreadsheet for forecasting my personal financial budget for the next 3 years. The goal is to pay as much as possible on the highest priority debt until it is paid off and then the next debt in priority begins to receive all extra monthly funds until it is paid and so on. I want the debt payments to auto calculate their payments, but I run into a circular reference error since computing the debt payment requires considering the debt balance which is computed using the debt payment. In addition the net income and thus checking balance use the debt payments to derive their values so it makes another circular reference when trying to self compute the debt payment.
Is there a financial function that can compute the maximum possible payment without referencing the debt balance? Something that takes a starting balance and interest rate and looks at the sum of payments already made? I think I can get around the Checking Balance circular reference.
Below is a sample of my spreadsheet. Income and Expense rows are manually input. Debt is currently manually entered as well but I want it to auto compute as outlined above. Net income, Checking Balance and Debt Balance are computed rows.
---------------------------------------------------------------------------------------------
| | Jan | Feb | Mar | Apr | May | June | July | Aug | Sep | ...
---------------------------------------------------------------------------------------------
| INCOME
---------------------------------------------------------------------------------------------
| Salary 1 | 6500 | 6500 | 6500 | 6500 | 7000 | 7000 | 7000 | 7000 | 7000 | ...
---------------------------------------------------------------------------------------------
| Salary 2 | 2500 | 2500 | 2500 | 2500 | 2500 | 2500 | 2500 | 2500 | 2500 | ...
---------------------------------------------------------------------------------------------
| Misc | 500 | | | | | 500 | | | | ...
---------------------------------------------------------------------------------------------
| EXPENSES
---------------------------------------------------------------------------------------------
| Gas | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | ...
---------------------------------------------------------------------------------------------
| Food | 800 | 800 | 800 | 800 | 800 | 800 | 800 | 800 | 800 | ...
---------------------------------------------------------------------------------------------
| Auto Ins | 150 | | | | | | 150 | | | ...
---------------------------------------------------------------------------------------------
| Misc | 500 | 1000 | 500 | 500 | 500 | 2500 | 2500 | 500 | 500 | ...
----------------------------------------------------------------------------------------------
| DEBT
---------------------------------------------------------------------------------------------
| Auto | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | ...
---------------------------------------------------------------------------------------------
| Mortgage | 2500 | 2500 | 2500 | 2500 | 2500 | 2500 | 2500 | 2500 | 2500 | ...
---------------------------------------------------------------------------------------------
| Student Loan | 700 | 700 | 700 | 700 | 700 | 700 | 700 | 700 | 700 | ...
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
| NET INCOME
---------------------------------------------------------------------------------------------
| | 3950 | 3100 | 3600 | 3600 | 4100 | 2600 | 1950 | 4100 | 4100 | ...
----------------------------------------------------------------------------------------------
| CHECKING BALANCE
---------------------------------------------------------------------------------------------
| | 3950 | 7050 | 10650 | 14250 | 18350 | 20950 | 22900 | 27000 | 31100 | ...
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
| DEBT BALANCE
---------------------------------------------------------------------------------------------
| Auto | 14809 | 14618 | 14427 | 14235 | 14043 | 13850 | 13657 | 13463 | 13269 | ...
---------------------------------------------------------------------------------------------
| Mortgage |249571 |249141 |248710 |248278 |247844 |247409 |246974 |246537 |246100 | ...
---------------------------------------------------------------------------------------------
| Student Loan | 84541 | 84050 | 83616 | 83148 | 82677 | 82203 | 81726 | 81245 | 80761 | ...
---------------------------------------------------------------------------------------------
EDIT – 09/25/2014
To clarify further consider the tables above. Here's what I am trying to do.
netIncome = SUM(INCOME) - SUM(EXPENSE) - SUM(DEBT)
checkingBalance = previousBalance + netIncome
if (debtBalance > 0 && debtBalance <= debtMinimumPayment) // last payment is equal to or less than min payment, pay it off
thePayment = debtBalance
else if (debtBalance > 0) // we owe something greater than minimum payment
if (paymentAbove = 0 && checkingBalance > debtMinimumPayment) // the debt in the row above is paid off, now we begin applying extra funds to this debt
if (checkingBalance < debtBalance) // payoff as much as we have available
thePayment = checkingBalance
else // we have enough funds to payoff this debt - do it
thePayment = debtBalance
/if
else
thePayment = debtMinimumPayment
/if
else // debt already paid off
thePayment = 0
/if
Unfortunately in the sudo code above I cannot reference "netIncome" and "debtBalance" because those values are computed using "debtPayment" which is what I'm trying to compute. My spreadsheet does not allow me to use those cells and states that you cannot reference a cell that references this cell. So I was hoping there might be a financial function in Excel that would help or some other way to skin this cat.
As it turns out, I do not believe there is a way around this with native formulas in Excel or Numbers. As such I utilized Applescript to do the logic one row at a time. not perfect but it saves me a lot of time manually entering values.
It looks like you might be referring to Ramsey or The Financial Fitness Pack from life leadership. I see what you are trying to do, but do you want to pay off the lowest amount first like these courses suggest or the highest interest rate first? There are psychological reasons for paying the lowest one off first and then applying that payment to the next one.
Put all the minimum payments in your spreadsheet for each row along with the total debt and interest.
example
total amount extra to apply: 25
total Interest Jan Feb Mar Apr May
1253 17.3% Visa card: 25 25 25 25 25
4700 22.1% Master : 35 35 35 35 35
...
Next you will need to add a row to update the amount. calculations are simplified, you can use these or get more exact calculations.
example
jan feb mar apr
Visa card : min min min min
paid : *A* *A* *A* *A*
int : *B* *B* *B* *B*
new total : *C* *C* *C* *C*
Master card: min min min min
paid : *D* *D* *D* *D*
int : *B* *B* *B* *B*
new total : *C* *C* *C* *C*
...
*A* formula = min payment + extra payment
*B* formula = (interest/12)*total
*C* formula = total - paid + *A*
after the first column use 'new total' instead of total from above in all formulas
now you can add an if statement to each of the paid boxes. the if statement should be something like
if new total < min then pay new total
when new total = 0 payment will be 0
for the every debt after the first one you change the formula for A to be something like
*D* formula = min + (extra payment - *A* from above)
the min is whatever the minimum payment is for that debt.