Comment fonctionnent les calculs itératifs?

Publié le 16 juillet 2014
par Sophie Marchand M.Sc.
Calculs itératifs

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.

Itérations

 

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)…

Calculs itératifs

 

… 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.

Calculs itératifs

 

… 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).

Calculs itératifs

 

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:

Itérations

 

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.

Calculs itératifs

 

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut