Comment créer une formule CAGR dans Excel

Découvrez comment calculer le taux de croissance annuel composé (TCAC) dans Excel à l'aide d'opérateurs ou de différentes fonctions Excel.

TCAC signifie Taux de croissance annuel composé, qui mesure le taux de croissance annuel (taux lissé) d'un investissement chaque année pendant un intervalle donné. Par exemple, supposons que vous ayez acheté des actions d'une valeur de 100 $ en 2011 et qu'elles valent 400 $ en 2021, le TCAC serait le taux auquel votre investissement dans cette action a augmenté chaque année.

Les marchés boursiers étant volatils, la croissance d'un investissement peut varier d'une année à l'autre. Le retour sur investissement peut augmenter ou diminuer. Le TCAC permet de lisser les rendements d'un investissement comme s'il avait augmenté à un taux équilibré chaque année.

Bien qu'il n'y ait pas de fonction CAGR dans Excel, il existe plusieurs façons de calculer le CAGR dans Excel. Cet article vous montre comment créer des formules CAGR claires et faciles à comprendre à l'aide de différentes fonctions Excel.

Comment créer un Taux de croissance annuel composé (TCAC) formule dans Excel

Le taux de croissance annuel composé (TCAC) est très utile pour les affaires, la planification financière, la modélisation financière et l'analyse des investissements. La formule du TCAC calcule la croissance annuelle d'un investissement qui peut être utilisée pour comparer à d'autres investissements.

Pour calculer le TCAC, vous avez besoin de trois entrées principales : la valeur de départ, la valeur de fin et le nombre de périodes (années) d'un investissement.

Formule TCAC

La syntaxe de la formule CARG est :

TCAC =(Valeur de fin/Valeur de début)1/n - 1

où:

  • Valeur finale – Solde final de l'investissement à la fin de la période d'investissement.
  • Valeur de départ – Solde d'ouverture de l'investissement au début de la période d'investissement.
  • m – Nombre d'années que vous avez investi.

Calculer le TCAC dans Excel

Maintenant que vous avez appris l'arithmétique derrière les intérêts composés, voyons comment vous pouvez calculer le TCAC dans Excel. Il existe 5 façons de créer une formule Excel pour calculer le TCAC :

  • Utilisation d'opérateurs arithmétiques
  • Utilisation de la fonction RRI
  • Utilisation de la fonction POWER.
  • Utilisation de la fonction TAUX.
  • Utilisation de la fonction IRR.

Calcul du TCAC dans Excel à l'aide d'opérateurs

Le moyen direct de calculer le TCAC consiste à utiliser des opérateurs. Utilisez la formule générique ci-dessus pour calculer le TCAC.

Supposons que nous ayons des données de vente pour une certaine entreprise dans la feuille de calcul ci-dessous.

La colonne A indique les années au cours desquelles les revenus sont gagnés. La colonne B indique les revenus de l'entreprise pour l'année respective. Avec la formule CAGR dans Excel, vous pouvez calculer le taux de croissance annuel des revenus.

Entrez la formule ci-dessous pour calculer le TCAC par la méthode directe :

=(B11/B2)^(1/9)-1

Dans l'exemple ci-dessous, la valeur de départ de l'investissement se trouve dans la cellule B2 et la valeur de fin dans la cellule B11. Le nombre d'années (période) entre le début et la fin de la période d'investissement est 9. Habituellement, chaque période de cycle d'investissement commence un an et se termine l'année suivante, par conséquent, la première période du cycle, dans ce cas, est 2011 -2012 et le dernier cycle est 2019-2020. Le nombre total d'années investies est donc « 9 »

Le résultat sera en nombres décimaux et non en pourcentage comme indiqué ci-dessus. Pour le convertir en pourcentage, allez dans l'onglet "Accueil", cliquez sur le menu déroulant qui dit "Général" dans le groupe Nombre et sélectionnez l'option "% Pourcentage".

Maintenant, nous avons le taux de croissance annuel composé qui est de « 10,77 % » dans la cellule B13. Il s'agit du seul taux de croissance lissé pour l'ensemble de la période.

Calcul du TCAC dans Excel à l'aide de la fonction RRI

La fonction RRI mesure un taux d'intérêt équivalent périodique pour le rendement d'un investissement ou d'un prêt sur une période donnée.

Le taux d'intérêt est calculé en fonction de la valeur actuelle et future de l'investissement et de la durée.

La syntaxe :

=RRI(nper,pv,fv)
  • nper – nombre total de périodes (années)
  • pv – Ceci spécifie la valeur actuelle d'un investissement ou d'un prêt (identique à la valeur de départ)
  • fv – Ceci spécifie la valeur future d'un investissement ou d'un prêt (identique à la valeur finale)

Nous avons la valeur de nper dans la cellule A11, pv dans la cellule C2 et fv dans la cellule C11. Utilisez cette formule :

=RRI(A11,C2,C11)

Le TCAC est de « 10,77 % », qui se trouve dans la cellule B13.

Calcul du TCAC dans Excel à l'aide de la fonction POWER

Une autre méthode simple pour calculer le taux de croissance annuel composé (TCAC) dans Excel consiste à utiliser la fonction POWER. La fonction POWER remplace la ^ opérateur dans la fonction CAGR.

La syntaxe de la fonction POWER :

=POWER(nombre,puissance)

Arguments de la fonction POWER :

  • numéro – C'est le nombre de base trouvé en divisant la valeur de fin (EV) par la valeur de début (BV) (EV/BV).
  • Puissance – Il s'agit d'élever le résultat à un exposant de un divisé par la durée (1/nombre de périodes (n)).

Maintenant, les arguments sont définis de cette façon pour trouver la valeur CAGR :

=PUISSANCE(EV/BV,1/n)-1

Appliquons la formule à un exemple :

=PUISSANCE(C11/C2,1/A11)-1

Le résultat:

Calcul du TCAC dans Excel à l'aide de la fonction RATE

La fonction de taux est encore une autre fonction que vous pouvez utiliser pour trouver le TCAC dans Excel. Lorsque vous regardez la syntaxe de la fonction RATE, cela peut sembler un peu compliqué avec 6 arguments pas moins, mais une fois la fonction comprise, vous préférerez peut-être cette méthode pour trouver la valeur CAGR.

La syntaxe de la fonction RATE :

=RATE(nper,pmt,pv,[fv],[type],[deviner])

Où:

  • nper – Le nombre total de période de paiement (la durée du prêt).
  • PM (facultatif) – Le montant du paiement effectué au cours de chaque période.
  • PV – Ceci spécifie la valeur actuelle du prêt/investissement (valeur initiale (BV))
  • [Fv]– Ceci précise la valeur future du prêt/investissement, au dernier versement (valeur finale (EV))
  • [Taper] – Ceci spécifie quand les paiements pour le prêt/l'investissement sont dus, c'est 0 ou 1. L'argument 0 signifie que les paiements sont dus au début de la période et 1 signifie que les paiements sont dus à la fin de la période (la valeur par défaut est 0).
  • [Devine] – Votre estimation du taux. En cas d'omission, la valeur par défaut est de 10 %.

La raison pour laquelle la fonction RATE a six arguments est qu'elle peut être utilisée pour de nombreux autres calculs financiers. Mais nous pouvons convertir la fonction RATE en une formule CAGR, avec seulement trois arguments 1er (nper), 3e (pv) et 4e (fv) arguments :

=TAUX(nper,,-BV,EV)

Comme nous ne faisons pas de paiements réguliers (mensuels, trimestriels, annuels), nous laissons le deuxième argument vide.

Pour calculer le taux de croissance annuel composé à l'aide de la fonction TAUX, utilisez cette formule :

=TAUX(A11,,-C2,C11)

Si vous ne souhaitez pas calculer le nombre de périodes manuellement, utilisez la fonction ROW comme premier argument de la RATE fromula. Il calculera le Radio Nationale Publique pour vous.

=TAUX(LIGNE(A11)-LIGNE(A2),,-C2,C11)

Calcul du TCAC dans Excel à l'aide de la fonction IRR

Le TRI abrégé pour « Rendement du taux interne » est une fonction Excel qui calcule le TRI pour les paiements et les revenus se produisant à intervalles réguliers (c'est-à-dire mensuels, annuels).

La méthode IRR est utile lorsque vous devez calculer la valeur du TCAC pour les flux de trésorerie périodiques.

La syntaxe est :

=IRR(valeurs,[deviner])

Où:

  • valeurs – une gamme de paiements. L'éventail des paiements doit comporter au moins un flux de trésorerie négatif et un flux de trésorerie positif.
  • [devine] (Facultatif) – Cela représente votre estimation de la valeur du taux. S'il est ignoré, sa valeur par défaut est de 10 %.

La fonction Excel IRR vous oblige à réajuster l'ensemble de données de cette manière :

La valeur de départ doit être insérée sous forme de nombre négatif, la valeur de fin est un nombre positif et toutes les autres valeurs sous forme de zéros.

Voici la formule pour l'exemple :

=TRI(C2:C11)

Eh bien, voici comment vous pouvez calculer le taux de croissance annuel composé (CGAR) dans Excel.