Pricing d'options barrières Pb de matching BS/MC

Y compris Power BI, Power Query et toute autre question en lien avec Excel
c
curveater
Jeune membre
Jeune membre
Messages : 37
Inscrit le : 18 décembre 2018
Version d'Excel : Office 2013

Message par curveater » 22 décembre 2018, 12:06

Bonjour,

Je ne parviens pas à matcher le prix d'options à barrières de type Call Up and In avec les formules de type BS et la méthode de MC.
Voyez vous mon erreur?

Cf fichier en attachment
Pricer d'options vanilles.xlsm
(60.7 Kio) Téléchargé 8 fois
Cdt.

Ludovic
Sub CUIPRICE()
Dim Barrierefranchise As Integer
GetData
Dim Stinit As Double
Stinit = St
Dim moyenneCUI As Double
Dim unif As Double
moyenneCUI = 0
If St < L Then
    Dim Stplusdt As Double
    Dim i As Long
    Dim j As Long
    For i = 1 To N
        Barrierefranchise = 0
        For j = partieentiere(t * 260) To partieentiere((M * 260))
            Randomize
            unif = Rnd
            z = WorksheetFunction.Norm_Inv(unif, 0, 1)
            Stplusdt = St * (1 + z * sigma * WorksheetFunction.Power((M - t) / 260, 0.5) + (r - div) * ((M - t) / 260))
            St = Stplusdt
            If Stplusdt > L Then
                Barrierefranchise = 1
            End If
        Next
        moyenneCUI = moyenneCUI + Barrierefranchise * partiepositive(St * Exp((r - div - 0.5 * WorksheetFunction.Power(sigma, 2)) * (M - t) + sigma * WorksheetFunction.Power(M - t, 0.5) * z) - K)
        St = Stinit
    Next
ElseIf St > L Then
    moyenneCUI = 0
End If
Worksheets("Feuil1").Cells(17, 4) = Exp(-r * (M - t)) * moyenneCUI / N
End Sub

Function CUIPRICE(St As Double, t As Double, K As Double, r As Double, M As Double, sigma As Double, L As Double) As Double
If St < L Then
    If K > L Then
        CUIPRICE = function1(1, St, div, M, r, sigma, t, K)
    ElseIf K < L Then
        CUIPRICE = function2(1, St, div, M, r, sigma, t, K, L) - function3(1, -1, St, div, M, r, sigma, t, K, L) + function4(1, -1, St, div, M, r, sigma, t, K, L)
    End If
ElseIf St > L Then
    CUIPRICE = 0
End If
End Function
Function mu(r As Double, div As Double, sigma As Double) As Double
mu = r - div - 0.5 * WorksheetFunction.Power(sigma, 2)
End Function
Function eps(sigma As Double) As Double
eps = (mu(r, div, sigma) / WorksheetFunction.Power(sigma, 2)) + 1
End Function
Function x(St As Double, t As Double, M As Double, K As Double) As Double
x = (WorksheetFunction.Ln(St / K) / (sigma * WorksheetFunction.Power(M - t, 0.5))) + eps(sigma) * sigma * WorksheetFunction.Power((M - t), 0.5)
End Function
Function x1(St As Double, t As Double, M As Double, L As Double) As Double
x1 = (WorksheetFunction.Ln(St / L) / (sigma * WorksheetFunction.Power(M - t, 0.5))) + eps(sigma) * sigma * WorksheetFunction.Power((M - t), 0.5)
End Function
Function y(St As Double, t As Double, M As Double, L As Double) As Double
y = (WorksheetFunction.Ln(WorksheetFunction.Power(L, 2) / (St * K)) / (sigma * WorksheetFunction.Power(M - t, 0.5))) + eps(sigma) * sigma * WorksheetFunction.Power(M, 0.5)
End Function
Function y1(St As Double, t As Double, M As Double, L As Double) As Double
y1 = (WorksheetFunction.Ln(L / St) / (sigma * WorksheetFunction.Power(M - t, 0.5))) + eps(sigma) * sigma * WorksheetFunction.Power(M, 0.5)
End Function
Function function1(fi As Double, St As Double, div As Double, M As Double, r As Double, sigma As Double, t As Double, K As Double) As Double
function1 = fi * St * Exp(-div * (M - t)) * WorksheetFunction.Norm_Dist(fi * x(St, t, M, K), 0, 1, True) - fi * K * Exp(r * (M - t)) * WorksheetFunction.Norm_Dist(fi * x(St, t, M, K) - fi * sigma * WorksheetFunction.Power((M - t), 0.5), 0, 1, True)
End Function
Function function2(fi As Double, St As Double, div As Double, M As Double, r As Double, sigma As Double, t As Double, K As Double, L As Double) As Double
function2 = fi * St * Exp(-div * (M - t)) * WorksheetFunction.Norm_Dist(fi * x1(St, t, M, L), 0, 1, True) - fi * K * Exp(r * (M - t)) * WorksheetFunction.Norm_Dist(fi * x1(St, t, M, L) - fi * sigma * WorksheetFunction.Power((M - t), 0.5), 0, 1, True)
End Function
Function function3(fi As Double, nu As Double, St As Double, div As Double, M As Double, r As Double, sigma As Double, t As Double, K As Double, L As Double) As Double
function3 = fi * St * Exp(-div * (M - t)) * WorksheetFunction.Power((L / St), 2 * eps(sigma)) * WorksheetFunction.Norm_Dist(nu * y(St, t, M, L), 0, 1, True) - fi * K * Exp(r * (M - t)) * WorksheetFunction.Power((L / St), 2 * (eps(sigma) - 1)) * WorksheetFunction.Norm_Dist(nu * y(St, t, M, L) - nu * sigma * WorksheetFunction.Power((M - t), 0.5), 0, 1, True)
End Function
Function function4(fi As Double, nu As Double, St As Double, div As Double, M As Double, r As Double, sigma As Double, t As Double, K As Double, L As Double) As Double
function4 = fi * St * Exp(-div * (M - t)) * WorksheetFunction.Power((L / St), 2 * eps(sigma)) * WorksheetFunction.Norm_Dist(nu * y1(St, t, M, L), 0, 1, True) - fi * K * Exp(r * (M - t)) * WorksheetFunction.Power((L / St), 2 * (eps(sigma) - 1)) * WorksheetFunction.Norm_Dist(nu * y1(St, t, M, L) - nu * sigma * WorksheetFunction.Power((M - t), 0.5), 0, 1, True)
End Function
ob.JPG
  • Sujets similaires
    Réponses
    Vues
    Dernier message