8.3 Goal Seek | Microsoft Office Automation with Visual FoxPro

Goal Seek

Goal Seek is a feature that allows you to set up a formula and specify the result and all but one of the values in the formula, then have Excel solve for that value. While our simple example can be solved entirely with FoxPro functions, it illustrates how the Goal Seek function works. There are many kinds of problems that can use Goal Seek; the most popular example is the PMT() function, which takes three parameters (loan amount, term in months, and the interest rate) and determines the payment. Most of us, when applying for a loan, know the loan amount, the desired term, and a desired payment. Goal Seek allows us to provide the solution to the PMT function, and then find the value of one of the parameters the interest rate to achieve the desired payment.

We need a simple spreadsheet with three data cells, and another cell for the payment function. Let s assume our mortgage is a $150,000, 30-year loan (360 months), and we d like a payment of $975. All we need to find is an interest rate. First, we set up the spreadsheet with the three values (use any interest rate you want) to set up the payment, like this:

oSheet.Range("A1").Value = "Amount"

oSheet.Range("B1").Value = 150000

oSheet.Range("B1").NumberFormat = "$###,###"

oSheet.Range("A2").Value = "Term"

oSheet.Range("B2").Value = 360

oSheet.Range("C2").Value = "months"

oSheet.Range("A3").Value = "Rate"

oSheet.Range("B3").Value = .075

oSheet.Range("B3").NumberFormat = "0.00%"

oSheet.Range("A4").Value = "Payment"

oSheet.Range("B4").Value = "=PMT(B3/12, B2, B1)"

oSheet.Range("B4").NumberFormat = "$###,###"

The payment shown in cell B4 is $1,048.82 (well, technically, it s -1,048.82; it s a negative number because you owe it, so be sure to set your goal to a negative number). To get it to $975, use the GoalSeek method to solve for the interest rate. GoalSeek is a method of the Range object. The specified range is the cell containing the goal that is to be sought. The method takes two parameters: the first is the goal, or the answer for the range, and the second is the range (a cell) to change in order to solve the problem. To get the interest rate, give the following:

oSheet.Range("B4").GoalSeek(-975, oSheet.Range("B3"))

And, voila, you have the result: 6.77% (see Figure 8). Did you notice that the numbers flashed and recalculated? That s because Excel uses an iterative process: it changes the value of the variable s cell, then checks the result. If the result is incorrect, it gets a new value for the variable and repeats the process. Generally, this runs very quickly, although complex equations or an initial value that s drastically different from the result can slow it down. Now comes the hard part trying to find a bank that will lend you the money at 6.77% interest.

Figure 8. The results of the GoalSeek method. The desired payment is $975, and the amount and term are fixed; GoalSeek found the necessary interest rate.