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