Jak vytvořit vzorec CAGR v aplikaci Excel

Zjistěte, jak můžete vypočítat Compound Annual Growth Rate (CAGR) v Excelu pomocí operátorů nebo různých funkcí Excelu.

CAGR je zkratka pro Compound Annual Growth Rate, která měří roční míru růstu (vyhlazenou míru) investice každý rok během daného intervalu. Předpokládejme například, že jste v roce 2011 koupili akcie v hodnotě 100 USD a v roce 2021 mají hodnotu 400 USD, CAGR by byla míra, jakou vaše investice do této akcie rostla každý rok.

Akciové trhy jsou volatilní, růst investice se může rok od roku lišit. Návratnost investice se může zvýšit nebo snížit. CAGR pomáhá hladké návratnosti investice, jako by rostla vyrovnaným tempem každý rok.

Ačkoli v Excelu není žádná funkce CAGR, existuje několik způsobů, jak můžete v Excelu vypočítat CAGR. Tento článek ukazuje, jak vytvořit jasné a snadno srozumitelné vzorce CAGR pomocí různých funkcí aplikace Excel.

Jak vytvořit a Složená roční míra růstu (CAGR) vzorec v Excelu

Složená roční míra růstu (CAGR) je velmi užitečná pro podnikání, finanční plánování, finanční modelování a investiční analýzu. Vzorec CAGR vypočítává roční růst investice, který lze použít k porovnání s jinými investicemi.

K výpočtu CAGR potřebujete tři primární vstupy: počáteční hodnotu investice, konečnou hodnotu a počet období (roků).

Formule CAGR

Syntaxe vzorce CARG je:

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

kde:

  • Koncová hodnota Konečný zůstatek investice na konci investičního období.
  • Počáteční hodnotaPočáteční zůstatek investice na začátku investičního období.
  • nPočet let, které jste investovali.

Výpočet CAGR v Excelu

Nyní, když jste se naučili aritmetiku složeného úročení, pojďme se podívat, jak můžete vypočítat CAGR v Excelu. Existuje 5 způsobů, jak vytvořit vzorec Excel pro výpočet CAGR:

  • Použití aritmetických operátorů
  • Použití funkce RRI
  • Pomocí funkce POWER.
  • Pomocí funkce RATE.
  • Použití funkce IRR.

Výpočet CAGR v Excelu pomocí operátorů

Přímým způsobem výpočtu CAGR je použití operátorů. K výpočtu CAGR použijte výše uvedený obecný vzorec.

Předpokládejme, že v tabulce níže máme údaje o prodeji určité společnosti.

Sloupec A obsahuje roky, ve kterých jsou příjmy dosaženy. Ve sloupci B jsou uvedeny tržby společnosti v příslušném roce. Pomocí vzorce CAGR v aplikaci Excel můžete vypočítat roční tempo růstu příjmů.

Zadejte níže uvedený vzorec pro výpočet CAGR přímou metodou:

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

V níže uvedeném příkladu je počáteční hodnota investice v buňce B2 a konečná hodnota v buňce B11. Počet let (období) mezi začátkem a koncem investičního období je 9. Obvykle každé období investičního cyklu začíná jeden rok a končí další rok, proto je prvním obdobím cyklu v tomto případě rok 2011 -2012 a poslední cyklus je 2019-2020. Celkový počet investovaných let je tedy „9“

Výsledek bude v desetinných číslech, nikoli v procentech, jak je uvedeno výše. Chcete-li jej převést na procento, přejděte na kartu „Domů“, klikněte na rozevírací seznam „Obecné“ ve skupině Číslo a vyberte možnost „% procent“.

Nyní máme složenou roční míru růstu, která je „10,77 %“ v buňce B13. Toto jediné vyhlazené tempo růstu za celé časové období.

Výpočet CAGR v Excelu pomocí funkce RRI

Funkce RRI měří periodickou ekvivalentní úrokovou míru pro návratnost investice nebo půjčky za určité časové období.

Úroková sazba se vypočítává na základě současné a budoucí hodnoty investice a doby trvání.

syntaxe:

=RRI(nper,pv,fv) 
  • nper – celkový počet období (roků)
  • pv – Toto specifikuje současnou hodnotu investice nebo půjčky (stejnou jako počáteční hodnota)
  • F v – Toto specifikuje budoucí hodnotu investice nebo půjčky (stejnou jako konečná hodnota)

Máme hodnotu pro nper v buňce A11, pv v buňce C2 a fv v buňce C11. Použijte tento vzorec:

=RRI(A11;C2;C11)

CAGR je „10,77 %“, což je v buňce B13.

Výpočet CAGR v Excelu pomocí funkce POWER

Další snadnou metodou pro výpočet složené roční míry růstu (CAGR) v Excelu je použití funkce POWER. Funkce POWER nahrazuje funkci ^ operátor ve funkci CAGR.

Syntaxe funkce POWER:

=POWER(číslo,síla)

Argumenty funkce POWER:

  • číslo – Je to základní číslo zjištěné vydělením koncové hodnoty (EV) počáteční hodnotou (BV) (EV/BV).
  • Napájení – Je to zvýšit výsledek na exponent jedna dělený trváním (1/počet období (n)).

Nyní jsou argumenty definovány tímto způsobem, aby našli hodnotu CAGR:

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

Aplikujme vzorec na příklad:

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

Výsledek:

Výpočet CAGR v Excelu pomocí funkce RATE

Funkce Rate je další funkcí, kterou můžete použít k nalezení CAGR v Excelu. Když se podíváte na syntaxi funkce RATE, může to vypadat trochu komplikovaně se 6 argumenty, ale jakmile pochopíte funkci, můžete tuto metodu pro nalezení hodnoty CAGR preferovat.

Syntaxe funkce RATE:

=RATE(nper,pmt,pv,[fv],[typ],[hádám]) 

Kde:

  • nperCelkový počet období plateb (doba půjčky).
  • Pmt (nepovinné) – Výše ​​platby provedené v každém období.
  • Pv – Toto specifikuje současnou hodnotu půjčky/investice (počáteční hodnota (BV))
  • [F v]– Toto specifikuje budoucí hodnotu půjčky/investice při poslední platbě (konečná hodnota (EV))
  • [Typ] – Toto určuje, kdy jsou platby za půjčku/investici splatné, je to buď 0 nebo 1. Argument 0 znamená, že platby jsou splatné na začátku období a 1 znamená, že platby jsou splatné na konci období (výchozí je 0).
  • [Tipni si] – Váš odhad sazby. Pokud je vynechán, výchozí hodnota je 10 %.

Důvod, proč má funkce RATE šest argumentů, je ten, že ji lze použít pro mnoho dalších finančních výpočtů. Ale můžeme převést funkci RATE na vzorec CAGR, pouze se třemi argumenty 1. (nper), 3. (pv) a 4. (fv) argument:

=RATE(nper,,-BV,EV)

Protože neprovádíme pravidelné platby (měsíční, čtvrtletní, roční), ponecháme druhý argument prázdný.

Chcete-li vypočítat složenou roční míru růstu pomocí funkce RATE, použijte tento vzorec:

=RATE(A11;,-C2;C11)

Pokud nechcete počítat počet období ručně, použijte funkci ROW jako první argument RATE fromula. Vypočítá to npr pro tebe.

=RATE(ŘÁDEK(A11)-ŘÁDEK(A2),,-C2,C11)

Výpočet CAGR v Excelu pomocí funkce IRR

Zkratka IRR pro „Internal Rate Return“ je funkce Excelu, která vypočítává IRR pro platby a příjmy, které se vyskytují v pravidelných intervalech (tj. měsíčně, ročně).

Metoda IRR je užitečná, když potřebujete vypočítat hodnotu CAGR pro pravidelné peněžní toky.

Syntaxe je:

=IRR(hodnoty,[hádám])

Kde:

  • hodnoty – rozsah plateb. Rozsah plateb by měl mít alespoň jeden záporný a jeden kladný peněžní tok.
  • [tipni si] (Volitelné) – Představuje váš odhad hodnoty sazby. Pokud je ignorována, výchozí hodnota je 10 %.

Funkce Excel IRR vyžaduje, abyste znovu upravili sadu dat tímto způsobem:

Počáteční hodnota by měla být vložena jako záporné číslo, konečná hodnota je kladné číslo a všechny ostatní hodnoty jako nuly.

Zde je vzorec pro příklad:

=IRR(C2:C11)

No, toto jsou způsoby, jak můžete vypočítat složenou roční míru růstu (CGAR) v aplikaci Excel.