Goal Seek in Microsoft Excel

Continuing with our Excel Tutorials, in this article, I’ll take you through using Goal Seek in Microsoft Excel 2007. The function is same as that of earlier versions of Excel as well as Excel 2010. The screenshots below are taken in 2007.

Goal Seek is yet another What-If Analysis Tool that comes inbuilt in Excel. Unlike data tables, which allow to change one or more variables to find how the output varies, goal seek allows you to “set” the value of the output at a particular value and find out what value of the input variable achieves that output.

For the sake of this tutorial, we’ll take a look at finding Net Present Value. In short, Net Present Value or NPV is used to figure out if a long term project is financially feasible or not. I won’t go much into a detailed explanation of NPV as the Wikipedia article covers that. Let’s get down to the tutorial directly.

Open up a new Excel sheet and enter the data as shown in the figure below.

Cell B8 contains the NPV formula, i.e. =NPV(B7,B2:B6)+B1.

Let’s look at the NPV formula. NPV(Interest Rate, Value1, Value2, ...), where:

Interest Rate is the rate at which the cash flows are discounted, i.e. discount rate. Value1, Value2, etc. are the differect cashflows throught the years. We add B1 separately as that is the initial investment. If you include B1 in the formula, then that is discounted initially as well!

So, at 7% interest we find that the NPV is $-677.87, i.e. the project is not feasible at this percentage.

So, where does Goal Seek come in? Let me introduce you to the concept of Internal Rate of Return. The IRR is that interest rate at which the NPV equals zero.

We will now use Goal Seek to set NPV to zero.

In Excel 2007, goto Data > What-If Analysis > Goal Seek. In Excel 2003, the menu path is Tools > Goal Seek or you can use the shortcut key Alt + T + G in this order.

Enter the parameters in the Goal Seek window that popups as shown in the screenshot below.

Hit OK and you will get a window with the Goal Seek Status.

You can download the Excel file which I used in this tutorial. I’ve also added the IRR formula, so you can compare the results.