bonjour, je suis vraiment débutante en excel et vba et voici un code vba que j'aimerais faire marcher sous excel, mais je ne sais pas comment faire!
ce code est tiré du pdf www.esg.uqam.ca/recherche/document/2006/11-06.pdf
pour ceux qui y comprennent qqchose ;-)
voici le code :
Sub simopt()
‘Simulation de Monte Carlo pour calculer prix d’une option asiatique
‘Range("d4 :iv6000").ClearContents
‘Range("option1").ClearContents
Dim iopt1, s1, x1, rf1, q1, t1, sigma1, nsimg1, pas
Dim rnmutg, sigtg, sumg, randnsg, S1g, payoff1g, sigsum, sigmoyenne
Dim I As Integer
Dim j As Integer
‘Randomize
iopt1 = 1
s1 = 80
x1 = 85
rf1 = 0.05
q1 = 0
t1 = 1
sigma1 = 0.2
pas = 100
nsimg1 = 100
rnmutg = (rf1 – q1 – 0.5 * sigma1^2) * (t1/pas)
sigtg = sigma1 * Sqrt(t1/pas)
For k = 1 To 100
sumg = 0
For i = 1 To nsimg1
S1g = s1
S2g = s1
sigsum1 = 0
sigsum2 = 0
For j=1 To pas
randnsg = Application.NormSInv(Rnd)
S1g = S1g * Exp(rnmutg + randnsg * sigtg)
‘Range(“prix1”).Offset(j-1, i-1)= S1g
sigsum1 = sigsum1 + S1g
S2g = S2g * Exp(rnmutg – randnsg * sigtg)
sigsum2 = sigsum2 + S2g
Next j
sigmoyenne1 = sigsum1 / pas
‘Range(“prix”).Offset(i-1,0) = sigmoyenne
sigmoyenne2 = sigsum2 / pas
payoff1g = 0.5 * Application.Max(iopt1 * (sigmoyenne1 – x1),0) + 0.5 * Application.Max(iopt1 * (sigmoyenne2 – x1), 0)
‘Range("cash").Offset(i-1, 0) = payoff1g
sumg = sumg + payoff1g
Next i
Option 1 = Exp (-rf1 * t1) * sumg/nsimg1
Range("histo5").Offset(k,0) = option1
Next k
End Sub
Sophie
ce code est tiré du pdf www.esg.uqam.ca/recherche/document/2006/11-06.pdf
pour ceux qui y comprennent qqchose ;-)
voici le code :
Sub simopt()
‘Simulation de Monte Carlo pour calculer prix d’une option asiatique
‘Range("d4 :iv6000").ClearContents
‘Range("option1").ClearContents
Dim iopt1, s1, x1, rf1, q1, t1, sigma1, nsimg1, pas
Dim rnmutg, sigtg, sumg, randnsg, S1g, payoff1g, sigsum, sigmoyenne
Dim I As Integer
Dim j As Integer
‘Randomize
iopt1 = 1
s1 = 80
x1 = 85
rf1 = 0.05
q1 = 0
t1 = 1
sigma1 = 0.2
pas = 100
nsimg1 = 100
rnmutg = (rf1 – q1 – 0.5 * sigma1^2) * (t1/pas)
sigtg = sigma1 * Sqrt(t1/pas)
For k = 1 To 100
sumg = 0
For i = 1 To nsimg1
S1g = s1
S2g = s1
sigsum1 = 0
sigsum2 = 0
For j=1 To pas
randnsg = Application.NormSInv(Rnd)
S1g = S1g * Exp(rnmutg + randnsg * sigtg)
‘Range(“prix1”).Offset(j-1, i-1)= S1g
sigsum1 = sigsum1 + S1g
S2g = S2g * Exp(rnmutg – randnsg * sigtg)
sigsum2 = sigsum2 + S2g
Next j
sigmoyenne1 = sigsum1 / pas
‘Range(“prix”).Offset(i-1,0) = sigmoyenne
sigmoyenne2 = sigsum2 / pas
payoff1g = 0.5 * Application.Max(iopt1 * (sigmoyenne1 – x1),0) + 0.5 * Application.Max(iopt1 * (sigmoyenne2 – x1), 0)
‘Range("cash").Offset(i-1, 0) = payoff1g
sumg = sumg + payoff1g
Next i
Option 1 = Exp (-rf1 * t1) * sumg/nsimg1
Range("histo5").Offset(k,0) = option1
Next k
End Sub
Sophie
