Иванова Лидия Редактор-эксперт

Как рассчитать сложный процент в Excel и Google Таблицах

Сложный процент превращает ставку в накопления — каждый период доход начисляется на сумму вместе с уже накопленными процентами. Excel и Google Таблицы умеют считать сложный процент одной формулой или встроенной финансовой функцией. Разберём базовый расчёт в Excel, разберём функции FV, EFFECT, NPER, RATE, PMT в Google Таблицах и оставим готовые шаблоны формул для копирования.з

Содержание

Базовая формула расчёта сложного процента

Сложный процент — это начисление дохода на сумму вклада вместе с уже накопленными процентами. В отличие от простого процента, где база расчёта остаётся неизменной, при сложной схеме база растёт с каждым периодом капитализации. Excel и Google Таблицы реализуют формулу сложного процента в одну строку.

Формула сложного процента из учебника:

FV=S×(1+r)n

В формуле используются показатели:

  • FV (future value) — будущая сумма, ₽;
  • S (sum) — начальная сумма, ₽;
  • r (rate) — ставка за один период капитализации (доля, не процент);
  • n (number) — количество периодов капитализации.

При S = 100 000 ₽, r = 10 %, n = 5 (ежегодная капитализация) формула даёт 161 051 ₽. Прибыль — 61 051 ₽.

Как рассчитать сложный процент в Excel

Формула расчёта в Excel

В ячейке Excel базовая формула сложного процента пишется напрямую через оператор возведения в степень ^. Excel рассчитает итоговую сумму по формуле сложного процента:

=A2*(1+B2)^C2

Где A2 — начальная сумма, B2 — ставка за один период капитализации (не годовая, если периодов в году больше одного), C2 — количество таких периодов. Формула корректна, только если B2 — это ставка именно за период.

Если у вас на руках годовая ставка и число периодов в году — используйте универсальный вид с числом периодов в году в ячейке D2:

=A2*(1+B2/D2)^(C2*D2)

Здесь D2 = 12 для ежемесячной капитализации, 4 — для ежеквартальной, 365 — для ежедневной. C2 — срок в годах.

ВАЖНО!
В Excel и Google Таблицах денежные потоки в финансовых функциях считаются со знаками: то, что вы отдаёте (стартовый взнос, регулярный платёж), идёт со знаком минус, то, что получаете в итоге, — со знаком плюс. Если все потоки одного знака, формула вернёт ошибку или нелогичный результат.

Функция FV

Excel-функция FV (в русском варианте БС) считает будущую стоимость по сложному проценту, в том числе с регулярными взносами. Синтаксис:

=БС(ставка; кпер; плт; пс; тип) или английский вариант =FV(rate; nper; pmt; pv; type)

В формуле FV используются аргументы:

  • rate — ставка за период;
  • nper — количество периодов;
  • pmt — регулярный взнос (со знаком минус, если деньги вносите);
  • pv — текущая сумма (со знаком минус);
  • type — 0 для взноса в конце периода, 1 — в начале.

Для ежемесячной капитализации =FV(10%/12; 5*12; 0; -100000) вернёт 164 530,89 ₽ — на 3 479,89 ₽ больше, чем при ежегодной.

Функция EFFECT

EFFECT в Excel приводит номинальную ставку с любой частотой капитализации к эффективной годовой ставке. Это та самая EAR (effective annual rate), которая показывает эффективную годовую доходность с учётом капитализации, без поправки на инфляцию.

=ЭФФЕКТ(номинальная_ставка; кол_пер) или =EFFECT(nominal_rate; npery)

Для номинала 10 % с ежемесячной капитализацией =EFFECT(10%; 12) = 10,4713 %. То есть капитализация добавляет к номиналу примерно 0,47 п. п. эффективной доходности.

ВАЖНО!
Не путайте номинальную и эффективную ставку. В банковском договоре по вкладу указывают номинал; реальный годовой доход с учётом капитализации показывает EAR. Для сравнения двух предложений с разной частотой капитализации сравнивайте именно EAR, а не номинал.

Один сценарий через все функции Excel

Возьмём один сценарий и пройдём по всем функциям Excel. Условия общие для всех расчётов: начальная сумма 100 000 ₽, ставка 10 % годовых, срок 5 лет, для отдельного примера — ежемесячное пополнение 5 000 ₽.

Заносим формулы в одну таблицу и сравниваем результаты:

Базовая формула, функции FV и EFFECT в Excel

В этом примере результаты такие:

  • Базовая формула с ежегодной капитализацией — 161 051,00 ₽;
  • FV с ежемесячной капитализацией — 164 530,89 ₽;
  • EFFECT для номинала 10 % при m=12 — 10,4713 %;
  • FV с пополнениями 5 000 ₽ в месяц — 551 716,25 ₽ при общем взносе 400 000 ₽.

Как рассчитать сложный процент в Google Таблицах

В Google Таблицах работают те же финансовые функции, что и в Excel: логика расчётов полностью совпадает с Excel, различается только синтаксис — имена функций английские, разделитель аргументов в Google Таблицах — запятая (для русской локали может быть и точка с запятой, в зависимости от настроек).

Функция FV

Считает будущую стоимость:

=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

Тот же сценарий: =FV(10%/12, 5*12, 0, -100000) вернёт 164 530,89 ₽.

Функция EFFECT

Эффективная годовая ставка:

=EFFECT(nominal_rate, periods_per_year)

=EFFECT(10%, 12) вернёт 10,4713 %.

Функция NPER

NPER возвращает количество периодов, за которое стартовая сумма дойдёт до цели:

=NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])

Чтобы 100 000 ₽ удвоились до 200 000 ₽ при 10 % годовых с ежемесячной капитализацией: =NPER(10%/12, 0, -100000, 200000) = 83,52 месяца, или почти 7 лет.

Функция RATE

RATE возвращает ставку за период, при которой сумма дойдёт до цели за заданное количество шагов:

=RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning])

Чтобы 100 000 ₽ удвоились до 200 000 ₽ за 60 месяцев: =RATE(60, 0, -100000, 200000)*12 = 13,94 % годовых (номинал). RATE возвращает ставку за один период; в этом сценарии период — месяц, поэтому для перевода в годовую номинальную ставку результат умножают на 12.

Функция PMT

Формула PMT считает регулярный взнос, нужный для накопления цели:

=PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])

Накопить 1 000 000 ₽ за 5 лет под 10 % годовых с ежемесячной капитализацией без стартового взноса: =PMT(10%/12, 5*12, 0, -1000000) = 12 913,71 ₽ в месяц. Целевая сумма стоит со знаком минус — это та же логика потоков: для PMT целевая сумма трактуется как платёж, который вы обязались собрать, поэтому формула возвращает положительный взнос.

Один сценарий через все функции Google Таблиц

Те же входные данные, но прогон через пять функций сразу:

Пять финансовых функций для сложного процента в Google Таблицах

Получаем такие результаты:

  • FV — 164 530,89 ₽;
  • EFFECT — 10,4713 %;
  • NPER до удвоения — 83,52 мес.;
  • RATE для удвоения за 60 мес. — 13,94 % годовых;
  • PMT для накопления 1 млн ₽ — 12 913,71 ₽ в месяц.

Какую функцию выбрать

Каждая функция отвечает на свой вопрос — короткая шпаргалка:

  • FV — узнать итоговую сумму на счёте через N периодов;
  • PMT — рассчитать регулярный взнос, чтобы прийти к цели;
  • NPER — посчитать, за сколько периодов стартовая сумма дойдёт до цели;
  • RATE — понять, какая ставка нужна, чтобы успеть к цели за заданный срок;
  • EFFECT — сравнить два предложения с разной частотой капитализации по реальной отдаче.

Готовые шаблоны формул для копирования

ВАЖНО!
Названия функций зависят от языка Excel. В русской версии Excel используются локализованные имена: FV = БС, EFFECT = ЭФФЕКТ, NPER = КПЕР, RATE = СТАВКА, PMT = ПЛТ. Если формула не считается — проверьте язык функций в вашей версии Excel.

Скопируйте формулу в любую ячейку и подставьте свои числа. Подразумевается, что A2 — стартовая сумма, B2 — годовая ставка, C2 — срок в годах, D2 — целевая сумма, E2 — регулярный ежемесячный взнос.

Шаблоны для Excel (разделитель аргументов в Excel — точка с запятой):

  • Базовая формула — =A2*(1+B2)^C2
  • FV с ежемесячной капитализацией — =FV(B2/12; C2*12; 0; -A2)
  • FV с ежемесячными пополнениями E2 — =FV(B2/12; C2*12; -E2; -A2)
  • EFFECT — =EFFECT(B2; 12)
  • NPER до целевой суммы D2 — =NPER(B2/12; 0; -A2; D2)
  • RATE для удвоения за 60 мес. — =RATE(60; 0; -A2; A2*2)*12
  • PMT для цели D2 без стартового взноса — =PMT(B2/12; C2*12; 0; -D2)
  • PMT для цели D2 со стартовым взносом A2 — =PMT(B2/12; C2*12; A2; -D2)

Шаблоны для Google Таблиц (разделитель аргументов — запятая):

  • Базовая формула — =A2*(1+B2)^C2
  • FV — =FV(B2/12, C2*12, 0, -A2)
  • FV с пополнениями E2 — =FV(B2/12, C2*12, -E2, -A2)
  • EFFECT — =EFFECT(B2, 12)
  • NPER — =NPER(B2/12, 0, -A2, D2)
  • RATE — =RATE(60, 0, -A2, A2*2)*12
  • PMT без стартового взноса — =PMT(B2/12, C2*12, 0, -D2)
  • PMT со стартовым взносом A2 — =PMT(B2/12, C2*12, A2, -D2)
ВАЖНО!
Разделитель аргументов и десятичный разделитель зависят от региональной настройки. Если формула не считает — проверьте настройки локали: в Excel это «Файл → Параметры → Дополнительно», в Google Таблицах — «Файл → Настройки → Общие → Язык таблицы и регион».

Расчёты в Excel и Google Таблицах справочные. Фактический доход или нужный взнос зависят от условий договора с банком, налогового режима, точных дат операций и специальных условий по ставке (пороговый остаток, пополнения, льготный период). Возможны расхождения в один-два десятка копеек из-за округления промежуточных значений.

Связанные калькуляторы по теме

Автор, редактор-эксперт