Как да създадете CAGR формула в Excel

Научете как можете да изчислите комбинирания годишен темп на растеж (CAGR) в Excel, като използвате оператори или различни функции на Excel.

CAGR означава сложен годишен темп на растеж, който измерва годишния темп на растеж (изгладен темп) на инвестиция всяка година през даден интервал. Например, да предположим, че сте закупили акции на стойност $100 през 2011 г. и те са на стойност $400 през 2021 г., CAGR би бил темпът, с който вашата инвестиция в тези акции нараства всяка година.

Фондовите пазари са нестабилни, ръстът на инвестицията може да варира от година на година. Възвръщаемостта на инвестицията може да се увеличи или намали. CAGR помага за плавната възвръщаемост на инвестиция, както е нараснала с балансиран темп всяка година.

Въпреки че в Excel няма функция CAGR, има няколко начина, по които можете да изчислите CAGR в Excel. Тази статия ви показва как да създавате ясни и лесни за разбиране формули за CAGR, използвайки различни функции на Excel.

Как да създадете a Сложен годишен темп на растеж (CAGR) формула в Excel

Сложният годишен темп на растеж (CAGR) е много полезен за бизнес, финансово планиране, финансово моделиране и инвестиционен анализ. Формулата CAGR изчислява годишния растеж на една инвестиция, която може да се използва за сравнение с други инвестиции.

За да изчислите CAGR, имате нужда от три основни входа: начална стойност на инвестицията, крайна стойност и брой периоди (години).

CAGR формула

Синтаксисът на формулата CARG е:

CAGR =(Крайна стойност/Начална стойност)1/n - 1

където:

  • Крайна стойност – Краен баланс на инвестицията в края на инвестиционния период.
  • Начална стойност – Начално салдо на инвестицията в началото на инвестиционния период.
  • н – Брой години, които сте инвестирали.

Изчисляване на CAGR в Excel

След като научихте аритметиката зад сложната лихва, нека видим как можете да изчислите CAGR в Excel. Има 5 начина да създадете формула на Excel за изчисляване на CAGR:

  • Използване на аритметични оператори
  • Използване на функцията RRI
  • Използване на функцията POWER.
  • Използване на функция RATE.
  • Използване на функцията IRR.

Изчисляване на CAGR в Excel с помощта на оператори

Директният начин за изчисляване на CAGR е чрез използване на оператори. Използвайте горната обща формула, за да изчислите CAGR.

Да предположим, че имаме данни за продажбите за определена компания в електронната таблица по-долу.

В колона А са посочени годините, през които са спечелени приходите. В колона Б има приходите на дружеството през съответната година. С формулата CAGR в excel можете да изчислите годишния темп на растеж на приходите.

Въведете формулата по-долу, за да изчислите CAGR по директния метод:

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

В примера по-долу началната стойност на инвестицията е в клетка B2, а крайната стойност в клетка B11. Броят на годините (периода) между началото и края на инвестиционния период е 9. Обикновено всеки период на инвестиционен цикъл започва една година и завършва следващата година, следователно първият период от цикъла в този случай е 2011 г. -2012 г., а последният цикъл е 2019-2020 г. Така общият брой инвестирани години е „9“

Резултатът ще бъде в десетични числа, а не в проценти, както е показано по-горе. За да го преобразувате в процент, отидете в раздела „Начало“, щракнете върху падащото меню, което казва „Общи“ в групата „Числа“ и изберете опцията „% процент“.

Сега имаме комбинирания годишен темп на растеж, който е „10,77%“ в клетка B13. Това е единственият изгладен темп на растеж за целия период от време.

Изчисляване на CAGR в Excel с помощта на функцията RRI

Функцията RRI измерва периодичен еквивалентен лихвен процент за възвръщаемост на инвестиция или заем за определен период от време.

Лихвеният процент се изчислява въз основа на настоящата и бъдещата стойност на инвестицията и продължителността.

Синтаксисът:

=RRI(nper,pv,fv)
  • nper – общ брой периоди (години)
  • pv – Това определя настоящата стойност на инвестиция или заем (същата като началната стойност)
  • fv – Това определя бъдещата стойност на инвестиция или заем (същата като крайната стойност)

Имаме стойността за nper в клетка A11, pv в клетка C2 и fv в клетка C11. Използвайте тази формула:

=RRI(A11,C2,C11)

CAGR е „10,77%“, което е в клетка B13.

Изчисляване на CAGR в Excel с помощта на функция POWER

Друг лесен метод за изчисляване на сложния годишен темп на растеж (CAGR) в Excel е използването на функцията POWER. Функцията POWER замества ^ оператор във функцията CAGR.

Синтаксисът на функцията POWER:

=МОЩНОСТ(число,мощност)

Аргументи на функцията POWER:

  • номер – Това е базовото число, намерено чрез разделяне на крайната стойност (EV) на началната стойност (BV) (EV/BV).
  • мощност – Това е да се повиши резултатът до степен на единица, разделена на продължителност (1/брой периоди (n)).

Сега аргументите са дефинирани по този начин, за да се намери стойността на CAGR:

=МОЩНОСТ(EV/BV,1/n)-1

Нека приложим формулата към пример:

=МОЩНОСТ(C11/C2,1/A11)-1

Резултатът:

Изчисляване на CAGR в Excel с помощта на функция RATE

Функцията Rate е още една функция, която можете да използвате, за да намерите CAGR в Excel. Когато погледнете синтаксиса на функцията RATE, може да изглежда малко сложен с не по-малко 6 аргумента, но след като разберете функцията, може да предпочетете този метод за намиране на стойността на CAGR.

Синтаксисът на функцията RATE:

=RATE(nper,pmt,pv,[fv],[type],[pogoes])

Където:

  • nper – Общият брой периоди на плащания (срокът на кредита).
  • Pmt (по избор) – сумата на плащането, извършено във всеки период.
  • Pv – Това определя настоящата стойност на заема/инвестицията (начална стойност (BV))
  • [Fv]– Това указва бъдещата стойност на заема/инвестицията при последното плащане (крайна стойност (EV))
  • [Тип] – Това указва кога плащанията по заема/инвестицията са дължими, това е 0 или 1. Аргументът 0 означава, че плащанията са дължими в началото на периода, а 1 означава, че плащанията са дължими в края на периода (по подразбиране е 0).
  • [Познай] – Вашето предположение за скоростта. Ако се пропусне, по подразбиране е 10%.

Причината, поради която функцията RATE има шест аргумента, е, че може да се използва за много други финансови изчисления. Но можем да преобразуваме функцията RATE във формула CAGR само с три аргумента 1-ви (nper), 3-ти (pv) и 4-ти (fv) аргументи:

= СТАВКА(nper,,-BV,EV)

Тъй като не извършваме редовни плащания (месечни, тримесечни, годишни), оставяме втория аргумент празен.

За да изчислите комбинирания годишен темп на растеж с помощта на функция RATE, използвайте тази формула:

= РАБОТА(A11,,-C2,C11)

Ако не искате да изчислявате броя на периодите ръчно, използвайте функцията ROW като първи аргумент на формулата RATE. Тя ще изчисли npr за теб.

=ЦЕНА(РЕД(A11)-РЕД(A2),,-C2,C11)

Изчисляване на CAGR в Excel с помощта на функция IRR

Съкращението IRR за „Вътрешна възвръщаемост на лихвените проценти“ е функция на Excel, която изчислява IRR за плащания и приходи, извършвани на редовни интервали (т.е. месечни, годишни).

Методът IRR е полезен, когато трябва да изчислите стойността на CAGR за периодични парични потоци.

Синтаксисът е:

=IRR(стойности,[предположение])

Където:

  • стойности – набор от плащания. Обхватът на плащанията трябва да има поне един отрицателен и един положителен паричен поток.
  • [предполагам] (По избор) – Това представлява вашето предположение за стойността на курса. Ако се игнорира, по подразбиране е 10%.

Функцията Excel IRR изисква от вас да коригирате повторно набора от данни по този начин:

Началната стойност трябва да се вмъкне като отрицателно число, крайната стойност е положително число, а всички останали стойности като нули.

Ето формулата за примера:

=IRR(C2:C11)

Е, това са начините, по които можете да изчислите комбинирания годишен темп на растеж (CGAR) в Excel.