Как рассчитать CAGR в Excel

Обновлено: 20.11.2024

CAGR или совокупный годовой темп роста — это метод расчета темпов роста определенной суммы за год. По умолчанию у нас нет встроенной формулы в Excel для расчета CAGR, вместо этого мы создаем категории в таблицах и в В таблицах мы применяем следующую формулу для расчета CAGR, которая выглядит следующим образом: (конечный баланс/начальный баланс)˄(1/количество лет) – 1.

Формула CAGR в Excel (Совокупный годовой темп роста)

Формула CAGR в Excel — это функция, которая отвечает за возврат значения CAGR, т. е. значение среднего годового прироста из предоставленного набора значений. Если вы занимаетесь финансовым анализом или планированием Финансовый анализ или планирование Финансовое планирование и анализ (FP&A) — это составление бюджета, анализ и прогнозирование финансовых данных в соответствии с финансовыми целями и поддержкой стратегических решений. Это помогает инвесторам узнать, является ли компания стабильной и прибыльной для инвестиций. подробнее , вам нужно будет рассчитать совокупный годовой темп роста в значении Excel в электронных таблицах Excel.

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

Например, если в 2010 году вы купили золото на 200 долларов США, а в 2018 году оно стоит 500 долларов США, CAGR – это скорость, с которой эти инвестиции растут каждый год.

  • Конечная стоимость = конечная стоимость инвестиций
  • Начальная стоимость = начальная стоимость инвестиции
  • n = количество инвестиционных периодов (месяцев, лет и т. д.)

Возвращаемое значение:

  • Возвращаемое значение будет числовым значением, которое можно преобразовать в проценты, поскольку значение CAGR эффективно, когда оно выражено в процентах.

Как использовать формулу CAGR в Excel с примерами?

Давайте разберемся, как использовать формулу CAGR в Excel на примерах.

Давайте рассмотрим приведенную ниже таблицу Excel. Посмотрите на данные.

Ниже приведены шаги формулы CAGR в Excel –

    Вы можете видеть приведенную выше электронную таблицу, в которой столбец A был отнесен к категории YEAR, а столбец B был отнесен к категории AMOUNT.

В столбце YEAR значение начинается с ячейки A2 и заканчивается ячейкой A10.

Снова в столбце СУММА значение начинается с ячейки B2 и заканчивается ячейкой B10.

Таким образом, мы видим, что начальная стоимость инвестиций (SV) — это ячейка B2, а конечная стоимость инвестиций (EV) — ячейка B10.

В приведенном выше примере совокупного годового роста в Excel конечное значение – B10, начальное значение – B2, а количество периодов – 9. См. снимок экрана ниже.

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

Вы также можете использовать формулу СТЕПЕНЬ в Excel Формула СТЕПЕНЬ В Excel функция СТЕПЕНЬ вычисляет степень данного числа или основания. Чтобы использовать эту функцию, вы можете использовать ключевое слово =POWER( в ячейке и указать два аргумента, один как число, а другой как мощность. Узнайте больше о методе нахождения значения CAGR в электронной таблице Excel. Формула будет «=POWER (окончание Значение/Начальное значение, 1/9)-1". Вы можете видеть, что функция СТЕПЕНЬ заменяет ˆ, которая использовалась в традиционной формуле CAGR в Excel. Если мы используем функцию СТЕПЕНЬ в приведенной выше электронной таблице Excel, где мы использовали при традиционном методе определения среднегодового темпа роста результат будет равен 0,110383 или 11,03%. Посмотрите на снимок экрана ниже.

Это гораздо менее используемый метод расчета среднегодового среднегодового темпа роста (CAGR) или процента, но также и простой способ. Синтаксис функции СТАВКА в Excel Функция СТАВКА В Excel Функция Ставка в Excel используется для расчета ставки, взимаемой за период кредита. Требуемые входные данные для этой функции: количество периодов оплаты, pmt, текущая стоимость и будущая стоимость. читать дальше может показаться вам немного сложным, но если вы хорошо знаете термины, это тоже не составит для вас особого труда. Синтаксис функции RATE приведен ниже-

=СТАВКА (nper, pmt, pv, [fv], [тип], [предположение])

Теперь давайте объясним приведенные выше термины.

  • nper — (обязательно) общее количество платежей, совершенных за определенный период.
  • pmt — (обязательно) это сумма платежа, который производится за каждый период.
  • pv — (обязательно) текущее значение.
  • fv — (необязательно) это будущая стоимость.
  • тип — это означает, что платежи должны быть произведены. Значение равно 0 или 1. 0 означает, что платеж должен был быть произведен в начале, а 1 – в конце периода.

IRR IRR Внутренняя норма доходности (IRR) — это ставка дисконтирования, которая устанавливает нулевую чистую текущую стоимость всех будущих денежных потоков от проекта. Он сравнивает и выбирает лучший проект, при этом выбирается проект с IRR, превышающим минимально допустимую доходность (пороговую ставку). читать далее — это сокращение от внутренней нормы доходности. Метод IRR полезен, когда вам нужно найти значение CAGR (Совокупный годовой темп роста) для платежей различной стоимости, которые были сделаны в течение определенного периода времени. Синтаксис функции IRR в Excel Синтаксис функции IRR в Excel Внутренняя норма доходности, или IRR, рассчитывает прибыль, полученную от финансовых инвестиций. IRR — это встроенная в Excel функция, которая вычисляет IRR, используя диапазон значений в качестве входных данных и оценочное значение в качестве второго входного параметра. Читать дальше: «=IRR (значения, [предположение])». Значения означают общий диапазон Чисел, который представляет денежные потоки. Этот раздел должен содержать одно положительное и одно отрицательное значение. [Угадай] в необязательном аргументе синтаксиса, что означает ваше предположение о возможной частоте возврата.

Ошибки формулы CAGR

Что нужно помнить

Рекомендуемые статьи

Это пошаговое руководство по формуле CAGR в Excel. Здесь мы обсуждаем, как использовать формулу CAGR в Excel, используя базовую формулу, метод мощности, функцию скорости и функцию IRR, и используем ее для решения проблем в Excel вместе с примером Excel и загружаемыми шаблонами Excel. Вы также можете посмотреть на эти полезные функции в Excel

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

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

CAGR с функцией RRI

В Excel 2013 и более поздних версиях вы можете использовать функцию RRI для расчета CAGR с помощью простой формулы. Формула в H9:

где C11 — конечная стоимость в 5-м году, C6 — начальная стоимость (первоначальные инвестиции), а B11 ​​— общее количество периодов.

Примечание. В отличие от большинства других финансовых функций в Excel, fv (будущее значение, третий аргумент) не нужно вводить в RRI как отрицательное число.

CAGR с ручной формулой

Формула для расчета CAGR вручную:

В показанном примере формула в H7 выглядит следующим образом:

где C11 — конечная стоимость в 5-м году, C6 — начальная стоимость или первоначальные инвестиции, а B11 ​​— общее количество периодов.

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

CAGR с функцией GEOMEAN

Функция GEOMEAN вычисляет среднее геометрическое, а также может использоваться для расчета CAGR. Чтобы рассчитать CAGR с помощью GEOMEAN, нам нужно использовать относительные изменения (процентное изменение + 1), иногда называемое фактором роста. У нас уже есть эти значения в столбце E, поэтому мы можем использовать их непосредственно в функции GEOMEAN. Формула в H8:

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

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

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

Что такое совокупный годовой темп роста?

Совокупный годовой темп роста (сокращенно CAGR) – это финансовый термин, который измеряет среднегодовой темп роста инвестиций за определенный период времени.

Чтобы лучше понять логику CAGR, рассмотрим следующий пример. Предположим, вы видите в финансовом отчете вашей компании следующие цифры:

Нет ничего сложного в том, чтобы рассчитать годовой темп роста, используя обычную формулу процентного увеличения, как показано на снимке экрана ниже:

Но как получить одно число, показывающее темпы роста за 5 лет? Есть два способа вычислить это - среднегодовой и составной темпы роста. Совокупный темп роста является лучшим показателем по следующим причинам:

  • Средний годовой темп роста (AAGR) представляет собой среднее арифметическое ряда темпов роста, и его легко рассчитать с помощью обычной формулы СРЕДНЕГО. Однако он полностью игнорирует эффект начисления сложных процентов, поэтому рост инвестиций может быть переоценен.
  • Совокупный годовой темп роста (CAGR) – это среднее геометрическое, отражающее норму прибыли на инвестиции, как если бы она увеличивалась с постоянной скоростью каждый год. Другими словами, CAGR – это "сглаженный" темп роста, который при ежегодном начислении будет эквивалентен сумме ваших инвестиций за определенный период времени.

Формула CAGR

Общая формула CAGR, используемая в бизнесе, финансах и инвестиционном анализе, выглядит следующим образом:

  • BV – начальная стоимость инвестиций.
  • EV – конечная стоимость инвестиций.
  • n — количество периодов (например, лет, кварталов, месяцев, дней и т. д.)

Как показано на следующем снимке экрана, формулы Average и CAGR возвращают разные результаты:

Чтобы упростить понимание, на следующем изображении показано, как рассчитывается CAGR для разных периодов с точки зрения BV, EV и n:

Как рассчитать CAGR в Excel

Теперь, когда у вас есть общее представление о том, что такое совокупный годовой темп роста, давайте посмотрим, как вы можете рассчитать его на листах Excel. Всего существует 4 способа создания формулы Excel для CAGR.

Формула 1: прямой способ создания калькулятора CAGR в Excel

Зная общую формулу CAGR, описанную выше, создание калькулятора CAGR в Excel занимает несколько минут, если не секунд. Просто укажите следующие значения на листе:

  • BV – начальная стоимость инвестиций.
  • EV – конечная стоимость инвестиций.
  • n – количество периодов.

Затем введите формулу CAGR в пустую ячейку:

В этом примере BV находится в ячейке B1, EV — в ячейке B2, а n — в ячейке B3. Итак, мы вводим следующую формулу в ячейку B5:

=(B2/B1)^(1/B3)-1

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

Для расчета CAGR в нашем образце таблицы используется следующая формула:

=(B7/B2)^(1/(СТРОКА(B7)-СТРОКА(B2)))-1

Совет. Если выходное значение отображается в виде десятичного числа, примените процентный формат к ячейке формулы.

Формула CAGR 2: функция RRI

Самый простой способ рассчитать совокупный годовой темп роста в Excel — использовать функцию RRI, которая предназначена для возврата эквивалентной процентной ставки по кредиту или инвестиции за определенный период на основе текущей стоимости, будущей стоимости и общей суммы. количество периодов:

  • Кпер – общее количество периодов.
  • Pv – текущая стоимость инвестиций.
  • Fv — это будущая стоимость инвестиции.

С nper в B4, pv в B2 и fv в B3 формула принимает следующий вид:

Формула CAGR 3: функция POWER

Еще один быстрый и простой способ расчета среднегодового темпа роста в Excel — использование функции СТЕПЕНЬ, которая возвращает результат возведения числа в определенную степень.

Синтаксис функции POWER следующий:

Где число — это базовое число, а степень — это показатель степени, в который нужно возвести базовое число.

Чтобы сделать калькулятор Excel CAGR на основе функции POWER, определите аргументы следующим образом:

  • Число — конечное значение (EV) / начальное значение (BV)
  • Сила – 1/количество периодов (n)

А вот и наша МОЩНАЯ формула CAGR в действии:

=МОЩНОСТЬ(B7/B2,1/5)-1

Как и в первом примере, вы можете использовать функцию ROW для расчета количества периодов:

Формула CAGR 4: функция RATE

Еще один метод расчета CAGR в Excel — использование функции RATE, которая возвращает процентную ставку за период аннуитета.

На первый взгляд, синтаксис функции СТАВКА выглядит немного сложным, но как только вы поймете аргументы, вам может понравиться этот способ расчета CAGR в Excel.

  • Кпер - общее количество платежей по аннуитету, т.е. количество периодов, в течение которых должен быть выплачен кредит или инвестиция. Обязательно.
  • Pmt – сумма платежа за каждый период. Если он опущен, необходимо указать аргумент fv.
  • Pv — текущая стоимость инвестиций. Обязательно.
  • Fv – будущая стоимость инвестиций по окончании n платежей. Если этот параметр опущен, формула принимает значение по умолчанию, равное 0.
  • Тип — необязательное значение, указывающее срок платежа:
    • 0 (по умолчанию) — платежи должны быть произведены в конце периода.
    • 1 — платежи должны быть произведены в начале периода.

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

    Напомню вам, что:

    • BV — это начальная стоимость инвестиции.
    • EV – это конечная стоимость инвестиций.
    • n – количество периодов.

    Для расчета совокупного темпа роста в этом примере используется следующая формула:

    Чтобы избавить себя от необходимости подсчитывать количество периодов вручную, вы можете попросить функцию ROW вычислить его за вас:

    =СТАВКА(СТРОКА(B7)-СТРОКА(B2),,-B2,B7)

    Формула CAGR 5: функция IRR

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

    • Значения – диапазон чисел, представляющих денежные потоки. Диапазон должен содержать хотя бы одно отрицательное и хотя бы одно положительное значение.
    • [Guess] — необязательный аргумент, представляющий ваше предположение о возможной норме прибыли. Если этот параметр опущен, используется значение по умолчанию 10 %.

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

    • Начальная стоимость инвестиций должна быть указана как отрицательное число.
    • Конечная стоимость инвестиций — положительное число.
    • Все промежуточные значения равны нулю.

    После того как ваши исходные данные реорганизованы, вы можете рассчитать CAGR с помощью этой простой формулы:

    Где B2 — начальная стоимость, а B7 — конечная стоимость инвестиций:

    Вот как можно рассчитать CAGR в Excel. Если вы внимательно следили за примерами, то могли заметить, что все 4 формулы возвращают одинаковый результат — 17,61%. Чтобы лучше понять и, возможно, перепроектировать формулы, вы можете загрузить образец рабочего листа ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!


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

    Совокупный годовой темп роста (CAGR)

    CAGR означает совокупный годовой темп роста. CAGR — это среднегодовой темп роста за определенный период времени. Другими словами, CAGR представляет собой доход, который был бы получен при условии постоянного темпа роста в течение периода. На самом деле темпы роста должны меняться из года в год.

    Формула CAGR

    Рассчитать CAGR в Excel

    FV, PV, N

    Если у вас есть FV, PV и n, просто подставьте их в стандартное уравнение CAGR, чтобы вычислить CAGR.

    Функция оценки

    Вы также можете использовать функцию оценки Excel:
    = СТАВКА (nper, pmt, pv, [fv], [type], [guess])
    nper — общее количество периодов оплаты
    pmt – Сумма платежа. Оплата должна быть постоянной.
    pv – Текущая стоимость.
    fv – [необязательно] Будущая стоимость или баланс денежных средств, который вы хотите получить после последней PMT. 0, если опущено.
    type – [необязательно] Тип платежа. 1 на начало периода. 0 для конца периода (по умолчанию, если опущено).
    угадай – [необязательно] Ваше предположение о том, какой будет ставка. 10 %, если опущено.


    =RATE(B5,C5,D5)
    Результат: 12%

    Вы можете использовать функцию RATE только в том случае, если денежные потоки происходят на периодической основе, то есть денежные потоки должны происходить в одно и то же время ежегодно, раз в полгода, ежеквартально и т. д. Если денежные потоки не являются периодическими, используйте XIRR вместо этого вычисляет CAGR.

    Функция ЧИСТВНДОХ

    Для непериодических денежных потоков используйте функцию ЧИСТВНР для расчета среднегодового темпа роста. При использовании функции ЧИСТНДОХ вы создаете два столбца. Первый столбец содержит денежные потоки. Второй столбец содержит соответствующие даты денежных потоков.

    =XIRR(B5:B8;C5:C8;0,25)
    Результат: 24 %

    Функция IRR

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

    =IRR(B5:B10,.25)
    Результат: 8%

    Применить процентное форматирование к CAGR

    После того, как вы закончите расчет CAGR, ваш результат может отображаться в виде десятичной дроби: например. .103. Чтобы преобразовать ваш ответ в проценты (10,3%), вам нужно изменить форматирование чисел на проценты:

    Практический лист Excel

    Попрактикуйтесь в функциях и формулах Excel с помощью наших 100 % бесплатных тренировочных листов!

    Читайте также: