7月 2018 ~ 自營家 Peter

2018/07/31

2018/07/30

2018/07/27

2018/07/26

20170726

2018/07/26 下午2:07 是人非神 - 日誌留言搶頭香 !!!

Black-Scholes and Greeks in VBA

上午9:12 閱讀思考留言搶頭香 !!!

In the following code

OptionType is either “C” for a call or “P” for a put
S is the stock price
X is the strike price
T is the time to expiry
r is the risk free rate
v is the volatility
d is the dividend yield

Function dOne(S, X, T, r, v, d)
 
dOne = (Log(S / X) + (r - d + 0.5 * v ^ 2) * T) / (v * (Sqr(T)))
 
End Function
 
Function NdOne(S, X, T, r, v, d)
 
NdOne = Exp(-(dOne(S, X, T, r, v, d) ^ 2) / 2) / (Sqr(2 * Application.WorksheetFunction.Pi()))
 
End Function
 
Function dTwo(S, X, T, r, v, d)
 
dTwo = dOne(S, X, T, r, v, d) - v * Sqr(T)
 
End Function
 
Function NdTwo(S, X, T, r, v, d)
 
NdTwo = Application.NormSDist(dTwo(S, X, T, r, v, d))
 
End Function
 
Function OptionPrice(OptionType, S, X, T, r, v, d)
 
If OptionType = "C" Then
    OptionPrice = Exp(-d * T) * S * Application.NormSDist(dOne(S, X, T, r, v, d)) - X * Exp(-r * T) * Application.NormSDist(dOne(S, X, T, r, v, d) - v * Sqr(T))
ElseIf OptionType = "P" Then
    OptionPrice = X * Exp(-r * T) * Application.NormSDist(-dTwo(S, X, T, r, v, d)) - Exp(-d * T) * S * Application.NormSDist(-dOne(S, X, T, r, v, d))
End If
 
End Function
 
Function OptionDelta(OptionType, S, X, T, r, v, d)
 
If OptionType = "C" Then
    OptionDelta = Application.NormSDist(dOne(S, X, T, r, v, d))
ElseIf OptionType = "P" Then
    OptionDelta = Application.NormSDist(dOne(S, X, T, r, v, d)) - 1
End If
End Function
 
Function OptionTheta(OptionType, S, X, T, r, v, d)
 
If OptionType = "C" Then
    OptionTheta = -((S * v * NdOne(S, X, T, r, v, d)) / (2 * Sqr(T)) - r * X * Exp(-r * (T)) * NdTwo(S, X, T, r, v, d)) / 365
ElseIf OptionType = "P" Then
    OptionTheta = -((S * v * NdOne(S, X, T, r, v, d)) / (2 * Sqr(T)) + r * X * Exp(-r * (T)) * (1 - NdTwo(S, X, T, r, v, d))) / 365
End If
 
End Function
 
Function Gamma(S, X, T, r, v, d)
 
Gamma = NdOne(S, X, T, r, v, d) / (S * (v * Sqr(T)))
 
End Function
 
Function Vega(S, X, T, r, v, d)
 
Vega = 0.01 * S * Sqr(T) * NdOne(S, X, T, r, v, d)
 
End Function
 
Function OptionRho(OptionType, S, X, T, r, v, d)
 
If OptionType = "C" Then
    OptionRho = 0.01 * X * T * Exp(-r * T) * Application.NormSDist(dTwo(S, X, T, r, v, d))
ElseIf OptionType = "P" Then
    OptionRho = -0.01 * X * T * Exp(-r * T) * (1 - Application.NormSDist(dTwo(S, X, T, r, v, d)))
End If
 
End Function