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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
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,113,903
Messages
5,544,974
Members
410,647
Latest member
LegenDSlayeR
Top