March 17, 2009

Delta of an European Option using VBA

The delta of an option is the sensitivity of an option price relative to changes in the price of the underlying asset. It tells option traders how fast the price of the option will change as the underlying stock/future moves. In matematical terms it will be given as - . In the Black Scholes model this is given by - Here is a short VBA snippet to calculate the delta of a european option.
Function BSOptionDelta(S, X, r, q, mat, sigma, CallPut As String)
    Dim eqt, NDOne
    NDOne = Application.NormSDist(BSDOne(S, X, r, q, mat, sigma))
    eqt = Exp(-q * mat)
    Select Case CallPut
        Case "Call": BSOptionDelta = NDOne
        Case "Put": BSOptionDelta = NDOne - 1
        Case Else: MsgBox ("You can only specify 'Call' or 'Put'")
    End Select
    BSOptionDelta = eqt * BSOptionDelta
End Function

Private Function BSDOne(S, X, r, q, mat, sigma)
'   Returns Black-Scholes d1 value
    BSDOne = (Log(S / X) + (r - q + 0.5 * sigma ^ 2) * mat) / (sigma * Sqr(mat))
End Function

No comments:

Post a Comment