Limit Goalseek VBA

Excellent_Mick

New Member
Joined
Apr 30, 2018
Messages
4
Hi there,

I have a very simple goalseek which changes an equity stake in a business to try to reach a certain IRR result.

Variable cell: Equity Stake (% input)
Goal seek target: Target IRR (% input)
Calculation: Active IRR (formula driven on line of cashflows)

In certain scenarios it is not possible for the target IRR to be reached even with 100% equity. In this case I want the variable cell to go to 100% equity and for a message to appear stating ("IRR target not achieved"). At the moment, the goalseek produces some crazy answers if it can't find a solution and I have to manually set the cell to 100% but I want to change this.

The coding I'm using is:
Range("rng_goal").Goalseek Goal:=Range("rng_target").Value, ChangingCell:=Range("rng_changecell")

Any help is much appreciated!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,056
Hi

I would use Solver instead, are you familiar with it? There you will be able to specify constraints to the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,414
Messages
5,571,966
Members
412,429
Latest member
brahmaiah
Top