Sub experiment()
debug2 = False
time_saving_mode = False
If time_saving_mode Then
Application.ScreenUpdating = False
Else
Application.ScreenUpdating = True
End If
If time_saving_mode = False Then
Application.StatusBar = "......................................................................."
test6 = DoEvents
End If
Range("start_time") = Time
Range("prob_scenario") = 1
Count = 1
Sheets("Summary").Calculate ' Make sure have recent parameters
low_price = Range("low_start") ' Can change these
high_price = Range("high_start") ' Can change
increment = (high_price - low_price) / 5 ' Divisor can change speed
If time_saving_mode = False Then
Application.StatusBar = "Part 1: Find Slope and Intercept ...................................................."
test6 = DoEvents
End If
Count = 1
iteration = 0 ' Counts iteration for output
For ppa_price = low_price To high_price Step increment
iteration = iteration + 1
find_npv ' Call routine to find NPV; need to calculate
npv_output(iteration) = npv_result ' Store Iteration in an array
ppa_output(iteration) = ppa_price
Count = Count + 1
If npv_result > 0 Then Exit For ' When hit positive NPV stop (start with low price)
Next ppa_price
' Now go back and get numbers between zero
If iteration > 1 Then ' After exit, make low and high for slope
low_ppa = ppa_output(iteration - 1)
low_npv = npv_output(iteration - 1)
End If
high_ppa = ppa_output(iteration)
high_npv = npv_output(iteration)
' call find npv
' Y is the NPV
' X is the PPA Price
' Equation: NPV = A + B x Price
' B = Slope = (y1 - y2)/(x1 - x2)
If debug2 Then MsgBox " High PPA " & high_ppa & " High NPV " & Format(high_npv, "###,###.00") & " Low PPA " & low_ppa & " Low NPV " & Format(low_npv, "###,###.00")
b = (high_npv - low_npv) / (high_ppa - low_ppa)
' high_npv = a + b x high_ppa
' a = high_npv - b x high_ppa
a = high_npv - b * high_ppa
If debug2 Then
Range("a") = a
Range("b") = b
End If
' MsgBox " Slope " & a & " Intercept " & b
' now find zero npv point
' NPV = a + b * ppa_price
' 0 = a + b * ppa_price
' -a = b * ppa_price
' - a/b = ppa_price
If b <> 0 Then ppa_price_base = -a / b ' Theoretical zero NPV from Line
ppa_price = ppa_price_base
find_npv ' Find zero NPV from line
If debug2 Then MsgBox " Trying from Straight Line " & Chr(13) & _
" PPA Price from Line " & Format(ppa_price, "00.00") & " NPV Computed " & Format(npv_result, "0.00") & " Increment " & increment
' Now re-do a and b
iteration = iteration / 10 ' Set divisor for slope calculation -- the high and low bound
re_start_iteration: ' Try new line with more precise slope and intercept
ppa_price = ppa_price_base + iteration / 2 ' Last ppa price plus iteartion
find_npv
high_ppa = ppa_price
high_npv = npv_result
ppa_price = ppa_price_base - iteration / 2 ' start of iteration
find_npv
low_ppa = ppa_price
low_npv = npv_result
' MsgBox " High PPA " & high_ppa & " Low PPA " & low_ppa
' Now re-compute the slope and intercept
If (high_ppa - low_ppa) <> 0 Then b = (high_npv - low_npv) / (high_ppa - low_ppa)
a = high_npv - b * high_ppa
If debug2 Then
Range("a") = a
Range("b") = b
End If
If b <> 0 Then ppa_price_base = -a / b
ppa_price = ppa_price_base
find_npv ' Get the zero NPV value from the line
If debug2 Then MsgBox " Trying from Straight Line " & Chr(13) & _
" PPA Price from Line " & Format(ppa_price, "00.00") & " NPV Computed " & Format(npv_result, "0.00") & " Increment " & increment
iteration = iteration / 8
Count = Count + 1
If Abs(npv_result) < 100 Then ' Put in criteria
If debug2 Then MsgBox " Final NPV " & Chr(13) & _
" NPV " & Format(npv_result, "###,###.00") & " PPA Price " & Format(ppa_price, "00.00")
GoTo finish_sub
End If
If Count > 180 Then Exit Sub
If time_saving_mode = False Then
Application.StatusBar = "Part 2 ................................ Iteration " & Count & " NPV " & Format(npv_result, "###,##.00")
test6 = DoEvents
End If
GoTo re_start_iteration:
finish_sub:
Range("ppa_price").Value = ppa_price
Range("ppa_price").Calculate
Range("end_time") = Time
Range("iterations") = Count
Sheets("Summary").Calculate
Application.StatusBar = False
test6 = DoEvents
End Sub Circular Reference On-Line Course – Advanced UDF’s
In this page I have included some advanced issues that can be solved with the UDF functions. In the case below you can use ideas of a slope and intercept to develop a dynamic goal seek.
.
.
.