Как посчитать cagr в excel

Обновлено: 08.01.2025

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

CAGR или совокупный годовой темп роста - это норма прибыли, которая требуется с начала инвестирования до его конечного срока.

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

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

Объяснение CAGR (совокупный годовой темп роста)

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

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

В реальном сценарии такой рост маловероятен, однако он сглаживает отдачу, так что его легко сравнить с другими инвестициями.

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

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

CAGR Formula в Excel очень прост и удобен в использовании. Давайте разберем работу составной формулы годового темпа роста в Excel на нескольких примерах.

Вы можете скачать этот шаблон CAGR Formula Excel здесь - Шаблон CAGR Formula Excel

CAGR Формула в Excel - Пример № 1

У нас есть данные для компании ABC, как показано ниже,

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

Начальный баланс равен 100, а конечный баланс равен 150. Теперь мы подсчитаем количество лет. Первый период цикла - 2010–2011 годы, а последний - 2013–2014 годы, рассчитанный на четыре года.

Теперь в ячейке C2 рассчитаем годовой темп роста по формуле.

Таким образом, результат будет:

Теперь нажмите на знак% на главной вкладке в общем разделе.

Теперь у нас есть совокупный годовой темп роста, который составляет 11%.

CAGR Формула в Excel - Пример № 2

Давайте составим готовую формулу, в которую мы вставим значения, и она рассчитает совокупный годовой темп роста в excel.

Нам нужна начальная стоимость. Поэтому в ячейке A1 введите для нее заголовок как SV (начальное значение).

Теперь для конечного значения. Поэтому в ячейке B1 введите для нее заголовок как EV (конечное значение).

Для общего количества лет в ячейке C1 введите для него заголовок «NoY».

В ячейке D1 введите заголовок для конечного значения и введите заголовок как CAGR (составной годовой темп роста).

Скопируйте значение D2 в ячейку E2 и используйте знак% в ячейке E2 или напишите = D2 в ячейке E2 и нажмите знак%.

Теперь попробуйте поместить любые случайные значения в начальные значения, конечные значения и количество лет. Ячейка D2 дает значение CAGR.

И точная процентная ставка отображается в ячейке E2.

CAGR Формула в Excel - Пример № 3

Давайте возьмем другой пример формулы CAGR в Excel. У нас есть данные ниже,

Найти начальное значение в качестве начального баланса, который является B1.
Найти конечное значение в качестве конечного баланса, который является B5.
Подсчитайте количество лет, которое составляет 3.
В ячейке C1 запишите формулу для CAGR, которая (Конечный баланс / Начальный баланс) ˄ (1 / Количество лет) - 1.

Нажмите на знак% на домашней вкладке под общим разделом, чтобы получить значение CAGR.

Окончательное значение составляет 1%, что является необходимым ежегодным темпом роста.

Совокупный годовой темп роста в Excel сгладил темп роста за период.
Это означает, что рост был устойчивым.

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

Что нужно помнить о формуле CAGR в Excel

CAGR Formula в Excel рассчитывает только среднегодовой темп роста за период в несколько лет.
CAGR не показывает неравномерность темпов роста в средние годы.
Это только сгладило темп роста за период.

Что показывает PEG

Итак, коэффициент показывает, на сколько акции вырастут в будущем. Вспомним, как рассчитывается P/E: он представляет собой отношение рыночной стоимости ценных бумаг к прибыли компании на одну акцию. Но прибыль получена из данных отчетности, которая составлена по результатам прошлого года. Рыночная цена – это показатель стоимости бумаги на текущую дату. В результате получается некоторое искажение индикатора роста цены. Для устранения этого недостатка и разработан коэффициент Price Earnings Growth Ratio, что в переводе означает «коэффициент роста ценовой прибыли».

Индикатор определяет степень переоцененности или недооцененности акций рынком. Он нужен для сравнительного анализа предприятий с аналогичными показателями прибыли и выручки, но разной стоимостью ценных бумаг. Дело в том, что молодые компании, не успевшие зарекомендовать себя на рынке, не могут похвастаться высокой ликвидностью акций, поскольку они еще не завоевали доверие инвесторов. Но дальновидные инвесторы умеют распознавать такие компании. В результате они имеют возможность купить бумаги по низкой цене, а впоследствии получить прибыль. Одним из инструментов для анализа будущей доходности и является показатель PEG.

Как рассчитать коэффициент PEG (3 способа)

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

Первый способ – наиболее популярный. Индикатор можно вычислить через P/E. Формула выглядит так:

\( EGR \) – прогнозируемый рост прибыли на все акции. Он рассчитывается как среднее значение по данным компаний одной отрасли с аналогичными показателями прибыли.

\( P \) – рыночная стоимость всех ценных бумаг;

\( ЧП \) – чистая прибыль по данным финансовой отчетности;

\( N \) – количество бумаг.

Второй способ. Коэффициент PEG можно рассчитать через общую стоимость (капитализацию) и прибыль компании:

\( P \) – рыночная цена одной акции;

\( EGR \) – прогнозируемый темп роста прибыли на одну бумагу.

Третий способ. Расчет мультипликатора через среднегодовой темп роста – CAGR. Подробно об этом показателе вы можете почитать в отдельной статье. Напомню его формулу:

\( EV \) – инвестиции на конец периода;

\( BV \) – инвестиции на начало периода;

\( n \) – количество лет, за которые производится исследование.

А коэффициент PEG рассчитывается так:

P/E рассчитан исходя из стоимости всех ценных бумаг.

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

Интерпретация значения коэффициента PEG

Значение показателя	Интерпретация
3	Бумаги переоценены. Инвестировать можно, но существует риск получения убытков в будущем, когда спрос на акции упадет и котировки снизятся

Нормативное значение

Норматива для данного показателя не существует. Из таблицы мы видим, что чем выше значение Price Earnings Growth Ratio, тем более переоценены акции предприятия, а, следовательно, снижается его инвестиционная привлекательность. Отрицательное значение, в свою очередь, говорит об убыточной деятельности, а значит, и выплачивать дивиденды предприятию нечем.

Известный американский инвестор Питер Линч предложил считать оптимальным значение коэффициента равным единице. Именно при таком результате инвестиционный риск считается минимальным.

Применение PEG для поиска недооцененных акций

Для поиска компаний, привлекательных для инвестирования, используются специальные сервисы. Один из них – FinBox.io. В разделе «Screener» следует выбрать предприятия со значением PEG больше нуля, но менее единицы. Здесь же можно задать фильтры по странам и обороту компании.

Помимо значения нужного нам мультипликатора, имеет смысл добавить фильтр по Z-score (показателя вероятности банкротства, рассчитанного по модели Альтмана). Подробнее об этом коэффициенте вы можете почитать в статье «Модель Альтмана». Напомню, что его оптимальное значение должно быть выше 2,9.

Вот несколько примеров эмитентов недооцененных российских акций:

Наименование компании	Price Earnings to Growth Ratio (2020 г.)
Татнефть	0,26
ЛСР	0,05
Сургутнефтегаз	0,11
Черкизово	0,03

Мультипликатор PEGY (модификация PEG с учетом выплаты дивидендов)

Этот показатель применяется в фундаментальном анализе дивидендных компаний. Для его расчета в классической формуле PEG добавляется дивидендная доходность:

DY (dividend yield) – годовая дивидендная доходность.

Рассчитаем PEGY для компании «Ростелеком» за 2020 год:

Интерпретировать значения PEGY следует по той же шкале, что и PEG: результат от нуля до единицы указывает на то, что акции недооценены. Таким образом, Ростелеком можно отнести к дивидендным российским компаниям, привлекательным для инвестиций.

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

Основные преимущества показателя:

возможность определить перспективы предприятия – будущую прибыль и дивидендную доходность;
в сочетании с другими показателями PEG позволяет максимально глубоко оценить целесообразность инвестиций.

субъективность оценки. Еще раз напомню, что PEG следует использовать только в совокупности с другими мультипликаторами (ROE, ROI, FCF, Z-score и др.);
чистая прибыль не является единственным критерием надежности компании. Высокий показатель CAPEX (капитальных затрат), к примеру, может быть временным явлением. Кроме того, высокая чистая прибыль нередко обусловлена использованием заемных средств;
любые прогнозы могут оказаться ошибочными.

Примеры расчета PEG и PEGY

Рассчитаем коэффициенты PEG и PEGY по итогам 2020 года для нескольких компаний, рассматриваемых в качестве инвестиций. Для этого я буду использовать данные из открытых источников и редактор Excel.

Наименование эмитента	P/E	EGR (EPS)	DY	PEG	PEGY
Сургутнефтегаз	2,27	20,30	17,00	0,11	0,06
АКРОН	91,80	94,70	0,30	0,97	0,97
Фосагро	16,30	361,40	5,00	0,05	0,04
Газпром	63,40	5,70	3,50	11,12	6,89

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

Пример использования коэффициента PEG

А теперь сравним две американские компании – Apple и Tesla и сделаем выводы:

Тикер	P/E	EGR	PEG
AAPL	28,37	19,84	1,43
TSLA	440,92	54,03	8,16

Нетрудно сделать вывод, что компания Apple более привлекательна для долгосрочных инвестиций, поскольку у нее более низкие значения P/E и PEG. Акции Tesla сильно переоценены – это подтверждается ведущими финансовыми аналитиками. Инвестируя в эту компанию в 2021, можно неплохо заработать на разнице котировок. Но, поскольку Tesla активно вкладывается в криптовалюту, покупка ценных бумаг этого эмитента сопряжена с высоким риском.

Заключение

Коэффициент PEG – это модификация показателя PE с учетом роста ожидаемой доходности.
Показатель используется в сравнительном анализе. Простыми словами, коэффициент PEG позволяет выявить недооцененные компании, привлекательные для долгосрочных инвестиций.
Помимо расчета PEG, необходимо учитывать риски, характерные для вложений в определенную компанию или отрасль, а также рассчитать показатели свободного денежного потока, текущей ликвидности, окупаемости инвестиций (ROI), Z-score по модели Альтмана и др.
Показатель PEGY является модификацией PEG. Этот индикатор учитывает дивидендную доходность и применяется для сравнительного анализа эмитентов одной отрасли.

CAGR - это сокращение от совокупного годового темпа роста. Как следует из названия, это не что иное, как годовой темп роста бизнеса в течение определенного периода времени. Это выражается в процентах. И это значение очень полезно при сравнении показателей с прошлой нормой доходности, а также используется в качестве меры для определения будущей стоимости. Чтобы найти это, все, что нам нужно, это начальная стоимость инвестиций, конечная стоимость и количество лет. CAGR является наилучшей мерой для расчета доходности инвестиций, которые могут увеличиваться или уменьшаться в стоимости в течение определенного периода времени. Можно проверить две разные инвестиционные CAPG и то, насколько хорошо они выполняются по сравнению с другими инвестициями, чтобы можно было пойти с хорошими инвестициями. Однако это не является мерой оценки риска, который можно получить, инвестируя в конкретную инвестицию. Сложный годовой темп роста может быть рассчитан по следующей формуле.

CAGR = (EV / BV) (1 / NY) – 1

Примеры формулы CAGR (с шаблоном Excel)

Давайте рассмотрим пример, чтобы лучше понять расчет CAGR.

Вы можете скачать этот шаблон CAGR Formula Excel здесь - Шаблон CAGR Formula Excel

CAGR Formula - Пример № 1

Давайте предположим, что курс золота увеличивается, как показано ниже. Рассмотрим на момент инвестирования это RS.15000. Учитывайте только указанные цены или цены на конец года. Рассчитайте CAGR.

Примечание: NY = 3. Здесь указано общее количество лет 4. Но эти цены являются ценами на конец года, а не годовыми ценами. Так что нужно учитывать только завершенные годы. Так что это 3, а не 4.

Решение:

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

CAGR = (EV / BV) (1 / NY) - 1

CAGR = (25554/15000) (1/3 ) -1
CAGR = 19, 4%

CAGR Formula - Пример № 2

Давайте предположим, что цена инвестиций, как показано ниже. Рассмотрим на момент инвестирования это RS.4000. Рассмотрим цены в годовом исчислении. Рассчитайте CAGR.

Решение:

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

CAGR = (EV / BV) (1 / NY) - 1

CAGR = (24000/4000) (1/3) -1
CAGR = 81, 7%

объяснение

Шаг 1: Запишите значение EV. Здесь EV представляет конечную стоимость или будущую цену инвестиции. Тем не менее, конечное значение для каждого года может быть дано либо напрямую, либо в процентах от темпов роста каждый год. Если оно указано в процентном значении, лицо, выполняющее расчеты, должно преобразовать его в цену с помощью начальной цены, а затем использовать для расчета. Однако значение CAGR результата учитывается только в%.

Шаг 2: Запишите значение BV, которое является ничем иным, как текущей стоимостью или начальной ценой. Мы должны предположить, что каждый конец года урожайность реинвестируется.

Шаг 3: Запишите значение переменной NY, которая представляет количество лет. CAGR может быть в форме года или в форме дней. Это полностью зависит от того, как мы хотим рассчитать это. Скажем, если мы хотим рассчитать CAGR с точки зрения года, экспоненциальная форма будет 1 / NY. В противном случае, если мы хотим, чтобы это было в днях, мы можем переставить экспоненциальную форму в 365 / TND, в которой TND - это общее количество дней. Таким образом, приведенная выше формула может быть перестроена так, как показано ниже.

CAGR = (EV / BV) (1 / NY) - 1 = (EV / BV) (365 / TND) - 1

Шаг 4: Примените все значения в приведенном выше уравнении, чтобы узнать CAGR. В некоторых случаях может возникнуть путаница при определении количества лет. Для расчета, если указаны цены на конец года, следует учитывать только завершенные годы. В противном случае мы можем рассмотреть полные годы.

Актуальность и использование формулы CAGR

CAGR является наилучшей мерой для расчета доходности инвестиций, которые могут увеличиваться или уменьшаться в стоимости в течение определенного периода времени. Можно проверить две разные инвестиционные CAPG и то, насколько хорошо они выполняются по сравнению с другими инвестициями, чтобы можно было пойти с хорошими инвестициями. Основная часть заключается в том, что CAGR - очень простой и удобный метод для расчета нормы прибыли просто с 3 атрибутами. Это очень полезно при сравнении разных инвестиций с одинаковой нестабильностью. Он рассчитывает норму прибыли стабильно в течение периода времени с его предполагаемой составляющей. И он подлежит обработке, так как ввод данных для периода времени определяется пользователем, а не по определенной формуле. Поскольку пользователь желает, вводить ли время как в годах или заменять общее количество дней, преобразованное из заданного количества лет. Он может быть представлен только для положительного года, а не для отрицательного, или для того, чтобы показать лучшую производительность по сравнению с другими инвестициями, как было сказано ранее.

CAGR Формула Калькулятор

Вы можете использовать следующий калькулятор CAGR

Обяснение на CAGR (Съставен годишен темп на растеж)

CAGR се използва за изчисляване на средния ръст на една инвестиция. Поради нестабилността на пазара растежът от година на година може да варира. Инвестицията може да се увеличи и може да намалее. CAGR помага за плавна възвръщаемост, когато се очаква темповете на растеж да бъдат нестабилни и непостоянни.

Както беше обяснено по-горе, ще приведа същия аспект, комбинираният годишен ръст не е реална възвръщаемост. Това е представителна фигура. Това е число, което описва степента, с която инвестицията би нараснала, ако имаше една и съща ставка всяка година през периода.

В реален сценарий този вид растеж е малко вероятно да се случи, но той изглажда възвръщаемостта, така че да бъде лесно сравнен с другите инвестиции.

CAGR може да се разглежда като темп на растеж, който получаваме от първоначалната стойност на инвестицията до крайната стойност на инвестицията, ако приемем, че инвестицията се е усложнявала за определен период.

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

CAGR Formula в Excel е много проста и лесна за използване. Нека разберем работата на съставната годишна формула за растеж в Excel чрез някои примери.

Можете да изтеглите този шаблон на формула CAGR формула тук - шаблон на формула на CAGR формула

CAGR формула в Excel - пример №1

Имаме данни за ABC Company, както е посочено по-долу,

Данните показват стойността на продажбите, извършени през съответната година. Колона А има година, в която се извършват продажбите. В колона Б са извършени продажби за компанията през съответната година. Нека приемем, че продажната цена е в кр. (Crores). Както виждаме продажбите се увеличават и намаляват. По формула на CAGR в excel можем да изчислим годишния темп на растеж на продажбите.

Началният баланс е 100, а крайният - 150. Сега броим броя на годините. Първият период на цикъла е 2010-2011 г., а последният 2013-2014 г., който е за четири години.

Сега в Cell C2 нека изчислим годишния темп на растеж по формулата.

Така резултатът ще бъде: -

Сега кликнете върху% знак в раздела Начало в общата секция.

Сега имаме комбинираният годишен растеж, който е 11%.

CAGR формула в Excel - пример №2

Нека направим готова формула, в която ще вмъкнем стойностите и тя ще изчисли сложния годишен темп на растеж в excel.

Нуждаем се от начална стойност. Така че в клетката A1 напишете заглавка за нея като SV (начална стойност).

Сега за крайна стойност. Така че в клетка B1 напишете заглавката като EV (Ending Value).

За общия брой години в клетка C1 напишете заглавието за нея като „Не“.

В клетка D1 въведете заглавие за крайната стойност и напишете заглавието като CAGR (Съединение годишен процент на растеж).

Копирайте стойността на D2 в клетка E2 и използвайте% знак в клетката E2 или напишете = D2 в клетка E2 и щракнете върху знак%.

Сега опитайте да поставите произволни стойности в началните стойности, крайните стойности и броя на годините. Клетката D2 дава стойност на CAGR.

И точен процент процент се показва в клетка E2.

CAGR формула в Excel - пример №3

Нека вземем още един пример за формула CAGR в Excel. Имаме по-долу данни,

Намерете начална стойност като начален баланс, който е B1.
Намерете крайна стойност като крайния баланс, която е B5.
Пребройте броя на годините, който е 3.
В клетка C1 запишете формулата за CAGR, която е (Краен баланс / Начален баланс) ˄ (1 / Брой години) - 1.

Изходът ще бъде: -

Кликнете върху% sign в началния раздел под общия раздел, за да получите стойността на CAGR.

Крайната стойност е 1%, което е необходимия годишен темп на растеж.

Професионалисти

Сложен годишен темп на растеж с отличен изгладен темп на растеж за период.
Това предполага, че растежът е стабилен.

Против

По CAGR не можем да предположим, че темпът на растеж ще бъде същия в бъдеще.
По CAGR не можем да имаме представа за неравномерния растеж в средните години.
CAGR не е абсолютна стойност.

Неща, които трябва да запомните за формулата на CAGR в Excel

CAGR Formula в Excel изчислява само средния годишен темп на растеж за период от години.
CAGR не показва неравномерността на темповете на растеж през средните години.
Той само изглажда темповете на растеж за определен период.

Препоръчителни статии

Това е ръководство за формулата на CAGR в excel. Тук обсъждаме сложния годишен темп на растеж в excel и как да използваме CAGR формулата в excel, заедно с практически примери и шаблон за excel за сваляне. Можете да разгледате и другите ни предложени статии -

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