SOLVER Not Working Correctly - What Am I Missing?

I have what I thought would be a simple issue to solve. A client has a very complicated spreadsheet that calculates (among other things) IRR on an investment property. They want to set a target IRR, and determine how much they should pay for the property in order to achieve that IRR.

Within a single sheet, there is a table of inputs (including the property purchase price) for multiple scenarios (the user selects one of these scenarios). There is a cell showing the resulting IRR (for whichever scenario is selected), to which I assigned the range name CurrentIRR.

I added a cell for Target IRR (range name = TargetIRR). I then set up a short macro to run Solver to find the purchase price that results in the target IRR. Because there are multiple scenarios, the macro has to set a Range variable (PurchasePriceCell) to whichever cell has the purchase price for the currently-selected scenario. My macro code basically looks like this:

Please Login or Register  to view this content.

When I run this as is, the SolverOK command writes the cell address ($H$43) into the PurchasePriceCell, rather than a numerical value ($H$43 is indeed the PurchasePriceCell address). I thought perhaps Solver wanted the "ByChange" reference as an address, so I tried:

Please Login or Register  to view this content.

But when I run that, the original (numerical) value remains unchanged in the PurchasePriceCell (i.e. Solver appears to do nothing). So I tried:

Please Login or Register  to view this content.

But then I get a compile error "Argument not optional" (????)
I also tried:

Please Login or Register  to view this content.

But then Solver gives a "solution" of zero (which is incorrect).
What am I doing wrong? If I run Solver manually it works fine (and gives a correct result).

Usually, it is prudent to start with SolverReset to avoid any inconsistent state left over from a previous use of Solver.

Review https://peltiertech.com/Excel/SolverVBA.html to see if that helps.

-----

It would be nice to solve the Solver problem.

But we might be able to determine the target IRR algebraically, if there is only one cash flow (purchase price?) to derive.

However, your original posting is lacking detail, and it has some seeming contradictions.

-----

Originally Posted by Merf

They want to set a target IRR, and determine how much they should pay for the property in order to achieve that IRR.

Within a single sheet, there is a table of inputs (including the property purchase price)

So which is it: determine the purchase price, or it is given as input?

(I suspect that you mean "original purchase price" and "future sales price". But I don't know.)

-----

Originally Posted by Merf

Within a single sheet, there is a table of inputs [...] for multiple scenarios [...]. There is a cell showing the resulting [...] to which I assigned the range name CurrentIRR. I added a cell for Target IRR (range name = TargetIRR)

"A picture is worth 1000 words. And an Excel file is worth a 1000 pictures."

Please follow the instructions in the bright yellow banner at the top of the page (hard to miss, heh?) and attach an example file with some representative data, formulas and (manual) Solver set-up.

I don't understand what the cash flows are that the IRR is based on.

But suppose there is a series of properly-signed cash flows in CF1, CF2, CF3 and CF4, and you want to determine CF5 to achieve a target IRR.

The formula in CF5 might be:

=-NPV(targIRR,CF1:CF4)*(1+targIRR)^5

That is just a "concept" formula. It might or might not have anything to do with your IRR problem, since again you have not provided sufficient information. GIGO.

Yes, purchase price is an input.
I want to use Solver to set that input to whatever value is required to produce the desired IRR.
I cannot attach the file as it is confidential to the client. The exact formulas used are unimportant anyway.
My basic question is: Why, if I can manually run Solver and get the correct answer, does my VBA code (attempting to run the same Solver problem) not work?

And hey, I just figured it out myself...
This code actually works correctly:

Please Login or Register  to view this content.