My first Loop and it is not working!

[K-MAN69]

Hi People I am trying to do this loop and it is not giving me an error message but it is not giving me the correct result either.

Basically I want the Variable Vol_Guess to be reinvested back into my formulas after NRF is minused.

I have tested the result on a spread sheet by manually performing the iteration by manually copy and pasting the result back into the begining Variable and it works well so my problem is in the way I am coding the Loop part.

Any help would be great

please use
?AA_ImpliedVol_FutOpt(#12/06/2012#,#19/03/2012#,100,98,5,0.01,"C")
in the immediate window - the result should be around 0.2037

Option Explicit

Function OptionPriceBlack76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, RFR As Double, Vol As Double, OptType As String) As Double
Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve

TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * Application.Pi) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)

'Price
If OptType = "C" Then
OptionPriceBlack76 = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
ElseIf OptType = "P" Then
OptionPriceBlack76 = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
End If
End Function

Function Vega_Black76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, Vol As Double, RFR As Double, OptType As String) As Double
Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve

TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.14159265358979) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
Vega_Black76 = Spot * ert * N_Dash_d1 * TimeYears ^ 0.5
End Function

Function AA_ImpliedVol_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, OptPrice As Double, RFR As Double, OptType As String)
Dim Vol_Guess As Double 'This is our first guess for Vol
Dim Accuracy As Double 'How accurate you want the calc to be
Dim i As Integer 'This is the iteration counter
Dim MaxIter As Integer 'This is the maximum allowable iterations the calc will do
Dim Value_1 As Double
Dim Vega_1 As Double
Dim NRF As Double '(OptPrice [Using Guess] - Observed OptPrice)/Vega [Using Guess]
Vol_Guess = 0.1
Accuracy = 0.00000001
MaxIter = 1000
i = 1
Do
i = i + 1
Value_1 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, Vol_Guess, OptType)
Vega_1 = Vega_Black76(Expiry, DealDate, Spot, Strike, Vol_Guess, RFR, OptType)
NRF = (Value_1 - OptPrice) / Vega_1
Vol_Guess = Vol_Guess - NRF

Loop Until Abs(NRF) <= Accuracy Or i = MaxIter
AA_ImpliedVol_FutOpt = Vol_Guess

End Function

 

[AlphaFrog]

With your example arguments, it seems to iterate only three times.

Perhaps if you Debug.Print each iteration of the function results and compare them to your manual calculation iterations, that may help isolate where the problem is.

    [color=darkblue]Do[/color]
        i = i + 1
        Value_1 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, Vol_Guess, OptType)
        Vega_1 = Vega_Black76(Expiry, DealDate, Spot, Strike, Vol_Guess, RFR, OptType)
        NRF = (Value_1 - OptPrice) / Vega_1
        Vol_Guess = Vol_Guess - NRF
[COLOR="Blue"]Debug.Print "Iteration: " & i
Debug.Print "Value_1: " & Value_1
Debug.Print "Vega_1: " & Vega_1
Debug.Print "VolGuess: " & Vol_Guess[/COLOR]
    [color=darkblue]Loop[/color] [color=darkblue]Until[/color] Abs(NRF) <= Accuracy [color=darkblue]Or[/color] i = MaxIter

Result:
Iteration: 2
Value_1: 4.43597772533114
Vega_1: 32.5573246564391
VolGuess: 0.117323974884936
Iteration: 3
Value_1: 5.00223516825644
Vega_1: 32.7957517376061
VolGuess: 0.117255820683919
Iteration: 4
Value_1: 5.00000003347899
Vega_1: 32.7950257097728
VolGuess: 0.117255819663063

 

[K-MAN69]

Thanks for the comment but the first thing I pick up is the fact that the looping has finished before the satisfaction of:

Loop Until Abs(NRF) <= Accuracy Or i = MaxIter

How has it ended before this occured?

 

[K-MAN69]

Thanks for the comment but the first thing I pick up is the fact that the looping has finished before the satisfaction of:

Loop Until Abs(NRF) <= Accuracy Or i = MaxIter

How has it ended before this occured?

sorry I forgot to post my results to prove NRF was not less than 0.0000001

?AA_ImpliedVol_FutOpt(#12/06/2012#,#19/03/2012#,100,98,5,0.01,"C")
Value_1: 4.43598212841841
Vega_1: 32.5573246564391
NRF: -1.73238396438707E-02
Vol: 0.117323839643871
Iteration: 2
Value_1: 5.00223770210036
Vega_1: 32.7957502982674
NRF: 6.82314653578522E-05
Vol: 0.117255608178513
Iteration: 3
Value_1: 5.00000002478942
Vega_1: 32.7950234438826
NRF: 7.5588977703348E-10
Vol: 0.117255607422623
Iteration: 4
0.117255607422623

 

[AlphaFrog]

The NRF value in the last iteration 7.5588977703348E-10 (or 0.00000000075588977703348) is less than 0.0000001

 

[K-MAN69]

I am an idiot - thanks for tolerating me!

 

[K-MAN69]

Its working!!!!

Herer is my code complete and working!

[COLOR=magenta]Option Explicit[/COLOR]

[COLOR=magenta]Function OptionPriceBlack76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, RFR As Double, Vol As Double, OptType As String)[/COLOR]
[COLOR=magenta]Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
 
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.14159265358979) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
 
'Price
If OptType = "C" Then
            OptionPriceBlack76 = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
ElseIf OptType = "P" Then
            OptionPriceBlack76 = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
End If[/COLOR]
[COLOR=magenta]End Function[/COLOR]

[COLOR=magenta]Function Vega_Black76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, Vol As Double, RFR As Double, OptType As String) As Double[/COLOR]
[COLOR=magenta]Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
 
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.14159265358979) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)[/COLOR]
[COLOR=magenta]Vega_Black76 = Spot * ert * N_Dash_d1 * TimeYears ^ 0.5[/COLOR]
[COLOR=magenta]End Function[/COLOR]

[COLOR=magenta]Function AA_ImpliedVol_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, OptPrice As Double, RFR As Double, OptType As String)[/COLOR]
[COLOR=magenta]Dim Vol As Double 'This is our first guess for Vol
Dim Accuracy As Double 'How accurate you want the calc to be
Dim i As Integer 'This is the iteration counter
Dim MaxIter As Integer 'This is the maximum allowable iterations the calc will do
Dim Value_1 As Double
Dim Vega_1 As Double
Dim NRF As Double '(OptPrice [Using Guess] - Observed OptPrice)/Vega [Using Guess][/COLOR]
[COLOR=magenta]Vol = 0.1
Accuracy = 0.00000000001
MaxIter = 100
i = 1[/COLOR]
[COLOR=magenta]Do
   Value_1 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, Vol, OptType)
   Vega_1 = Vega_Black76(Expiry, DealDate, Spot, Strike, Vol, RFR, OptType)
   NRF = (Value_1 - OptPrice) / Vega_1
   Vol = Vol - NRF
   i = i + 1
   
Debug.Print "Value_1: " & Value_1
Debug.Print "Vega_1: " & Vega_1
Debug.Print "NRF: " & NRF
Debug.Print "Vol: " & Vol
Debug.Print "Iteration: " & i
    
Loop Until Abs(NRF) <= Accuracy Or i = MaxIter[/COLOR]
[COLOR=magenta]AA_ImpliedVol_FutOpt = Vol[/COLOR]
[COLOR=magenta] 
End Function[/COLOR]