Excel : Comment résoudre une référence circulaire?

Publié le 09 décembre 2012
par Francis Paquet M.Sc., EEE

Excel : Comment résoudre une référence circulaire?

Voici une question fréquemment posée par nos clients: “Comment résoudre une référence circulaire dans le cas du calcul d’une marge de crédit utilisée pour payer les impôts et les intérêts sur la marge de crédit”?

 

Avertissement: Nous vous proposons ci-bas une résolution théorique de ce problème. Toutefois, sachez qu’en réalité, le traitement d’une référence circulaire dans le cas d’une marge de crédit est un peu plus complexe, puisqu’il faut notamment tenir compte des plafonds et autres subtilités semblables.

 

Référence circulaire : Cas d’une marge de crédit

 

Préparation des données

 

D’abord, posons:

  • Intérêt sur la marge de crédit =  a
  • BAII = Bénéfice Avant Intérêt et Impôt = b
  • BAI  = Bénéfice Avant Impôt = d
  • Impôt = c
  • TI = Taux d’impôt
  • Autres paiements d’intérêts = e
  • Position d’ouverture de la marge de crédit  = o
  • Position de fermeture de la marge de crédit = f
  • Trésorerie disponible pour rembourser marge de crédit = v
  • TIMC = Taux d’intérêt de la marge de crédit
.
Donc, on a:
  • BAI = b – e – a
  • Impôt = c = d  * TI, ou, en remplaçant par le développement de d,
  • c = (b – e – a) * TI
  • c = (b – e ) * TI –  a * TI
  • Bénéfice net = dc
.
L’astuce consistera ensuite à isoler a.

 

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

 

Trésorerie disponible pour rembourser la marge de crédit:

  • v = -(Encaisse d’ouverture (probablement zéro) + Variation de la trésorerie avant intérêt sur la marge de crédit et avant impôt)
♦♦♦ Dans l’équation ci-dessus, comme la marge de crédit est présentée en montant positif, v a donc un signe inverse à la trésorerie disponible. Si l’entreprise peut rembourser $1M sur la marge de crédit, v = -$1M.

.

♦♦♦ Si la valeur absolue de v est supérieure à o, on va prendre o comme variation et ça simplifie le problème! Sinon, on va prendre v comme variation (en plus ou en moins) de la marge de crédit.

 

  • Position de fermeture = f = o + v + a + c. L’intérêt se calcule sur le solde moyen de la période, soit (o + f) / 2.

 

À partir de là, on a assez d’information pour résoudre l’équation qui va nous permettre de calculer a, le montant d’intérêt sur la marge de crédit.

 

Résolution de l’équation

 

  • a  = (o + f) / 2 * TIMC
    • où f = o + v + a + c
  • a  =( o + o + v + a  + c ) / 2 * TIMC
  • a = TIMC * ( o + v/2) + TIMC / 2 * a + TIMC / 2 * c
    • où c = (b – e ) * TI –  a * TI
  • a = TIMC * ( o + v/2) + TIMC / 2 * a + TIMC / 2 * [(b – e) * TI –  a * TI]

 

Si on met maintenant en évidence TIMC et qu’on isole les termes en a, on obtient:

  •  a = TIMC * [o + v/2 + (b – e )/2 * TI] + a  *TIMC /2 * (1 – TI)
  • a  * [ 1- TIMC/2 * (1 – TI)] = TIMC * [o + v/2 + (b – e )/2 * TI]
  •  = TIMC * [o + v/2 + (b – e )/2 * TI] /[ 1- TIMC/2 * (1 – TI)]

 

Une fois que a est connu, c, d et f se calculent aisément. Bien sûr, pour cela, il faut être un brin à l’aise avec l’algèbre!

 

Application de l’équation

Voici un exemple de résultat obtenu à partir de l’équation démontrée ci-dessus.

Calcul de référence circulaire dans Excel

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

    

Formation complémentaire

Afin d’approfondir vos connaissances avec la modélisation financière dans Excel, nous vous recommandons notre formation  Excel – Modélisation financière (niveau 1).

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Le-CFO-masque_Commentaires-Excel-Mod-fin-v2
CFO-Masque_Formations-en-ligne_FBLe CFO masqué offre un vaste choix de formations en ligne en informatique décisionnelle avec Excel et Power BI. Si vous désirez organiser des formations de groupe privées, faites nous simplement parvenir un courriel à info@lecfomasque.com.

Laisser un commentaire

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

Scroll to Top