Jak stworzyć formułę CAGR w Excelu

Dowiedz się, jak obliczyć złożony roczny wskaźnik wzrostu (CAGR) w programie Excel przy użyciu operatorów lub różnych funkcji programu Excel.

CAGR to skrót od Compound Annual Growth Rate, który mierzy roczną stopę wzrostu (stopę wygładzoną) inwestycji każdego roku w określonym przedziale czasu. Załóżmy na przykład, że kupiłeś akcje o wartości 100 USD w 2011 r. i są one warte 400 USD w 2021 r., CAGR byłby tempem, w jakim Twoja inwestycja w te akcje rosła każdego roku.

Giełdy są niestabilne, wzrost inwestycji może się zmieniać z roku na rok. Zwrot z inwestycji może wzrosnąć lub spaść. CAGR pomaga w płynnym zwrocie inwestycji, tak jakby rosła w zrównoważonym tempie każdego roku.

Chociaż w programie Excel nie ma funkcji CAGR, istnieje kilka sposobów obliczania CAGR w programie Excel. W tym artykule pokazano, jak tworzyć przejrzyste i łatwe do zrozumienia formuły CAGR przy użyciu różnych funkcji programu Excel.

Jak stworzyć Składana roczna stopa wzrostu (CAGR) formuła w Excelu

Złożona roczna stopa wzrostu (CAGR) jest bardzo pomocna w biznesie, planowaniu finansowym, modelowaniu finansowym i analizie inwestycji. Formuła CAGR oblicza roczny wzrost inwestycji, który można wykorzystać do porównania z innymi inwestycjami.

Aby obliczyć CAGR, potrzebujesz trzech podstawowych danych wejściowych: wartości początkowej inwestycji, wartości końcowej i liczby okresów (lat).

Formuła CAGR

Składnia formuły CARG to:

CAGR =(Wartość końcowa/Wartość początkowa)1/n - 1

gdzie:

  • Wartość końcowa – Saldo końcowe inwestycji na koniec okresu inwestycyjnego.
  • Wartość początkowa – Saldo początkowe inwestycji na początku okresu inwestycyjnego.
  • n – Liczba lat, które zainwestowałeś.

Obliczanie CAGR w Excelu

Teraz, gdy nauczyłeś się arytmetyki związanej z oprocentowaniem składanym, zobaczmy, jak możesz obliczyć CAGR w programie Excel. Istnieje 5 sposobów tworzenia formuły Excel do obliczania CAGR:

  • Korzystanie z operatorów arytmetycznych
  • Korzystanie z funkcji RRI
  • Korzystanie z funkcji ZASILANIE.
  • Korzystanie z funkcji RATE.
  • Korzystanie z funkcji IRR.

Obliczanie CAGR w Excelu za pomocą operatorów

Bezpośrednim sposobem obliczenia CAGR jest użycie operatorów. Użyj powyższego wzoru ogólnego, aby obliczyć CAGR.

Załóżmy, że w poniższym arkuszu kalkulacyjnym mamy dane sprzedażowe dla pewnej firmy.

Kolumna A zawiera lata, w których uzyskiwane są przychody. Kolumna B zawiera przychody firmy w danym roku. Dzięki formule CAGR w Excelu możesz obliczyć roczną stopę wzrostu przychodów.

Wpisz poniższy wzór, aby obliczyć CAGR metodą bezpośrednią:

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

W poniższym przykładzie początkowa wartość inwestycji znajduje się w komórce B2, a wartość końcowa w komórce B11. Liczba lat (okresu) między początkiem a końcem okresu inwestycyjnego wynosi 9. Zazwyczaj każdy okres cyklu inwestycyjnego rozpoczyna się w jednym roku i kończy w kolejnym, stąd pierwszym okresem cyklu w tym przypadku jest rok 2011 -2012 i ostatni cykl to 2019-2020. Więc całkowita liczba zainwestowanych lat to „9”

Wynik będzie podany w liczbach dziesiętnych, a nie w procentach, jak pokazano powyżej. Aby przekonwertować go na wartość procentową, przejdź do zakładki „Strona główna”, kliknij menu rozwijane „Ogólne” w grupie Numer i wybierz opcję „% Procent”.

Teraz mamy złożoną roczną stopę wzrostu, która wynosi „10,77%” w komórce B13. Jest to pojedyncza wygładzona stopa wzrostu przez cały okres.

Obliczanie CAGR w Excelu za pomocą funkcji RRI

Funkcja RRI mierzy okresową równoważną stopę procentową zwrotu z inwestycji lub pożyczki w określonym okresie czasu.

Oprocentowanie obliczane jest na podstawie obecnej i przyszłej wartości inwestycji oraz czasu trwania.

Składnia:

=RRI(liczba_wyd;wartość_przydatna)
  • nper – łączna liczba okresów (lat)
  • pv – Określa bieżącą wartość inwestycji lub pożyczki (taka sama jak wartość początkowa)
  • fv – Określa przyszłą wartość inwestycji lub pożyczki (tak samo jak wartość końcowa)

Mamy wartość Nper w komórce A11, pv w komórce C2 i fv w komórce C11. Użyj tej formuły:

=RRI(A11;C2;C11)

CAGR to „10,77%”, który znajduje się w komórce B13.

Obliczanie CAGR w Excelu za pomocą funkcji POWER

Inną łatwą metodą obliczania złożonej rocznej stopy wzrostu (CAGR) w programie Excel jest użycie funkcji MOC. Funkcja POWER zastępuje ^ operatora w funkcji CAGR.

Składnia funkcji POWER:

=MOC(liczba;moc)

Argumenty funkcji POWER:

  • numer – Jest to liczba podstawowa znaleziona przez podzielenie wartości końcowej (EV) przez wartość początkową (BV) (EV/BV).
  • moc – Jest to podniesienie wyniku do wykładnika jedności podzielonego przez czas trwania (1/liczba okresów (n)).

Teraz argumenty są zdefiniowane w ten sposób, aby znaleźć wartość CAGR:

=MOC(EV/BV;1/n)-1

Zastosujmy formułę do przykładu:

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

Wynik:

Obliczanie CAGR w Excelu za pomocą funkcji RATE

Funkcja Rate to kolejna funkcja, której możesz użyć do znalezienia CAGR w Excelu. Kiedy spojrzysz na składnię funkcji RATE, może wyglądać na nieco skomplikowaną z 6 argumentami nie mniej, ale kiedy zrozumiesz funkcję, możesz preferować tę metodę do znajdowania wartości CAGR.

Składnia funkcji RATE:

=RATE(liczba;spłata;wartość_aktualna;[przysł.];[typ];[zgadnij])

Gdzie:

  • nper – Całkowita liczba okresów spłat (okres kredytowania).
  • Pmt (opcjonalnie) – Kwota płatności dokonanej w każdym okresie.
  • Pv – Określa bieżącą wartość pożyczki/inwestycji (wartość początkowa (BV))
  • [Fv]– Określa przyszłą wartość pożyczki/inwestycji przy ostatniej spłacie (wartość końcowa (EV))
  • [Rodzaj] – Określa, kiedy są należne płatności za pożyczkę/inwestycję, jest to 0 lub 1. Argument 0 oznacza, że ​​płatności są należne na początku okresu, a 1 oznacza, że ​​płatności są należne na koniec okresu (wartość domyślna to 0).
  • [Odgadnąć] – Twoje przypuszczenie o stawce. Jeśli zostanie pominięty, domyślnie wynosi 10%.

Powodem, dla którego funkcja RATE ma sześć argumentów, jest to, że może być używana do wielu innych obliczeń finansowych. Możemy jednak przekonwertować funkcję RATE na formułę CAGR z tylko trzema argumentami: 1. (nper), 3. (pv) i 4. (fv):

=RATE(nper;,-BV;EV)

Ponieważ nie dokonujemy regularnych płatności (miesięcznych, kwartalnych, rocznych), drugi argument pozostawiamy pusty.

Aby obliczyć złożoną roczną stopę wzrostu za pomocą funkcji RATE, użyj poniższego wzoru:

=WSPÓŁCZYNNIK(A11;-C2;C11)

Jeśli nie chcesz ręcznie obliczać liczby okresów, użyj funkcji WIERSZ jako pierwszego argumentu wzoru STAWKA. Obliczy npr dla Was.

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

Obliczanie CAGR w Excelu za pomocą funkcji IRR

Skrót IRR od „Internal Rate Return” to funkcja programu Excel, która oblicza IRR dla płatności i dochodów występujących w regularnych odstępach czasu (tj. miesięcznych, rocznych).

Metoda IRR jest pomocna, gdy trzeba obliczyć wartość CAGR dla okresowych przepływów pieniężnych.

Składnia to:

=IRR(wartości;[zgadnij])

Gdzie:

  • wartości – zakres płatności. Zakres płatności powinien mieć co najmniej jeden ujemny i jeden dodatni przepływ środków pieniężnych.
  • [odgadnąć] (Opcjonalne) — reprezentuje szacowaną wartość stawki. Jeśli zostanie zignorowany, domyślnie wynosi 10%.

Funkcja Excel IRR wymaga ponownego dostosowania zestawu danych w ten sposób:

Wartość początkową należy wstawić jako liczbę ujemną, wartość końcową jako liczbę dodatnią, a wszystkie inne wartości jako zera.

Oto wzór na przykład:

=IRR(C2:C11)

Oto sposoby obliczania złożonej rocznej stopy wzrostu (CGAR) w programie Excel.