Kako ustvariti formulo CAGR v Excelu

Naučite se, kako lahko izračunate sestavljeno letno stopnjo rasti (CAGR) v Excelu z uporabo operaterjev ali različnih Excelovih funkcij.

CAGR pomeni sestavljena letna stopnja rasti, ki meri letno stopnjo rasti (zglajeno stopnjo) naložbe vsako leto v določenem intervalu. Denimo, da ste na primer leta 2011 kupili delnice v vrednosti 100 USD in leta 2021 vredne 400 USD, CAGR bi bil stopnja, po kateri je vaša naložba v to delnico rasla vsako leto.

Delniški trgi so nestanovitni, rast naložb se lahko iz leta v leto razlikuje. Donosnost naložbe se lahko poveča ali zmanjša. CAGR pomaga gladko donosnost naložbe, kot je vsako leto rasla po uravnoteženi stopnji.

Čeprav v Excelu ni funkcije CAGR, lahko v Excelu izračunate CAGR na več načinov. V tem članku je prikazano, kako ustvariti jasne in lahko razumljive formule CAGR z uporabo različnih Excelovih funkcij.

Kako ustvariti a Sestavljena letna stopnja rasti (CAGR) formula v Excelu

Sestavljena letna stopnja rasti (CAGR) je zelo koristna za poslovanje, finančno načrtovanje, finančno modeliranje in analizo naložb. Formula CAGR izračuna letno rast naložbe, ki jo je mogoče uporabiti za primerjavo z drugimi naložbami.

Za izračun CAGR potrebujete tri primarne vhode: začetno vrednost naložbe, končno vrednost in število obdobij (let).

Formula CAGR

Sintaksa formule CARG je:

CAGR =(Končna vrednost/Začetna vrednost)1/n - 1

kje:

  • Končna vrednost – Končno stanje naložbe ob koncu naložbenega obdobja.
  • Začetna vrednost – Začetno stanje naložbe na začetku naložbenega obdobja.
  • n – Število let, ki ste jih vložili.

Izračun CAGR v Excelu

Zdaj, ko ste se naučili aritmetike za sestavljene obresti, poglejmo, kako lahko izračunate CAGR v Excelu. Obstaja 5 načinov za ustvarjanje formule v Excelu za izračun CAGR:

  • Uporaba aritmetičnih operaterjev
  • Uporaba funkcije RRI
  • Uporaba funkcije POWER.
  • Uporaba funkcije RATE.
  • Uporaba funkcije IRR.

Izračun CAGR v Excelu z uporabo operaterjev

Neposreden način za izračun CAGR je z uporabo operaterjev. Za izračun CAGR uporabite zgornjo splošno formulo.

Recimo, da imamo v spodnji preglednici podatke o prodaji za določeno podjetje.

V stolpcu A so leta, v katerih so bili prihodki zasluženi. V stolpcu B so prihodki podjetja v posameznem letu. S formulo CAGR v excelu lahko izračunate letno stopnjo rasti prihodka.

Vnesite spodnjo formulo za izračun CAGR z neposredno metodo:

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

V spodnjem primeru je začetna vrednost naložbe v celici B2, končna vrednost pa v celici B11. Število let (obdobja) med začetkom in koncem investicijskega obdobja je 9. Običajno se vsako obdobje naložbenega cikla začne eno leto in konča naslednje leto, zato je prvo obdobje cikla v tem primeru leto 2011. -2012, zadnji cikel pa je 2019-2020. Torej je skupno število vloženih let "9"

Rezultat bo v decimalnih številkah in ne v odstotkih, kot je prikazano zgoraj. Če ga želite pretvoriti v odstotek, pojdite na zavihek »Domov«, kliknite spustni meni z napisom »Splošno« v skupini Številke in izberite možnost »% Odstotek«.

Zdaj smo dobili sestavljeno letno stopnjo rasti, ki je "10,77%" v celici B13. To je enotna zglajena stopnja rasti za celotno časovno obdobje.

Izračun CAGR v Excelu z uporabo funkcije RRI

Funkcija RRI meri periodično enakovredno obrestno mero za donosnost naložbe ali posojila v določenem časovnem obdobju.

Obrestna mera se izračuna na podlagi sedanje in prihodnje vrednosti naložbe ter trajanja.

Sintaksa:

=RRI(nper,pv,fv)
  • nper – skupno število obdobij (let)
  • pv – To določa sedanjo vrednost naložbe ali posojila (enako kot začetna vrednost)
  • fv – To določa prihodnjo vrednost naložbe ali posojila (enako kot končna vrednost)

Imamo vrednost za nper v celici A11, pv v celici C2 in fv v celici C11. Uporabite to formulo:

=RRI(A11,C2,C11)

CAGR je "10,77 %", kar je v celici B13.

Izračun CAGR v Excelu s funkcijo POWER

Druga enostavna metoda za izračun sestavljene letne stopnje rasti (CAGR) v Excelu je uporaba funkcije POWER. Funkcija POWER nadomešča ^ operaterja v funkciji CAGR.

Sintaksa funkcije POWER:

=POWER(število,moč)

Argumenti funkcije POWER:

  • številko – To je osnovno število, ki ga najdemo z deljenjem končne vrednosti (EV) z začetno vrednostjo (BV) (EV/BV).
  • moč – Rezultat je dvig na eksponent enega, deljeno s trajanjem (1/število obdobij (n)).

Zdaj so argumenti definirani na ta način, da najdemo vrednost CAGR:

=MOČ(EV/BV,1/n)-1

Uporabimo formulo za primer:

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

rezultat:

Izračun CAGR v Excelu z uporabo funkcije RATE

Funkcija Rate je še ena funkcija, ki jo lahko uporabite za iskanje CAGR v Excelu. Ko pogledate sintakso funkcije RATE, je morda videti nekoliko zapleteno s 6 argumenti, vendar ko boste razumeli funkcijo, vam bo morda bolj všeč ta metoda za iskanje vrednosti CAGR.

Sintaksa funkcije RATE:

=RATE(nper,pmt,pv,[fv],[vrsta],[ugani])

Kje:

  • nper – Skupno število plačilnih rokov (rok posojila).
  • Pmt (neobvezno) – znesek plačila v posameznem obdobju.
  • Pv – To določa sedanjo vrednost posojila/naložbe (začetna vrednost (BV))
  • [Fv]– To določa prihodnjo vrednost posojila/naložbe ob zadnjem plačilu (končna vrednost (EV))
  • [Vrsta] – To določa, kdaj zapadejo plačila za posojilo/naložbo, je 0 ali 1. Argument 0 pomeni, da so plačila zapadla na začetku obdobja, 1 pa pomeni, da plačila zapadejo na koncu obdobja (privzeto je 0).
  • [ugibaj] – Vaše ugibanje o ceni. Če je izpuščen, je privzeto nastavljen na 10%.

Razlog, zakaj ima funkcija RATE šest argumentov, je, da se lahko uporablja za številne druge finančne izračune. Lahko pa funkcijo RATE pretvorimo v formulo CAGR s samo tremi argumenti 1. (nper), 3. (pv) in 4. (fv) argumenti:

=RATE(nper,,-BV,EV)

Ker ne izvajamo rednih plačil (mesečno, četrtletno, letno), pustimo drugi argument prazen.

Če želite izračunati sestavljeno letno stopnjo rasti s funkcijo RATE, uporabite to formulo:

=STOP (A11,,-C2,C11)

Če ne želite ročno izračunati števila obdobij, uporabite funkcijo ROW kot prvi argument iz RATE. Izračunal bo npr zate.

=STOP(VRSTICA(A11)-VRSTICA(A2),,-C2,C11)

Izračun CAGR v Excelu s funkcijo IRR

IRR okrajšava za 'Internal Rate Return' je Excelova funkcija, ki izračuna IRR za plačila in prihodke, ki se pojavljajo v rednih intervalih (tj. mesečni, letni).

Metoda IRR je koristna, ko morate izračunati vrednost CAGR za občasne denarne tokove.

Sintaksa je:

=IRR(vrednosti,[ugani])

Kje:

  • vrednote – vrsta plačil. Obseg plačil mora imeti vsaj en negativen in en pozitiven denarni tok.
  • [ugibati] (Neobvezno) – To predstavlja vašo ugibanje vrednosti stopnje. Če se zanemari, je privzeto nastavljeno na 10%.

Funkcija Excel IRR zahteva, da ponovno prilagodite nabor podatkov na ta način:

Začetno vrednost je treba vstaviti kot negativno število, končno vrednost je pozitivno število, vse druge vrednosti pa kot ničle.

Tukaj je formula za primer:

=IRR(C2:C11)

No, to so načini, na katere lahko izračunate sestavljeno letno stopnjo rasti (CGAR) v Excelu.