' calcul de B&S
' S = cours du sous jacent; K = prix d'exercice (strike); r = taux d'intérêts annuel sans risque (cst)
' T distance à l'échance (en années); sigma = volatilité du sous jacent; Nd = loi normale centrée réduite
Function BS(TypeOption, S, K, r, sigma, T)
Dim d1, d2 As Double
Dim Z As Integer
d1 = (Log(S / K) + (r + 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
d2 = d1 - sigma * Sqr(T)
Z = Switch(TypeOption)
BS = Z * (S * Nd(Z * d1) - K * Exp(-r * T) * Nd(Z * d2))
End Function
' fonction de la loi normale
Function Nd(d)
Dim d As Double
Nd = WorksheetFunction.Norm_S_Dist(d)
End Function
' fonction de determination Put ou Call
Function Switch(TypeOption)
Dim TypeOption As Integer
TypeOption = UCase(TypeOption)
If TypeOption = "C" Or TypeOption = "CALL" Then
Switch = 1
ElseIf TypeOption = "P" Or TypeOption = "PUT" Then
Switch = -1
End If
End Function
Sub test()
Dim S, K, r, T, sigma As Double
Dim TypeOption As Integer
S = Cells(4, 2)
K = Cells(5, 2)
r = Cells(6, 2)
T = Cells(7, 2)
sigma = Cells(8, 2)
TypeOption = Cells(9, 2)
Cells(3, 5) = BS(TypeOption, S, K, r, sigma, T)
End Sub