Modelling Calendar Spreads

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.

Cheers.

 

http://www.hoadley.net/options/strategymodel.htm

http://www.voptions.com/

 

Choad,

Thanks.

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.

Cheers.

 

A four leg options position simulator:

http://www.888optionsnet.com/investigator_2/wi_positionSimulator.asp

 

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))
Else
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

 

These are the Black-Scholes calculations I'm using in my Visual Basic routines:

Code:

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

 

cnms2:

I appreciate it.

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

Thanks again.

 

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)

Cheers.

 

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?

Thanks

Don

 

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.

Thanks

Don