En modélisation financière, plusieurs utilisateurs d’Excel “forcent” des références circulaires, i.e. qu’ils demandent à Excel d’effectuer un calcul itératif et de soumettre un résultat satisfaisant, à l’intérieur de certaines balises. Toutefois, il faut être très prudent avec les références circulaires car elles rendent les modèles financiers très instables. C’est pourquoi, personnellement, je recommande toujours de résoudre les problématiques de références circulaires par un calcul algébrique. Cet article a néanmoins comme objectif de mieux vous faire comprendre comment fonctionnent les calculs itératifs dans Excel.
Activer la fonction de calculs itératifs
Afin de résoudre des références circulaires, vous pouvez vous rendre dans vos options Excel, dans le libellé Formules (ou Formulas) et cocher la case Permettre les calculs itératifs (Enable iterative calculation) dans la section des options de calculs. Par la suite, vous pourrez spécifier certains paramètres, soit un maximum d’itérations et un changement maximum.
Exemple de calcul itératif
Dans l’exemple ci-bas, on souhaite calculer une dépense d’intérêt de 10% applicable sur le solde de fin d’un compte de crédit rotatif (marge de crédit). Ainsi, la dépense d’intérêts se calcule en appliquant 10% sur le solde de fin de période de la marge de crédit (tel qu’illustré ci-bas)…
… mais en même temps, le solde de fin de la marge de crédit est de 10 (solde intial) plus les intérêts de la période en cours…. il y a donc une référence circulaire.
… et l’avoir se calcule ensuite en retranchant du solde de départ (30), le montant des intérêts de la période (puisque ceux-ci-viennent diminuer le bénéfice net de la période et donc les BNR au bilan).
Résolution algébrique
Tel que précisé en introduction, pour ma part, je préfère résoudre les références circulaires par l’algèbre, pour ne pas introduire d’instabilité dans mes modèles financiers. Voici comment je résolverais la situation précédente:
Comment fonctionnent les calculs itératifs?
Pour résoudre notre calcul sans passer par l’algèbre, il faudra permettre les calculs itératifs dans Excel (permettre les références circulaires). Dans ce cas, Excel tentera d’abord d’appliquer 10% sur 10, le solde de départ de la marge de crédit, pour obtenir 11. Ensuite, il divisera (11-10)=1 sur 11, ce qui donne 9.09%. Il poursuivra ensuite ses itérations, jusqu’à ce qu’il obtienne 11.1111 (dans le cas ou le maximum de changement serait fixé à 0.0001). En divisant (11.1111-10) sur 11.1111, on obtient 9.9999% soit tout près de 10% (écart inférieur à 0.0001). Il s’arrêtera donc de calculer à 11.1111.