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.
Notice: You have the author's
permission to use the tutorials on this site for your personal
use only. If you would like to use these tutorials for
training classes or other commercial use, please contact kstille@esmartweb.com.
Distribution in print or on the Web without the author's permission
is strictly prohibited.
Featured Sites
HandofGrace.com
Quality,
affordable sterling silver beaded bracelets & mothers
bracelets. Swarovski crystals, fresh water pearls, Venetian
glass and other unique findings are featured in our bracelets.
The perfect gift for any occasion!
BakedByMom.com
Offering homemade cookie and candy boxes or gifts at reasonable prices, Baked by Mom strives to provide the best quality product in it's class. Try the White Chocolate Chip & Lime cookies, Rocky Road Candy and Caramels - they get rave reviews!
,
from the standard toolbar. The Paste Function
dialog box is displayed as below.
