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 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
| Sub pricer()
'Récupération des données depuis la feuille Excel
Dim d_f_c(4) As Double, delta_t As Double
Dim d_f_nc(6) As Double, zc_forward(6) As Double, vol_forward(6) As Double, taux As Double, div As Double, vol As Double
Dim spot As Integer, a As Double, CASS As Integer, APPROCHEE As Integer, payoff() As Double, SJ() As Double
taux = Cells(5, 2)
div = Cells(6, 2)
vol = Cells(7, 2)
d_f_c(1) = (1 / 1 + taux)
d_f_c(2) = (1 / 1 + taux) ^ 2
d_f_c(3) = (1 / 1 + taux) ^ 2.5
d_f_c(4) = (1 / 1 + taux) ^ 3
For i = 1 To 6
d_f_nc(i) = Cells(15, 1 + i)
Next i
For i = 1 To 6
zc_forward(i) = Cells(18 + i, 1 + i)
Next i
For i = 1 To 6
vol_forward(i) = Cells(28 + i, 1 + i)
Next i
delta_t = 0.5
n = 3 / 0.5
spot = 4880
'a = box_muller
'b = box_muller
'MsgBox a
'MsgBox b
'jai deux cas à traiter : cas constant:CASS=1 et cas non constant:CASS=2 que j'ai lié ici à la variable CASS CASS prend 1 ou 2 selon la méthode choisie
CASS = Range("CASS").Value
'il faut que je propose le choix à l'utilisateur entre les 3 méthodes selon le cas | APPROCHEE prend 1, 2 ou 3 selon la méthode choisie
APPROCHEE = Range("APPROCHEE").Value
If APPROCHEE = 3 Then
k = 2 * n
Else: k = n
End If
'Matrice et Tableau de sorties
ReDim SJ(1 To k, 1 To 7)
ReDim payoff(1 To k, 1 To 1)
For i = 1 To UBound(SJ, 1)
k = n + i
payoff(i, 1) = 0
SJ(i, 1) = spot
If APPROCHEE = 3 Then
payoff(k, 1) = 0
SJ(k, 1) = spot
End If
Select Case (CASS)
Case 1
Select Case (APPROCHEE)
Case 1
For j = 2 To UBound(SJ, 2)
g = WorksheetFunction.NormSInv(Rnd())
SJ(i, j) = SJ(i, j - 1) * (1 + (taux - div) * delta_t + vol * Sqr(delta_t) * g)
Next j
Case 2
For j = 2 To UBound(SJ, 2)
g = box_muller()
SJ(i, j) = SJ(i, j - 1) * (1 + (taux - div) * delta_t + vol * Sqr(delta_t) * g)
Next j
Case 3
For j = 2 To UBound(SJ, 2)
g = box_muller()
SJ(i, j) = SJ(k, j - 1) * (1 + (taux - div) * delta_t + vol * Sqr(delta_t) * g)
SJ(k, j) = SJ(k, j - 1) * (1 + (taux - div) * delta_t + vol * Sqr(delta_t) * g)
Next j
End Select
Case 2
Select Case (APPROCHEE)
Case 1
For j = 2 To UBound(SJ, 2)
g = WorksheetFunction.NormSInv(Rnd()) 'utilise le générateur de variables uniformes d'excel
SJ(i, j) = SJ(i, j - 1) * (1 + (zc_forward(j - 1) - div) * delta_t + vol_forward(j - 1) * Sqr(delta_t) * g)
Next j
Case 2
For j = 2 To UBound(SJ, 2)
g = box_muller()
SJ(i, j) = SJ(i, j - 1) * (1 + (zc_forward(j - 1) - div) * delta_t + vol_forward(j - 1) * Sqr(delta_t) * g)
Next j
For j = 2 To UBound(SJ, 2)
g = box_muller()
SJ(i, j) = SJ(k, j - 1) * (1 + (zc_forward(j - 1) - div) * delta_t + vol_forward(j - 1) * Sqr(delta_t) * g)
SJ(k, j) = SJ(k, j - 1) * (1 + (zc_forward(j - 1) - div) * delta_t + vol_forward(j - 1) * Sqr(delta_t) * g)
Next j
End Select
End Select
payoff(i, 1) = prix(SJ(i, 1), SJ(i, 2), SJ(i, 3), SJ(i, 4), SJ(i, 5), SJ(i, 6), SJ(i, 7))
Next i
MsgBox payoff(3, 1)
MsgBox SJ(1, 1)
MsgBox SJ(1, 2)
MsgBox SJ(2, 3)
End Sub
'Mon problème se trouve dans la fonction qui est censée renvoyer le payoff de l'option, je ne sais pas comment calculer n(d1)
Function prix(so As Double, s0_5 As Double, s1 As Double, s1_5 As Double, s2 As Double, s2_5 As Double, s3 As Double) As Double
Dim d1 As Double
Dim d2 As Double
Dim vol As Double, r As Double
r = Cells(5, 2)
vol = Cells(7, 2)
d1 = (r + (vol * vol) / 2) * T / vol * Sqr(T)
d2 = d1 - vol * Sqr(T)
If (s0_5 / s0 - 1) >= 0 Then
prix = 7 / 100 * s0 * d_f1ans
ElseIf (s1_5 / s0 - 1) >= 0 Then
prix = 14 / 100 * s0 * d_f2ans
ElseIf (s1 + s2 / 2 * s0 - 1 / 2) >= 0 Then
prix = 21 / 100 * s0 * d_f2_5sans
Else
prix = s0 * WorksheetFunction.Norm_S_Dist(d1, True) - s0 * Exp(-r * T) * WorksheetFunction.Norm_S_Dist(d2, True) * d_f3ans
End Function
'génère des valeurs en utilisant la méthode de box muller
Function box_muller() As Double
Dim u1 As Double, u2 As Double, y1 As Double, y2 As Double, pi As Double
pi = 4 * Atn(1)
Randomize
u1 = Rnd()
Randomize
u2 = Rnd()
y1 = Sqr(-2 * Log(u1)) * Cos(2 * pi * u2)
'y2 = Sqr(-2 * Log(u1) * Sin(2 * pi * u2)
box_muller = y1
End Function |
Partager