Excel Goal Seek

Excel Goal Seek is another tool in the What-if Analysis suite on the Data tab of the ribbon. Goal Seek makes it easy to find the value required to meet a target value you specify.

Watch the Excel Goal Seek Video

Download Workbook

Excel Goal Seek

Let’s say your 13 year old son wants to save $10,000 to buy a car when they graduate school. They have a part-time job and can afford to save $100 a month in an account that pays 5% interest per annum.

We can use the FV function to calculate how much they’ll have after 5 years (60 months) of saving $100 at 5% interest p.a. compounded monthly:

=FV(5%/12,60,-$100,0,0)

=$6,800.61

But how many months will it take to save $10,000? I could manually change the number of months in the formula until I got close to $10,000, but that could take a lot of trial and error. It’s more efficient to let Excel’s Goal Seek do the work.

In the image below, column B contains my original calculation which I’ve copied to column C for Goal Seek to work its magic. Cell C7 contains the FV formula. This is my target value cell that I want to return $10,000.

Constraints: They can’t save more than $100 per month and they can’t get a higher interest rate, therefore the only variable is the number of months they can save.

All I need to do now is go to the Data tab > What-if Analysis > Goal Seek:

This opens the Excel Goal Seek dialog box (image below):

• The Set cell is the cell that I want to contain $10,000. Note: the ‘Set cell’ must contain a formula.
• The ‘To value’ is the target value I’d like the set cell to contain. Remember, the target is $10,000.
• The ‘By changing cell’ is the cell I want to adjust. In my case it’s the number of saving months (periods). Note: if you prefer, this cell can be left empty for Goal Seek to solve but it cannot contain a formula.

Goal Seek may go through several iterations before it finds the solution. In which case you’ll see the window shown below:

Once Goal Seek finds the answer it will change the cell you referenced in the ‘by changing cell’ field, which in my example is cell C5:

And we can see it’ll take 84 months to save $10,000.

What if they managed to increase their savings by foregoing the latest video games each month. (I can hear it now…”Mum, I’d rather poke myself in the eye than not have the latest game…and anyway, no one calls them ‘video games’ anymore”).

But Mum is persuasive 😉 so she runs another Goal Seek in column D on the amount saved:

And Goal Seek shows that with an increase of $47.05 per month they can save $10,000 in 60 months:

Now comes the hard part of convincing my son to start saving for his own car!