Excel

Black Scholes Formula

Ease of Use

Intermediate

Version tested with

Excel XP 

Submitted by:

thefrasers

Description:

Use this add-in to generate simple Black-Scholes-Merton (BSM) prices, Deltas, and Gammas for a simple option. Some basic option knowledge is assumed. Under no circumstances should this programme be relied upon in a live market situation. 

Discussion:

This stops people from typing in lots of rather tedious mathematical formulae whenever an option price is calculated. I use this a lot in teaching; there are lots of issues around BSM, so you should only look at this as an example of how options prices and behavior are generated rather than any type of trading tool. I am a VBA Newbie, so the code may well be inefficient (I think default values could be used) - but it does work. *Use at your own risk* 

Code:

instructions for use 

			
 ' Copy from below...
 
 ' Use freely
 
Option Compare Text 
Option Explicit 
Option Base 0 
Global Const Pi = 3.14159265358979 
 
 'Little N
 
Public Function Littlen(X As Double) As Double 
    Littlen = 1 / Sqr(2 * Pi) * Exp(-X ^ 2 / 2) 
     
End Function 
 
Function BlackScholes(SpotPrice As Double, Strike As Double, Rate As Double, _ 
    Time As Double, Dividend As Double, Vol As Double, CallPut As String, Reg0Fut1 As Integer, Output As Integer) As Double 
     
     ' Arguments
     'Output - Price = 0, Delta = 1, Gamma = 2
     
     
    Dim volrootime As Double 
    Dim d1 As Double 
    Dim d2 As Double 
    Dim DiscF As Double 
    Dim DivF As Double 
    Dim Optprice As Double 
    Dim topline1 As Double 
    Dim topline2 As Double 
    Dim topline As Double 
    Dim Price As Double 
    Dim Delta As Double 
    Dim Gamma As Double 
     
    DiscF = Exp(-Rate * Time) 
    DivF = Exp(-Dividend * Time) 
    volrootime = (Time ^ 0.5) * Vol 
     
     ' Reg0Fut1 function distinguishes between regular options (0) and options on futures (1)
     
    If Reg0Fut1 = 0 Then 
         
        topline1 = Log(SpotPrice / Strike) 
        topline2 = ((Rate - Dividend) + ((Vol ^ 2) / 2)) * Time 
        topline = topline1 + topline2 
        d1 = topline / volrootime 
        d2 = d1 - volrootime 
         
         
        If CallPut = "C" Then 
             
            Price = (SpotPrice * DivF * Application.NormSDist(d1)) - _ 
            (Strike * DiscF * Application.NormSDist(d2)) 
            Delta = DivF * Application.NormSDist(d1) 
            Gamma = (Littlen(d1) * DivF) / (SpotPrice * volrootime) 
             
        Else 
             
            Price = Strike * DiscF * WorksheetFunction.NormSDist(-d2) - _ 
            SpotPrice * DivF * WorksheetFunction.NormSDist(-d1) 
             
            Delta = DivF * (Application.NormSDist(d1) - 1) 
            Gamma = (Littlen(d1) * DivF) / (SpotPrice * volrootime) 
             
        End If 
         
    Else 
         
        topline1 = Log(SpotPrice / Strike) 
        topline2 = ((Vol ^ 2) / 2) * Time 
        topline = topline1 + topline2 
        d1 = topline / volrootime 
        d2 = d1 - volrootime 
         
         
        If CallPut = "C" Then 
             
            Price = DiscF * ((SpotPrice * Application.NormSDist(d1)) - _ 
            (Strike * Application.NormSDist(d2))) 
            Delta = DiscF * Application.NormSDist(d1) 
            Gamma = (Littlen(d1) * DiscF) / (SpotPrice * volrootime) 
             
        Else 
             
            Price = DiscF * ((Strike * Application.NormSDist(-d2)) - _ 
            (SpotPrice * Application.NormSDist(-d1))) 
            Delta = DiscF * (Application.NormSDist(d1) - 1) 
            Gamma = (Littlen(d1) * DiscF) / (SpotPrice * volrootime) 
             
        End If 
    End If 
     
    Select Case Output 
         
    Case 0 ' Price
        BlackScholes = Price 
         
    Case 1 'Delta
        BlackScholes = Delta 
         
    Case 2 ' Gamma
        BlackScholes = Gamma 
         
    End Select 
     
End Function 
 
 'stop copying now!

How to use:

  1. Open the Excel VBA page (Alt-F11)
  2. Start a new module (Insert-Module)
  3. Copy and paste the code.
  4. Close VBE
  5. A user-defined function should be created (BlackScholes). If you insert the function it asks for these inputs ...
  6. SpotPrice = asset price
  7. Strike = strike price
  8. Rate = interest rate governing period to expiry (annualised continuously compounded)
  9. Time = time to expiry in years
  10. Dividend = dividend/income rate of asset until expiry (annualised continuously compounded)
  11. Vol = implied volatility
  12. CallPut = C for call, P for put
  13. Type User inputs whether it is an 'ordinary' option (type 0), or an option on a futures contract (type 1 (Black 76 model))
  14. Output : user decides what outputs are required. 0 = price, 1= Delta, 2 = Gamma.
 

Test the code:

  1. Try out the following option...
  2. Asset Price = 100
  3. Strike = 100
  4. Time = 0.25
  5. Vol = 25%
  6. Rate = 5%
  7. Div = 1%
  8. For a regular call option, the price should be 5.458423181, the Delta should be 55.527%, and the gamma should be 0.031514097. If it was a call on futures the values would be 4.921641213, 51.840%, and 0.031457423.
  9. For a regular put option, the values are 4.465890991, -44.224%, and 0.031514097.
  10. For a put on futures the values will be identical to those for the call (put call parity) except for the delta which is -46.918%.
 

Sample File:

BS Examp.zip 10.89KB 

Approved by mdmackillop


This entry has been viewed 317 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express