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

Black Scholes Assumptions

The Black-Scholes model of the market for an equity makes the following explicit assumptions:

• It is possible to borrow and lend cash at a known constant risk-free interest rate.
• The stock price follows a geometric Brownian motion with constant drift and constant volatility and they are lognormally distributed.
• There are no transaction costs.
• The stock does not pay a dividend (this was modified by Merton later on).
• All securities are perfectly divisible (i.e. it is possible to buy any fraction of a share).
• There are no restrictions on short selling.
• Markets are efficient.

How to Post VBA Code on Blogger

1. Prerequisite:
Textpad
2. Setup Instructions
Download the syntax file for VB code from the following location. Follow the installation instructions and create a new documnet class for *.vba files.
3. Ready to put code on your blog
1. In order to post code save it as a .vba text file.
2. Open the file using Textpad.
3. Check that you are happpy with the syntax highlighting.
4. Select the code you are intreested in copying.
5. From the context menu select "Copy Other" -> "As a HTML page".
6. Paste the contents in another text file.
7. You need to copy the CSS elements to your template - This needs to be done only once.
8. Copy the area from <pre> and paste it in blog "Edit Html".

Another way of doing syntax highlighting is explained in Fahd' blog