Boostez vos investissements : maîtrisez le calcul du taux de rentabilité avec Excel en 10 minutes !

Dans ce tutoriel, nous je vais vous montrer comment calculer le taux de rentabilité interne (TIR) d’un projet sur Excel.

Que vous soyez novice ou expert en finance, ce tutoriel vous guidera pas à pas dans l'analyse financière pour des décisions d'investissement éclairées.

Nous y découvrirons plusieurs fonctions dédiées, afin que vous puissiez utiliser la bonne fonction, au bon moment !

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, je vous propose de nous placer dans la peau d’une personne qui envisage d'investir dans l'ouverture d'une franchise de café.

Pour cela, cette personne dispose d’un investissement initial de 300 000 €.

Voici le tableau permettant de retrouver les principaux éléments de cette opération financière :

Ce tableau représente les flux de trésorerie pour chaque année de l'investissement dans la franchise de café. L'investissement initial de 300 000 € est effectué en année 0, tandis que les coûts de franchise, les dépenses opérationnelles et les revenus de vente de café sont répertoriés pour les années suivantes.

À partir de ces données, nous pourrons calculer le taux de rentabilité interne (TIR) de ce projet d'investissement.

En effet, le TIR est une mesure fondamentale en analyse financière qui permet de déterminer le rendement d'un investissement en tenant compte du temps et de la valeur de l'argent.

Dit autrement, le taux de rentabilité interne (TIR) est le taux d'intérêt auquel la valeur actuelle des flux de trésorerie entrants égale la valeur actuelle des flux de trésorerie sortants d'un investissement.

En d'autres termes, c'est le taux qui rend la valeur nette actuelle (VNA) de tous les flux de trésorerie égale à zéro.

Le TIR est crucial pour évaluer la rentabilité d'un projet d'investissement. En calculant le TIR, nous pouvons déterminer si un investissement est viable et s'il offre un rendement satisfaisant par rapport au coût initial.

Cela permet aux investisseurs de prendre des décisions éclairées sur l'allocation de leurs ressources financières.

2. Calcul du TIR avec Excel

Maintenant que nous connaissons les bases théoriques du TIR, passons à la pratique en utilisant Excel pour effectuer le calculer de manière simple et rapide.

En effet, Excel offre une gamme d'outils puissants qui simplifient ce processus, notamment la fonction TRI().

Pour l’utiliser, nous allons commencer par ajouter une colonne supplémentaire sur le tableau, laquelle va nous permettre de déterminer le montant des flux de trésorerie de chacune des années, lesquels correspondent à la somme des revenus et coûts estimés pour chaque année.

Nous commençons par sélectionner la cellule G6 qui est la première cellule située sur la droite du tableau, dans laquelle nous saisissons le nom de cette colonne, par exemple « Flux net ».

Puis, dans la cellule située juste en dessous, nous allons calculer le montant net de ce flux, lequel correspond à la somme des valeurs de cette ligne.

Ces valeurs sont positives s’il s’agit d’un revenu et négatives s’il s’agit d’un coût :

 =SOMME(B7:F7) 

Ensuite, il ne reste plus qu’à étendre la formule sur toutes les cellules de la colonne :

Maintenant, plaçons-nous sur la cellule C14 afin d’y saisir la formule suivante :

 =TRI(G7:G12) 

Comme nous pouvons le constater ici, la syntaxe de la fonction est la suivante :

 =TRI(valeurs;[estimations]) 

Où :

• Valeurs : C'est la plage de cellules contenant les flux de trésorerie pour lesquels nous souhaitons calculer le TRI. Pour fonctionner, il est indispensable que cette plage de cellules contienne au moins une valeur positive et une valeur négative. Il est également essentiel de respecter l'ordre chronologique des mouvements de trésorerie dans cette plage. Si des cellules contiennent du texte, des valeurs logiques ou encore si elles sont vides, elles ne seront pas prises en compte dans le calcul.
• Estimations : Il s’agit d’un argument optionnel qui permet d’indiquer à Excel une estimation initiale du TIR. En effet, Excel utilise une méthode itérative pour calculer le TIR, ce qui signifie qu'il effectue des calculs répétés jusqu'à obtenir un résultat suffisamment précis. Parfois, la fonction peut ne pas converger vers un résultat après un 20 itérations successives. Dans ce cas, spécifier une estimation initiale peut aider à obtenir un résultat, mais dans la plupart des cas, cet argument n'est pas nécessaire et pourra être omis.

Excel calculera ensuite le TIR en fonction des valeurs des flux de trésorerie entrants et sortants pour chaque année, et nous pouvons constater ici que sur la période étudiée, celui-ci est négatif à hauteur de -3%, ce qui signifie que…

Voici quelques conseils pour être certain d’obtenir le bon résultat :

• Assurez-vous que les données sont correctement formatées dans Excel pour éviter les erreurs de calcul.
• Vérifiez que les signes des flux de trésorerie sont corrects : les revenus devraient être positifs tandis que les coûts devraient être négatifs.
• Si nécessaire, ajustez les estimations pour obtenir un TIR plus précis, en fournissant une valeur dans la fonction TRI.

3. Différence et relation avec la fonction VAN

Dans un tutoriel précédent, nous avions déjà eu l’occasion de découvrir une autre fonction Excel proche de la fonction TRI(), il s’agit de la fonction VAN().

Il s’agit d’une autre fonction financière qui permet de calculer la Valeur Actuelle Nette d’un projet et il est important de comprendre la différence entre ces deux fonctions, ainsi que leur relation :

• Comme nous venons de le voir, le TRI est le taux d'intérêt auquel la valeur actuelle des flux de trésorerie entrants égale la valeur actuelle des flux de trésorerie sortants d'un investissement. En d'autres termes, le TIR est le taux de rendement d'un investissement.
• La VAN quant à elle est la somme des flux de trésorerie futurs actualisés à un taux donné (généralement le coût du capital). Elle mesure la valeur nette de tous les flux de trésorerie générés par un investissement, actualisée à la date de l'investissement initial. Une VAN positive indique que l'investissement est rentable, tandis qu'une VAN négative signifie qu'il est non rentable.

Ainsi, le TIR calculé par la fonction TRI correspond au taux d'intérêt qui rend la VAN égale à zéro.

En d'autres termes, le taux de rentabilité interne calculé par la fonction TRI est le taux d’intérêt correspondant à une valeur actuelle nette égale à 0 (zéro).

La formule suivante montre la relation existante entre les fonctions VAN et TRI :

 =VAN(C14;G7:G12) 

Ici le résultat sera donc égal à zéro.

4. La fonction TRI.PAIEMENTS pour des données non uniformément espacées

La fonction TRI() que nous venons de découvrir permet uniquement de calculer le taux de rendement d’un projet pour lequel les périodes sont régulières, c’est-à-dire uniformes dans le temps.

Lorsque cette hypothèse n’est pas applicable, alors nous utiliserons plutôt la fonction TRI.PAIEMENTS.

Celle-ci est en effet une extension de la fonction TRI(), conçue pour traiter des séries de flux de trésorerie qui peuvent être espacés de manière irrégulière dans le temps.

La syntaxe de la fonction TRI.PAIEMENTS est légèrement différente de celle de la fonction TRI() car elle prend en compte à la fois les montants des flux de trésorerie et les dates auxquelles ces flux se produisent.

Voici la syntaxe de la fonction TRI.PAIEMENTS :

 =TRI.PAIEMENTS(montants_flux, dates_flux,  [estimation_taux]) 

Où :

• montants_flux : c'est le tableau ou la plage contenant les montants des flux de trésorerie associés à l'investissement. Chaque valeur dans cette plage doit être positive si elle représente un revenu ou un paiement perçu par le propriétaire de l'investissement, et négative si elle représente un paiement effectué par le propriétaire.
• dates_flux : c'est le tableau ou la plage contenant les dates correspondant aux flux de trésorerie dans montants_flux.
• estimation_taux (optionnel) : il s'agit d'une estimation du taux de rendement interne, similaire à l'argument d'estimation utilisé dans la fonction TRI().

Supposons maintenant un scénario où les coûts fixes et variables varient chaque année en raison de facteurs externes.

Dans ce nouvel exemple, les décaissements initiaux sont répartis en 3 versements :

• un premier versement de 50k€ en mai lors de la signature du bon de commande,
• un second du même montant en juillet pour le début des travaux
• et un troisième pour le solde à la fin des travaux en décembre

Ensuite, les coûts de franchises sont à régler au début de chaque année, et les revenus, ainsi que les coûts fixes et variables sont mensualisés.

Nous nous retrouvons donc bien avec des périodes non homogènes, et donc le résultat retourné par la fonction TRI() n’est pas correct :

Dans ce cas, nous pourrions utiliser la fonction TRI.PAIEMENTS pour calculer le taux de rendement interne de manière précise, en prenant en compte les flux de trésorerie à des dates spécifiques :

 =TRI.PAIEMENTS(G7:G69;A7:A69) 

Et dans ce cas, nous retrouvons bien un taux de rentabilité proche du taux précédent, sans être identique :

En utilisant la fonction TRI.PAIEMENTS, nous pouvons obtenir une évaluation plus précise du taux de rendement interne de notre investissement, en tenant compte de la nature irrégulière de nos flux de trésorerie.

Cela nous permet de prendre des décisions plus éclairées sur la viabilité et la rentabilité de notre projet d'investissement dans la franchise de café.