Calculer le taux de rendement interne, Excel VBA

Calculer le taux de rendement interne, Excel VBA

Le taux de rendement interne (TRI) est un indicateur clé en finance qui permet de mesurer la rentabilité d’un investissement sur une période donnée. En d’autres termes, le TRI est le taux d’actualisation qui annule la valeur nette présente (VNP) des flux de trésorerie d’un projet d’investissement. Ce taux permet donc de déterminer si un projet ou un investissement est rentable. 

Voici un exemple de code détaillé en VBA Excel pour calculer le Taux de Rendement Interne (TRI) d’un investissement à partir de flux de trésorerie. 

Étapes avant de commencer : 

1. Préparer les données : Vous devez entrer les flux de trésorerie dans une colonne d’Excel (par exemple, de A2 à A7). 
2. Ajouter le code VBA : Accédez à l’éditeur VBA en appuyant sur Alt + F11, puis insérez un nouveau module (via Insertion > Module). 

Code VBA pour calculer le TRI 

Function TauxRendementInterne(flux As Range) As Double 
    Dim guess As Double 
    Dim taux As Double 
    Dim npv As Double 
    Dim tolerance As Double 
    Dim iteration As Integer 
    Dim maxIterations As Integer 
    ' Initialisation des variables 
    guess = 0.1 ' Taux de départ (10%) 
    maxIterations = 100 ' Nombre maximum d'itérations 
    tolerance = 0.00001 ' Tolérance pour déterminer la précision du résultat 
    ' On commence la recherche du taux qui annule la VNP 
    For iteration = 1 To maxIterations 
        npv = 0 ' Reset de la VNP à chaque itération 
        ' Calcul de la VNP pour le taux courant 
        For i = 1 To flux.Count 
            npv = npv + flux.Cells(i).Value / (1 + guess) ^ (i - 1) 
        Next i 
        ' Si la VNP est proche de zéro, on a trouvé notre TRI 
        If Abs(npv) < tolerance Then 
            TauxRendementInterne = guess 
            Exit Function 
        End If 
        ' Ajustement du taux selon la direction de la VNP 
        guess = guess - npv / Derivative(flux, guess) 
    Next iteration 
    ' Si aucune solution n'est trouvée, on retourne une valeur d'erreur 
    TauxRendementInterne = CVErr(xlErrNA) 
End Function 
Function Derivative(flux As Range, guess As Double) As Double 
    ' Fonction pour calculer la dérivée de la VNP par rapport au taux 
    Dim epsilon As Double 
    Dim npv1 As Double 
    Dim npv2 As Double 
    Dim derivative As Double 
    epsilon = 0.00001 ' Petite valeur pour calculer la dérivée 
    npv1 = 0 
    npv2 = 0 
    ' Calcul de la VNP pour deux taux légèrement différents 
    For i = 1 To flux.Count 
        npv1 = npv1 + flux.Cells(i).Value / (1 + guess) ^ (i - 1) 
        npv2 = npv2 + flux.Cells(i).Value / (1 + guess + epsilon) ^ (i - 1) 
    Next i 
    ' Calcul de la dérivée par différence finie 
    derivative = (npv2 - npv1) / epsilon 
    Derivative = derivative 
End Function

Explications du code 

1. Fonction TauxRendementInterne : 

• Cette fonction prend en entrée une plage de cellules contenant les flux de trésorerie. 

• Le taux de rendement interne est calculé en utilisant une méthode d’approximation itérative (méthode de Newton-Raphson), où nous ajustons progressivement le taux jusqu’à ce que la valeur nette présente (VNP) soit proche de zéro. 

• Le taux initial (guess) est choisi arbitrairement à 10 % et peut être ajusté selon les besoins. 

• La tolérance permet de définir la précision des résultats, ici fixée à 0.00001. 

• Le nombre maximal d’itérations est défini à 100. 

2. Fonction Derivative : 

• Cette fonction calcule la dérivée de la VNP par rapport au taux de rendement. Elle est utilisée pour ajuster le taux lors de l’itération. La dérivée est calculée par une différence finie, c’est-à-dire en évaluant la VNP à deux valeurs très proches du taux actuel. 

Comment utiliser le code dans Excel 

1. Entrez vos flux de trésorerie dans une colonne d’Excel (par exemple, de A2 à A7). 
2. Dans une cellule vide, utilisez la fonction TauxRendementInterne que vous avez définie en VBA. Par exemple, si vos flux sont dans la plage A2:A7, vous pouvez entrer la formule suivante dans une cellule vide : 

=TauxRendementInterne(A2:A7) 

Exemple : 

Si vos flux de trésorerie sont les suivants : 

• Année 0 (Investissement initial) : -1000 € 

• Année 1 : 300 € 

• Année 2 : 400 € 

• Année 3 : 500 € 

• Année 4 : 600 € 

Les flux dans Excel seront : 

A2: -1000 
A3: 300 
A4: 400 
A5: 500 
A6: 600 

En entrant la formule =TauxRendementInterne(A2:A6) dans une cellule, vous obtiendrez le taux de rendement interne correspondant. 

Points à vérifier : 

• Si le TRI ne converge pas (par exemple, si les flux sont trop complexes), il se peut que l’algorithme n’arrive pas à trouver une solution. Vous pouvez essayer de modifier le taux initial (guess) ou la tolérance pour améliorer la convergence. 

Ce code peut être adapté pour des cas plus complexes, comme des flux de trésorerie irréguliers, mais cette approche de base vous donne une bonne base pour le calcul du TRI en VBA.