Modelling Calendar Spreads

Discussion in 'Options' started by Floyd Bates, May 12, 2006.

  1. All:

    Can someone direct me to a simple Excel spreadsheet that models the profit and loss profiles for calendar spreads?

    If this is not possible, maybe someone can direct me to pseudocode, algorithms, or any other information that may help me in producing such a spreadsheet?

    I have spent the past few weeks searching for something like this on the internet without any success.

    Thanks in advance.

  2. Choad


  3. Choad,


    I took a quick glance through the code sample that you recommended.

    Now, that I have a working model. Would anyone be interested in testing it against one of their own.

    Thanks for sharing.

  4. cnms2


  5. For those interested, here are the Excel VBA functions for the calendar spread calculations:

    Function BlackScholes(Price As Double, Strike As Double, Time As Double, Deviation As Double, RiskFreeRate As Double, PCVar As String)
    Dim d As Double, C As String, P As String
    d = (LN(Price / Strike) + Time * (RiskFreeRate + ((Deviation ^ 2) / 2))) / (Deviation * (Time ^ 0.5))
    If PCVar = "C" Then
    BlackScholes = Price * WorksheetFunction.NormSDist(d) - Strike * Exp(-RiskFreeRate * Time) * WorksheetFunction.NormSDist(d - Deviation * (Time ^ 0.5))
    BlackScholes = -Price * WorksheetFunction.NormSDist(-d) + Strike * Exp(-RiskFreeRate * Time) * WorksheetFunction.NormSDist(Deviation * (Time ^ 0.5) - d)
    End If
    End Function

    Static Function LN(X)
    LN = Log(X) / Log(2.718282)
    End Function
  6. cnms2


    These are the Black-Scholes calculations I'm using in my Visual Basic routines:
    d1 = (WorksheetFunction.Ln(Price / Strike) _
       + (RiskFree + Volatility ^ 2 / 2) * (days / 365)) _
        / Volatility / Math.Sqr(days / 365)
    N_d1 = WorksheetFunction.NormSDist(d1)
    d2 = d1 - Volatility * Math.Sqr(days / 365)
    N_d2 = WorksheetFunction.NormSDist(d2)
    B_S = Price * N_d1 _
       -  Strike * Math.Exp(-RiskFree * days / 365) * N_d2
  7. cnms2:

    I appreciate it.

    I intend to take your code/results and compare it to what I posted.

    Thanks again.
  8. cnms2:

    Not that I ever doubted you, but I thought it was worth pointing out that our results are identical.

    Ref: Options as a Strategic Investment, Lawrence G. McMillan; pages 947-949

    For those that are following this thread, here is the simple calculation for delta.

    Delta = Normsdist(d1)

  9. I have a homegrown Excel spreadsheet that uses the B-S formula. When I use the Excel Normsdist function to calculate Delta it doesn't seem to work, I get either a one or zero.

    For example, the Normsdist of a near the money D1 with a value=19.63 returns 1.

    Any idea what is wrong?


  10. Sorry, I found the problem. The contract I happened to choose was at expiration. Hence no time value and Delta would be one since it was slightly ITM.

    The formula seems to work well now.


    #10     May 13, 2006