|
|
It get #Name when I insert the function-> my problem is with
ImpliedVol2--> I defined the option pricing in the other functions.
Option Explicit
Function ImpliedVol2(optType, S0, K, rcinterest, q, T, trueprice)
Dim optionpricelow As Double
Dim optionpricehigh As Double
Dim lowervol As Double
Dim uppervol As Double
Dim sigma As Double
Dim EJ As Double
sigma = 5
optionpricelow = BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)
sigma = 200
optionpricehigh = BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)
If optionpricehigh - trueprice > trueprice - optionpricelow Then
uppervol = (200 + 5) / 2
lowervol = 5
Else
uppervol = 200
lowervol = (200 + 5) / 2
End If
Do While EJ > 0.0001
sigma = lowervol
optionpricelow = BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)
sigma = uppervol
optionpricehigh = BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)
If optionpricehigh - trueprice > trueprice - optionpricelow Then
uppervol = (uppervol + lowervol) / 2
lowervol = lowervol
Else
uppervol = uppervol
lowervol = (uppervol + lowervol) / 2
End If
EJ = optionpricehigh - optionpricelow
Loop
ImpliedVol2 = sigma
End Function
Function BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)
' Calculates Black-Scholes-Merton option price
' optType = 1 for call, -1 for put
' This function uses Functions BSD1 and BSD2
Dim exprT, expqT, ND1, ND2
If S0 > 0 And K > 0 And T > 0 And sigma > 0 Then
exprT = Exp(-rcinterest * T)
expqT = Exp(-q * T)
ND1 = Application.NormSDist(optType * _
BSD1(S0, K, rcinterest, q, T, sigma))
ND2 = Application.NormSDist(optType * _
BSD2(S0, K, rcinterest, q, T, sigma))
BSMOptPrice = optType * (S0 * expqT * ND1 - _
K * exprT * ND2)
ElseIf S0 > 0 And K > 0 And sigma > 0 And T = 0 Then
BSMOptPrice = Application.Max(0, optType * (S0 - K))
Else
'MsgBox "One of the inputs provided is invalid"
BSMOptPrice = 0
End If
End Function
Private Function BSD1(S0, K, rcinterest, q, T, sigma)
' Calculates D1 for Balck-Scholes-Merton option pricing
BSD1 = (Log(S0 / K) + (rcinterest - q + 0.5 * sigma ^ 2) * T) / _
(sigma * Sqr(T))
End Function
Private Function BSD2(S0, K, rcinterest, q, T, sigma)
' Calculates D2 for Balck-Scholes-Merton option pricing
BSD2 = BSD1(S0, K, rcinterest, q, T, sigma) - (sigma * Sqr(T))
End Function
Please help.
|
|
|