=(B2/A2)^(1/C2)-1 возвращает среднегодовой темп роста (CAGR, compound annual growth rate) от начального значения в A2 до конечного значения в B2 за C2 лет. Это единая постоянная годовая ставка, которая превратила бы начальное значение в конечное. =ЭКВ.СТАВКА(C2;A2;B2) (по-английски RRI) даёт тот же результат. В таблицах ниже формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | End | Years | CAGR | RRI |
| 2 | $50,000 | $80,000 | 4 | 12.47% | 12.47% |
=(B2/A2)^(1/C2)-1Выручка выросла с $50,000 до $80,000 за 4 года, это CAGR 12.47%. Задайте результату процентный формат (Главная > Процентный формат, или Ctrl+Shift+%, на Mac Control+Shift+%), иначе он будет показан десятичной дробью вроде 0,1247. Замените C2 на 2, и тот же рост за вдвое меньшее время даст 26.49% в год.
Формула CAGR по шагам
CAGR = (end / start) ^ (1 / years) - 1
end/startэто общий коэффициент роста: 80 000 / 50 000 = 1,6, то есть выручка в 1,6 раза больше, чем была.^(1/years)извлекает корень 4-й степени из этого коэффициента, то есть годовой коэффициент, который, умноженный сам на себя 4 раза, даёт 1,6. Возведение в степень 1/4 это корень 4-й степени.-1превращает коэффициент в ставку: 1,1247 становится 12,47%.
Скобки важны. =B2/A2^(1/C2)-1 возводит в степень только A2, потому что ^ вычисляется раньше /, и даёт бессмысленный результат.
ЭКВ.СТАВКА (Excel 2013 и новее, а также Google Таблицы) принимает те же три числа в другом порядке: =ЭКВ.СТАВКА(nper; pv; fv), то есть годы, начало, конец.
CAGR по таблице значений за каждый год
Когда на каждый год есть строка, возьмите первое и последнее значение и посчитайте периоды по самим годам. Шесть строк с 2021 по 2026 год это 5 периодов роста, и частая ошибка взять 6.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Year | Users | Growth | Measure | Rate | |
| 2 | 2021 | 12,000 | CAGR | 16.72% | ||
| 3 | 2022 | 18,000 | 50.0% | Average growth | 18.86% | |
| 4 | 2023 | 15,300 | -15.0% | |||
| 5 | 2024 | 19,900 | 30.1% | |||
| 6 | 2025 | 24,500 | 23.1% | |||
| 7 | 2026 | 26,000 | 6.1% |
=(B7/B2)^(1/(A7-A2))-1Пользователей стало больше с CAGR 16.72%, а среднее пяти годовых темпов роста 18.86%. Среднее тянет вверх скачок на 50% в 2022 году, а падение в 2023 году учитывается не полностью. CAGR это честное единое число: =B2*(1+F2)^5 попадает ровно в 26 000. Замените B3 на 8000: среднее резко упадёт, а CAGR не сдвинется вообще, потому что зависит только от первого и последнего значения. Изменение за один год само по себе описано на странице процентное изменение.
Прогноз будущего значения по CAGR
Если повернуть формулу, темп роста говорит, где окажется значение: =start*(1+rate)^years. Здесь 10 000 растут на 8% в год:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Value | Rate | |
| 2 | 0 | $10,000 | 8% | |
| 3 | 1 | $10,800 | ||
| 4 | 2 | $11,664 | ||
| 5 | 3 | $12,597 | ||
| 6 | 4 | $13,605 | ||
| 7 | 5 | $14,693 |
=$B$2*(1+$D$2)^A3Через 5 лет при 8% 10 000 превращаются в $14,693. Подставьте это конечное значение обратно в формулу CAGR с 5 годами, и снова получится 8%. То же начисление сложных процентов работает в планах накоплений и кредитах на странице ПЛТ.
CAGR между двумя датами
Когда период не равен целому числу лет, используйте точную длину в годах как показатель степени. ДОЛЯГОДА(start;end;1) возвращает её по двум датам; 1 считает фактические дни, а значение по умолчанию считает месяцы по 30 дней.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Bought | Sold | Paid | Sold for | Years | CAGR |
| 2 | 2022-03-15 | 2026-09-30 | $8,000 | $11,500 | 4.55 | 8.31% |
=(D2/C2)^(1/E2)-1С марта 2022 по сентябрь 2026 года проходит около 4,55 года, CAGR 8.31%. Если по пути деньги вносились и выводились (ежемесячные взносы, частичные продажи), CAGR уже не подходит: используйте ЧИСТВНДОХ со страницы ЧПС и ВСД.
Попробуйте: рост населения города
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Years | Pop 2016 | Pop 2026 | CAGR |
| 2 | 10 | 412,000 | 538,000 |
Ваша очередь: В D2 посчитайте среднегодовой темп роста от населения в B2 до населения в C2 за число лет из A2.
Подсказка: (end/start)^(1/years)-1. Оставьте скобки вокруг деления и вокруг показателя степени.
CAGR, средний рост и ВСД
| Что у вас есть | Что использовать | Формула |
|---|---|---|
| Начальное значение, конечное значение и число лет | CAGR | =(end/start)^(1/years)-1 или =ЭКВ.СТАВКА(years;start;end) |
| Изменение от одного года к следующему | Процентное изменение | =new/old-1 |
| Список годовых ставок, который нужно свести к одной | Среднее геометрическое | =СРГЕОМ(1+C3:C7)-1, оно равно CAGR |
| Деньги, которые со временем вносятся и выводятся | ВСД или ЧИСТВНДОХ | =ВСД(values), =ЧИСТВНДОХ(values;dates) |
Не показывайте простое СРЗНАЧ годовых темпов роста как «средний годовой рост» для всего, что растёт по сложным процентам: выручки, цен, инвестиций. После +50% и затем -50% среднее равно 0%, но значение упало до 75% от начального.
Часто задаваемые вопросы
Какая формула CAGR в Excel?
=(end/start)^(1/years)-1, например =(B2/A2)^(1/C2)-1. Задайте результату процентный формат. =ЭКВ.СТАВКА(C2;A2;B2) даёт тот же результат в Excel 2013 и новее.
Сколько лет брать для CAGR?
Число периодов между первым и последним значением, а не число значений. С 2021 по 2026 год проходит 5 лет, хотя в таблице 6 строк.
Чем CAGR отличается от среднего годового роста?
Средний рост это простое среднее процентных изменений каждого года. CAGR это единая постоянная ставка, которая превращает начальное значение в конечное. После падения и восстановления среднее завышает рост: +50%, а затем -50% в среднем дают 0%, но оставляют вас на 75% от начала, а это CAGR около -13,4%.
Как посчитать CAGR между двумя датами в Excel?
Используйте точную длину периода в годах как показатель степени: =(B2/A2)^(1/ДОЛЯГОДА(C2;D2;1))-1, где C2 и D2 это начальная и конечная даты. 1 считает фактические дни.
Можно ли посчитать CAGR с отрицательным числом?
Осмысленно нет. Начальное значение 0 даёт #ДЕЛ/0!, а когда у начала и конца разные знаки, формула возвращает #ЧИСЛО! или число, которое ничего не значит. В этом случае показывайте изменение в абсолютных величинах.
