Calcul de TRI (taux de rendement interne) dans Excel

Le tutoriel montre comment calculer l'IRR d'un projet dans Excel avec des formules et la fonction de recherche d'objectif. Vous apprendrez également comment créer un modèle de taux de retour interne pour effectuer automatiquement tous les calculs IRR.

Lorsque vous connaissez le taux de rendement interne d'un investissement proposé, vous pensez peut-être que vous avez tout ce dont vous avez besoin pour l'évaluer - plus le TRI est gros, mieux c'est. En pratique, ce n'est pas si simple. Microsoft Excel fournit trois fonctions différentes pour trouver le taux de rendement interne, et vraiment comprendre ce que vous calculez avec TRI sera très utile.

• Qu'est-ce que le taux de rendement interne (IRR)?
• Calcul de TRI manuel dans Excel
• Comment calculer les TRI avec des formules Excel
  • Formule IRR pour calculer le taux de rendement interne
  • Formule XIRR pour trouver IRR pour les flux de trésorerie avec un timing inégal
  • Formule mir pour obtenir un TRI modifié
• Taux de rendement interne Calculatrice / modèle
• Comment faire IRR avec recherche de but

Qu'est-ce que IRR?

Le taux de rendement interne (TRI) est une métrique couramment utilisée pour estimer la rentabilité d'un investissement potentiel. Parfois, il est également appelé taux de rendement de trésorerie réduit ou taux de rendement économique .

Techniquement, l'IRR est le taux d'actualisation qui rend la valeur actuelle nette de tous les flux de trésorerie (entrées et sorties) à partir d'un certain investissement égal à zéro.

Le terme «interne» indique que le TRI prend en compte uniquement les facteurs internes; Des facteurs externes tels que l'inflation, le coût du capital et les divers risques financiers sont exclus du calcul.

Que révèle le TRI?

Dans la budgétisation des capitaux, l'IRR est largement utilisée pour évaluer la rentabilité d'un investissement potentiel et classer plusieurs projets. Le principe général est aussi simple que celui-ci: plus le taux de rendement interne est élevé, plus le projet est attrayant.

Lors de l'estimation d'un seul projet, les analystes financiers comparent généralement le TRI au coût moyen pondéré d'une entreprise du taux de capital ou d'obstacles , ce qui est le taux de rendement minimum sur un investissement que l'entreprise peut accepter. Dans une situation hypothétique, lorsque l'IRR est le seul critère pour prendre une décision, un projet est considéré comme un bon investissement si son TRI est supérieur au taux d'obstacle. Si le TRI est inférieur au coût du capital, le projet doit être rejeté. En pratique, il existe de nombreux autres facteurs qui influencent la décision telle que la valeur actuelle nette (VAN), la période de récupération, la valeur de rendement absolu, etc.

Limitations de TRI

Bien que l'IRR soit une méthode très populaire pour évaluer les projets d'immobilisations, il a un certain nombre de défauts inhérents qui peuvent conduire à des décisions sous-optimales. Les principaux problèmes avec l'IRR sont:

• Mesure relative . L'IRR considère le pourcentage mais pas la valeur absolue, en conséquence, il peut favoriser un projet avec un taux de rendement élevé mais une très faible valeur en dollars. Dans la pratique, les entreprises peuvent préférer un grand projet avec un TRI inférieur à un petit avec un TRI plus élevé. À cet égard, la VAN est une meilleure métrique car elle considère un montant réel gagné ou perdu en entreprenant un projet.
• Le même taux de réinvestissement . L'IRR suppose que tous les flux de trésorerie générés par un projet sont réinvestis au taux égal à l'IRR lui-même, ce qui est un scénario très irréaliste. Ce problème est résolu par Mirl qui permet de spécifier différents taux de financement et de réinvestissement.
• Plusieurs résultats . Pour les projets avec des flux de trésorerie positifs et négatifs alternés, plus d'un TRI peut être trouvé. Le problème est également résolu dans Mirm, qui est conçu pour produire un seul taux.

Malgré ces lacunes, l'IRR continue d'être une mesure importante de la budgétisation des capitaux et, à tout le moins, vous devriez jeter un coup d'œil sceptique avant de prendre une décision d'investissement.

Calcul IRR dans Excel

Comme le taux de rendement interne est le taux d'actualisation auquel la valeur actuelle nette d'une série donnée de flux de trésorerie est égale à zéro, le calcul de l'IRR est basé sur la formule traditionnelle de la VAN:

Si vous n'êtes pas très familier avec la notation de sommation, la forme étendue de la formule IRR peut être plus facile à comprendre:

Où:

• CF ₀ - L'investissement initial (représenté par un nombre négatif)
• Cf ₁ , cf ₂ … cf _n - flux de trésorerie
• I - le numéro de période
• N - Périodes totales
• IRR - Taux de rendement interne

La nature de la formule est telle qu'il n'y a pas de moyen analytique de calculer l'IRR. Nous devons utiliser l'approche "Deviner and Check" pour la trouver. Pour mieux comprendre le concept du taux de rendement interne, effectuons un calcul IRR à un exemple très simple.

Exemple : vous investissez 1000 $ maintenant et récupérez 500 $ et 660 $ au cours des 2 prochaines années. Quel taux d'actualisation rend la valeur actuelle nette zéro?

Comme notre première supposition, essayons le taux de 8%:

• Maintenant: PV = - 1 000 $
• Année 1: PV = 500 $ / (1 0,08) ¹ = 462,96 $
• Année 2: PV = 660 $ / (1 0,08) ² = 565,84 $

Additionnant ceux-ci, nous obtenons la VAN égale à 28,81 $:

Oh, même pas près de 0. Peut-être une meilleure supposition, disons 10%, peut changer les choses?

• Maintenant: PV = - 1 000 $
• Année 1: PV = 500 $ / (1 0,1) ¹ = 454,55 $
• Année 2: PV = 660 $ / (1 0,1) ² = 545,45 $
• NPV: -1000 454,55 $ 545,45 $ = 0,00 $

C'est ça! À 10% de taux d'actualisation, la VAN est exactement 0. Ainsi, le TRI pour cet investissement est de 10%:

C'est ainsi que vous calculez manuellement le taux de rendement interne. Microsoft Excel, d'autres logiciels et divers calculatrices de TRI en ligne s'appuient également sur cette méthode d'essai et d'erreur. Mais contrairement aux humains, les ordinateurs peuvent faire plusieurs itérations très rapidement.

Comment calculer le TRI dans Excel avec des formules

Microsoft Excel fournit 3 fonctions pour trouver le taux de rendement interne:

• IRR - La fonction la plus couramment utilisée pour calculer le taux de rendement interne pour une série de flux de trésorerie qui se produisent à intervalles réguliers .
• XIRR - trouve TRI pour une série de flux de trésorerie qui se produisent à des intervalles irréguliers . Parce qu'il prend en considération les dates exactes des paiements, cette fonction offre une meilleure précision de calcul.
• Mir - Renvoie le taux de rendement interne modifié , qui est une variante de TRI qui considère à la fois le coût d'emprunt et les intérêts composés reçus sur le réinvestissement des flux de trésorerie positifs.

Vous trouverez ci-dessous les exemples de toutes ces fonctions. Par souci de cohérence, nous utiliserons le même ensemble de données dans toutes les formules.

Formule IRR pour calculer le taux de rendement interne

Supposons que vous envisagez un investissement de 5 ans avec les flux de trésorerie en B2: B7. Pour déterminer le TRI, utilisez cette formule simple:

=IRR(B2:B7)

Note. Pour que la formule IRR fonctionne correctement, assurez-vous que vos flux de trésorerie ont au moins un négatif (sortie) et une valeur positive (entrée), et toutes les valeurs sont répertoriées sur ordre chronologique .

Pour plus d'informations, veuillez consulter la fonction Excel IRR.

Formule Xirr pour trouver IRR pour les flux de trésorerie irréguliers

En cas de flux de trésorerie avec un moment inégal, l'utilisation de la fonction IRR peut être risquée, car elle suppose que tous les paiements se produisent à la fin d'une période et que toutes les périodes sont égales. Dans ce cas, Xirr serait un choix plus sage.

Avec les flux de trésorerie en B2: B7 et leurs dates en C2: C7, la formule irait comme suit:

=XIRR(B2:B7,C2:C7)

Notes:

• Bien que la fonction XIRR ne nécessite pas nécessairement des dates dans l'ordre chronologique, la date du premier flux de trésorerie (investissement initial) devrait être la première dans le tableau.
• Les dates doivent être fournies sous forme de dates d'excel valides ; La fourniture de dates au format texte met Excel au risque de les mal interpréter.
• La fonction Excel Xirr utilise une formule différente pour arriver à un résultat. La formule XIRR réduit les paiements ultérieurs sur la base d'une année de 365 jours, en conséquence, XIRR renvoie toujours un taux de rendement interne annualisé .

Pour plus de détails, veuillez consulter la fonction Excel Xirr.

Formule mir pour élaborer le TRI modifié

Pour gérer une situation plus réaliste lorsque les fonds du projet sont réinvestis à un taux plus proche du coût du capital d'une entreprise, vous pouvez calculer le taux de rendement interne modifié en utilisant une formule miraire:

=MIRR(B2:B7,E1,E2)

Lorsque B2: B7 sont des flux de trésorerie, E1 est le taux de financement (le coût de l'emprunt de l'argent) et E2 est le taux de réinvestissement (l'intérêt reçu sur le réinvestissement des bénéfices).

Note. Étant donné que la fonction du miroir Excel calcule l'intérêt des composés sur les bénéfices, son résultat peut être sensiblement différent de ceux des fonctions IRR et XIRR.

IRR, XIRR et MIR - Quel est le meilleur?

Je crois que personne ne peut donner une réponse générique à cette question car la base théorique, les avantages et les inconvénients des trois méthodes sont toujours contestés parmi les universitaires financiers. Peut-être que la meilleure approche serait de faire les trois calculs et de comparer les résultats:

Généralement, il est considéré que:

• XIRR fournit une meilleure précision de calcul que le TRI car il prend en considération les dates exactes des flux de trésorerie.
• L'IRR donne souvent une évaluation trop optimiste de la rentabilité du projet, tandis que Mirl donne une image plus réaliste.

Calculatrice IRR - Modèle Excel

Si vous devez effectuer régulièrement le calcul de l'IRR dans Excel, la mise en place d'un modèle de taux de retour interne peut vous faciliter la vie.

Notre calculatrice comprendra les trois formules (IRR, XIRR et MIR) afin que vous n'aurez pas à vous soucier du résultat le plus valide, mais pourrait les considérer tous.

1. Entrez les flux de trésorerie et les dates en deux colonnes (A et B dans notre cas).
2. Entrez le taux de financement et réinvestissez le taux de 2 cellules distinctes. Facultativement, les nommez vend respectivement finance_rate et reinvest_rate .
3. Créez deux gammes définies dynamiques, nommées Cash_flows et dates .

   En supposant que votre feuille de travail est nommée Sheet1 , le premier flux de trésorerie (investissement initial) se trouve dans la cellule A2, et la date du premier flux de trésorerie est dans la cellule B2, faites les gammes nommées en fonction de ces formules:

   Cash_flows:

   =OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),1)

   Dates:

   =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1)

   Les étapes détaillées peuvent être trouvées dans la façon de créer une gamme dynamique nommée dans Excel.

4. Utilisez les noms que vous venez de créer comme arguments des formules suivantes. Veuillez noter que les formules peuvent être saisies dans n'importe quelle colonne autre que A et B, qui sont réservées exclusivement pour les flux de trésorerie et les dates, respectivement.

   =IRR(Cash_flows)

   =XIRR(Cash_flows, Dates)

   =MIRR(Cash_flows, Finance_rate, Reinvest_rate)

Fait! Vous pouvez désormais saisir n'importe quel nombre de flux de trésorerie dans la colonne A, et vos formules de rendement internes dynamiques recalculent en conséquence:

Par précaution contre les utilisateurs imprudents qui peuvent oublier de remplir toutes les cellules d'entrée requises, vous pouvez envelopper vos formules dans la fonction IFERROR pour éviter les erreurs:

=IFERROR(IRR(Cash_flows), "")

=IFERROR(XIRR(Cash_flows, Dates), "")

=IFERROR(MIRR(Cash_flows, Finance_rate, Reinvest_rate), "")

Veuillez garder à l'esprit que si les cellules finance_rate et / ou reinvest_rate sont vides, la fonction d'excel miraire suppose qu'elles sont égales à zéro.

Comment faire irr ir dans excel avec le but cherche

La fonction Excel IRR effectue uniquement 20 itérations pour arriver à un taux et XIRR effectue 100 itérations. Si, après cela, de nombreuses itérations, un résultat précis à 0,00001% n'est pas trouvé, un #Num! une erreur est renvoyée.

Si vous recherchez plus de précision pour votre calcul IRR, vous pouvez forcer Excel à faire plus de 32 000 itérations en utilisant la fonction de recherche d'objectif, qui fait partie de l'analyse.

L'idée est d'obtenir un objectif pour trouver un taux de pourcentage qui rend la VAN égale à 0. Voici comment:

1. Configurez les données source de cette manière:
   • Entrez les flux de trésorerie dans une colonne (B2: B7 dans cet exemple).
   • Mettez le TRI attendu dans une cellule (B9). La valeur que vous entrez n'a pas vraiment d'importance, il vous suffit de "nourrir" quelque chose à la formule NPV, alors mettez tout pourcentage qui vous vient à l'esprit, disons 10%.
   • Entrez la formule NPV suivante dans une autre cellule (B10): =NPV(B9,B3:B7) B2

   

2. Dans l'onglet Données , dans le groupe de prévisions , cliquez sur What If Analysis > Objectif Seek…
3. Dans la boîte de dialogue de recherche de but , définissez les cellules et les valeurs à tester:
   • Définir la cellule - la référence à la cellule NPV (B10).
   • Pour valeur - Type 0, qui est la valeur souhaitée pour la cellule définie.
   • En modifiant la cellule - la référence à la cellule IRR (B9).

   Une fois terminé, cliquez sur OK .

   

4. La boîte de dialogue d' état de recherche d'objectif apparaîtra et vous fera savoir si une solution a été trouvée. En cas de succès, la valeur dans la cellule IRR sera remplacée par une nouvelle qui rend NPV Zero.

   Cliquez sur OK pour accepter la nouvelle valeur ou annuler pour récupérer celui d'origine.

   

De la même manière, vous pouvez utiliser la fonction de recherche d'objectif pour trouver XIRR. La seule différence est que vous devrez utiliser la formule XNPV au lieu de NPV.

Note. La valeur IRR trouvée avec la recherche d'objectifs est statique , elle ne recalcule pas dynamiquement comme le font les formules. Après chaque modification des données d'origine, vous devrez répéter les étapes ci-dessus pour obtenir un nouvel TRI.

C'est comment faire le calcul de l'IRR dans Excel. Pour examiner de plus près les formules discutées dans ce tutoriel, vous êtes invités à télécharger notre exemple de classeur ci-dessous. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Calculatrice Excel Irr - Exemples (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!