Consulting

Results 1 to 6 of 6

Thread: #Value error msg in VBA code

  1. #1
    VBAX Newbie
    Joined
    Jul 2014
    Posts
    1
    Location

    Question #Value error msg in VBA code

    Hi all,

    I'm a bit new to VBA and i've written a code for creating a function which calculates call and put prices using black-scholes model.

    The problem is that after I input the variables (share price - S, exercise price - K, interest rate - r, dividend yield - q, option life - T, volatility - sigma, call- 1 / put - 0, I get #Value error instead of the call and put prices which is what this code is supposed to generate.

    Could anyone tell me where I have made a mistake, please?


    Thanks!

    ---------
    Function BSMValue(S, K, r, q, T, sigma, i As Boolean)
    
    Dim ert, eqt
    Dim DOne, DTwo, NDone, NDtwo
    
    ert = Exp(-q * T)
    eqt = Exp(-r * T)
    
    Select Case i
    
    Case 1 ' call option
    DOne = (Log(S / K) + (r - q + 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
    DTwo = (Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
    
    NDone = Application.NormDist(DOne)
    NDtwo = Application.NormDist(DTwo)
    
    BSMValue = S * ert * NDone - K * eqt * NDtwo
    
    Case 0 'put option
    DOne = -(Log(S / K) + (r - q + 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
    DTwo = -(Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
    
    NDone = Application.NormDist(DOne)
    NDtwo = Application.NormDist(DTwo)
    
    BSMValue = -S * ert * NDone + K * eqt * NDtwo
    End Select
    
    End Function
    

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,598
    Location
    BS is not my field, but some suggestions for code

    1. Explictly Dim each variable -- otherwise it's a Variant

    2. I guessing that either you wanted NormSDist which takes one parm, or there were 3 missing if you use NormDist. Help has a good writeup on both

    3. I tend to be on the wordy side (I tell myself it's self documenting code) so I always find meaningful variable names easier to figure out 5 or 6 months later

    'Function BSMValue(SharePrice As Double, ExercisePrice As Double, InterestRate As Double, _
    ' DividentYield As Double, OptionLife As Double, Volatility As Double, GeneratePut As Boolean) As Double





    Option Explicit
    Function BSMValue(S As Double, K As Double, r As Double, q As Double, T As Double, sigma As Double, i As Boolean) As Double
     
    Dim ert  As Double, eqt  As Double
    Dim DOne  As Double, DTwo  As Double, NDone  As Double, NDtwo  As Double
     
    ert = Exp(-q * T)
    eqt = Exp(-r * T)
     
    If i Then
        ' call option
        DOne = (Log(S / K) + (r - q + 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
        DTwo = (Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
         
        NDone = Application.NormSDist(DOne)
        NDtwo = Application.NormSDist(DTwo)
         
        BSMValue = S * ert * NDone - K * eqt * NDtwo
     
    Else
        'put option
        DOne = -(Log(S / K) + (r - q + 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
        DTwo = -(Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
         
        NDone = Application.NormSDist(DOne)
        NDtwo = Application.NormSDist(DTwo)
         
        BSMValue = -S * ert * NDone + K * eqt * NDtwo
    End If
     
    End Function
    Hope this helps
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,494
    You don't need any variable at all, let alone any declaration:

    Function BSMValue(S, K, r, q, T, sigma, i As Boolean)
        BSMValue = -S * Exp(-q * T) * Application.NormDist(i * (Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))) + K * Exp(-r * T) * Application.NormDist(i * (Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T)))
    End Function
    NB a boolean is true or false; it doesn't convert automatically to -1 or 0

  4. #4

  5. #5

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,892
    Location
    Please read this post on cross-posting: http://www.excelguru.ca/content.php?...-cross-posters

    We appreciate your compliance with forum netiquette and not cross-posting anymore without providing the appropriate links.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •