Як створити формулу 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.

Припустимо, що в таблиці нижче є дані про продажі певної компанії.

У стовпці А вказуються роки, за які були отримані доходи. У стовпці B зазначено виручку компанії за відповідний рік. За допомогою формули 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(число,потужність)

Аргументи функції POWER:

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

Тепер аргументи визначаються таким чином, щоб знайти значення CAGR:

=POWER(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],[gues])

де:

  • nper – Загальна кількість періодів платежів (термін кредиту).
  • Pmt (необов’язково) – сума платежу, здійсненого за кожен період.
  • Pv – Це вказує поточну вартість позики/інвестиції (початкова вартість (BV))
  • [Fv]– Це вказує майбутню вартість позики/інвестиції при останньому платежі (кінцева вартість (EV))
  • [Тип] – Це вказує, коли платежі за позику/інвестицію настають, це 0 або 1. Аргумент 0 означає, що платежі підлягають сплаті на початку періоду, а 1 означає, що платежі підлягають сплаті в кінці періоду (за умовчанням 0).
  • [Вгадай] – Ваша припущення про курс. Якщо його опустити, значення за замовчуванням становить 10%.

Причина, чому функція RATE має шість аргументів, полягає в тому, що її можна використовувати для багатьох інших фінансових розрахунків. Але ми можемо перетворити функцію RATE у формулу CAGR лише з трьома аргументами: 1-й (nper), 3-й (pv) і 4-й (fv) аргументи:

=RATE(nper,,-BV,EV)

Оскільки ми не здійснюємо регулярні платежі (щомісячні, щоквартальні, щорічні), ми залишаємо другий аргумент порожнім.

Щоб обчислити сукупний річний темп приросту за допомогою функції RATE, скористайтеся цією формулою:

=СТАВ(A11,,-C2,C11)

Якщо ви не хочете обчислювати кількість періодів вручну, використовуйте функцію ROW як перший аргумент формули RATE. Він буде обчислювати npr для вас.

=СТАВ(РЯДК(A11)-РЯДК(A2),,-C2,C11)

Розрахунок CAGR в Excel за допомогою функції IRR

Скорочення IRR від «Return Internal Rate Return» — це функція Excel, яка обчислює IRR для платежів і доходів, які відбуваються через регулярні проміжки часу (тобто щомісячно, щорічно).

Метод IRR корисний, коли вам потрібно розрахувати значення CAGR для періодичних грошових потоків.

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

=IRR(значення,[відгадка])

де:

  • цінності – діапазон платежів. Діапазон платежів повинен мати принаймні один негативний і один позитивний грошові потоки.
  • [здогадатися] (Необов’язково) – це ваше припущення значення ставки. Якщо ігнорувати, значення за замовчуванням становить 10%.

Функція Excel IRR вимагає від вас повторно налаштувати набір даних таким чином:

Початкове значення слід вставити як від’ємне число, кінцеве значення – додатне число, а всі інші значення – як нулі.

Ось формула для прикладу:

=IRR(C2:C11)

Ось як можна обчислити зведений річний темп зростання (CGAR) в Excel.