Using the Goal Seek function in Excel: a brief tutorial

Goal Seek is a really useful and easy function to use in Excel, but a surprising number of people aren’t aware of it. If you’re a regular spreadsheet user and have ever run into a situation where you wanted to figure out what number to “back into” to get a result you want, or you have a hairy equation that you don’t want to toy with, using Goal Seek is a much easier approach than using trial-and-error. Here’s a simple tutorial on how to use Goal Seek example using the famous CAGR formula.

CAGR, or compound annual growth rate, is one way to measure your annualized return given an initial amount, and ending amount, and a period of time. As regular readers might recall, the formula goes like this:

If you wanted to put this into an Excel spreadsheet, it might look like what’s below (taken from the first part of my CAGR excel spreadsheet) (click to enlarge):

It’s pretty straightforward. Initial amount, ending amount, and # of years are all numbers you input, and CAGR is the equation above written for Excel (e.g. taking the proper cells as inputs).

But what if instead of calculating CAGR, you wanted to calculate the number of years it would take you to get to a certain return, given an initial amount and ending amount?

You could go about this two ways: either rewriting the CAGR equation above to solve for “# of years” or use the Goal Seek function. In the third part of my CAGR spreadsheet, I actually solved for “# of years”, but if you couldn’t remember or didn’t want to do this, here’s how you’d use Goal Seek to approximate the same thing:

Under the “Tools” menu item, select “Goal Seek” (click to enlarge):

This will generate a small popup window, which you must fill in (click to enlarge):

The “Set cell:” field is the cell that has the equation and result that you want. In this case, let’s try to find the number of years it would take for a $100 initial investment and $110 ending investment to result in a CAGR of 5%. So, we’d put in “B10” for the “Set cell:” field. The one hitch with Goal Seek is that “Set cell:” must always contain an equation. Otherwise, Goal Seek won’t work.

We want that value to be 5% (or 0.05…Goal Seek will accept either one), so enter that for “To value:”. And finally, for “By changing cell:” we enter in “B9” (or, if you use your mouse to point-and-click, Excel populates it with $B$9…just ignore the dollar signs in this case…they don’t do anything different) (click to enlarge):

Hit the “OK” button, and voila: Goal Seek will fill in the blanks with the number you’re looking for, which in this case is ~1.939 years for a value of 5.04% (click to enlarge):

Remember, Goal Seek backs into a solution using numerical iterations, so it won’t necessarily find the exact solution you’re looking for. It might come “close enough” and stop. In this case, 5.04% was the closest it could come to solving for 5%.

In case you’re wondering, this comes pretty close to using the other method of solving for “# of years” directly, which comes out to be 1.95 years using natural logarithms.

So, the next time you’re making your projections and want to play around with the numbers, remember Excel’s handy Goal Seek function!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************