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).
SETUP: Using Goal Seek in the Debt Reduction Calculator to Calculate the Monthly Payment
- 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.
Result of using Goal Seek to Calculate the Monthly Payment to Pay Off Debt
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.
Comments
TIP: For Office 2007, here is where you can find the Goal Seek, along with other intriguing functions:
1. First, select the last populated row in the Months to Pay Off column (as shown above, cell E31).
2. Select the Data Toolbar Tab.
3. In the Data Tools sub-section of the toolbar, select What-If Analysis. That’s where Goal Seek lives.
Follow the example shown above.
Very Slick! Thanks!
May I say, I am so happy that I’ve found your website. It is absolutely awesome! Your debt calculator is amazing and I thank you for that. I’m also a YNAB fan. I’m kicking myself out of debt, and I’m so grateful that you’ve created this website to help people. It’s wonderful. I’ve given you a big shout on my website! Please keep up the good work, and thank you again.