In 2010, Jake Stichler wrote a post about "Finding a Magic Number" that tells him how much he has to pay each month to reach his debt payoff goal, using the Vertex42 Debt Reduction Calculator to help calculate that magic number.
We had a conversation in which he suggested that it would be cool to have a spreadsheet that could "specify debts, interest rates, and a goal date for zero debt, then automatically find the amount that needs to be spent on the debt in the specified snowball method to hit that date goal." Currently, to find the monthly payment that results in a specific payoff goal can require around 20 iterations.
Luckily, both Excel and OpenOffice have a neat little tool that can make this a breeze. It's called "Goal Seek." And, you don't have to re-enter your data anywhere, or use a different worksheet.
Here is how to calculate your monthly payment based on a particular payoff goal. Let's say that we want to pay off all our debt in 48 months, using the Snowball approach. The image below shows exactly how to set things up using Goal Seek (which is available from the Tools menu in Excel 2003 and OpenOffice and somewhere else in Excel 2007).
- If you are using OpenOffice, first select the Monthly Payment cell (C20)
- Go to Tools > Goal Seek
- In the Set Cell field, reference the Months to Pay Off value for the LAST creditor listed (in this case, cell E31
- In the To Value field, enter the # of month you want to set as a goal (48 in this case)
- In the By Changing Cell field, reference the cell containing the Monthly Payment (C20)
- Hit OK
Here is the result of running goal seek for this example. The Monthly Payment has been automatically changed to $698.63 to reach the goal of paying off the debt in 48 months.
Now, to have some more fun (in the geek sense), you can change the debt payoff strategy to the Avalanche method (highest interest rate first) to see how much that can lower your Monthly Payment.
In general, Goal Seek is a very useful tool when you are using a spreadsheet-based financial calculator and want to solve for a value instead of entering it as an input.
Good luck reaching your goals.