Goal Seek is an absolute gem of a feature that will tell you how to get the answer that you want. If you know the desired output, Goal Seek will tell you the inputs that you need.
In the example below, we have the FV() function calculating how much we would have saved if we put away £150 a month for 3 years at an interest rate of 5.5%.
Lets say we want to save £10,000 over these 3 years, but we do not know how much to save each month in order to do so. This is what Goal Seek can tell us.
Click What If Analysis on the Data Tab and Select Goal Seek.
Goal Seek asks for 3 items of information from you:
- Set cell – This is the cell holding the value that we know we want to achieve. So in this example that is cell C8.
- To value – This is the value of the set cell. In this example it will be 10000.
- By changing cell – Finally what cell should Goal Seek change. We would like to know how much to save each month and that is in cell B5
You are greeted with the Goal Seek Status box informing you that Goal Seek found a solution. Behind the box the cell contents will have changed to display the Goal Seek solution. We can see that we need to save £256.13 a month in order to make £10,000 in 3 years.
Click Ok to keep the solution or Cancel to reset the values.