Todays ms excel lesson 16-11-2011 (goal seek)

GOAL SEEK FUNCTION IN EXCEL

Purpose:

To find out the variable that derives the output that you are looking at. For e.g. you want to know how much to invest today so as to get Rs 13,000 after 3 years @ 9% interest p.a.

How to do

a)      Conventional approach

One way to fulfill your requirement is use the trial and error method to find out the results. You can input the initial investment as any value (say Rs 10,000) in cell B3 and calculate the maturity value in cell E3 as shown below

You can now use the trial and error method to find out the initial investment to derive the final maturity amount of Rs 13,000. By doing the same you will arrive at the following results:

b)      Using Goal Seek function

But there is an even easier way to find the answer. That is to use Goal Seek, an Excel function provided by Excel.

Where do I find goal seek in Excel 2007 / 2010

Go to “Data” tab to locate Goal seek

Where do I find goal seek in Excel 2003 or earlier

Go to “Tools” menu to locate Goal seek

Enter the following parameters and press Ok

The output will be displayed as follows:

In case excel is unable to find out the result, it will display a message as follows: