Cum se creează o formulă CAGR în Excel

Aflați cum puteți calcula Rata de creștere anuală compusă (CAGR) în Excel folosind operatori sau diferite funcții Excel.

CAGR înseamnă Compound Annual Growth Rate, care măsoară rata anuală de creștere (rata netezită) a unei investiții în fiecare an, într-un interval dat. De exemplu, să presupunem că ați cumpărat acțiuni în valoare de 100 USD în 2011 și valorează 400 USD în 2021, CAGR ar fi rata la care investiția dvs. în acel stoc a crescut în fiecare an.

Piețele de valori sunt volatile, creșterea unei investiții poate varia de la an la an. Rentabilitatea investiției poate crește sau scădea. CAGR ajută la rentabilitatea netedă a unei investiții, așa cum ar fi crescut la o rată echilibrată în fiecare an.

Deși nu există o funcție CAGR în Excel, există mai multe moduri în care puteți calcula CAGR în Excel. Acest articol vă arată cum să creați formule CAGR clare și ușor de înțeles folosind diferite funcții Excel.

Cum se creează un Rata de creștere anuală compusă (CAGR) formulă în Excel

Rata anuală de creștere compusă (CAGR) este foarte utilă pentru afaceri, planificare financiară, modelare financiară și analiza investițiilor. Formula CAGR calculează creșterea anuală a unei investiții care poate fi utilizată pentru a compara cu alte investiții.

Pentru a calcula CAGR aveți nevoie de trei intrări primare: valoarea inițială a investiției, valoarea finală și numărul de perioade (ani).

Formula CAGR

Sintaxa formulei CARG este:

CAGR =(Valoare de sfârșit/Valoare de început)1/n - 1

Unde:

  • Valoarea finală – Soldul final al investiției la sfârșitul perioadei de investiție.
  • Valoarea de început – Soldul inițial al investiției la începutul perioadei de investiție.
  • n – Numărul de ani în care ai investit.

Calcularea CAGR în Excel

Acum că ați învățat aritmetica din spatele dobânzii compuse, să vedem cum puteți calcula CAGR în Excel. Există 5 moduri de a crea o formulă Excel pentru a calcula CAGR:

  • Utilizarea operatorilor aritmetici
  • Folosind funcția RRI
  • Folosind funcția POWER.
  • Folosind funcția RATE.
  • Folosind funcția IRR.

Calcularea CAGR în Excel folosind operatori

Modul direct de a calcula CAGR este utilizarea operatorilor. Utilizați formula generică de mai sus pentru a calcula CAGR.

Să presupunem că avem date de vânzări pentru o anumită companie în foaia de calcul de mai jos.

Coloana A are anii în care sunt realizate veniturile. Coloana B prezintă veniturile companiei în anul respectiv. Cu formula CAGR în excel, puteți calcula rata anuală de creștere a veniturilor.

Introduceți formula de mai jos pentru a calcula CAGR prin metoda directă:

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

În exemplul de mai jos, valoarea inițială a investiției este în celula B2 și valoarea finală în celula B11. Numărul de ani (perioada) dintre începutul și sfârșitul perioadei de investiție este de 9. De obicei, fiecare perioadă de ciclu de investiții începe cu un an și se termină în anul următor, prin urmare, prima perioadă a ciclului, în acest caz, este 2011 -2012 iar ultimul ciclu este 2019-2020. Deci numărul total de ani investiți este „9”

Rezultatul va fi în numere zecimale, nu în procente, așa cum se arată mai sus. Pentru a-l converti într-un procent, accesați fila „Acasă”, faceți clic pe meniul drop-down care spune „General” în grupul Număr și selectați opțiunea „% Procent”.

Acum, avem rata de creștere anuală compusă care este „10,77%” în celula B13. Aceasta este rata de creștere netezită unică pentru întreaga perioadă de timp.

Calcularea CAGR în Excel folosind funcția RRI

Funcția RRI măsoară o rată periodică echivalentă a dobânzii pentru rentabilitatea unei investiții sau a unui împrumut într-o anumită perioadă de timp.

Rata dobânzii este calculată pe baza valorii prezente și viitoare a investiției și a duratei.

Sintaxa:

=RRI(nper,pv,fv)
  • nper – numărul total de perioade (ani)
  • pv – Aceasta specifică valoarea actuală a unei investiții sau a unui împrumut (la fel ca valoarea inițială)
  • fv – Acesta specifică valoarea viitoare a unei investiții sau a unui împrumut (la fel ca valoarea finală)

Avem valoarea nper în celula A11, pv în celula C2 și fv în celula C11. Utilizați această formulă:

=RRI(A11,C2,C11)

CAGR este „10,77%”, care se află în celula B13.

Calcularea CAGR în Excel folosind funcția POWER

O altă metodă ușoară de a calcula Rata de creștere anuală compusă (CAGR) în Excel este utilizarea funcției POWER. Funcția POWER înlocuiește ^ operator în funcția CAGR.

Sintaxa funcției POWER:

=PUTERE(numar,putere)

Argumente ale funcției POWER:

  • număr – Este numărul de bază găsit prin împărțirea valorii finale (EV) la valoarea de început (BV) (EV/BV).
  • putere – Este să ridici rezultatul la un exponent de unu împărțit la durată (1/număr de perioade (n)).

Acum, argumentele sunt definite astfel pentru a găsi valoarea CAGR:

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

Să aplicăm formula unui exemplu:

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

Rezultatul:

Calcularea CAGR în Excel folosind funcția RATE

Funcția Rate este o altă funcție pe care o puteți utiliza pentru a găsi CAGR în Excel. Când vă uitați la sintaxa funcției RATE, poate părea puțin complicată cu 6 argumente nu mai puțin, dar odată ce ați înțeles funcția, este posibil să preferați această metodă pentru a găsi valoarea CAGR.

Sintaxa funcției RATE:

=RATE(nper,pmt,pv,[fv],[tip],[ghici])

Unde:

  • nper – Numărul total de perioade de plăți (termenul împrumutului).
  • Pmt (opțional) – Suma plății efectuate în fiecare perioadă.
  • Pv – Acesta specifică valoarea actuală a împrumutului/investiției (valoarea de început (BV))
  • [Fv]– Acesta specifică valoarea viitoare a împrumutului/investiției, la ultima plată (valoare finală (EV))
  • [Tip] – Acesta specifică când sunt scadente plățile pentru împrumut/investiție, este fie 0, fie 1. Argumentul 0 înseamnă că plățile sunt scadente la începutul perioadei și 1 înseamnă că plățile sunt scadente la sfârșitul perioadei (implicit este 0).
  • [Ghici] – Estimarea ta asupra ratei. Dacă este omisă, valoarea implicită este de 10%.

Motivul pentru care funcția RATE are șase argumente este că poate fi utilizată pentru multe alte calcule financiare. Dar putem converti funcția RATE într-o formulă CAGR, cu doar trei argumente argumentele 1 (nper), 3 (pv) și 4 (fv):

=RATE(nper,,-BV,EV)

Deoarece nu efectuăm plăți regulate (lunare, trimestriale, anuale), lăsăm gol al doilea argument.

Pentru a calcula rata de creștere anuală compusă folosind funcția RATE, utilizați această formulă:

=RATE(A11,,-C2,C11)

Dacă nu doriți să calculați manual numărul de perioade, utilizați funcția ROW ca prim argument al RATE. Acesta va calcula NPR Pentru dumneavoastră.

=RATE(RÂND(A11)-RÂND(A2),,-C2,C11)

Calcularea CAGR în Excel folosind funcția IRR

Prescurtarea IRR pentru „Internal Rate Return” este o funcție Excel care calculează IRR pentru plăți și venituri care apar la intervale regulate (adică lunar, anual).

Metoda IRR este utilă atunci când trebuie să calculați valoarea CAGR pentru fluxurile de numerar periodice.

Sintaxa este:

=IRR(valori,[ghici])

Unde:

  • valorile – o serie de plăți. Gama de plăți ar trebui să aibă cel puțin un flux de numerar negativ și unul pozitiv.
  • [ghici] (Opțional) – Aceasta reprezintă estimarea dvs. despre valoarea ratei. Dacă este ignorată, valoarea implicită este de 10%.

Funcția Excel IRR necesită reajustarea setului de date în acest fel:

Valoarea de pornire trebuie inserată ca număr negativ, valoarea finală este un număr pozitiv și toate celelalte valori ca zerouri.

Iată formula pentru exemplu:

=IRR(C2:C11)

Ei bine, acestea sunt modalitățile prin care puteți calcula rata de creștere anuală compusă (CGAR) în Excel.