Comment fonctionnent les calculs itératifs?

Publié le 16 juillet 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Calculs itératifs

Comment fonctionnent les 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_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

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

Scroll to Top