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

Узнайте, как можно рассчитать среднегодовой темп роста (CAGR) в Excel с помощью операторов или различных функций Excel.

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

Фондовые рынки нестабильны, рост инвестиций может меняться из года в год. Возврат инвестиций может увеличиваться или уменьшаться. CAGR помогает сгладить возврат инвестиций, как если бы они росли со сбалансированной скоростью каждый год.

Хотя в Excel нет функции CAGR, есть несколько способов рассчитать CAGR в Excel. В этой статье показано, как создавать четкие и простые для понимания формулы CAGR с использованием различных функций Excel.

Как создать Совокупный среднегодовой темп роста (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.

Предположим, у нас есть данные о продажах определенной компании в таблице ниже.

В столбце A указаны годы получения доходов. В столбце B указана выручка компании за соответствующий год. С помощью формулы CAGR в Excel вы можете рассчитать годовой темп роста выручки.

Введите формулу ниже, чтобы рассчитать CAGR прямым методом:

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

В приведенном ниже примере начальная стоимость инвестиции находится в ячейке B2, а конечное значение - в ячейке B11. Количество лет (период) между началом и концом инвестиционного периода составляет 9. Обычно каждый период инвестиционного цикла начинается с одного года и заканчивается в следующем году, следовательно, первым периодом цикла в данном случае является 2011 год. -2012 и последний цикл 2019-2020 гг. Таким образом, общее количество вложенных лет составляет «9».

Результат будет в десятичных числах, а не в процентах, как показано выше. Чтобы преобразовать его в процент, перейдите на вкладку «Главная», щелкните раскрывающееся меню с надписью «Общие» в группе чисел и выберите вариант «% в процентах».

Теперь мы получили совокупный годовой темп роста, который в ячейке B13 составляет «10,77%». Это единый сглаженный темп роста за весь период времени.

Расчет CAGR в Excel с помощью функции RRI

Функция RRI измеряет периодическую эквивалентную процентную ставку для возврата инвестиций или ссуды за определенный период времени.

Процентная ставка рассчитывается на основе текущей и будущей стоимости инвестиций и продолжительности.

Синтаксис:

= RRI (nper; pv; fv)
  • кпер - общее количество периодов (лет)
  • pv - Здесь указывается текущая стоимость инвестиции или кредита (такая же, как начальная стоимость).
  • fv - Здесь указывается будущая стоимость инвестиции или ссуды (такая же, как конечная стоимость)

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

= RRI (A11; C2; C11)

CAGR составляет «10,77%», что соответствует ячейке B13.

Расчет CAGR в Excel с помощью функции POWER

Еще один простой метод расчета среднегодового темпа роста (CAGR) в Excel - использование функции СТЕПЕНЬ. Функция POWER заменяет ^ оператор в функции CAGR.

Синтаксис функции СТЕПЕНЬ:

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

Аргументы функции СТЕПЕНЬ:

  • номер - Это базовое число, полученное путем деления конечного значения (EV) на начальное значение (BV) (EV / BV).
  • сила - Возвести результат в степень единицы, деленную на продолжительность (1 / количество периодов (n)).

Теперь аргументы определены таким образом, чтобы найти значение CAGR:

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

Применим формулу к примеру:

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

Результат:

Расчет CAGR в Excel с помощью функции RATE

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

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

= СТАВКА (кол-во, п.п., pv, [fv], [тип], [предположение])

Где:

  • кпер – Общее количество платежей за период (срок кредита).
  • PMT (необязательно) - сумма платежа за каждый период.
  • Pv - Здесь указывается приведенная стоимость ссуды / инвестиции (начальная стоимость (BV))
  • [Fv]- Здесь указывается будущая стоимость ссуды / инвестиции при последнем платеже (конечная стоимость (EV))
  • [Тип] - Указывает, когда должны быть произведены платежи по ссуде / инвестициям, это либо 0, либо 1. Аргумент 0 означает, что платежи подлежат оплате в начале периода, а 1 означает, что платежи должны быть произведены в конце периода (по умолчанию 0).
  • [Предполагать] - Ваше предположение о скорости. Если не указано, по умолчанию используется 10%.

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

= СТАВКА (кпер ,, - BV, EV)

Поскольку мы не производим регулярные платежи (ежемесячно, ежеквартально, ежегодно), второй аргумент оставляем пустым.

Чтобы рассчитать совокупный годовой темп роста с помощью функции СТАВКА, используйте эту формулу:

= СКОРОСТЬ (A11 ,, - C2; C11)

Если вы не хотите рассчитывать количество периодов вручную, используйте функцию СТРОКА в качестве первого аргумента формулы RATE. Он вычислит энергетический ядерный реактор для тебя.

= СКОРОСТЬ (СТРОКА (A11) -РОН (A2) ,, - C2, C11)

Расчет CAGR в Excel с помощью функции IRR

IRR, сокращение от «Internal Rate Return», - это функция Excel, которая рассчитывает IRR для платежей и доходов, происходящих через регулярные промежутки времени (т. Е. Ежемесячно, ежегодно).

Метод IRR полезен, когда вам нужно рассчитать значение CAGR для периодических денежных потоков.

Синтаксис:

= IRR (значения; [предположить])

Где:

  • ценности - ряд выплат. В диапазоне выплат должен быть как минимум один отрицательный и один положительный денежные потоки.
  • [предполагать] (Необязательно) - это ваше предположение о стоимости ставки. В случае игнорирования значение по умолчанию составляет 10%.

Функция Excel IRR требует, чтобы вы перенастроили набор данных следующим образом:

Начальное значение должно быть вставлено как отрицательное число, конечное значение - положительное число, а все остальные значения - как нули.

Вот формула для примера:

= ВСД (C2: C11)

Итак, это способы, которыми вы можете рассчитать среднегодовой темп роста (CGAR) в Excel.