Continuing with our Excel Tutorials, in this article, I’ll take you through using Goal Seek in Microsoft Excel. The function is same as that of earlier versions of Excel as well as Excel 2010.
Goal Seek is yet another What-If Analysis Tool that is extremely useful and forms an integral part of many Excel modelling exercises. Unlike data tables, which allow you 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. If you’re scratching your head at this stage wondering what I just said, then read on, and it will get much clearer.
For the sake of this tutorial, we’ll take a look at finding the Net Present Value. NPV method is used to figure out if a long-term project is financially feasible or not. If you’re not familiar with NPV, I suggest going through this Wikipedia article on NPV. Go ahead. I’ll be here when you get back.
Let’s get down to business then.
Open up a new Excel sheet and enter the data as shown in the figure below. Or alternatively, just download the file from the end of this post.
Cell B8 contains the NPV formula, i.e.
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, also known as the discount rate.
Value1, Value2, ... are the various cashflows throught the years. Instead of entering them separately, the better option is to pass a single range as the second parameter to the NPV function. We then add B1 separately as that is the initial investment. If you include B1 in the formula, then it will be discounted initially as well! A more preferable option would be to use the XNPV function which let’s you set a series of dates. I usually use that instead of NPV. But, I digress.
So, at 7% rate we find that the NPV is $-677.87, i.e. the project is not feasible at this rate.
So, where does Goal Seek come in? Let me introduce you to the concept of Internal Rate of Return. The IRR is that discount rate at which the NPV equals zero.
We will now use Goal Seek to set NPV to zero.
In Excel 2007, Excel 2010 and Excel 2013, 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 pop ups as shown in the screenshot below.
Hit OK and you will get a window with the Goal Seek Status.
Excel’s Goal Seek is an extremely useful tool. It allows you to find the right input if you already have an idea of what the output should be. While the example above shows you how to use goal seek to find the internal rate of return of a project, you can find various other reasons to use goal seek. e.g. if you are aware of your forecasted budget and are aware of what your pricing might be, you could use the goal seek to decide the price to sell your product at. Of course, you’d choose this approach if your calculations would be more complicated. e.g. if you’ve got multi-tiers of sales interdependent on one variable.
If you’ve used this in the past, I’d like to know why. Let me know in the comments below.
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.