Bonjour,

Après une multitude d'essai, au moment d'exécuter ma simulation excel beug et après quelques minutes se ferme. Il ne m'indique aucune erreur donc je ne sais pas ou est le problème.

J'ai bien déclaré toutes les variables, vérifié les formules et toujours le même résultat... J'ai essayé d'un autre pc et mm résultat

voici le code :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
91
92
93
94
95
96
97
Option Explicit
Sub obpi()
Dim Nb_Points As Integer, Plancher As Integer, i As Integer, j As Integer
Dim rf As Double, S0 As Double, Mu As Double, Sigma As Double
Dim Strike_Plancher As Double, dT As Double, Nb_Simuls As Double, spot As Double
Dim Position_Actions As Double, Valo_Position As Double, alpha As Double, Dist_Echéance As Double
Dim Position_Obligs As Double, Taux_Renta As Double
 
    'Paramétrage du sous-jacent
    S0 = 100
    Mu = -0.08
    Sigma = 0.35
 
    'Monétaire
    rf = 0.03
    Plancher = 80
    Strike_Plancher = Deter_Strike(S0, Plancher, rf, Sigma, 1, Plancher / 100)
 
    Nb_Points = 252
    dT = 1 / Nb_Points
    Nb_Simuls = 1000
 
    ReDim Perf(Nb_Simuls, 1) As Double
 
    For i = 1 To Nb_Simuls
 
        spot = S0
        Dist_Echéance = 1
        alpha = Prop_Actions(spot, Strike_Plancher, rf, Sigma, Dist_Echéance)
 
        Position_Actions = alpha * S0
        Position_Obligs = (1 - alpha) * S0
 
        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_Echéance = WorksheetFunction.Max(Dist_Echéance - dT, 0.00000001)
            alpha = Prop_Actions(spot, Strike_Plancher, rf, Sigma, Dist_Echéance)
            Position_Actions = alpha * Valo_Position
            Position_Obligs = (1 - alpha) * Valo_Position
 
        Next j
 
 
        Perf(i, 1) = Valo_Position
    Next i
End Sub
 
Function Deter_Strike(S, K, R, Sigma, T, prop_gar)
Dim epsilon As Integer
Dim d1 As Double, d2 As Double, erreur As Double
 
    epsilon = 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(Mu, Sigma, dT)
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(spot, Strike_Plancher, rf, Sigma, Dist_Echéance)
Dim d1 As Double, d2 As Double
    d1 = (Log(spot / Strike_Plancher) + (rf + Sigma ^ 2 / 2) * Dist_Echéance) / (Sigma * Sqr(Dist_Echéance))
    d2 = d1 - Sigma * Sqr(Dist_Echéance)
    Prop_Actions = spot * Nd(d1) / (spot * Nd(d1) + Strike_Plancher * Exp(-rf * Dist_Echéance) * Nd(-d2))
End Function
 
Function BS_STD(TypeOption, S, K, R, Sigma, T)
Dim d1 As Double, d2 As Double, z As Double
    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(d)
    Nd = WorksheetFunction.NormSDist(d)
End Function
Function Switch(TypeOption)
    TypeOption = UCase(TypeOption)
    If TypeOption = "C" Or TypeOption = "CALL" Then
        Switch = 1
    Else
        Switch = -1
    End If
End Function
Merci d'avance pour votre aide