One of the very cool things about using a tool like Microsoft Excel is the “What If” possibilities. You can set up your spreadsheet then work through various scenarios to see if you can get the outcome you want, or at the very least something approaching an ideal outcome.
Let’s take a look at the powerful but rarely used ‘goal seek’ feature of Excel, using debt reduction as an example.
You know the concept behind restructuring debts. Normally this takes the form of negotiating more favourable (or achievable) terms, or taking out a new loan that has a more manageable payment structure. This helps people get out from under the crushing weight of credit card debts and other short term loan arrangements, and putting the payments over a longer period, bringing the monthly payments down to a figure that fits within available cash flow.
If you know exactly what you have available to pay, and how much you need to pay off, then you can find a loan that suits your purposes, and hopefully get your cash flow back into a realm where you can sleep at night!
At the same time, you do not want to overpay, by either paying too high an interest rate, or by paying over to long a period. Just like the three bears, you don’t want too much or too little, but just right.
How do you know exactly what you are looking for?
The idea here is to use the Excel Goal-Seek function to calculate the terms of the loan you need to get in order to buy out your expensive debt, and still maintain a positive (or balanced) cash flow.
Desperately Goal Seeking Excel
The Goal Seek function is found under the ‘What if analysis’ menu in the data ribbon. When you select this feature it will rapidly go through various options in your spreadsheet, filling out the cells you select, until it gets to the result you ask for.
What if we couldn’t afford $500? What if we needed to get that down to $400?
Using Goal-Seek we can see that if the interest rate stayed the same, the loan term would have to be … wait for it … 127.3692 months. And the total at the end of the term? That $30k debt is now a princely sum of $50,947.70!
OK, less of the negative. What if you found a generous bank (don’t laugh) that offers you a more manageable deal by shaving off some of that interest?
To keep your payment term to 86 months and the monthly payment down at $400 you would need to find an extremely generous bank offering you a loan of only 2%!
The Limits of ‘What If’
Play with the figures enough and you will see there are scenarios where you simply can not get to the desired monthly payment, interest rate or loan term. The longer the loan goes on for, the more you have to pay in interest, and from a certain point the monthly payment will increase and not decrease. Interest rates do not go below 0% no matter how nice your bank manager is, therefore a $300 a month payment over 86 months will never pay back a $30k loan.
Summary
Doing a quick what-if analysis can really help you see clearly what you need to do, and to try out different scenarios at the limit of your ability or comfort zone. Armed with that knowledge you can then make an informed decision.
Just like in real life, if you can not get a perfect loan then you will need to either accept an imperfect one, or improve your cash flow outlook by either increasing your income or decreasing your cost of living, or both. Looks like another month of ramen noodles, eh? Ha.
P.S.
Don’t forget to check out our PDF to Excel Converter. It can save you a lot of precious time you now spend on retyping PDF data.
