Как рассчитать средний или сложный годовой темп роста в Excel?

Чтобы рассчитать сложный годовой темп роста в Excel, используйте базовую формулу =((Конечное значение/Начальное значение)^(1/Периоды)-1). Применить её очень просто:

1. Выберите пустую ячейку, например E3, введите в неё приведённую ниже формулу и нажмите клавишу Enter. См. снимок экрана:

=(C12/C3)^(1/(10-1))-1

Примечание. В приведённой выше формуле C12 — ячейка с конечным значением, C3 — ячейка с начальным значением, а 10–1 — период между ними; вы можете изменить эти параметры в соответствии со своими потребностями.

2. Иногда результат расчёта может отображаться без процентного формата. Выделите ячейку с результатом, перейдите на вкладку Главная и нажмите кнопку Процентный формат, чтобы преобразовать число в проценты. Затем настройте количество знаков после запятой с помощью кнопок Увеличить разрядность или Уменьшить разрядность. См. снимок экрана:

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

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

Примечание. В ячейку F3 введите =C3, в ячейку G3 — =B3, в ячейку F4 — =-C12, а в ячейку G4 — =B12. Либо введите исходные данные в эту таблицу напрямую. Обязательно поставьте знак минус перед конечным значением.

2. Выберите пустую ячейку под этой таблицей, введите в неё приведённую ниже формулу и нажмите клавишу Enter.

=XIRR(F3:F4,G3:G4)

3. Чтобы отформатировать результат как процент, выделите ячейку с функцией XIRR, нажмите кнопку Процентный формат на вкладке Главная, а затем настройте количество знаков после запятой с помощью кнопок Увеличить разрядность или Уменьшить разрядность. См. снимок экрана:

Быстро сохраните таблицу CAGR как мини-шаблон и используйте повторно всего одним щелчком мыши в будущем

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

Чтобы рассчитать средний годовой темп роста в Excel, сначала вычислите годовые темпы роста за каждый год по формуле = (Ending Value - Beginning Value) / Начальное значение, а затем найдите среднее арифметическое этих значений. Выполните следующие шаги:

1. Помимо исходной таблицы, введите приведённую ниже формулу в пустую ячейку C3, а затем протяните маркер заполнения до диапазона C3:C11.

=(C4-C3)/C3

2. Выделите диапазон D4:D12, щёлкните кнопку Процентный формат на вкладке Главная, а затем измените количество знаков после запятой с помощью кнопок Увеличить разрядность или Уменьшить разрядность. См. снимок экрана:

3. Чтобы найти среднее всех годовых темпов роста, введите приведённую ниже формулу в ячейку F4 и нажмите клавишу Enter.

=AVERAGE(D4:D12)

В настоящий момент среднегодовой темп роста уже рассчитан и отображается в ячейке C12.