Find Value of Cell Such That Two Other Cells Are Equal

[JohnTravolski]

I have a spreadsheet with several complicated formulas and I'm wanting a cell B6 to display a value that would make two other cells equivalent (one of the cells depends on B6 and the other cell does not). I imagine it would have to be done using some numerical method, but I don't know if Excel can even do this at all. Take, for example, the following spreadsheet:



The PV Factor column has the formula

=(1+$B$6)^(DATEDIF(F2,TODAY(),"d")/365)

and the "Expected Present Value" cell has the formula

=SUMPRODUCT(E:E,G:G)

while the "Actual Value" column was a manually-entered constant. I'm wanting to the find the value of the "Long Term Constant Return" that makes the cells "Actual Value" and "Expected Present Value" equal. Unfortunately, sumproduct is not an invertible function, so I cannot analytically solve for the Long Term Constant Return with a formula. I need to use some kind of numerical method to find an approximate answer, but I want Excel to do this for me automatically and display the result in a cell.

How can I have Excel calculate this value for me? I have attached the spreadsheet here: solve excel equation.xlsx

 

[Legacy 456155]

Solver came up with 16.9032496047279%
Is that correct?

 

[JohnTravolski]

Solver came up with 16.9032496047279%
Is that correct?

Yes, that sounds right, but I'm not familiar with solver. Can it be set up to calculate that value automatically?

 

[Legacy 456155]

Hi John.

Can it be set up to calculate that value automatically?

Using Solver, I don't know. If you are open to a bit of code in your project, we could automate the calculation of "Long Term Constant Return" by responding to a change in the "Actual Value" cell. Tell me what you think.

 

[JohnTravolski]

Hi John.

Using Solver, I don't know. If you are open to a bit of code in your project, we could automate the calculation of "Long Term Constant Return" by responding to a change in the "Actual Value" cell. Tell me what you think.

Hi, I found a way to do it by calling "Goal Seek" in VBA. It still requires me to click a button in excel to run it, but it works well enough. This was the line I needed in VBA (the cells I reference here don't match my worksheet because that was just a simplified version, but it should be clear enough):
Range("C5").GoalSeek goal:=Range("E5").Value, ChangingCell:=Range("B22")

 

[Legacy 456155]