Как создать ипотечный калькулятор в «МойОфис Таблица»

[vaspvort]

Для просмотра ссылки необходимо нажать Вход или Регистрация

• Какие бывают ипотечные платежи
• Как рассчитать ипотечный кредит
• Считаем ставку по ипотеке при помощи функции СТАВКА
• Считаем ежемесячный платеж по ипотеке при помощи функции ПЛТ
• Считаем платежи по ипотеке «в ручном режиме»
• Составляем помесячный график платежей по ипотеке за 7 шагов

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

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

В качестве удобной альтернативы вы можете создать собственный универсальный калькулятор платежей по ипотеке в табличном редакторе. Так вы сможете самостоятельно оценить предложения разных банков и выбрать лучшие условия. Рассмотрим, как это сделать, на примере «МойОфис Таблица».

Какие бывают ипотечные платежи​

Существует два основных вида ипотечных платежей: аннуитетный и дифференцированный.

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

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

Как рассчитать ипотечный кредит​

Для расчета ипотечного кредита вам потребуется несколько показателей:

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

Предположим, что вы хотите взять ипотеку на сумму 10 000 000 рублей, первоначальный взнос составит 20% от суммы ипотечного кредита, срок кредита — 20 лет, а годовая процентная ставка — 10%.

В этом случае ежемесячный платеж по ипотеке составит 77201 рублей. Общая сумма платежа составит 18 528 415 рублей, а общая сумма переплаты — 10 528 415 рублей.

Для того чтобы все эти расчеты не составлять в голове, а иметь в наглядном виде, давайте рассчитаем процентную ставку при помощи табличного редактора «МойОфис Таблица». Вы можете воспользоваться

Для просмотра ссылки необходимо нажать Вход или Регистрация

онлайн-версией или бесплатной настольной.

Считаем ставку по ипотеке при помощи функции СТАВКА​

В табличном редакторе «МойОфис Таблица» реализовано несколько финансовых функций, которые позволяют оперативно посчитать параметры кредитов. Одна из них – формула СТАВКА, которая возвращает значение процентной ставки по аннуитету.

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

Мы указали такие параметры – 5 млн рублей, 60 месяцев и 108 тысяч 712 рублей ежемесячного платежа.

Ежемесячный платеж по ипотеке запишите со знаком минус, потому что это та сумма, которую вам предстоит возвращать банку.

Теперь рассчитаем ставку по кредиту с помощью специальной финансовой функции СТАВКА. У нее три обязательных атрибута:

• количество_периодов — это срок кредитования в месяцах;
• выплатазапериод — ежемесячный платеж;
• текущая_стоимость — сумма кредита.

Вставим формулу в ячейку B5: =СТАВКА(B3;B4;B2).

В результате вычисления получим ставку по ипотеке в размере 0,92%. Обратите внимание, что полученное значение в ячейке В5 соответствует ставке за месяц – для получения годовой ставки его предстоит умножить на 12. Мы это сделаем в ячейке С5, используя формулу =B5*12.

Как рассчитать переплату по ипотеке​

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

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

Чтобы рассчитать общую сумму платежей по ипотеке, умножьте значение ежемесячного платежа на значение срока кредита в месяцах. Для этого напишем в ячейке В6 формулу =В4*B3

Получаем значение -6 522 720 ₽.

Теперь сложите это значение с суммой кредита, чтобы узнать переплату.

Переплата за весь период по этому ипотечному кредиту будет равна -1 522 720 ₽.

Считаем ежемесячный платеж по ипотеке при помощи функции ПЛТ​

В случае если вы знаете уже ставку по кредиту, можно самостоятельно рассчитать размер ежемесячного платежа. Для этого понадобится новая функция ПЛТ. У нее три обязательных атрибута:

• ставка — годовая процентная ставка;
• количество_платежей — это срок кредитования в месяцах;
• текущая_стоимость — сумма кредита.

Все эти данные уже есть в нашей таблице. Заодно мы проверим верность предыдущих расчетов Поэтому в ячейку B10 введем формулу: =ПЛТ(B5; B3; B2), где:

• B5 — месячная процентная ставка, ее можно получить, если разделить годовую процентную ставку на 12;
• B3 — количество платежей;
• B2 — сумма кредита.

В результате получаем ежемесячный платеж 108 712 ₽, который соответствует предложению банка из нашего прошлого расчета.

Считаем платежи по ипотеке «в ручном режиме»​

Как альтернативу функции ПЛТ, вы можете использовать «ручную» формулу:

где:

PM — размер ежемесячного платежа по ипотеке,

S — остаток ссудной задолженности на дату начала периодического платежа,

G — месячная процентная ставка, выраженная в виде числовой десятичной дроби (не в %) и равная 1/12 от годовой процентной ставки по кредиту (например, годовая % ставка — 12% или 0,12, G = 0,01),

T — количество периодических процентных периодов, оставшихся до окончательного погашения ссудной задолженности.

Другими словами: остаток задолженности на дату начала периодического платежа необходимо умножить на отношение ежемесячной процентной ставки в виде числовой десятичной дроби к величине, равной 1-(1+ ежемесячная процентная ставка), возведенной в степени -(срок кредита-1).

То есть, в нашем примере формула будет выглядеть следующим образом:
=B2*B5/(1-(1+B5)^-(B3-1))

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

Составляем помесячный график платежей по ипотеке за 7 шагов​

Теперь посчитаем график ипотечных платежей.

1. Отступим пару строк и добавим названия для новых столбцов: Месяц, Остаток, Общий платеж, проценты, Платеж в тело ипотечного кредита

Для просмотра ссылки необходимо нажать Вход или Регистрация

1. В ячейке C14 укажите ваш ежемесячный платеж. Обратите внимание, что для этого нужно записать значение уже без знака минус
2. В ячейке В14 укажите сумму ипотечного кредита, т.е. 5 млн рублей, которые у вас уже записаны в таблице, поэтому приметим формулу =B2
3. Осталось посчитать сколько процентов будет оплачено в первом ежемесячном платеже. Для этого, в ячейке D14 укажите следующую формулу =B14*$B$5, то есть, остаток суммы кредита умножаем на ежемесячную процентную ставку по ипотеке. Обратите внимание, что формат значения ячейки вам нужно перевести в «числовой»

Для просмотра ссылки необходимо нажать Вход или Регистрация

1. Теперь посчитаем, сколько денег из общего ежемесячного платежа по ипотеке ушло в погашение самого кредита. Для этого, и общего платежа C14 необходимо вычесть D14, т.е. в ячейке E11 укажем формулу =C14-D14.

Для просмотра ссылки необходимо нажать Вход или Регистрация

1. Начиная со второго месяца остаток по кредиту будет уменьшаться из-за вычета реально уплаченного долга, составляющего тело кредита. Для этого в ячейке В15 укажем формулу =B14-E14

Для просмотра ссылки необходимо нажать Вход или Регистрация

1. После этого вам достаточно скопировать расчеты на оставшиеся месяцы и вы получите график, в котором будет постепенное уменьшение тело ипотечного кредита

Для просмотра ссылки необходимо нажать Вход или Регистрация

Так таблица «МойОфис» становится универсальным калькулятором для расчета ипотеки, который позволяет сравнивать предложения от разных банков — достаточно скопировать формулы и подставить нужные значения.

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

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

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

Для просмотра ссылки необходимо нажать Вход или Регистрация