Goal Seek in Excel
Excel has a built-in feature to let you seek a value in
an equation or function by changing the value of one variable involved
in its calculation. For example, Excel provides a function to determine
the monthly payment for a loan, given the amount of the loan, the interest
rate, and the number of payments to be made. The image below shows that
function used to determine the monthly payment for a $75,000 mortgage
with a6.9% annual rate for 30 years.
Suppose you decided you can afford a larger payment and
would like to determine how large a loan, given the same rate and years,
would yield a payment of $600/month. You could do this by trial and error:
keep changing the value in cell B2 until cell B6 shows $600.00.
An easier method is to use the built-in Goal Seek feature.
Click on "Tools" and "Goal Seek" and the dialog box
shown appears. The labels are fairly self-explanitory: You want Excel
to set the value in cell B6 (where the function determines the payment
amount) to a value of 600 by changing the value in cell B2 (the loan amount).
Click "OK", anf the cells and dialog boxes change as shown:
Click OK and the cells stay as shown. Click cancel, and
the cells return to their original values.
|
