General Topics
Technical Topics
Brokerage Firms
Community Lounge
Site Support

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

1. ### Floyd Bates

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.

Cheers.

3. ### Floyd Bates

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.

5. ### Floyd Bates

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

6. ### cnms2

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```

7. ### Floyd Bates

cnms2:

I appreciate it.

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

Thanks again.

8. ### 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.

9. ### 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

10. ### Don87109

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

#10     May 13, 2006
ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: