# 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. .

. .
```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```