1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
| Sub OBPI()
Dim Strike_Plancher As Double, dT As Double, Nb_Simuls As Double, pPlanch As Double
Dim Rf As Double, So As Double, Mu As Double, Sigma As Double, Spot As Double
Dim Taux_Renta As Double, Dist_Echeance As Double, Valo_Position As Double
Dim Position_Obligs As Double, Position_Actions As Double, Alpha As Double
Dim Nb_Points As Integer, Plancher As Integer, i As Integer, j As Integer
Dim perf() As Double
'Paramétrage du sous-jacent
So = 100: Mu = -0.08: Sigma = 0.35
'Monétaire
Rf = 0.03: Plancher = 80
pPlanch = Plancher / 100
Strike_Plancher = Deter_Strike(So, Plancher, Rf, Sigma, 1, pPlanch)
Nb_Points = 252: Nb_Simuls = 500
dT = 1 / Nb_Points
ReDim perf(1 To Nb_Simuls, 1 To 1)
For i = 1 To Nb_Simuls
Spot = So
Dist_Echeance = 1
Alpha = Prop_Actions(Spot, Strike_Plancher, Rf, Sigma, Dist_Echeance)
Position_Actions = Alpha * So
Position_Obligs = (1 - Alpha) * So
For j = 1 To Nb_Points
Taux_Renta = Taux_Renta_Action(Mu, Sigma, dT)
Spot = Spot * Exp(Taux_Renta)
Valo_Position = Position_Actions * Exp(Taux_Renta) + Position_Obligs * Exp(Rf * dT)
Dist_Echeance = WorksheetFunction.Max(Dist_Echeance - dT, 0.00000001)
Alpha = Prop_Actions(Spot, Strike_Plancher, Rf, Sigma, Dist_Echeance)
Position_Actions = Alpha * Valo_Position
Position_Obligs = (1 - Alpha) * Valo_Position
Next j
perf(i, 1) = Valo_Position
Next i
Sheets("Feuil2").Range("A2:A" & Nb_Simuls + 1) = perf
MsgBox "Terminé"
End Sub
Function Deter_Strike(ByVal S As Double, ByVal K As Double, ByVal R As Double, ByVal Sigma As Double, ByVal T As Double, ByVal Prop_Gar) As Double
Dim Epsilon As Double, D1 As Double, D2 As Double, Erreur As Double
Randomize
Epsilon = Abs(WorksheetFunction.NormSInv(Rnd))
Do
D1 = (Log(S / K) + (R + 0.5 * Sigma ^ 2) * T) / (Sigma * Sqr(T))
D2 = D1 - Sigma * Sqr(T)
K = (-Prop_Gar * (S + BS_STD("Put", S, K, R, Sigma, T)) + K * Prop_Gar * Exp(-R * T) * Nd(-D2)) / (Prop_Gar * Exp(-R * T) * Nd(-D2) - 1)
Erreur = Prop_Gar * (S + BS_STD("Put", S, K, R, Sigma, T)) - K
Loop Until Abs(Erreur) < Epsilon
Deter_Strike = K
End Function
Function Taux_Renta_Action(ByVal Mu As Double, ByVal Sigma As Double, ByVal dT As Double) As Double
Dim Epsilon As Double
Epsilon = WorksheetFunction.NormSInv(Rnd)
Taux_Renta_Action = (Mu - Sigma ^ 2 / 2) * dT + Sigma * Epsilon * Sqr(dT)
End Function
Function Prop_Actions(ByVal Spot As Double, ByVal Strike_Plancher As Double, ByVal Rf As Double, ByVal Sigma As Double, ByVal Dist_Echeance As Double) As Double
Dim D1 As Double, D2 As Double
D1 = (Log(Spot / Strike_Plancher) + (Rf + Sigma ^ 2 / 2) * Dist_Echeance) / (Sigma * Sqr(Dist_Echeance))
D2 = D1 - Sigma * Sqr(Dist_Echeance)
Prop_Actions = Spot * Nd(D1) / (Spot * Nd(D1) + Strike_Plancher * Exp(-Rf * Dist_Echeance) * Nd(-D2))
End Function
Function BS_STD(ByVal TypeOption As String, ByVal S As Double, ByVal K As Double, ByVal R As Double, ByVal Sigma As Double, ByVal T As Double) As Double
Dim D1 As Double, 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_STD = z * (S * Nd(z * D1) - K * Exp(-R * T) * Nd(z * D2))
End Function
Function Nd(ByVal d As Double) As Double
Nd = WorksheetFunction.NormSDist(d)
End Function
Function Switch(ByVal TypeOption As String) As Integer
If UCase(TypeOption) = "C" Or UCase(TypeOption) = "CALL" Then
Switch = 1
Else
Switch = -1
End If
End Function |
