Function PrixObligation(maturityDate As Date, paymentFrequency As Integer, couponRate As Double, _
    firstCouponDate As Date, valuationDate As Date, discountCurve As Range, _
    dayCountConvention As String, amortFrequency As Integer, firstAmortDate As Date, amortization As Range) As Double

    Dim daysToMaturity As Integer
    Dim NbCoupons As Integer
    Dim couponPayment As Double
    Dim discountFactor As Double
    Dim timeToPayment As Double
    Dim discountRate As Double
    Dim timeToMaturity As Double
    Dim i As Integer
    Dim daysInYear As Integer
	Dim amortPercentage As Double
	Dim principalRemaining As Double
	Dim principalRepaid As Double
	Dim timeToAmortize As Double
    Dim iteration As Integer
	Dim repetition As Integer
	Dim amortizationDate As Date

    'Détermination du nombre de jours dans une année en fonction de la convention de base
    Select Case dayCountConvention
        Case "AA"
            daysInYear = 365
        Case "A5"
            daysInYear = 365
        Case "A0"
            daysInYear = 360
        Case Else
            ' Par défaut, utilisation de 365 jours
            daysInYear = 365
    End Select

    'Détermination de l'itération de paiement en fraction mensuelle
    iteration = 12 / paymentFrequency

    'Détermination de l'itération de remboursement en fraction mensuelle
    repetition = 12 / amortFrequency
    
	'Initialisation du capital à 100%
    principalRemaining = 1

    'Détermination de la première date de coupon
    While firstCouponDate <= valuationDate
'        firstCouponDate = firstCouponDate + (daysInYear / paymentFrequency)
        firstCouponDate = Application.WorksheetFunction.EDate(firstCouponDate, iteration)
    Wend


    'Détermination de la première date de remboursement et du capital rembourser	
    While firstAmortDate <= valuationDate
	    amortPercentage = Application.WorksheetFunction.VLookup(firstAmortDate, amortization, 2, True)
	    principalRemaining = principalRemaining - amortPercentage
'        firstAmortDate = firstAmortDate + (daysInYear / amortFrequency)
        firstAmortDate = Application.WorksheetFunction.EDate(firstAmortDate, repetition)
    Wend


    ' Calcul du nombre de jours jusqu'à la maturité
    daysToMaturity = maturityDate - valuationDate

    'Initialisation du nombre des coupons futurs
     NbCoupons = ((maturityDate - firstCouponDate) / (daysInYear / paymentFrequency)) + 1

    'Calcul du temps avant maturité pour connaitre le nombre des coupons
    timeToMaturity = daysToMaturity / daysInYear

    ' Calcul du montant de chaque paiement de coupon
    couponPayment = couponRate / paymentFrequency

    ' Initialisation du prix de l'obligation
    PrixObligation = 0

    ' Calcul du prix de l'obligation en sommant les flux actualisés

    For i = 1 To NbCoupons
        ' Calcul du temps jusqu'au paiement du coupon
        timeToPayment = (firstCouponDate - valuationDate) + (i - 1) * (daysInYear / paymentFrequency)

        ' Calcul du temps jusqu'au prochain amortissement
        timeToAmortize = (firstAmortDate - valuationDate) + (i - 1) * (daysInYear / amortFrequency)

        'Détermination de l'équivalent de timeToAmortize en date
         amortizationDate = valuationDate + timeToAmortize
		 
    If timeToPayment < timeToAmortize Then
 
       'dans ce cas pas d'amortissement 
        amortPercentage = 0

       'dans ce cas pas de capital à rembourser
		principalRepaid = 0

       'dans ce cas le capital reste le même
		principalRemaining = principalRemaining


    Else
        'détermination du pourcentage à amortir
	    amortPercentage = Application.WorksheetFunction.VLookup(amortizationDate, amortization, 2, True)
		
        ' Interpolation linéaire pour obtenir le taux de remise correspondant
		discountRate = InterpLineaire(discountCurve, timeToAmortize / daysInYear)
		
		' Calcul du facteur d'actualisation
        discountFactor = Exp(-discountRate * timeToAmortize / daysInYear)

        'actualisation du pourcentage remboursé		
		principalRepaid = amortPercentage * discountFactor

        'reduction de capital		
		principalRemaining = principalRemaining - amortPercentage		
		
    End If		 

        ' Interpolation linéaire pour obtenir le taux de remise correspondant
        discountRate = InterpLineaire(discountCurve, timeToPayment / daysInYear)

        ' Calcul du facteur d'actualisation
        discountFactor = Exp(-discountRate * timeToPayment / daysInYear)

        ' Ajout du flux actualisé au prix de l'obligation
        PrixObligation = PrixObligation + (couponPayment * discountFactor) * principalRemaining + principalRepaid
    Next i
    On Error GoTo 0 ' Réactiver la gestion des erreurs normale

    ' Ajout du remboursement du principal à l'échéance
    PrixObligation = PrixObligation + principalRemaining * Exp(-discountRate * daysToMaturity / daysInYear)

End Function

Function InterpLineaire(discountCurve As Range, timeToPayment As Double) As Double
    Dim i As Integer
    Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double

    ' Recherche des points de la courbe les plus proches
    For i = 1 To discountCurve.Rows.Count - 1
        If discountCurve.Cells(i, 1).Value <= timeToPayment And discountCurve.Cells(i + 1, 1).Value >= timeToPayment Then
            x1 = discountCurve.Cells(i, 1).Value
            x2 = discountCurve.Cells(i + 1, 1).Value
            y1 = discountCurve.Cells(i, 2).Value
            y2 = discountCurve.Cells(i + 1, 2).Value
            Exit For
        End If
    Next i

    ' Interpolation linéaire
    If x2 <> x1 Then 'on gère le cas d'une division par 0
        InterpLineaire = y1 + (timeToPayment - x1) * (y2 - y1) / (x2 - x1)
    Else
        InterpLineaire = 0
    End If

End Function