Business Solutions: Excel-Goal Seeking Command
By James D. Duncan, CPA  April 2006

       We use Excel to accumulate data and extract answers. This month I want to explore the goal seeking function. This is a part of the suite of commands called what-if analysis. I took a quick look in the spreadsheet help and searched for “goal”. This gave me a very abbreviated explanation of the command. I then clicked on help and Office on the web.
       On the Microsoft site I search on “goal” and found 53 matches. From this page I chose “About Goal Seek”. I am sure each selection would have taken me to a fascinating explanation of more features that I would like to use. But, let’s look at the goal section.
       This has explanation and a sample. Click on the Show All and get the full verbiage. This always gives insight and more explanation into the theory and workings of the system.
       Let me give you the sample that Microsoft has. The goal is to determine (seek) what the interest rate would be on a $ 100,000 loan for 180 months with payments of $ 900. Input the following information. The PMT function gives us a payment of $ 984.74. We can play with this or use the Goal seek function.

Only changes other than plain text are that B1 and B4 are formatted for currency. B3 is formatted for percentages. The formula “=PMT(B3/12, b2,b1)” is entered in B4.
        Now let us do the magic part. Click on Tools, select “Goal Seek…” and you will get an input box to fill in. Let me show you what I did first time. I sure did not get what I wanted. I had to study what the PMT formula was asking for and what I had input. Here is my input. Made sense to me.

And the result was not correct. The Goal Seek Status window displays the following.



The spreadsheet interest field and payment field were filled with errors indicating data problems. When I clicked on cancel the original information comes back.. I tried the goal seeking again. What I had failed to understand was that the “PMT” formula wanted the payment in negative format. The “To Value” for cell B4 should be –900 not a +900. The payments are of descending values. Get help on the “PMT” function and it will show you that the payment value is to be negative. As my friend Boyd says, push it back to the top of the hill.



Now we have the payment of $ 900 and the answer for an interest rate is 7.02%. Just way cool as they say.



That is it for April 2006. I always get tickled when someone says; “I can’t believe it is April already”. My thought is, April? What happened to 1987? Well, time goes so fast when you are having fun. Get out there and have some fun. Volunteer to help an organization, help your friends, and if you are not helping at least one new organization a year…..say yes to the next place that asks you. Sure, put a time limit on it, do not do it again if it really cramped your schedule…BUT just do it!!!

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James