Что вы узнаете

  1. Как спрогнозировать свой доход по вкладам.
  2. Как посчитать условия кредита без обращения в банк.
  3. Как посчитать, что выгоднее: взять кредит или накопить на покупку.

Как спрогнозировать доход по вкладам

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

С помощью функции FV() мы можем посчитать, какая получится сумма к дню закрытия вклада. FV означает future value, будущая стоимость. Эта функция считает, сколько денег у вас будет в зависимости от заданных условий. Чтобы функция работала, нужно задать такие параметры внутри скобок, разделяя их точкой с запятой:

  1. Ставку по вкладу.
  2. Количество выплат процентов на счет. Например, если проценты начисляют каждый месяц, а вклад открыт на год, укажем 12. Если проценты начисляют однажды, в конце срока, укажем 1.
  3. Ежемесячные пополнения. Их может не быть, в этом случае укажем 0.
  4. Первоначальный взнос по вкладу. Чтобы результат получился положительный, взнос нужно указывать с минусом, так как мы отдаем эти деньги банку.
  5. Когда вносятся платежи. Тут возможны два значения — 0 или 1. Если указать 0, таблица посчитает, что платежи вносятся в конце периода: будто вы открыли вклад 1 февраля, а деньги внесли только 28 февраля, перед началом следующего месяца. А если указать 1, таблица посчитает, что платеж вносится сразу, в начале периода — вклады работают именно так, этот вариант нам и нужен. Если вы считаете вклады, нужно всегда указывать 1. Значение 0 пригодится, когда мы будем использовать формулу в расчетах с кредитами, об этом будет пример дальше.

Перейдем к реальному примеру. Предположим, мы открыли вклад на год. Ставка — 5%, с капитализацией — это когда проценты на счет начисляют каждый месяц, а в следующем месяце проценты начисляют и на эти проценты. Положили на него 100 000 Р. Занесем в таблицу данные: взнос в рублях, ставку в процентах и срок в месяцах. Этого хватит, чтобы составить формулу:

В нашем случае формула будет выглядеть так:

=FV(B2/12;B3;0;-B1;1)

Разберемся, что есть что:

  1. B2/12 — это наша ставка по вкладу. Наш вклад — с ежемесячной выплатой процентов, поэтому указываем не годовую, а ежемесячную ставку. То есть делим нашу ставку на 12 — количество месяцев в году. Если бы выплаты были только однажды, в конце срока, то мы указали бы просто годовую ставку.
  2. B3 — количество выплат. В нашем случае совпадает со сроком вклада в месяцах, потому что выплаты ежемесячные. Мы получим 12 выплат, каждая из которых — 1/12 от годовой ставки по вкладу. Как и в предыдущем пункте, если бы вклад был с выплатой процентов в конце срока, поставили бы 1.
  3. 0 — ежемесячные пополнения. Если мы вкладываем деньги в начале срока и больше не трогаем вклад, пишем 0. Если бы были пополнения, указывали бы с минусом: мы отдаем эти деньги банку, пусть и на время.
  4. -B1 — сумма нашего первоначального взноса по вкладу. Указываем с минусом, потому что мы отдаем деньги банку.
  5. 1 — момент, когда вносим платеж. По вкладам деньги вносятся сразу при открытии, поэтому указываем 1 — платеж в начале периода.

Результат — 105 116,19 Р. Теперь мы можем менять условия и прикидывать, насколько существенная разница получается. Что будет, если мы положим не 100, а 150 тысяч? И не под 5, а под 4,5%?

Результат расчетов в таблице может незначительно отличаться от реального

Например, если посчитать годовой вклад на 1 000 000 Р под 5% в таблице, получится 1 051 161,9 Р, а если прикинуть ту же сумму в кредитном калькуляторе, получится 1 051 161,81 Р. Расхождение — 9 копеек.

Все дело в том, что функция в таблице считает вклад по 12 равным периодам, а в реальности есть нюансы: в каком-то месяце больше дней, в каком-то — меньше, так что сумма начисленных процентов от месяца к месяцу меняется. А еще бывают високосные годы — их формула тоже не учитывает, но в такие годы наши деньги работают на один день больше.

В любом случае расхождение получается незначительное — расчет в таблице однозначно подходит для прогнозирования.

Если мы планируем ежемесячно пополнять вклад, расчет немного усложняется. Дело вот в чем: если в FV() указать ежемесячные пополнения, функция посчитает, что мы вносим их с самого первого месяца.

Предположим, при открытии вклада мы хотим внести 50 000 Р, а в дальнейшем — каждый месяц по 10 000 Р. Чтобы формула считала корректно, первоначальные 50 000 Р придется разбить на две составляющие: 40 000 Р — взнос, 10 000 Р — ежемесячное пополнение. Будто мы положили 40 000 Р разовым платежом и сверху сразу внесли 10 000 Р ежемесячного пополнения, которые будем вносить и дальше. Итого при открытии внесли 50 000 Р, дальше — 10 000 Р в месяц. В формуле эти пополнения нужно указывать с минусом: мы отдаем деньги банку.


Разбираемся с кредитами

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

Посчитать приблизительный платеж можно и без похода в банк: поможет функция PMT() — от английского payment, платеж.

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

  1. Ставку по кредиту. Если платежи нужно делать каждый месяц, то годовую ставку сразу делим на 12, чтобы получить месячную.
  2. Срок кредита. Аналогично, если платежи каждый месяц, то и срок указываем в месяцах.
  3. Сумму кредита.

Предположим, ноутбук стоит 100 000 Р, а нам предлагают кредит под 10% годовых на год. Внесем все известные данные:

Теперь применим функцию PMT(). Вот как будет выглядеть формула:

=PMT(B1/12;B2;B3)

Что есть что:

  1. B1/12 — это наша ставка по кредиту, 10%, деленная на число месяцев в году. Делим, так как платежи ежемесячные. Если бы отдавать кредит нужно было раз в три месяца, делили бы на 3.
  2. B2 — количество платежей. В нашем случае совпадает со сроком кредита в месяцах, 12.
  3. B3 — сумма кредита, 100 000 Р.

Итогом работы такой формулы будет −8791,59 Р — это ежемесячный платеж, который нужно вносить за ноутбук. С минусом, потому что мы отдаем эти деньги. Если обратиться в банк, чтобы там рассчитали платеж на таких же условиях, нам назовут ту же сумму — вероятно, с расхождением в несколько копеек.


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

  • сумма кредита — 100 000 Р;
  • срок кредита — 12 месяцев;
  • ежемесячный платеж — 8791,59 Р.

Итого за год мы сделаем 12 платежей по 8791,59 Р — то есть за вещь стоимостью 100 000 Р мы заплатим 105 499,08 Р. Переплата — 5499,08 Р. В реальности сумма может незначительно отличаться: обычно платеж за последний месяц чуть больше или чуть меньше обычного среднего платежа.

А теперь — простое упражнение. Мы знаем, что за кредит нужно платить 8791,59 Р в месяц. Представим, что эту сумму в течение года мы не отдаем за кредит, а откладываем на вклад под 5%:

Посчитаем, сколько денег у нас будет через год. Воспользуемся уже известной нам функцией FV(). Формула такая:

=FV(B1/12;B2;-B3;-B4;1)

Что есть что:

  1. B1/12 — месячная ставка.
  2. B2 — срок кредита в месяцах.
  3. -B3 — сумма, которую мы будем откладывать каждый месяц. В нашем случае — те же 8791,59 Р, что мы платили бы по кредиту. С минусом, потому что мы отдаем эти деньги.
  4. -B4 — первоначальный платеж. У нас нет первоначального платежа, только ежемесячные, так что здесь будет 0.
  5. 1 — тип платежей. Считаем вклад, поэтому указываем 1.

Результат — 108 400,46 Р. Если предположить, что цена ноутбука за год не изменится, нам хватит этих денег на покупку — и еще 8400,46 Р останется.

Сравним: в случае с кредитом мы получили ноутбук сразу, потом платили по 8791,59 Р. Через год у нас есть ноутбук и нет кредита.

В случае с вкладом мы ничего сразу не получали, платили по 8791,59 Р в месяц, а через год получили ноутбук и еще 8400,46 Р сверху.

Эти упущенные 8400,46 Р — плата за то, что мы получили ноутбук сразу, а не ждали год. Такой расчет помогает принять решение: стоит ли брать вещь в кредит.


Как посчитать, насколько долго придется копить

Предположим, вы копите на что-то и хотите посчитать, сколько времени понадобится. Срок, необходимый для достижения цели, посчитает функция NPER() — number of periods, количество периодов.

Как и в предыдущих случаях, чтобы воспользоваться формулой, нужно ввести в скобках через точку с запятой определенные параметры. В случае с NPER() они такие:

  1. Ставка, с которой мы будем копить. Если можно открыть вклад под 5%, предположим, что такая ставка будет и дальше. Как и в случае с FV(), если вклад с капитализацией, указываем месячную ставку.
  2. Сумма, которую мы можем откладывать каждый месяц. Указываем с минусом, так как мы отдаем эти деньги банку.
  3. Сумма, которая уже накоплена. Если какие-то деньги уже отложены, тоже указываем с минусом.
  4. Финансовая цель — сумма, которую мы хотим накопить.

Подумаем о вечном: вот вы решили накопить на пенсию 30 000 000 Р. Откладывать готовы по 30 000 Р в месяц. Предположим, что ставка по вкладу всегда будет 5% годовых. Посчитаем, сколько времени вам понадобится, чтобы отложить нужную сумму.

Заносим все в таблицу:

Вот как будет выглядеть формула в нашем случае:

=NPER(B3/12;-B2;B4;B1)

Что есть что:

  1. B3/12 — наша ставка по вкладу с учетом ежемесячной выплаты процентов, 5%/12.
  2. -B2 — наш ежемесячный платеж, 30 000 Р. С минусом, потому что отдаем деньги.
  3. B4 — сколько денег у нас уже накоплено. Если бы какая-то сумма уже была накоплена, ее бы указывали с минусом — мы ее отдали банку.
  4. B1 — наша финансовая цель, 30 000 000 Р.

Результат — 395 месяцев, то есть почти 33 года. Можно улучшить таблицу — например посчитать, к какой дате мы накопим эту сумму. Поможет функция EDATE() — expiration date. Она позволяет прибавить к дате определенное количество месяцев. Считать будем от сегодняшней даты, тогда формула будет выглядеть так:

=EDATE(TODAY();B6)

Здесь TODAY() — функция, которая всегда выводит сегодняшнюю дату, а B6 — количество месяцев, которое мы прибавляем.

Если сегодня 2 февраля 2021 года, нужную сумму мы накопим к 2 декабря 2053 года. Однако! Хорошо, что наша таблица получилась интерактивной: можно прикинуть, как изменится срок, если мы будем откладывать больше денег или найдем способ получать больше 5% по вкладу:


Как еще можно использовать эти функции

Мы рассмотрели три функции:

  1. FV() — отвечает за финальную сумму, которая получится при заданном сроке, ставке и платеже.
  2. PMT() — отвечает за ежемесячный платеж, который нужен, чтобы достичь цели, при заданной ставке и сроке.
  3. NPER() — отвечает за срок, который понадобится, чтобы достичь цели, при заданной ставке и платеже.

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

Самый простой способ сделать все это — выписать вводные данные в отдельные ячейки вверху таблицы, а в формулу подставлять уже адреса этих ячеек.

Пример 1. Посчитаем, сколько нужно откладывать в месяц. Мы хотим за год накопить 100 000 Р. Деньги храним на вкладе под 5%.

Вводим в таблицу исходные данные:

У нас уже есть функция, которая считает регулярные платежи, — PMT(). Можно использовать ее не только для кредитов, но и для любых регулярных отчислений:

Формула будет выглядеть так:

=PMT(B2/12;B3;0;B1)

Что есть что:

  1. B2/12 — ставка при условии ежемесячного начисления процентов.
  2. B3 — количество периодов, то есть месяцев. Другими словами, срок, за который мы хотим накопить. В нашем случае — 12.
  3. 0 — сумма, которую мы уже накопили. Если такой суммы нет, оставляем 0. Если бы что-то было накоплено, указали бы с минусом — эти деньги мы отдали банку, хоть и временно.
  4. B1 — наша цель, 100 000 Р.

Теперь можно менять исходные данные и сразу же видеть результат. Например, мы хотим накопить не 100 000, а 150 000 Р — и не за год, а за полтора. Меняем значения в строках «Цель» и «Срок», остальное таблица пересчитает сама:

Посмотреть шаблон
Посмотреть шаблон

Пример 2. Считаем, какую сумму могут дать в кредит. Мы собираемся брать ипотеку и знаем, что максимальный платеж, который мы можем себе позволить, — 15 000 Р в месяц. Ипотеку дают под 6,5% годовых. Посчитаем, какую сумму можно получить. Будем рассматривать ипотеку на 30 лет, потом мы сможем уменьшить срок.

Вводим исходные данные:

Мы хотим узнать финальную сумму, за нее отвечает функция FV(). Составляем формулу:

=FV(-B2/12;B3;-B1;0;0)

Самое сложное тут — разобраться с плюсами и минусами:

  1. -B2/12 — ставка, деленная на количество месяцев. Вводим с минусом, потому что считаем не вклад, а кредит: ставка будет работать против нас.
  2. B3 — срок, в течение которого будем отдавать ипотеку. Вводим с плюсом.
  3. -B1 — ежемесячный платеж, который мы можем себе позволить. Вводим с минусом, потому что будем отдавать эти деньги банку.
  4. 0 — накопленная сумма.
  5. 0 — тип платежа. По вкладу мы сначала вносим деньги, а потом получаем проценты, поэтому в прошлых примерах ставили 1. По кредитам же все наоборот: проценты начисляются за месяц, а потом мы их выплачиваем. Поэтому указываем 0 или оставляем поле пустым.

Получается 2 377 323,81 Р. Это сумма, которую нам даст банк при максимальном сроке ипотеки и нашем ежемесячном платеже. А теперь можно рассматривать разные сценарии.

Если вам нужно больше денег от банка — можно увеличивать ежемесячный платеж и уменьшать ставку по ипотеке:

А если вас пугает ипотека на 30 лет, можно уменьшать срок ипотеки — но тогда сумма, которую даст банк, тоже уменьшится:

Посмотреть шаблон
Посмотреть шаблон

Что вы узнали

  1. Функция FV() позволяет узнать, какая итоговая сумма получится при заданной ставке, сроке и начальной сумме. Можно посчитать, сколько денег принесет вклад или какую сумму в кредит выдаст банк.
  2. Функция PMT() считает ежемесячный платеж: можно узнать, сколько придется платить за кредит или какую сумму нужно откладывать, чтобы накопить на покупку в заданный срок.
  3. Функция NPER() считает, сколько периодов — обычно месяцев — понадобится для достижения цели. Можно посчитать, как долго придется копить при заданной ставке и ежемесячном платеже.