Black Scholes Merton model in excel?

[skajdfjksdfd]

Does anyone have any experience with this, or know how to turn the formula into a excel function?

 

[doug reich]

There's a thousand of them out there on the web. Since the formula is incredibly unwieldy to put into an excel formula, usually it's done as a macro. (I can tell you from experience with this that it is torture trying to debug a 6-line excel formula looking for the missing negative sign.)

Take your pick:
black scholes excel - Google Search

 

[Vadim]

Here is a VBA code for BS and Greeks.

[high]
Function optionBS(Put_Call As String, S As Double, e As Double, Tmt As Double, r As Double, q As Double, sigma As Double, Command As String) As Double
' Typ -> Call or Put ' Command -> Price, Delta, Gamma, Theta, Vega, Rho
Tmt = Application.Max(0.00001, Tmt)
Dim Sign As Integer
Dim d1 As Double, d2 As Double, ed1 As Double, ed2 As Double

Select Case UCase$(Left$(Put_Call, 1))
Case "C": Sign = 1
Case "P": Sign = -1
Case Else: MsgBox "Not valid."
optionBS = 0
Exit Function
End Select

If (sigma * S * Tmt > 0) Then
d1 = (Log(S / e) + (r - q + 0.5 * sigma * sigma) * Tmt) / (sigma * Sqr(Tmt))
ed1 = Gauss(Sign * d1)
d2 = d1 - sigma * Sqr(Tmt)
ed2 = Gauss(Sign * d2)
Else
If (S < e) Then
ed1 = 0.5 * (1 - Sign)
Else:
ed1 = 0.5 * (1 + Sign)
End If
ed2 = ed1
End If

Select Case UCase$(Left$(Command, 1))
' Price
Case "P":
optionBS = Sign * (S * Exp(-q * Tmt) * ed1 - e * Exp(-r * Tmt) * ed2)

' Delta
Case "D":
optionBS = Sign * Exp(-q * Tmt) * ed1

' Gamma
Case "G":
If S * sigma * Tmt > 0 Then
optionBS = Exp(-q * Tmt) * phi(d1) / (sigma * S * Sqr(Tmt))
Else:
optionBS = 0
End If

' Theta 1/Day
Case "T":
optionBS = (-0.5 * S * sigma * Exp(-q * Tmt) * phi(d1) / Sqr(Tmt) _
+ Sign * (q * S * Exp(-q * Tmt) * ed1 - r * e * Exp(-r * Tmt) * ed2)) / 365.25

' Vega [1/%]
Case "V":
optionBS = 0.01 * Exp(-q * Tmt) * phi(d1) * S * Sqr(Tmt)

' Divi-Yield-Sensitivit?t [1/%]
Case "Q":
optionBS = -0.01 * Sign * S * Tmt * Exp(-q * Tmt) * ed1

' Rho [1/%]
Case "R":
optionBS = 0.01 * Sign * e * Tmt * Exp(-r * Tmt) * ed2

Case Else:
MsgBox "Illegal output query '" & Command & "'"
optionBS = 0
End Select
End Function
[/high]

 

[skajdfjksdfd]

So, i'm fairly sure that for what i'm doing I can't use Black-sholes... one of the prices i am working with is electricity, and since this is (for the most part) non-storable it creates a non-hedgeable risk. Does anyone have any suggestions for an alternative?

 

[Sanket Patel]

Perhaps you could try a cross-hedge with something like propane...?

 

[grfiv]

For a fairly-complete set of Excel VBA functions for Black Scholes Merton plus the Greeks, etc., try this link

http://www.philadelphia-reflections.com/blog/2394.htm

For the same thing in Python, try this one:

http://www.philadelphia-reflections.com/blog/2396.htm