construction d'une courbe d'actualisation avec VBA

kiddus i

XLDnaute Nouveau
bonjour
je suis toujours à la rehcrehce d'une âme charitable qui pourrait m'aider car visiblement ils ont oublié qq chose dans le bouquin (du moins je pense...)
donc je reprends : travaillant dans la finance je souhaiterai réaliser un petit programme de valorisation des swap sous vba, j'ai dc acheté le bouquin qui était sensé me rendre heureux mais ca ne marche pas:confused::mad:!!!
je pense avoir isoler le pbm mais je ne sais pas comment le résoudre...
je vous fais donc suivre les codes....


Option Explicit
Option Base 1

Function CTableau(vDonnees)

'Cette fonction renvoie un tableau de double.
'Elle permet de convertir l'argument vDonnees,
'qui peut être de différents types,
'en un type unique et connu.

Dim TableauRetour(), i As Integer
'La fonction TypeName permet de connaître le type de vDonnees
Select Case TypeName(vDonnees)

Case "Range"
'C'est une plage de cellules
'vDonnees.Cells.Count compte le nombre de cellules de
'la plage vDonnees.
ReDim TableauRetour(1 To vDonnees.Cells.Count)
For i = 1 To vDonnees.Cells.Count
'Nous affectons au tableau à l'indice i
'La valeur de la cellule i
TableauRetour(i) = vDonnees.Cells(i)
Next
Case "Integer()", "Double()", "Date()"
'C'est un tableau
ReDim TableauRetour(1 To UBound(vDonnees))
For i = 1 To UBound(vDonnees)
TableauRetour(i) = vDonnees(i)
Next
Case Else
'C'est un nombre ou une date, nous l'affectons
' à un tableau à une dimension
ReDim TableauRetour(1 To 1)
TableauRetour(1) = vDonnees
End Select

CTableau = TableauRetour

End Function

Function ChangeTaux(DateDeCalcul As Date, DateDeMaturite As Date, _
dblDonnee As Double, iTypeDonnee As Integer, iFrequence As Integer, _
iBase, iTypeDonneeCible As Integer, _
iFrequenceCible As Integer, iBaseCible) As Double

'=============================================================
'Cette fonction permet la conversion d'un taux ou d'un facteur
'd'actualisation en un autre taux (ou facteur d'actualisation)
'en modifiant la base de calcul et/ou son type
'=============================================================

'Définition des variables
Dim dblFA As Double 'Facteur d'actualisation
Dim dblValRet As Double 'Variable renvoyée
Dim dblF1 As Double 'Fraction année dans la base source
Dim dblF2 As Double 'Fraction année dans la base cible


dblF1 = FractionAnnee(DateDeCalcul, DateDeMaturite, iBase)
dblF2 = FractionAnnee(DateDeCalcul, DateDeMaturite, iBaseCible)

'Conversion de la donnée initiale
'en facteur d'actualisation
Select Case iTypeDonnee

Case 0
'la donnée initiale est un taux composé
dblFA = (1 + dblDonnee / iFrequence) ^ (iFrequence * dblF1)
dblFA = 1 / dblFA
Case 1
'la donnée initiale est un taux proportionnel
dblFA = (1 + dblDonnee * dblF1)
dblFA = 1 / dblFA
Case 2
'la donnée initiale est un facteur d'acualisation
dblFA = dblDonnee
Case 3
'la donnée initiale est un taux continu
dblFA = Exp(-dblDonnee * dblF1)
Case Else
'type inconnu
dblFA = 0
End Select

Select Case iTypeDonneeCible

Case 0
'la donnée cible est un taux composé
dblValRet = (((1 / dblFA) ^ (1 / (dblF2 * _
iFrequenceCible))) - 1) * iFrequenceCible
Case 1
'la donnée cible est un taux proportionnel
dblValRet = ((1 / dblFA) - 1) * (1 / dblF2)
Case 2
'la donnée cible est un facteur d'actualisation
dblValRet = dblFA

Case 3
'la donnée cible est un taux continu
dblValRet = Log(1 / dblFA) / dblF2
Case Else
'type inconnu, la fonction ne calcul rien
dblValRet = 0
End Select

ChangeTaux = dblValRet

End Function

Function InterpolationLineaire(TableauMaturites, TableauDonnees, _
DateCalculees, Optional EstFactActua As Boolean = False, Optional _
DateDeCalcul As Date)

'=============================================================
'Cette fonction détermine à partir d'un vecteur de dates
'et d'un vecteur de données, les données interpolées
'linéairement pour les dates du vecteur DateCalculées
'=============================================================

Dim TabMat
'Tableau des maturités des données sources
Dim TabData
'Données sources
Dim TabDates
'Date à calculer
Dim i As Integer, j As Integer
'Variables de boucle
Dim TabRetour
'Données renvoyées
Dim DateCalc As Date
'Date en cours de calcul (provenant de TabDates)
Dim DateInf As Date
'Date provenant de TabMat, inférieur à DateCalc
Dim DateSup As Date
'Date provenant de TabMat, supérieure à DateCalc
Dim dblTxInf As Double
'Donnée correspondant à DateInf
Dim dblTxSup As Double
'Donnée corespondant à DateSup
Dim dblTaux As Double
'Donnée interpolée

'Conversion des arguments en tableaux
TabMat = CTableau(TableauMaturites)
TabData = CTableau(TableauDonnees)
TabDates = CTableau(DateCalculees)

'Dimensionnement du tableau de retour
ReDim TabRetour(LBound(TabDates) To UBound(TabDates))


Select Case EstFactActua

Case True
'Les données sont des facteurs d'actualisation

For i = LBound(TabDates) To UBound(TabDates)
'Boucle sur les dates à calculer

If TabDates(i) <= TabMat(1) Then
'Test des bornes, cas de la borne inférieure
dblTaux = ChangeTaux(DateDeCalcul, (TabMat(1)), _
(TabData(1)), 2, 1, 2, 0, 1, 2)

TabRetour(i) = ChangeTaux(DateDeCalcul, (TabDates(i)), _
dblTaux, 0, 1, 2, 2, 1, 2)

ElseIf TabDates(i) >= TabMat(UBound(TabMat)) Then
'Test des bornes, cas de la borne supérieure
dblTaux = ChangeTaux(DateDeCalcul, (TabMat _
(UBound(TabMat))), (TabData(UBound(TabData))), _
2, 1, 2, 0, 1, 2)

TabRetour(i) = ChangeTaux(DateDeCalcul, (TabDates(i)), _
dblTaux, 0, 1, 2, 2, 1, 2)
Else
'Cas général
For j = 1 To UBound(TabMat)
'On recherche la première date supérieure à
'la date calculée
If TabMat(j) > TabDates(i) Then
Exit For
End If
Next

'Données utiles pour l'interpolation
dblTxInf = TabData(j - 1)
dblTxSup = TabData(j)
DateInf = TabMat(j - 1)
DateSup = TabMat(j)
DateCalc = TabDates(i)

'Calcul de l'interpolation
TabRetour(i) = ((DateCalc - DateInf) * dblTxSup + _
(DateSup - DateCalc) * dblTxInf) / (DateSup - DateInf)
End If
Next


Case Else
'Les données sont des taux
For i = LBound(TabDates) To UBound(TabDates)
'boucle sur les dates à calculer
If TabDates(i) <= TabMat(1) Then
'Test des bornes, cas de la borne inférieure
TabRetour(i) = TabData(1)
ElseIf TabDates(i) >= TabMat(UBound(TabMat)) Then
'Test des bornes, cas de la borne supérieure
TabRetour(i) = TabData(UBound(TabMat))
Else
For j = 1 To UBound(TabMat)
'On rehcerche la première date supérieure à
'la date calculée
If TabMat(j) > TabDates(i) Then
Exit For
End If
Next
'Données utilisées pour l'interpolation
dblTxInf = TabData(j - 1)
dblTxSup = TabData(j)
DateInf = TabMat(j - 1)
DateSup = TabMat(j)
DateCalc = TabDates(i)

'Calcul de l'interpolation
TabRetour(i) = ((DateCalc - DateInf) * dblTxSup + _
(DateSup - DateCalc) * dblTxInf) / (DateSup - DateInf)
End If
Next
End Select

InterpolationLineaire = TabRetour

End Function

Function InterpolationCubique(TableauMaturites, TableauDonnees, _
DateCalculees, _
Optional EstFactActua As Boolean = False, Optional _
DateDeCalcul As Date)

'=============================================================
'Cette fonction détermine à partir d'un vecteur de dates
'et d'un vecteur de données, les données interpolées
'par un polynome de degré 3 pour les dates du vecteur DatesCalculées
'=============================================================

Dim TabMat
'Tableau des maturités des données sources
Dim TabData
'Données sources
Dim TabDates
'Dates à calculer
Dim i As Integer, j As Integer
'Variables de boucle
Dim TabRetour
'Données renvoyées
Dim MatriceDate(1 To 4, 1 To 4) As Double
'Matrice des coefficients des paramètres
Dim MatDateInv As Variant
'Matrice inverse de MatriceDate
Dim VecTaux(4, 1)
'Vecteur des données solutions du système
Dim VecParam
'Vecteur des paramètres calculés



'Conversion des arguments en tableau
TabMat = CTableau(TableauMaturites)
TabData = CTableau(TableauDonnees)
TabDates = CTableau(DateCalculees)

'Dimensionnement des tableaux de retour
ReDim TabRetour(LBound(TabDates) To UBound(TabDates))


For i = LBound(TabDates) To UBound(TabDates)
'Boucle sur les dates à calculer

If TabDates(i) <= TabMat(2) Then
'Test des bornes, cas de la borne inférieure
TabRetour(i) = InterpolationLineaire(TabMat, TabData, _
TabDates(i), _
EstFactActua, DateDeCalcul)(1)
ElseIf TabDates(i) >= TabMat(UBound(TabMat) - 1) Then
'Test des bornes, cas de la borne supérieure
TabRetour(i) = InterpolationLineaire(TabMat, TabData, _
TabDates(i), _
EstFactActua, DateDeCalcul)(1)
ElseIf UBound(TabMat) < 4 Then
'Pas assez de données pour éffectuer une interpolation cubique
TabRetour(i) = InterpolationLineaire(TabMat, TabData, _
TabDates(i), _
EstFactActua, DateDeCalcul)(1)
Else
For j = 3 To UBound(TabMat) - 2
'On recherche la première date supérieure à
'la date calculée
If TabMat(j) > TabDates(i) Then
Exit For
End If
Next

'On renseigne la matrice de date
MatriceDate(1, 1) = CDbl(TabMat(j - 2) ^ 3)
MatriceDate(1, 2) = TabMat(j - 2) ^ 2
MatriceDate(1, 3) = CDbl(TabMat(j - 2))
MatriceDate(1, 4) = 1
MatriceDate(2, 1) = CDbl(TabMat(j - 1) ^ 3)
MatriceDate(2, 2) = TabMat(j - 1) ^ 2
MatriceDate(2, 3) = CDbl(TabMat(j - 1))
MatriceDate(2, 4) = 1
MatriceDate(3, 1) = CDbl(TabMat(j) ^ 3)
MatriceDate(3, 2) = TabMat(j) ^ 2
MatriceDate(3, 3) = CDbl(TabMat(j))
MatriceDate(3, 4) = 1
MatriceDate(4, 1) = CDbl(TabMat(j + 1) ^ 3)
MatriceDate(4, 2) = TabMat(j + 1) ^ 2
MatriceDate(4, 3) = CDbl(TabMat(j + 1))
MatriceDate(4, 4) = 1

'On renseigne le vecteur de données
VecTaux(1, 1) = TabData(j - 2)
VecTaux(2, 1) = TabData(j - 1)
VecTaux(3, 1) = TabData(j)
VecTaux(4, 1) = TabData(j + 1)

'Inversion de la matrice de date
MatDateInv = Application.WorksheetFunction.MInverse _
(MatriceDate)

'Résolution du système
VecParam = Application.WorksheetFunction.MMult(MatDateInv, _
VecTaux)

'Calcul de la valeur interpolée
TabRetour(i) = VecParam(1, 1) * TabDates(i) ^ 3 + _
VecParam(2, 1) * TabDates(i) ^ 2 + _
VecParam(3, 1) * TabDates(i) + _
VecParam(4, 1)
End If
Next

InterpolationCubique = TabRetour

End Function

Function Interpolation(TableauMaturites, TableauDonnees, DateCalculees, _
Optional TypeInterpolation As Boolean = False, _
Optional TypeDonnees As Boolean = False, Optional _
DateDeCalcul As Date)
'=============================================================
'Cette fonction détermine à partir d'un vecteur de dates
'et d'un vecteur de données, les données interpolées
'pour les dates du vecteur DateCalculées
'=============================================================
Select Case TypeInterpolation

Case True
Interpolation = InterpolationLineaire(TableauMaturites, _
TableauDonnees, DateCalculees, TypeDonnees, DateDeCalcul)
Case Else
Interpolation = InterpolationCubique(TableauMaturites, _
TableauDonnees, DateCalculees, TypeDonnees, DateDeCalcul)
End Select


End Function

Function CourbeActualisation(DateDeCalcul As Date, TableauDatesValeurs, _
TableauMaturites, TableauDonnees, _
TableauTypeDonnees, TableauBases)

'====================================
'Cette fonction construit une courbe
'de facteurs d'actualisation
'à partir d'instruments de marché
'====================================

Dim TabDatesVal 'Tableau des dates de valeurs des données
Dim TabDatesMat 'Tableau des dates de maturités
'des données
Dim TabData 'Tableau des données
Dim TabDataType 'Tableau des types de données
Dim TabDataBase 'Tableau des bases de données
Dim TabFacActu() 'Tableau des facteurs d'actualisation
Dim TabDateFacActu() 'Tableau des dates des facteurs
'd'actualisation
Dim sTypeData As String 'Type de la donnée courante
Dim i As Integer, j As Integer 'Variable de boucle
Dim iTailleTab As Integer 'Taille des vecteurs TabFacActu et
'TabDateFacActu
Dim dblStub As Double 'Taux courant jusqu'à la date de valeur
'd'un contrat future
Dim dblCompoFut As Double 'Composition de dblStub et du future
Dim dblFrac1 As Double, dblFrac2 As Double 'Fraction d'années
Dim TabFluxOblg 'Vecteur des dates de flux
'd'un instrument PAR
Dim TabFAOblg 'Facteurs d'actualisation aux dates
'de TabFluxOblg
Dim dblSum As Double 'Variable de somme
Dim TableauSortie() As Double 'Données renvoyées
Dim dblFAtp As Double 'Facteur d'actualisation en cours de
'calcul


'Conversion des arguments en tableaux
TabDatesVal = CTableau(TableauDatesValeurs)
TabDatesMat = CTableau(TableauMaturites)
TabData = CTableau(TableauDonnees)
TabDataType = CTableau(TableauTypeDonnees)
TabDataBase = CTableau(TableauBases)

'Dimensionnement du tableau de retour
ReDim TableauSortie(3, UBound(TabDataType))

'Vérification de la cohérence des données
'il faut que tous les vecteurs comportent
'le même nombre de lignes
If UBound(TabDatesVal) <> UBound(TabDatesMat) Or UBound(TabDatesVal) _
<> UBound(TabData) _
Or UBound(TabDatesVal) <> UBound(TabDataType) Or UBound(TabDatesVal) _
<> UBound(TabDataBase) Then
'il y a une erreur, on sort de la fonction
CourbeActualisation = TableauSortie
Exit Function
End If

'Boucle sur l'ensemble des instruments
For i = 1 To UBound(TabDataType)
'Traitement en fonction du type de données
sTypeData = TabDataType(i)
Select Case sTypeData


Case "MM"
'Taux monétaires (Money Market)
'Transformation en taux composé Exact/365
dblFAtp = ChangeTaux(DateDeCalcul, (TabDatesMat(i)), _
(TabData(i)), 1, 1, (TabDataBase(i)), 0, 1, "Exact/365")

Case "FUT"
'Instrument future
'composition avec le taux à sa date de départ
'et transformation en taux composé Exact/365
dblStub = Interpolation(TabDateFacActu, TabFacActu, _
TabDatesVal(i))(1)
'dblStub contient le taux allant de la date de calcul
'à la date de départ du future
dblFrac1 = FractionAnnee(DateDeCalcul, (TabDatesVal(i)), _
"Exact/365")

dblFrac2 = FractionAnnee((TabDatesVal(i)), (TabDatesMat(i)), _
(TabDataBase(i)))
'dblCompoFut contient la composition de dblStub
'avec le future
dblCompoFut = ((1 + dblStub) ^ dblFrac1) * (1 + _
(100 - TabData(i)) * dblFrac2 / 100)

dblFrac1 = FractionAnnee(DateDeCalcul, (TabDatesMat(i)), _
"Exact/365")

'Transformation en taux composé Exact/365
dblFAtp = dblCompoFut ^ (1 / dblFrac1) - 1

Case Else
'Instrument de type obligation ou swap

'Identification de la date des flux de l'instrument
TabFluxOblg = DatesDesFlux(DateDeCalcul, (TabDatesMat(i)), _
1, , 1)
'On cherche les facteurs d'actualisation aux dates de flux
TabFAOblg = Interpolation(TabDateFacActu, TabFacActu, _
TabFluxOblg)

dblFrac1 = FractionAnnee(DateDeCalcul, (TabFluxOblg(1)), _
(TabDataBase(i)))
dblFrac2 = FractionAnnee(DateDeCalcul, (TabFluxOblg(1)), _
"Exact/365")

'Application de la formule
'Calcul de la somme
'Le premier coupon est pondéré
dblSum = ((1 + TabData(i) * 100) ^ dblFrac1 - 1) / _
((1 + TabFAOblg(1)) ^ dblFrac2)

If UBound(TabFluxOblg) > 2 Then
'Si UBound(TabFluxOblg) = 2, alors c'est
'un instrument à 2 ans.
'On cherche le facteur d'actualisation à 2 ans,
'inutile de poursuivre la somme
For j = 2 To UBound(TabFluxOblg) - 1
dblFrac2 = FractionAnnee(DateDeCalcul, _
(TabFluxOblg(j)), "Exact/365")
dblSum = dblSum + TabData(i) * 100 / _
((1 + TabFAOblg(j)) ^ dblFrac2)
Next
End If

'Le facteur d'actualisation
dblFAtp = (100 - dblSum) / (100 + TabData(i) * 100)
dblFrac1 = FractionAnnee(DateDeCalcul, (TabDatesMat(i)), _
"Exact/365")

'Transformation en taux composé Exact/365
dblFAtp = (1 / dblFAtp) ^ (1 / dblFrac1) - 1
End Select

'On ajoute le nouveau facteur d'actualisation et sa date de maturité
'aux tableaux qui nous servent pour les interpolations
iTailleTab = iTailleTab + 1
ReDim Preserve TabFacActu(iTailleTab)
ReDim Preserve TabDateFacActu(iTailleTab)
TabDateFacActu(iTailleTab) = TabDatesMat(i)
TabFacActu(iTailleTab) = dblFAtp



'On renseigne le tableau de résultats
dblFrac1 = FractionAnnee(DateDeCalcul, (TabDatesMat(i)), "Exact/365")
TableauSortie(1, i) = TabDatesMat(i)
TableauSortie(2, i) = 1 / (1 + TabFacActu(iTailleTab)) ^ dblFrac1
TableauSortie(3, i) = TabFacActu(iTailleTab)

Next

CourbeActualisation = TableauSortie

End Function
 

Martius Calvo

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Bonjour Kiddus,

Grâce à vos réflexions et détails de fichiers et à l'astuce de Klément de rajouter l'instruction Variant dans CTableau, en repointant méthodiquement tous les codes , j'ai enfin une fonction Courbe d'actualisation qui fonctionne.

Je vous en remercie, j'espère que c'est le cas pour vous également.

En vous souhaitant de bonnes fêtes de fin d'année

Bien Cordialement

Martius Calvo
 

Davidc57

XLDnaute Occasionnel
Re : construction d'une courbe d'actualisation avec VBA

Bonjour Martius,

Tu parviens donc à transformer les taux de marchés en une courbe des FA ?

J'avais aussi un problème avec la interpolation cubique mais comme la linéaire me suffisait j'avais lâché l'affaire... je crois que je vais m'y repencher.

J'ai un fichier complet et fonctionnel pour calculer des évaluation de IRS (en euro) mais pour le moment je suis obligé d'utiliser une courbe 0 coupons fournis par un tiers. Je ne suis donc pas complètement autonome. Cette courbe 0 coupons je sais la transformer en courbe de FA et vice versa mais me manque le point de départ ... Passer des taux de marchés aux taux 0 coupons !

Malheureusement mon fichier est trop volumineux pour être joignable ici. Je dois me repencher sur le problème car depuis le temps je ne sais plus précisément quel est mon souci. En tout cas je suis toujours sur ce projet j'ai bien avancé cette année mais il reste encore du travail.

Je répond à ton message en espérant que tu le liras et garderas contact avec moi.

Pour info mon email perso est : david_c57[AROBAS]hotmail[POINT]fr

A bientôt j'espère.
 

Sphere369

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Bonjour Kiddus,

Grâce à vos réflexions et détails de fichiers et à l'astuce de Klément de rajouter l'instruction Variant dans CTableau, en repointant méthodiquement tous les codes , j'ai enfin une fonction Courbe d'actualisation qui fonctionne.

Je vous en remercie, j'espère que c'est le cas pour vous également.

En vous souhaitant de bonnes fêtes de fin d'année

Bien Cordialement

Martius Calvo

Bonjour Martius,

J'ai beau faire le changement conseille ( Case "Integer()", "Double()", "Date()", "Variant()" ), je ne parviens toujours pas a avoir des valeurs pour les futures ou les obligations.

Pourrais tu stp expliciter la demarche utilisee?

Cdt,

Sphere
 

doudouf

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Bonjour tout le monde,
après vous avoir lu , j'ai aussi acheté le livre, car j'ai besoin de construire une courbe zero coupon, mais je me perds un peu dans ce livre, c est a quel moment qu'il construit la courbe zero coupon? excusez ma question car je suis débutante dans ce domaine

Merci d'avance
 

c2809

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Bonjour à tous,
Je viens tardivement sur ce forum mais on ne sait jamais;
J'ai également acheté le même livre...
Cependant je bloque.
J'ai beau arriver à avoir le tableau des zero coupons & facteurs d'actualisation, pas moyen d'avoir les taux forward. sur des maturités allant de 0 à 12mois, j'ai 0%..., (mais cependant j'ai un peu de résultat, surement faux, sur les échéances de 2Y à 30Y). Il m'est donc difficile d'avoir des resultats juste pour la partie des taux variables...
Qq1 aurait il reussi?
Merci
 

MAKX

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Bonjour, J'ai besoin de votre aide concernant le code de la fonction CourbeActualisation. J'ai bien integré toutes les modifications citées sue ce forum et j'ai relu l'integralité des codes mais j'ai toujours les #VALEUR quand je lance la fonction. ça vient pricipalement de la partie "PAR" du code qui ne fonctionne pas mais comment y remédier.

J'ai besoin de votre aide car cela fait 3 semaines que je tourne en rond sans solution.
Ci-dessous le code que j'utilise à partir du livre et avec les modifications;

Option Explicit
Option Base 1

Function CTableau(vDonnees)
'///////////////////////////////////////////////////////////////////////////////////////////
'Cette fonction renvoie un tableau de double.
'Elle permet de convertir l'argument vDonnees, qui peut être de différents types,
'en un type unique et connu.
'///////////////////////////////////////////////////////////////////////////////////////////

Dim TableauRetour(), i As Integer
'La fonction TypeName permet de connaître le type de vDonnees
Select Case TypeName(vDonnees)

Case "Range"
'C'est une plage de cellules
'vDonnees.Cells.Count compte le nombre de cellules de
'la plage vDonnees.
ReDim TableauRetour(1 To vDonnees.Cells.Count)
For i = 1 To vDonnees.Cells.Count
'Nous affectons au tableau à l'indice i
'La valeur de la cellule i
TableauRetour(i) = vDonnees.Cells(i)
Next
Case "Integer()", "Double()", "Date()", "Variant()"
'C'est un tableau
ReDim TableauRetour(1 To UBound(vDonnees))
For i = 1 To UBound(vDonnees)
TableauRetour(i) = vDonnees(i)
Next
Case Else
'C'est un nombre ou une date, nous l'affectons
' à un tableau à une dimension
ReDim TableauRetour(1 To 1)
TableauRetour(1) = vDonnees
End Select

CTableau = TableauRetour

End Function

Function ChangeTaux(DateDeCalcul As Date, DateDeMaturite As Date, _
dblDonnee As Double, iTypeDonnee As Integer, iFrequence As Integer, _
iBase, iTypeDonneeCible As Integer, _
iFrequenceCible As Integer, iBaseCible) As Double

'================================================= ============
'Cette fonction permet la conversion d'un taux ou d'un facteur
'd'actualisation en un autre taux (ou facteur d'actualisation)
'en modifiant la base de calcul et/ou son type
'================================================= ============

'Définition des variables
Dim dblFA As Double 'Facteur d'actualisation
Dim dblValRet As Double 'Variable renvoyée
Dim dblF1 As Double 'Fraction année dans la base source
Dim dblF2 As Double 'Fraction année dans la base cible


dblF1 = FractionAnnee(DateDeCalcul, DateDeMaturite, iBase)
dblF2 = FractionAnnee(DateDeCalcul, DateDeMaturite, iBaseCible)

'Conversion de la donnée initiale
'en facteur d'actualisation
Select Case iTypeDonnee

Case 0
'la donnée initiale est un taux composé
dblFA = (1 + dblDonnee / iFrequence) ^ (iFrequence * dblF1)
dblFA = 1 / dblFA
Case 1
'la donnée initiale est un taux proportionnel
dblFA = (1 + dblDonnee * dblF1)
dblFA = 1 / dblFA
Case 2
'la donnée initiale est un facteur d'acualisation
dblFA = dblDonnee
Case 3
'la donnée initiale est un taux continu
dblFA = Exp(-dblDonnee * dblF1)
Case Else
'type inconnu
dblFA = 0
End Select

Select Case iTypeDonneeCible

Case 0
'la donnée cible est un taux composé
dblValRet = (((1 / dblFA) ^ (1 / (dblF2 * _
iFrequenceCible))) - 1) * iFrequenceCible
Case 1
'la donnée cible est un taux proportionnel
dblValRet = ((1 / dblFA) - 1) * (1 / dblF2)
Case 2
'la donnée cible est un facteur d'actualisation
dblValRet = dblFA

Case 3
'la donnée cible est un taux continu
dblValRet = Log(1 / dblFA) / dblF2
Case Else
'type inconnu, la fonction ne calcul rien
dblValRet = 0
End Select

ChangeTaux = dblValRet

End Function


Function InterpolationLineaire(TableauMaturites, TableauDonnees, _
DateCalculees, Optional EstFactActua As Boolean = False, Optional _
DateDeCalcul As Date)

'================================================= ============
'Cette fonction détermine à partir d'un vecteur de dates
'et d'un vecteur de données, les données interpolées
'linéairement pour les dates du vecteur DateCalculées
'================================================= ============

Dim TabMat 'Tableau des maturités des données sources
Dim TabData 'Données sources
Dim TabDates 'Date à calculer
Dim i As Integer, j As Integer 'Variables de boucle
Dim TabRetour 'Données renvoyées
Dim DateCalc As Date 'Date en cours de calcul (provenant de TabDates)
Dim DateInf As Date 'Date provenant de TabMat, inférieur à DateCalc
Dim DateSup As Date 'Date provenant de TabMat, supérieure à DateCalc
Dim dblTxInf As Double 'Donnée correspondant à DateInf
Dim dblTxSup As Double 'Donnée corespondant à DateSup
Dim dblTaux As Double 'Donnée interpolée

'Conversion des arguments en tableaux
TabMat = CTableau(TableauMaturites)
TabData = CTableau(TableauDonnees)
TabDates = CTableau(DateCalculees)

'Dimensionnement du tableau de retour
ReDim TabRetour(LBound(TabDates) To UBound(TabDates))


Select Case EstFactActua

Case True
'Les données sont des facteurs d'actualisation

For i = LBound(TabDates) To UBound(TabDates)
'Boucle sur les dates à calculer

If TabDates(i) <= TabMat(1) Then
'Test des bornes, cas de la borne inférieure
dblTaux = ChangeTaux(DateDeCalcul, (TabMat(1)), _
(TabData(1)), 2, 1, 2, 0, 1, 2)

TabRetour(i) = ChangeTaux(DateDeCalcul, (TabDates(i)), _
dblTaux, 0, 1, 2, 2, 1, 2)

ElseIf TabDates(i) >= TabMat(UBound(TabMat)) Then
'Test des bornes, cas de la borne supérieure
dblTaux = ChangeTaux(DateDeCalcul, (TabMat _
(UBound(TabMat))), (TabData(UBound(TabData))), _
2, 1, 2, 0, 1, 2)

TabRetour(i) = ChangeTaux(DateDeCalcul, (TabDates(i)), _
dblTaux, 0, 1, 2, 2, 1, 2)
Else
'Cas général
For j = 1 To UBound(TabMat)
'On recherche la première date supérieure à
'la date calculée
If TabMat(j) > TabDates(i) Then
Exit For
End If
Next

'Données utiles pour l'interpolation
dblTxInf = TabData(j - 1)
dblTxSup = TabData(j)
DateInf = TabMat(j - 1)
DateSup = TabMat(j)
DateCalc = TabDates(i)

'Calcul de l'interpolation
TabRetour(i) = ((DateCalc - DateInf) * dblTxSup + _
(DateSup - DateCalc) * dblTxInf) / (DateSup - DateInf)
End If
Next


Case Else
'Les données sont des taux
For i = LBound(TabDates) To UBound(TabDates)
'boucle sur les dates à calculer
If TabDates(i) <= TabMat(1) Then
'Test des bornes, cas de la borne inférieure
TabRetour(i) = TabData(1)
ElseIf TabDates(i) >= TabMat(UBound(TabMat)) Then
'Test des bornes, cas de la borne supérieure
TabRetour(i) = TabData(UBound(TabMat))
Else
For j = 1 To UBound(TabMat)
'On rehcerche la première date supérieure à
'la date calculée
If TabMat(j) > TabDates(i) Then
Exit For
End If
Next
'Données utilisées pour l'interpolation
dblTxInf = TabData(j - 1)
dblTxSup = TabData(j)
DateInf = TabMat(j - 1)
DateSup = TabMat(j)
DateCalc = TabDates(i)

'Calcul de l'interpolation
TabRetour(i) = ((DateCalc - DateInf) * dblTxSup + _
(DateSup - DateCalc) * dblTxInf) / (DateSup - DateInf)
End If
Next
End Select

InterpolationLineaire = TabRetour

End Function

Function InterpolationCubique(TableauMaturites, TableauDonnees, _
DateCalculees, _
Optional EstFactActua As Boolean = False, Optional _
DateDeCalcul As Date)

'================================================= ============
'Cette fonction détermine à partir d'un vecteur de dates
'et d'un vecteur de données, les données interpolées
'par un polynome de degré 3 pour les dates du vecteur DatesCalculées
'================================================= ============

Dim TabMat
'Tableau des maturités des données sources
Dim TabData
'Données sources
Dim TabDates
'Dates à calculer
Dim i As Integer, j As Integer
'Variables de boucle
Dim TabRetour
'Données renvoyées
Dim MatriceDate(4, 4) As Double
'Matrice des coefficients des paramètres
Dim MatDateInv As Variant
'Matrice inverse de MatriceDate
Dim VecTaux(4, 1)
'Vecteur des données solutions du système
Dim VecParam
'Vecteur des paramètres calculés


'Conversion des arguments en tableau
TabMat = CTableau(TableauMaturites)
TabData = CTableau(TableauDonnees)
TabDates = CTableau(DateCalculees)

'Dimensionnement des tableaux de retour
ReDim TabRetour(LBound(TabDates) To UBound(TabDates))


For i = LBound(TabDates) To UBound(TabDates)
'Boucle sur les dates à calculer

If TabDates(i) <= TabMat(2) Then
'Test des bornes, cas de la borne inférieure
TabRetour(i) = InterpolationLineaire(TabMat, TabData, _
TabDates(i), _
EstFactActua, DateDeCalcul)(1)
ElseIf TabDates(i) >= TabMat(UBound(TabMat) - 1) Then
'Test des bornes, cas de la borne supérieure
TabRetour(i) = InterpolationLineaire(TabMat, TabData, _
TabDates(i), _
EstFactActua, DateDeCalcul)(1)
ElseIf UBound(TabMat) < 4 Then
'Pas assez de données pour éffectuer une interpolation cubique
TabRetour(i) = InterpolationLineaire(TabMat, TabData, _
TabDates(i), _
EstFactActua, DateDeCalcul)(1)
Else
For j = 3 To UBound(TabMat) - 2
'On recherche la première date supérieure à
'la date calculée
If TabMat(j) > TabDates(i) Then
Exit For
End If
Next

'On renseigne la matrice de date
MatriceDate(1, 1) = CDbl(TabMat(j - 2) ^ 3)
MatriceDate(1, 2) = TabMat(j - 2) ^ 2
MatriceDate(1, 3) = CDbl(TabMat(j - 2))
MatriceDate(1, 4) = 1
MatriceDate(2, 1) = CDbl(TabMat(j - 1) ^ 3)
MatriceDate(2, 2) = TabMat(j - 1) ^ 2
MatriceDate(2, 3) = CDbl(TabMat(j - 1))
MatriceDate(2, 4) = 1
MatriceDate(3, 1) = CDbl(TabMat(j) ^ 3)
MatriceDate(3, 2) = TabMat(j) ^ 2
MatriceDate(3, 3) = CDbl(TabMat(j))
MatriceDate(3, 4) = 1
MatriceDate(4, 1) = CDbl(TabMat(j + 1) ^ 3)
MatriceDate(4, 2) = TabMat(j + 1) ^ 2
MatriceDate(4, 3) = CDbl(TabMat(j + 1))
MatriceDate(4, 4) = 1

'On renseigne le vecteur de données
VecTaux(1, 1) = TabData(j - 2)
VecTaux(2, 1) = TabData(j - 1)
VecTaux(3, 1) = TabData(j)
VecTaux(4, 1) = TabData(j + 1)

'Inversion de la matrice de date
MatDateInv = Application.WorksheetFunction.MInverse _
(MatriceDate)

'Résolution du système
VecParam = Application.WorksheetFunction.MMult(MatDateInv, _
VecTaux)

'Calcul de la valeur interpolée
TabRetour(i) = VecParam(1, 1) * TabDates(i) ^ 3 + _
VecParam(2, 1) * TabDates(i) ^ 2 + _
VecParam(3, 1) * TabDates(i) + _
VecParam(4, 1)
End If
Next

InterpolationCubique = TabRetour

End Function

Function Interpolation(TableauMaturites, TableauDonnees, DateCalculees, _
Optional TypeInterpolation As Boolean = False, _
Optional TypeDonnees As Boolean = False, Optional _
DateDeCalcul As Date)
'================================================= ============
'Cette fonction détermine à partir d'un vecteur de dates
'et d'un vecteur de données, les données interpolées
'pour les dates du vecteur DateCalculées
'================================================= ============
Select Case TypeInterpolation

Case True
Interpolation = InterpolationLineaire(TableauMaturites, _
TableauDonnees, DateCalculees, TypeDonnees, DateDeCalcul)
Case Else
Interpolation = InterpolationCubique(TableauMaturites, _
TableauDonnees, DateCalculees, TypeDonnees, DateDeCalcul)
End Select


End Function

Function CourbeActualisation(DateDeCalcul As Date, TableauDatesValeurs, _
TableauMaturites, TableauDonnees, _
TableauTypeDonnees, TableauBases)

'====================================
'Cette fonction construit une courbe
'de facteurs d'actualisation
'à partir d'instruments de marché
'====================================

Dim TabDatesVal 'Tableau des dates de valeurs des données
Dim TabDatesMat 'Tableau des dates de maturités des données
Dim TabData 'Tableau des données
Dim TabDataType 'Tableau des types de données
Dim TabDataBase 'Tableau des bases de données
Dim TabFacActu() 'Tableau des facteurs d'actualisation
Dim TabDateFacActu() 'Tableau des dates des facteurs d'actualisation
Dim sTypeData As String 'Type de la donnée courante
Dim i As Integer, j As Integer 'Variable de boucle
Dim iTailleTab As Integer 'Taille des vecteurs TabFacActu et TabDateFacActu
Dim dblStub As Double 'Taux courant jusqu'à la date de valeur d'un contrat future
Dim dblCompoFut As Double 'Composition de dblStub et du future
Dim dblFrac1 As Double, dblFrac2 As Double 'Fraction d'années
Dim TabFluxOblg 'Vecteur des dates de flux d'un instrument PAR
Dim TabFAOblg 'Facteurs d'actualisation aux dates de TabFluxOblg
Dim dblSum As Double 'Variable de somme
Dim TableauSortie() As Double 'Données renvoyées
Dim dblFAtp As Double 'Facteur d'actualisation en cours de calcul


'Conversion des arguments en tableaux
TabDatesVal = CTableau(TableauDatesValeurs)
TabDatesMat = CTableau(TableauMaturites)
TabData = CTableau(TableauDonnees)
TabDataType = CTableau(TableauTypeDonnees)
TabDataBase = CTableau(TableauBases)

'Dimensionnement du tableau de retour
ReDim TableauSortie(3, UBound(TabDataType))

'Vérification de la cohérence des données
'il faut que tous les vecteurs comportent
'le même nombre de lignes
If UBound(TabDatesVal) <> UBound(TabDatesMat) Or UBound(TabDatesVal) _
<> UBound(TabData) _
Or UBound(TabDatesVal) <> UBound(TabDataType) Or UBound(TabDatesVal) _
<> UBound(TabDataBase) Then
'il y a une erreur, on sort de la fonction
CourbeActualisation = TableauSortie
Exit Function
End If

'Boucle sur l'ensemble des instruments
For i = 1 To UBound(TabDataType)
'Traitement en fonction du type de données
sTypeData = TabDataType(i)
Select Case sTypeData


Case "MM"
'Taux monétaires (Money Market)
'Transformation en taux composé Exact/365
dblFAtp = ChangeTaux(DateDeCalcul, (TabDatesMat(i)), _
(TabData(i)), 1, 1, (TabDataBase(i)), 0, 1, "Exact/365")

Case "FUT"
'Instrument future
'composition avec le taux à sa date de départ
'et transformation en taux composé Exact/365
dblStub = Interpolation(TabDateFacActu, TabFacActu, _
TabDatesVal(i))(1)
'dblStub contient le taux allant de la date de calcul
'à la date de départ du future
dblFrac1 = FractionAnnee(DateDeCalcul, (TabDatesVal(i)), _
"Exact/365")

dblFrac2 = FractionAnnee((TabDatesVal(i)), (TabDatesMat(i)), _
(TabDataBase(i)))
'dblCompoFut contient la composition de dblStub
'avec le future
dblCompoFut = ((1 + dblStub) ^ dblFrac1) * (1 + _
(100 - TabData(i)) * dblFrac2 / 100)

dblFrac1 = FractionAnnee(DateDeCalcul, (TabDatesMat(i)), _
"Exact/365")

'Transformation en taux composé Exact/365
dblFAtp = dblCompoFut ^ (1 / dblFrac1) - 1

Case Else
'Instrument de type obligation ou swap

'Identification de la date des flux de l'instrument
TabFluxOblg = DatesDesFlux(DateDeCalcul, (TabDatesMat(i)), _
1, , 1)
'On cherche les facteurs d'actualisation aux dates de flux
TabFAOblg = Interpolation(TabDateFacActu, TabFacActu, _
TabFluxOblg)

dblFrac1 = FractionAnnee(DateDeCalcul, (TabFluxOblg(1)), _
(TabDataBase(i)))
dblFrac2 = FractionAnnee(DateDeCalcul, (TabFluxOblg(1)), _
"Exact/365")

'Application de la formule
'Calcul de la somme
'Le premier coupon est pondéré
dblSum = ((1 + TabData(i) * 100) ^ dblFrac1 - 1) / _
((1 + TabFAOblg(1)) ^ dblFrac2)

If UBound(TabFluxOblg) > 2 Then
'Si UBound(TabFluxOblg) = 2, alors c'est
'un instrument à 2 ans.
'On cherche le facteur d'actualisation à 2 ans,
'inutile de poursuivre la somme
For j = 2 To UBound(TabFluxOblg) - 1
dblFrac2 = FractionAnnee(DateDeCalcul, _
(TabFluxOblg(j)), "Exact/365")
dblSum = dblSum + TabData(i) * 100 / _
((1 + TabFAOblg(j)) ^ dblFrac2)
Next
End If

'Le facteur d'actualisation
dblFAtp = (100 - dblSum) / (100 + TabData(i) * 100)
dblFrac1 = FractionAnnee(DateDeCalcul, (TabDatesMat(i)), _
"Exact/365")

'Transformation en taux composé Exact/365
dblFAtp = (1 / dblFAtp) ^ (1 / dblFrac1) - 1
End Select

'On ajoute le nouveau facteur d'actualisation et sa date de maturité
'aux tableaux qui nous servent pour les interpolations
iTailleTab = iTailleTab + 1
ReDim Preserve TabFacActu(iTailleTab)
ReDim Preserve TabDateFacActu(iTailleTab)
TabDateFacActu(iTailleTab) = TabDatesMat(i)
TabFacActu(iTailleTab) = dblFAtp



'On renseigne le tableau de résultats
dblFrac1 = FractionAnnee(DateDeCalcul, (TabDatesMat(i)), "Exact/365")
TableauSortie(1, i) = TabDatesMat(i)
TableauSortie(2, i) = 1 / (1 + TabFacActu(iTailleTab)) ^ dblFrac1
TableauSortie(3, i) = TabFacActu(iTailleTab)

Next

CourbeActualisation = TableauSortie

End Function
 

stefen

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Salut à tous,
j'ai aussi le livre, mais il ne me suffit pas ! :p
Je dois determiner les écheanciers des crédits, c'est à dire pour un emprunt donné, un nombre d'echeance donné, et un type d'amortissement donné (constant, progressif, in fine...) je dois avoir en sortie les dates des remboursements, les interets et les montants des amortissements respectifs...

Si quelqu'un a deja travaillé sur un tel sujet, je veux bien de son aide !

Merci d'avance.
Stefen
 

user1110

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Bonjour tout le monde,

J'ai également acquis ce livre essentiellement pour avoir un aperçu de la programmation (script) qui concerne la finance. Mais comme vous, je suis coinçé au niveau de la courbe des taux depuis plusieurs semaines sans réussir à trouver un support autre les fichiers supplémentaires (qui reprennent la modif sur le type variant dans cette discussion)... De ce point de vue, l'ouvrage ne me paraît complet notamment du fait que es fichiers complémentaires comprennent une partie du code dans le livre. Pourquoi ne pas inclure l'ensemble du code? Mauvaise impression.

Bref, il n'y a d'autre choix que de se lancer dans le debugage. Alors pour la fonction Courbe actualisation que j'ai reprise de nombreuses fois sans succès, j'ai essayé avec un module sub au lieu d' un module fonction, au moins contrairement à l'emploi des fonction array dans Excel, on peut voir une partie du résultat et surtout vba montre la ligne qui ligne qui bug alors qu'une udf qui renvoie un array peut ne pas renvoyer l'endroit d'une erreur dans le code et simplement renvoyer #Value!

Voici la fonction qui bug de mon côté et je ne serais pas étonné que ce soit généralisé:

'Transformation en taux composé Exact/365
dblFAtp = (1 / dblFAtp) ^ (1 / dblFrac1) - 1

=runtime error 5
invalid procedure call or argument


Avec le module, j'ai commenté out dblFAtp = (1 / dblFAtp) ^ (1 / dblFrac1) - 1 que j'ai remplaçé avec une valeur aléatoire ex.: dblFAtp = dblFAtp = 0.3 et ça donne les même résultats que dans le livre jusqu'au 10 octobre 2011. Après ça part de travers, en fait pour simplifier, ça ne fonctionne que pour le produit Euribor...

Je suis coinçé à ce niveau, la fonction ne renvoie que #valeur...

J'ai également le même chose que certain ici, la fonction interpolation cubique ne fonctionne pas comme ç'est dit.

J'ai le sentiment que ce livre devrait s'appeler Calculer des dates avec Excel parce qu'au-delà des dates et en l'absence de référence officielle, le code manque de fiabilité.

Cdt,
 

stefen

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Salut,
Je vous conseil d'arreter de recopier le livre et de faire avec un bon cours de mathématiques financières. J'ai créé ma propre macro à partir de cours et elle fonctionne très bien, je retrouve les même resultat. Et je pense que ca sera plus clair pour vous plutot que de copier des macro qui utilises des matrices, c'est horrible.
 

user1110

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Bonjour,

Je suis d'accord en partie, travailler directement sur un livre de finance serait plus utile. Cependant, ce sont des livres souvent volumineux qui demande un certain investissement. En ce qui me concerne, je m'investis plutôt dans la programmation. Je suis comptable de formation, j'ai quelques bases en finance mais ce n'est pas l'essentiel.

Mais quand même je trouve que le code dans ce livre devraît être accessible entièrement sous forme numérique du point de vue du code présent. Si il y a bien eu écriture du code pour le livre ou pratique du code dans la réalité, le code pourraît être disponible sous forme de fichier.

Bon ce n'est pas non plus une grande histoire. Je veux dire aussi qu'il y a de très bonnes choses sur les dates et sur la partie finance applicable avec vba.

User
 

stefen

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Tu es comptable de formation, j'ai quelques questions à te poser, si tu as le temps de me répondre...
Je construis un tableau d'amortissement, avec la possibilité de faire des amortissements constants, in fine, échéances constantes, et amortissement progressif. Mon objectif est de construire les tableau d'amortissement, mais surtout derrière d'actualiser ces flux afin que la somme des flux actualisées, c'est à dire la somme des échéances actualisées soient égale au montant initial de l'emprunt. Mais je n'y arrive pas, je me trouve toujours avec un écart de quelques pourcent lorsque j'actualise avec des taux forward. Pourrais tu me dire ce qui pourrais faire foiré mon calcul ? J'y arrive avec l'amortissement constant et in fine, je retombe bien sur le montant avec aucun écart, mais pour l'échéance constante et l'amortissement progressif et bien je me retrouve avec un écart...
On peut aussi en parler en privé si tu prefère.
Merci d'avance
 

user1110

XLDnaute Nouveau
Re : construction d'une courbe d'actualisation avec VBA

Salut Stefen,

Ta question concerne beaucoup plus la partie finance d'une formation de comptable, d'expert-comptable, commissaire aux comptes... La partie finance ne dépasse pas 25% de la formation en temps de travail en effet le travail d'un comptable se situe dans les faits passés, ce qui est toujours la même chose, c'est pourquoi je me suis investi dans la programmation. Il y a des questions sur les flux actualisés Van et Tri essentiellement dans la partie finance d'entreprise ou gestion de trésorerie mais c'est pour la plupart l'application du cours. Je n'ai pas le souvenir d'avoir vu une définition du taux forward dans cette formation.

Je peux voir ce que tu veux faire dans ta question, seulement c'est au niveau du taux forward. Je ne vois pas pourquoi tu actualises avec un taux forward. Tu veux appliquer plusieurs taux forward? Je prends l'exemple du livre, a première vue la fonction TauxForward ne renvoie qu'un taux. Si tu m'en dire plus mais mon niveau très limité dans ce domaine j'avoue ne te sera pas probablement utile. Mais je peux essayer de comprendre si tu veux prendre le temps.

Merci,
Pascal
 

Discussions similaires