The hack that does algebra for you
Formulas, shortcuts and other useful tips on spreadsheets.
In last week’s edition of Excel Genius, we talked about using PMT to calculate a monthly payment to pay off a loan or meet a savings goal, with an interest rate and a constant delay. Today, PMT meets its partner in Goal search.
Let’s say the payout the spreadsheet gods spat out was too high or too low for your liking. You can use Goal Seek to make adjustments to other parts of the equation (interest rate, period, loan amount, or savings goal) to meet your target monthly payment.
Let’s say I want to save $ 10,000 in 12 months at a 1% interest rate, but that means I should set aside $ 828 each month, which is more than I can handle. So, I continue with Goal Seek in the GIF above to see how long it will take to reach my goal with the $ 600 I can afford to put aside each month.
In Excel: Highlight the cell whose value you are changing> Select “Data”> “What-if analysis”> “Goal finder”> Check that “Define cell” is the initially highlighted cell> In “To value”, enter the number of your objective for the highlighted cell> Enter in “By changing cells” the name of the cell you wish to manipulate
In Google Sheets: Follow the same process as above, but you will need to download the Goal Seek add-on created by Google first. From any Google Sheet, select “Add-ons”> “Get Add-ons”> Type “Goals Finder”