Ako vytvoriť vzorec CAGR v programe Excel

Zistite, ako môžete vypočítať zloženú ročnú mieru rastu (CAGR) v Exceli pomocou operátorov alebo rôznych funkcií Excelu.

CAGR je skratka pre Compound Annual Growth Rate, ktorá meria ročnú mieru rastu (vyhladenú mieru) investície každý rok počas daného intervalu. Predpokladajme napríklad, že ste v roku 2011 kúpili akcie v hodnote 100 USD a v roku 2021 majú hodnotu 400 USD, CAGR by bola miera, akou vaša investícia do týchto akcií rástla každý rok.

Akciové trhy sú nestále, rast investície sa môže z roka na rok líšiť. Návratnosť investície sa môže zvýšiť alebo znížiť. CAGR pomáha hladkej návratnosti investície, ako keby rástla vyrovnaným tempom každý rok.

Hoci v Exceli nie je žiadna funkcia CAGR, existuje niekoľko spôsobov, ako môžete vypočítať CAGR v Exceli. Tento článok vám ukáže, ako vytvoriť jasné a ľahko pochopiteľné vzorce CAGR pomocou rôznych funkcií programu Excel.

Ako vytvoriť a Zložená ročná miera rastu (CAGR) vzorec v Exceli

Zložená ročná miera rastu (CAGR) je veľmi užitočná pre podnikanie, finančné plánovanie, finančné modelovanie a investičnú analýzu. Vzorec CAGR vypočítava ročný rast investície, ktorý možno použiť na porovnanie s inými investíciami.

Na výpočet CAGR potrebujete tri primárne vstupy: počiatočnú hodnotu investície, konečnú hodnotu a počet období (rokov).

Formula CAGR

Syntax vzorca CARG je:

CAGR =(Konečná hodnota/Počiatočná hodnota)1/n - 1

kde:

  • Koncová hodnota – Konečný zostatok investície na konci investičného obdobia.
  • Počiatočná hodnota – Počiatočný zostatok investície na začiatku investičného obdobia.
  • n – Počet rokov, ktoré ste investovali.

Výpočet CAGR v Exceli

Teraz, keď ste sa naučili aritmetiku zloženého úroku, pozrime sa, ako môžete vypočítať CAGR v Exceli. Existuje 5 spôsobov, ako vytvoriť vzorec Excel na výpočet CAGR:

  • Používanie aritmetických operátorov
  • Použitie funkcie RRI
  • Používanie funkcie POWER.
  • Použitie funkcie RATE.
  • Použitie funkcie IRR.

Výpočet CAGR v Exceli pomocou operátorov

Priamy spôsob výpočtu CAGR je pomocou operátorov. Na výpočet CAGR použite vyššie uvedený všeobecný vzorec.

Predpokladajme, že v tabuľke nižšie máme údaje o predaji určitej spoločnosti.

V stĺpci A sú uvedené roky, v ktorých sa dosiahli výnosy. V stĺpci B sú uvedené tržby spoločnosti v príslušnom roku. Pomocou vzorca CAGR v programe Excel môžete vypočítať ročnú mieru rastu výnosov.

Zadajte nižšie uvedený vzorec na výpočet CAGR priamou metódou:

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

V nižšie uvedenom príklade je počiatočná hodnota investície v bunke B2 a konečná hodnota v bunke B11. Počet rokov (obdobie) medzi začiatkom a koncom investičného obdobia je 9. Obyčajne každé obdobie investičného cyklu začína jeden rok a končí nasledujúci rok, teda prvým obdobím cyklu je v tomto prípade rok 2011 -2012 a posledný cyklus je 2019-2020. Takže celkový počet investovaných rokov je „9“

Výsledok bude v desatinných číslach, nie v percentách, ako je uvedené vyššie. Ak ho chcete previesť na percentá, prejdite na kartu „Domov“, kliknite na rozbaľovaciu ponuku „Všeobecné“ v skupine Číslo a vyberte možnosť „Percento percent“.

Teraz máme zloženú ročnú mieru rastu, ktorá je „10,77 %“ v bunke B13. Toto jediné zmiernilo tempo rastu za celé časové obdobie.

Výpočet CAGR v Exceli pomocou funkcie RRI

Funkcia RRI meria periodickú ekvivalentnú úrokovú sadzbu pre návratnosť investície alebo pôžičky za určité časové obdobie.

Úroková sadzba sa vypočíta na základe súčasnej a budúcej hodnoty investície a trvania.

syntax:

=RRI(nper,pv,fv)
  • nper – celkový počet období (rokov)
  • pv – Toto špecifikuje súčasnú hodnotu investície alebo úveru (rovnako ako počiatočná hodnota)
  • fv – Toto špecifikuje budúcu hodnotu investície alebo úveru (rovnako ako konečná hodnota)

Máme hodnotu pre nper v bunke A11, pv v bunke C2 a fv v bunke C11. Použite tento vzorec:

=RRI(A11;C2;C11)

CAGR je „10,77 %“, čo je v bunke B13.

Výpočet CAGR v Exceli pomocou funkcie POWER

Ďalšou jednoduchou metódou na výpočet zloženej ročnej miery rastu (CAGR) v Exceli je použitie funkcie POWER. Funkcia POWER nahrádza funkciu ^ operátor vo funkcii CAGR.

Syntax funkcie POWER:

=POWER(číslo,sila)

Argumenty funkcie POWER:

  • číslo – Je to základné číslo zistené vydelením koncovej hodnoty (EV) počiatočnou hodnotou (BV) (EV/BV).
  • moc – Je to zvýšenie výsledku na exponent jedna delený trvaním (1/počet období (n)).

Teraz sú argumenty definované týmto spôsobom, aby ste našli hodnotu CAGR:

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

Aplikujme vzorec na príklad:

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

Výsledok:

Výpočet CAGR v Exceli pomocou funkcie RATE

Funkcia Rate je ďalšou funkciou, ktorú môžete použiť na nájdenie CAGR v Exceli. Keď sa pozriete na syntax funkcie RATE, môže to vyzerať trochu komplikovane so 6 argumentmi, ale keď pochopíte funkciu, môžete uprednostniť túto metódu na nájdenie hodnoty CAGR.

Syntax funkcie RATE:

=RATE(nper,pmt,pv,[fv],[typ],[hádať])

Kde:

  • nper – Celkový počet platobných období (doba pôžičky).
  • Pmt (voliteľné) – Výška platby uskutočnenej v každom období.
  • Pv – Toto špecifikuje súčasnú hodnotu úveru/investície (počiatočná hodnota (BV))
  • [Fv]– Toto špecifikuje budúcu hodnotu pôžičky/investície pri poslednej platbe (konečná hodnota (EV))
  • [Typ] – Toto určuje, kedy sú platby za úver/investíciu splatné, je to buď 0 alebo 1. Argument 0 znamená, že platby sú splatné na začiatku obdobia a 1 znamená, že platby sú splatné na konci obdobia (predvolené je 0).
  • [Hádaj] – Váš odhad sadzby. Ak sa vynechá, predvolená hodnota je 10 %.

Dôvod, prečo má funkcia RATE šesť argumentov, je ten, že ju možno použiť na mnohé iné finančné výpočty. Funkciu RATE však môžeme previesť na vzorec CAGR iba s tromi argumentmi 1. (nper), 3. (pv) a 4. (fv) argument:

=RATE(nper,,-BV,EV)

Keďže nevyplácame pravidelné platby (mesačné, štvrťročné, ročné), druhý argument necháme prázdny.

Ak chcete vypočítať zloženú ročnú mieru rastu pomocou funkcie RATE, použite tento vzorec:

=RATE(A11;,-C2;C11)

Ak nechcete počítať počet období ručne, použite funkciu ROW ako prvý argument hodnoty RATE fromula. To bude počítať npr pre teba.

=RATE(RIADOK(A11)-RIADOK(A2);,-C2,C11)

Výpočet CAGR v Exceli pomocou funkcie IRR

Skratka IRR pre „Internal Rate Return“ je funkcia Excelu, ktorá vypočítava IRR pre platby a príjem, ktoré sa vyskytujú v pravidelných intervaloch (t. j. mesačne, ročne).

Metóda IRR je užitočná, keď potrebujete vypočítať hodnotu CAGR pre pravidelné peňažné toky.

Syntax je:

=IRR(hodnoty,[hádam])

Kde:

  • hodnoty - rozsah platieb. Rozsah platieb by mal mať aspoň jeden negatívny a jeden pozitívny peňažný tok.
  • [hádajte] (Voliteľné) – predstavuje váš odhad hodnoty sadzby. Ak sa ignoruje, predvolená hodnota je 10 %.

Funkcia Excel IRR vyžaduje, aby ste znova upravili množinu údajov týmto spôsobom:

Počiatočná hodnota by mala byť vložená ako záporné číslo, koncová hodnota je kladné číslo a všetky ostatné hodnoty ako nuly.

Tu je vzorec pre príklad:

=IRR(C2:C11)

Toto sú spôsoby, ako môžete vypočítať zloženú ročnú mieru rastu (CGAR) v Exceli.