Goal seeking is one of the ways Excel can perform a "What if" analysis on data you have entered. For example, you can use a function to calculate the amount of money you would have after a period of years of investing a monthly amount at a particular interest rate. Then, using the Goal Seek feature, you can see how much you would have to invest monthly to reach a particular goal. In this tutorial, we'll see how much money we need to invest monthly to reach a goal of $100,000 for a child's college fund.
Setting Up the Data
Enter the necessary data in the worksheet, as below. We are using an investment of $100 per month to see what the initial result will be. The term in months shown below assumes you will start investing when the child is born and will need the money when the child reaches age 18 (18 x 12 = 216).
Entering the Function
-
Select the cell that is to contain the function. In our example, cell B5.
-
From the Insert menu, choose Function or choose the Paste Function button,
,
from the standard toolbar. The Paste Function
dialog box is displayed as below.
Since we need to calculate a future value, we'll use the FV function in the Financial category.
-
Choose OK and enter the variables for the function using the helper box. For more information on how to enter functions, see Using Functions.
Be sure to divide the Rate cell reference by 12, since our interest rate is annual but our payments are monthly.
-
Choose OK.
Using the Goal Seek Tool
Now we're ready to set our goal at $100,000 and see what our monthly investment needs to be.
-
Choose the cell you wish to target as the goal. In our case, cell B5.
-
From the Tools menu, choose Goal Seek. The Goal Seek dialog box appears as below.
-
Set cell already contains the reference to the goal cell. In the To value field, type the goal - 100,000.
-
Use the Collapse button outlined in yellow above to select the cell from the worksheet for the By changing cell field. In our case, we want the payment amount to change - cell B2.
-
Choose the Expand button above to return to the Goal Seek dialog box.
-
Choose OK. Excel confirms that it has been able to calculate the values for your goal:
-
Choose OK. Excel displays the changes in the worksheet.
The result is shown as a negative value because you will be less that amount each month.
Excel is an excellent way to quickly set up this type of "What if" analysis and can help businesses and individuals project future situations with a few clicks of the mouse.