excel - Scenario analysis macro calling on goal seek sub

Question

I have a scenario analysis macro that's calculating the NPV for 4 different scenarios (each have unique parameters) where the IRR equals a target:

Sub ScenarioTable()

'Declare variables.
Dim i As Integer

'Dim InitialScenario As Integer
Dim ScenarioCount As Range
Dim rngScenarioCopy As Range
Dim rngScenarioPaste As Range

'Populate variables with data from sheet.
Set InitialScenario = Range("Scenario_Switch")

'Set range variables to anchor cells on the worksheet.
Set ScenarioCount = Range("Scenario_Count")
Set rngScenarioCopy = Range("Scenario_Copy")
Set rngScenarioPaste = Range("Scenario_Paste")

'Start the counter at 1
i = 0

'Starting at an anchor point for each scenario number we test whether the cell is empty before continuing.
Do While ScenarioCount.Offset(1, 0).Errors.Item(1).Value = False
Set rngScenarioCopy = Range("Scenario_Copy")

'Paste output into column.
rngScenarioPaste.Offset(0, i).Value = rngScenarioCopy.Value

'Call goalseek macro
Call GoalSeek.KDGoalSeek

'Input scenario number into the scenario manager.
InitialScenario.Value = i + 2
i = i + 1

Loop

'Return scenario back to original number.
InitialScenario.Value = 1

End Sub

The code's output does result in an NPV for each scenario but the IRR in each case isn't equating to the target

My goalseek macro I'm using is:

Sub KDGoalSeek()
' Goal seeks a value using named ranges.
Application.ScreenUpdating = False


' Keyboard Shortcut: Ctrl+g

'Goal:=Range("Target") is the cell reflecting our goal value
'ChangingCell:=Range("GS_Input")the input cell that needs to adjust its value to achieve the goal
'Range("GS_Output"). is the cell that determines the changing cell i.e. the input
Range("GS_Output").GoalSeek Goal:=Range("Target"), ChangingCell:=Range("GS_Input")

Application.ScreenUpdating = True

End Sub

The goal seek macro on it's own is outputting the correct target IRR but when used in the scenario analysis macro it doesn't.

Appreciate the help on why the scenario analysis macro isn't outputting the targeted IRR and relevant NPV

Answer 1

等待大神答复