Neil sait calculer un taux de rendement interne simple à l’aide de la fonction IRR. Il se demande cependant comment calculer un TRI sur une transaction plus complexe, comme des remboursements mensuels sur un terme de 36 mois où les 12 premiers mois sont sans intérêt et les 24 prochains mois à 5% d’intérêt.

Il est utile de comprendre ce que l’on entend par taux de rendement interne (TRI). Il s’agit, en soi, d’un taux d’intérêt (le rendement) en moyenne sur la durée du prêt. Ainsi, le taux d’intérêt appliqué au prêt n’entre pas vraiment en jeu. (Ceci est démontré par le fait que les paramètres de la fonction IRR d’Excel n’incluent pas de paramètre de taux d’intérêt.) La seule chose qui compte est le principal, le terme et le moment où les remboursements sont effectués.

Puisque Neil reçoit des remboursements mensuels réguliers, la fonction IRR est en effet celle à utiliser. Excel inclut également des variantes de la fonction IRR, mais elles ne sont probablement pas appropriées. La fonction MIRR calcule le TRI lorsque vous réinvestissez les remboursements. Il permet de spécifier un taux d’intérêt, mais ce taux est appliqué au réinvestissement des remboursements; ce n’est pas le taux d’intérêt que vous facturez à la personne qui vous rembourse.

L’autre fonction Excel liée à IRR est la fonction XIRR. Cela ne convient pas aux besoins de Neil car il est utilisé pour calculer le TRI lorsque les remboursements ne sont pas réguliers. Par exemple, vous ne pouvez obtenir des remboursements que certains mois de l’année. Puisque Neil reçoit des paiements mensuels réguliers, la fonction IRR est toujours la meilleure à utiliser.

Voyons comment cela fonctionnerait dans le cas de Neil, alors. Disons qu’il accorde un prêt de 50 000 $ à quelqu’un à un taux d’intérêt de 5%. Cet intérêt n’est applicable que pour les 24 derniers mois du prêt, car Neil indique que les 12 premiers mois sont sans intérêt. Ainsi, les paiements pour les 12 premiers mois seraient de 1 388,89 USD par mois, ce qui est calculé en divisant simplement 50 000 USD par 36.

Après cette première année, les paiements seraient de 1 462,38 $ parce que la personne obtient essentiellement un prêt de 33 333,32 $ (le solde restant, soit 2/3 du montant initial du prêt) à un taux d’intérêt de 5% pendant 24 mois.

Lors du calcul du TRI, vous avez donc 36 paiements calculés – 12 d’entre eux à 1 388,89 $, puis 24 d’entre eux à 1 462,38 $. Placez la valeur d’origine dans la cellule A1 (en tant que valeur négative) et les paiements (en tant que valeurs positives) dans les cellules A2: A37. Vous pouvez ensuite appliquer la fonction IRR à la plage de 37 valeurs. (Voir la figure 1.)

image

Figure 1. Calcul du TRI basé sur un prêt de 50 000 $.

Notez que le TRI est de 0,19%. Puisque les flux de trésorerie représentent des paiements mensuels, il s’agit du TRI par mois. Pour l’ajuster à un taux annuel, il suffit de le multiplier par 12 et vous obtenez un TRI annuel de 2,24%. C’est ce que gagne en fait Neil sur son prêt de 50 000 $. Il s’agit de l’intérêt moyen gagné sur la durée du prêt. Il s’avère être un peu plus bas que le taux d’intérêt de 5%, car un tiers de la durée du prêt est vraiment sans intérêt.

Il est à noter que tous ces calculs sont basés sur le fait que le billet est réellement sans intérêt pendant les 12 premiers mois. Si ce n’est pas le cas – si les intérêts sont simplement différés pour être remboursés au cours des 24 derniers mois – alors le résultat est bien différent, mais les calculs sont les mêmes. La personne qui rembourserait le prêt ne paierait toujours que 1 388,89 $ par mois pendant les 12 premiers mois. Cependant, étant donné que les intérêts sont toujours calculés, après la première année, le solde impayé est de 35 504,12 $.

Ce solde impayé est ensuite utilisé pour déterminer le paiement régulier pour les 24 mois restants, ce qui correspondrait à un paiement mensuel de 1 557,62 $. Le branchement de ces chiffres dans le même calendrier de remboursement permet de calculer un nouveau TRI de 0,42%, annualisé à exactement 5%. (Voir la figure 2.)

image

Figure 2. Calcul du TRI basé sur un prêt de 50 000 $, avec intérêts différés.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (4359) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.