Какова формула расчета среднегодового темпа роста (CAGR) в Excel?

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

Что такое CAGR?

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

• 2015: 100 долларов
• 2016: 120 долларов
• 2017: 125 долларов

С конца 2015 года по конец 2016 года цена выросла на 20% (со 100 до 120 долларов). С конца 2016 года по конец 2017 года цена выросла на 4,17% (со 120 до 125 долларов). В годовом исчислении эти темпы роста различаются, но мы можем использовать приведенную ниже формулу, чтобы найти единый темп роста для всего периода времени.

CAGR требует трех входных данных: начальной стоимости инвестиции, ее конечной стоимости и периода времени (выраженного в годах). Онлайн-инструменты, в том числе калькулятор CAGR от Investopedia , дадут CAGR при вводе этих трех значений. Формула:

Подставляя указанные выше значения, мы получаем [(125/100) ^ (1/2) — 1] для CAGR 11,8%. Несмотря на то, что цена акций ежегодно росла разными темпами, общий темп роста можно определить как 11,8%.

Советы и приемы для расчета CAGR

Одна ошибка, которую легко сделать при вычислении CAGR, — это неправильный подсчет периода времени. Например, в приведенном выше примере есть три календарных года. Но поскольку данные представлены в виде цен на конец года, на самом деле у нас есть только два полных года. Вот почему уравнение читается как 1/2, а не 1/3.

Теперь предположим, что у нас есть акции, годовые данные о ценах которых были представлены в процентах, а не в долларовом выражении:

• 2015: 10%
• 2016: 15%
• 2017: -4%

В этом случае данные отображаются с начала года, например, полная годовая доходность в 2015 году (10%), полностью годовая доходность в 2016 году (15%) и полная годовая доходность в 2017 году (- 4%). Таким образом, при расчете CAGR мы фактически будем работать с периодом времени в три года.

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

Расчет CAGR в Excel

Математическая формула такая же, как и выше: вам нужны конечные значения, начальные значения и длина, измеряемая в годах. Хотя в Excel есть встроенная формула, она далека от идеала, поэтому мы объясним ее последнее.

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

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

Среднегодовой темп продаж за десятилетие составляет 5,43%.

Более сложная ситуация возникает, когда период измерения не в четных годах. Это почти уверенность, когда речь идет об инвестиционных декларациях, по сравнению с однолетними показателями продаж. Решение состоит в том, чтобы вычислить общее количество завершенных лет и добавить их к неполному году (так называемому году-заглушке).

Возьмем те же цифры, но пусть они будут курсами акций:

Плюсы и минусы CAGR

CAGR превосходит другие расчеты, например, в среднем возвращается, потому что он принимает во внимание тот факт, что значения соединения с течением времени.

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

• 2015: прирост 25%
• 2016: потеря 40%
• 2017: прирост на 54%

На самом деле это 5% CAGR, но годовая волатильность этих доходов огромна. Реальность такова, что многие инвестиции испытывают значительные краткосрочные взлеты и падения, и, сглаживая их, так сказать, CAGR может дать численно точное, но эмоционально обманчивое впечатление о производительности. Это похоже на карту, которая правильно сообщает вам, что ваш пункт назначения находится всего в пяти милях от вас, без указания неровности дороги.

CAGR также может изменяться в зависимости от периода измерения, который в конечном итоге (а часто и произвольно) выбирается. Среднегодовой темп роста может быть изменен, чтобы избежать негативного года на фондовом рынке (например, 2008 г.) или включить год высоких результатов (например, 2013 г.).

Суть

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