Solver vs. Goal Seek

[MS_Xsmell]

I am trying to use the Goal Seek function in a real estate cash flow model to solve the initial acquisition price to an amount required to achieve a target internal rate of return (IRR). The model I am using requires iterative calculations.

Goal seek will not work. It searches for the answer, varying from large negative to large positive numbers and eventually fails. I tried playing with the # of iterations and max change a bit, but couldn't seem to get it to work.

Solver however will find the correct answer without failing.

What's the difference between the two functions? Is there any way to get Goalseek to act like Solver so I can throw it into VBA or am I stuck with trying to figure out Solver?

Since I am only using variable, is it a difference in the default # iterations or Maximum Change?

Thanks.

 

[tusharm]

Solver is a much more sophisticated product than Goal Seek.

You can always program Solver with VBA. Use the macro recorder and also see Excel help for the various Solver functions.

 

[MS_Xsmell]

Thanks... I've been poking around with it and this seems to be working:

Sub Solve_Acq()
'
SolverOk SetCell:=Range("B109"), _
MaxMinVal:=3, _
ValueOf:=Range("B108").Value, _
ByChange:=Range("B111")

SolverSolve UserFinish:=True

End Sub

B109 = IRR Calculation
B108 = Target IRR
B111 = Initial Acquisition price

I need to test it for bugs, but so far, so good.

Thanks.