Урок 7. Считаем проценты и кредиты

Этот урок займет у вас 20—25 минут.

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

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

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

Перейдем к реальному примеру. Вы открыли вклад со следующими условиями:

1. Размер вклада — 100 000 Р.
2. Ставка — 5%. Вклад с капитализацией, то есть проценты начисляют каждый месяц, а в следующем месяце проценты начисляют и на эти проценты.
3. Срок — один год.

Так и занесем данные в таблицу — взнос в рублях, ставку в процентах и срок в месяцах:

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

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

Ставка по вкладу. Наш вклад — с ежемесячной выплатой процентов, поэтому указываем не годовую, а ежемесячную ставку. То есть делим нашу ставку на 12, количество месяцев в году: B2/12. Если бы выплаты были только однажды, в конце срока, то мы указали бы просто годовую ставку.

Количество выплат процентов на счет — в нашем случае совпадает со сроком вклада в месяцах, потому что выплаты ежемесячные. Мы получим 12 выплат: B3.

Ежемесячные пополнения — если мы вкладываем деньги в начале срока и больше не трогаем вклад, пишем 0. Если бы мы планировали пополнять вклад, пополнения нужно было бы указывать с минусом: мы отдаем эти деньги банку, пусть и на время.

Первоначальный взнос по вкладу — взнос нужно указывать с минусом, так как мы отдаем эти деньги банку: -B1.

Когда вносятся платежи — тут возможны два значения: 0 или 1. Если указать 1, таблица посчитает, что платеж вносится сразу, в начале периода. Вклады работают именно так. Значение 0 пригодится, когда мы будем рассчитывать кредиты, об этом позже.

Итак, собираем формулу — ее можно вписать в любую пустую ячейку:

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

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

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

Вернемся к нашему примеру с вкладом. Вот новые условия:

1. При открытии вклада мы вносим 50 000 Р.
2. Со второго месяца вносим каждый месяц по 10 000 Р.

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

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

Запомним

1. В таблице вы можете самостоятельно рассчитать доходность по вкладу. Для этого нужно использовать функцию FV(), отвечающую за будущую выгоду.
2. Если проценты начисляются ежемесячно — так происходит с большинством вкладов, — ставку следует указывать в формате «годовая ставка / 12». А количество выплат в таком случае равно количеству месяцев.
3. Любые пополнения и взносы в формуле мы указываем с минусом, потому что отдаем эти деньги банку.
4. Последний параметр функции FV() указывает на то, когда мы вносим деньги в банк. Есть два значения: 0 — для кредитов, когда сначала начисляются проценты, а потом мы платим, и 1 — для вкладов, когда сначала мы вносим деньги, а потом на них начисляются проценты.
5. Если мы пополняем вклад ежемесячно, первый платеж нужно разбить на две части: разовое пополнение при открытии и ежемесячное пополнение, которое мы будто бы вносим при открытии вклада. В первый месяц формула сложит эти два значения, а со второго будет учитывать только ежемесячный платеж.

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

В предыдущем примере мы знали ставку, срок и сумму пополнения, но не знали итоговую сумму. Сейчас мы рассматриваем пример, в котором мы знаем итоговую сумму, но не знаем срок. То есть у нас снова те же переменные, но неизвестна другая.

Итак, вы решили накопить на пенсию:

1. Хотите накопить 30 000 000 Р.
2. Откладывать готовы по 30 000 Р в месяц.
3. Ставка по вкладу, на который будете откладывать, — 5% годовых.
4. Вы только начинаете копить — отложенных денег у вас нет.

Посчитаем, сколько месяцев понадобится, чтобы накопить на пенсию. Заносим все последовательно в таблицу:

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

Ставка, с которой мы будем копить. Если можно открыть вклад под 5%, предположим, что такая ставка будет и дальше. Как и в случае с FV(), если вклад с капитализацией, указываем месячную ставку: B3/12.

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

Сумма, которая уже накоплена. Тоже указываем с минусом, так как эти деньги отдаем банку: -B4.

Финансовая цель — сумма, которую мы хотим накопить: B1.

Собираем все параметры в формулу: =NPER(B3/12;-B2;-B4;B1).

Результат — 395 месяцев, то есть почти 33 года

=EDATE(TODAY();B6)

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

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

Как посчитать, сколько нужно откладывать в месяц

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

Возьмем такие условия:

1. Хотим за год накопить 100 000 Р.
2. Деньги храним на вкладе под 5%.

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

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

Вот что и в какой последовательности нужно указать в скобках функции.

Ставка при условии ежемесячного начисления процентов. Как и раньше, берем годовую ставку и делим на 12: B2/12.

Количество периодов, то есть месяцев. Другими словами, срок, за который мы хотим накопить. В нашем случае — 12: B3.

Сумма, которую мы уже накопили. В нашем случае 0, но лучше указать адрес ячейки: вдруг появится накопленная сумма. Если что-то накоплено, нужно указывать ячейку с минусом — эти деньги мы отдали банку, хоть и временно: -B4.

Наша цель — 100 000 Р: B1.

Собрали в формулу: =PMT(B2/12;B3;-B4;B1).

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

Запомним

1. Функция NPER() помогает посчитать, сколько времени вам понадобится, чтобы достичь финансовой цели.
2. Функция PMT() помогает посчитать, какую сумму нужно откладывать, чтобы достичь финансовой цели вовремя.
3. Чтобы узнать, к какой дате вы сможете накопить столько, сколько хотите, используйте функцию EDATE(), «дата истечения». Она прибавляет к сегодняшней дате нужное количество месяцев, которое мы уже рассчитали выше.
4. Эти таблицы можно использовать как интерактивный калькулятор: если поменять исходные данные, результаты подстроятся автоматически.

Как посчитать платеж по кредиту без обращения в банк

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

Вот условия:

1. Нам предлагают кредит под 10% годовых на год.
2. Ноутбук стоит 100 000 Р.

Внесем все известные данные:

Теперь используем функцию PMT(). Вот что нам нужно внести в формулу.

Ставка по кредиту — здесь все как с вкладами. Делим значение ставки на количество месяцев в году, так как проценты по кредитам начисляют каждый месяц: B1/12.

Количество платежей зависит от срока кредита. В нашем случае — совпадает со сроком кредита в месяцах, потому что платежи ежемесячные: B2.

Сумма кредита указана в ячейке B3 — 100 000 Р.

Вот как это будет выглядеть в формуле: =PMT(B1/12;B2;B3).

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

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

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

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

Как посчитать, что выгоднее: взять кредит или накопить на покупку

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

Посчитаем, сколько денег у нас будет через год, это мы уже умеем. Воспользуемся известной нам функцией FV(). Формула такая: =FV(B1/12;B2;-B3;-B4;1).

Еще раз вспомним, что есть что.

Ставка по кредиту — проценты начисляются ежемесячно, поэтому годовую ставку делим на количество месяцев: B1/12.

Срок кредита в месяцах — B2.

Сумма, которую мы будем откладывать каждый месяц. В нашем случае — те же 8791,59 Р, что мы платили бы по кредиту. С минусом, потому что мы отдаем эти деньги: -B3.

Первоначальный платеж — у нас нет первоначального взноса, только ежемесячные, поэтому будет 0. Если бы платеж был, указали бы его с минусом: -B4.

Тип платежей — считаем вклад, поэтому указываем 1.

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

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

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

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

Как посчитать, какую сумму могут дать в кредит

Мы собираемся брать ипотеку — посчитаем, какую сумму можно получить.

Условия:

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

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

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

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

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

Составляем формулу: =FV(-B2/12;B3;-B1;0;0).

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

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

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

Запомним

1. С помощью PMT() вы можете рассчитать ежемесячный платеж по кредиту: нужна сумма, ставка и срок кредита.
2. Чтобы увидеть переплату по кредиту, нужно просто вычесть из суммы, которую мы выплатим за весь срок, сумму, которую изначально берем в кредит.
3. Вы можете определить, что выгоднее: купить вещь в кредит или накопить на нее. Для этого нужно посчитать, сколько мы получили бы денег, если бы ежемесячный платеж по кредиту переводили на вклад.
4. С помощью функции FV() можно не только узнать, сколько денег мы получим, но и посчитать, сколько денег мы можем взять в кредит с учетом комфортного для нас ежемесячного платежа и срока.

Функции, которые вы освоили

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