VBA kode for Black Scholes opsjonsprisingmodell

****************************************************************************
'* Cumulative Standard Normal Distribution *
'* (This function provides similar result as NORMSDIST( ) on Excel) *
'****************************************************************************

Function SNorm(z)
c1 = 2.506628
c2 = 0.3193815
c3 = -0.3565638
c4 = 1.7814779
c5 = -1.821256
c6 = 1.3302744
If z > 0 Or z = 0 Then
w = 1
Else: w = -1
End If
y = 1 / (1 + 0.2316419 * w * z)
SNorm = 0.5 + w * (0.5 - (Exp(-z * z / 2) / c1) * _
(y * (c2 + y * (c3 + y * (c4 + y * (c5 + y * c6))))))

End Function

'**********************************************************************
'* Black-Scholes European Call Price Computation *
'**********************************************************************

Function Call_Eur(s, x, t, r, sd)
Dim a As Single
Dim b As Single
Dim c As Single
Dim d1 As Single
Dim d2 As Single

a = Log(s / x)
b = (r + 0.5 * sd ^ 2) * t
c = sd * (t ^ 0.5)
d1 = (a + b) / c
d2 = d1 - sd * (t ^ 0.5)
Call_Eur = s * SNorm(d1) - x * Exp(-r * t) * SNorm(d2)

End Function

'*********************************************************************
'* Black-Scholes European Put Price Computation *
'*********************************************************************

Function Put_Eur(s, x, t, r, sd)
Dim a As Single
Dim b As Single
Dim c As Single
Dim d1 As Single
Dim d2 As Single

a = Log(s / x)
b = (r + 0.5 * sd ^ 2) * t
c = sd * (t ^ 0.5)
d1 = (a + b) / c
d2 = d1 - sd * (t ^ 0.5)
CallEur = s * SNorm(d1) - x * Exp(-r * t) * SNorm(d2)
Put_Eur = x * Exp(-r * t) - s + CallEur

End Function

Kilde: http://www.anthony-vba.kefra.com/vba/vba6.htm