Modelling Calendar Spreads

Floyd Bates · May 12, 2006

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.

Choad · May 12, 2006

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

http://www.voptions.com/

Floyd Bates · May 12, 2006

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.

cnms2 · May 12, 2006

A four leg options position simulator:

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

Floyd Bates · May 13, 2006

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

cnms2 · May 13, 2006

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

Floyd Bates · May 13, 2006

cnms2:

I appreciate it.

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

Thanks again.

Floyd Bates · May 13, 2006

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.

Don87109 · May 13, 2006

Quote from Floyd Bates:

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

Don87109 · May 13, 2006

Quote from Don87109:

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