Goal Seek may not have found a solution. Solver works.

[Ndmand]

I am building a financial modeling spreadsheet. The problem is with my loan repayment lines. My new formula requires that Excel look at a true false switch; if the target cell is false, then get the payment amount from line C17, if the target cell is not false, then get the repayment amount from line C24. The Loan Scheduler sheet is where all of the loan calculations are done, and the formula looks like the following: =IF('Loan Scheduler'!C23=FALSE,'Loan Scheduler'!C17,'Loan Scheduler'!C24)

I am trying to solve for particular loan payment amounts or a particular debt coverage ratio that involve the above line. Two problems occur: (a) goal seek reports that "goal seek may not have found a solution" although I give it 1000 iterations. (b) Goal seek pastes in a value (and it is way wrong) over the formula in my "By changing cell" box. This latter problem literally changes my spreadsheet, and I have to paste the formula back in.

Has anyone seen the types of "problems" specified above? I have been searching for Internet information describing the limitations of goal seek. I am even more troubled that goal seek pastes in a value, thereby, screwing up my formula.

Two more points: Solver can solve the question. Second, I have noticed that Excel makes wild guesses for the debt coverage ratio solution; I may be looking for a 1.25 debt coverage ratio, but the guesses are like 50,000,000. Thanks, and since this is my first post, I apologize if I have ignored protocol or information requirements.

 

[tusharm]

Don't know what version of XL you are using but in 2003, if you specify a Goal Seek 'by changing cell' that contains a formula, the result is an error "Cell must contain a value."

As far as the other problem(s) go, Goal Seek and Solver are constrained by the capabilities and limitations of the underlying techniques and algorithms. Using a IF statement is one of many ways that results in what is called a "non linear" problem; these are notoriously difficult to solve. Sometimes you can improve the performance of Solver by giving it a reasonable starting value.

 

[Ndmand]

Thanks for the reply. I am using Office XP, which includes Excel 2002. Often, the problem that I am trying to solve is an attempt to make the target value go from something like a 1.2 debt coverage ratio to 1.15 to find out how much cash can be financed at a given debt coverage ratio. In other words, the calculation is usually a small jump.

Goal seek succeeds or fails based on how the loan payment is calculated. If the loan payment is based on the following old formula (a), the goal seek can find the answer. However, if I use the seemingly more complicated new formula (b), then goal seek fails; solver, on the other hand can find the answer. I have been looking at formula (a) for quite some time, but can't figure out the problem. Both formulas have an IF formula.

formula (a): =IF(AND('Loan Scheduler'!C9=FALSE,$K76>0),'Loan Scheduler'!C3,IF((AND($Q76>0,'Loan Scheduler'!C$1>$Q76,'Loan Scheduler'!C$1<=($Q76+$V76))),($W76*$U76),0))

forumula (b): =IF('Loan Scheduler'!C23=FALSE,'Loan Scheduler'!C17,'Loan Scheduler'!C24)

Both formulas derive a loan payment. A major difference between formula (a) and (b) is that formula (a) independently calculates the loan payment. Formula (b) refers to the loan sheet to select the loan payment.

ALSO NOTE MY ERROR in the first post. When Excel is making what I called wild guesses, it really is trying to guess the loan amount, not the small debt coverage ratio. However, when I just asked it to set the debt coverage ratio from 1.12 to 1.2, it couldn't find a solution but paste in this number: 4.09173758407525E+153.

 

[tusharm]

I can't help you much with Goal Seek subtleties. I hardly ever use it since I prefer Solver. One thing I can do is repeat what I mentioned the first time around. Both of them are much better suited for what are known as "linear" problems in the world of Operations Research. Use of an IF statement makes a problem non-linear.

The products work by testing for the correct direction in which to change a variable (increase it or decrease it). So, if you specify A1 as the "by changing cell" Solver will change it by a "small" amount and see how that affects the result. It will then use that information to either increase or decrease A1.

Now, consider you have a IF statement like =IF(A1<100000,1,0) and A1 has a value of 0 (or a value of 200,000). A "small" change in A1 will have no effect on the result since the IF statement will show no change. Hence, Solver (and Goal Seek) will conclude that no change in A1 will yield the desired result.

You may have noticed I used quotes around small. That's because the word is relative. In the right (wrong?) circumstances, 4E153 might be considered "small."

OTOH, it's also possible that you have an unbounded problem. This would be something similar to maximizing x^2.

 

[Ndmand]

Thanks, Your Reply Was Very Helpful

Tusharm,

Thanks for taking the time to "repeat." Your reply was very helpful, and it gives me something to think about. Last night, I experimented with why my old formula would work, but my new formula did not. The result was rather odd.

I ended up using the old formula so that Goal Seek can be used. I don't want folks to think there is a problem with the spreadsheet (I don't think there is) when the problem is that Goal Seek can't find an answer, which I am beginning to believe is the case. Again, thanks, you've given me something to consider ... right after I figure out what an unbounded problem is.

Ndmand

 

[tusharm]

Re: Thanks, Your Reply Was Very Helpful

You are welcome. As far as an "unbounded" problem goes, what's the answer to "What value of x maximizes x^2?"

Tusharm,

Thanks for taking the time to "repeat." Your reply was very helpful, and it gives me something to think about. Last night, I experimented with why my old formula would work, but my new formula did not. The result was rather odd.

I ended up using the old formula so that Goal Seek can be used. I don't want folks to think there is a problem with the spreadsheet (I don't think there is) when the problem is that Goal Seek can't find an answer, which I am beginning to believe is the case. Again, thanks, you've given me something to consider ... right after I figure out what an unbounded problem is.

Ndmand