Формулы составного годового темпа роста
В учебнике объясняется, что такое совокупный годовой темп роста и как составить четкую и понятную формулу CAGR в Excel.
В одной из наших предыдущих статей мы рассказали о силе сложных процентов и о том, как их рассчитать в Excel. Сегодня мы сделаем еще один шаг и рассмотрим различные способы расчета совокупного годового темпа роста (CAGR).
Проще говоря, CAGR измеряет возврат инвестиций за определенный период времени. Строго говоря, это не бухгалтерский термин, но его часто используют финансовые аналитики, инвестиционные менеджеры и владельцы бизнеса, чтобы выяснить, как развивался их бизнес, или сравнить рост доходов конкурирующих компаний.
В этом руководстве мы не будем углубляться в арифметику и сосредоточимся на том, как написать эффективную формулу CAGR в Excel, которая позволяет рассчитать совокупный годовой темп роста на основе 3 основных входных значений: начального значения инвестиций, конечного значения и периода времени. .
Что такое совокупный годовой темп роста?
Совокупный годовой темп роста (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 в пустую ячейку:
знак равноэлектромобиль/БВ)^(1/н)-1
В этом примере BV находится в ячейке B1, EV — в B2, а n — в B3. Итак, вводим в ячейку B5 следующую формулу:
=(В2/В1)^(1/В3)-1
Если у вас есть все значения инвестиций, перечисленные в каком-либо столбце, вы можете добавить степень гибкости в свою формулу CAGR и автоматически рассчитывать количество периодов.
знак равноэлектромобиль/БВ)^(1/(СТРОКА(электромобиль)-СТРОКА(БВ)))-1
Чтобы рассчитать CAGR в нашем образце рабочего листа, формула выглядит следующим образом:
=(B7/B2)^(1/(СТРОКА(B7)-СТРОКА(B2))-1
Кончик. Если выходное значение отображается в виде десятичного числа, примените процентный формат к ячейке формулы.
Формула CAGR 2: функция RRI
Самый простой способ рассчитать совокупный годовой темп роста в Excel — использовать функцию RRI, которая предназначена для возврата эквивалентной процентной ставки по кредиту или инвестиции за определенный период на основе текущей стоимости, будущей стоимости и общего количества периодов. :
RRI(nper, pv, fv)
Где:
- Кпер – общее количество периодов.
- Pv — текущая стоимость инвестиций.
- Fv — будущая стоимость инвестиций.
С Например в В4, пв в В2 и фв в B3 формула принимает следующий вид:
=RRI(B4, B2, B3)
Формула CAGR 3: функция POWER
Еще один быстрый и простой способ расчета CAGR в Excel — использование функции POWER, которая возвращает результат возведения числа в определенную степень.
Синтаксис функции СТЕПЕНЬ следующий:
МОЩНОСТЬ(число, мощность)
Где число — это базовое число, а степень — это показатель степени, в который нужно возвести базовое число.
Чтобы сделать калькулятор Excel CAGR на основе функции POWER, определите аргументы следующим образом:
- Число – конечное значение (EV) / начальное значение (BV)
- Мощность – 1/количество периодов (n)
=СИЛА(электромобиль/БВ1/н)-1
А вот и наша МОЩНАЯ формула CAGR в действии:
=МОЩНОСТЬ(B7/B2,1/5)-1
Как и в первом примере, вы можете использовать функцию ROW для расчета количества периодов:
=МОЩНОСТЬ(B7/B2,1/(СТРОКА(B7)-СТРОКА(B2)))-1
Формула CAGR 4: функция RATE
Еще один метод расчета CAGR в Excel — использование функции RATE, которая возвращает процентную ставку за период аннуитета.
СТАВКА(nper, pmt, pv, [fv], [type], [guess])
На первый взгляд, синтаксис функции СТАВКА выглядит немного сложным, но как только вы поймете аргументы, вам может понравиться этот способ расчета CAGR в Excel.
- Кпер – общее количество платежей по аннуитету, т.е. количество периодов, в течение которых кредит или инвестиции должны быть выплачены. Необходимый.
- Pmt – сумма платежа за каждый период. Если он опущен, необходимо указать аргумент fv.
- Pv – текущая стоимость инвестиции. Необходимый.
- Fv – будущая стоимость инвестиции в конце nper платежей. Если этот параметр опущен, формула принимает значение по умолчанию, равное 0.
- Тип — необязательное значение, указывающее, когда должны быть произведены платежи:
- 0 (по умолчанию) — платежи должны быть произведены в конце периода.
- 1 – платежи подлежат оплате в начале периода.
- Угадай – твое предположение о том, какой может быть ставка. Если опущено, предполагается, что оно равно 10%.
Чтобы превратить функцию RATE в формулу расчета CAGR, вам необходимо предоставить 1-й (nper), 3-й (pv) и 4-й (fv) аргументы следующим образом:
=СТАВКА(н,,-БВ, электромобиль)
Я напомню вам, что:
- BV — начальная стоимость инвестиций.
- EV – это конечная стоимость инвестиций.
- n – количество периодов
Примечание. Обязательно укажите начальное значение (BV) как отрицательное число, иначе ваша формула CAGR вернет #ЧИСЛО! ошибка.
Для расчета скорости роста соединения в этом примере используется следующая формула:
=СТАВКА(5,,-B2,B7)
Чтобы избавить себя от необходимости вычислять количество периодов вручную, вы можете сделать так, чтобы функция ROW вычислила его за вас:
=СТАВКА(СТРОКА(B7)-СТРОКА(B2),,-B2,B7)
Формула CAGR 5: функция IRR
Функция IRR в Excel возвращает внутреннюю норму прибыли для ряда денежных потоков, происходящих через равные промежутки времени (т. е. дни, месяцы, кварталы, годы и т. д.). Он имеет следующий синтаксис:
IRR(значения, [guess])
Где:
- Values — диапазон чисел, представляющих денежные потоки. Диапазон должен содержать хотя бы одно отрицательное и хотя бы одно положительное значение.
- [Guess] – необязательный аргумент, представляющий ваше предположение о возможной норме доходности. Если опущено, берется значение по умолчанию 10%.
Поскольку функция Excel IRR не совсем предназначена для расчета сложного темпа роста, вам придется изменить исходные данные следующим образом:
- Начальная стоимость инвестиций должна быть введена как отрицательное число.
- Конечная стоимость инвестиций является положительным числом.
- Все промежуточные значения равны нулю.
После того, как ваши исходные данные реорганизованы, вы можете рассчитать CAGR с помощью этой простой формулы:
=ВНР(B2:B7)
Где B2 — начальная стоимость, а B7 — конечная стоимость инвестиций:
Ну, вот как вы можете рассчитать CAGR в Excel. Если вы внимательно следили за примерами, то могли заметить, что все 4 формулы возвращают одинаковый результат — 17,61%. Чтобы лучше понять и, возможно, перепроектировать формулы, вы можете загрузить образец рабочего листа ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Формулы расчета CAGR (файл .xlsx)
