Using the Goal Seek function in Excel: a brief tutorial
Corporate finance, Excel function tutorials, Personal finance
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:
CAGR = (ending amount / beginning amount)(1 / # of years) – 1
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?