как рассчитать график платежей по ипотеке в excel

Аннуитет. Выплата ссуды (кредита, займа) с досрочным погашением. Сокращаем регулярный платеж

history 21 июля 2020 г.

Рассчитаем в MS EXCEL график ежемесячных платежей при погашении кредита, взятого по аннуитетной схеме. Предусмотрим возможность частичного досрочного погашения для целей сокращения регулярного платежа. В графике вычислим остаток (тело) кредита и сумму начисленных процентов.

В статье Аннуитет. Расчет периодического платежа в MS EXCEL. Погашение ссуды (кредита, займа) показано как рассчитать график погашения кредита или ссуды в случае аннуитетной схемы погашения. В данной статье-продолжении научимся рассчитывать график с учетом частичного досрочного погашения.

Задача

Пусть был взят кредит в размере 100 000руб. на 5 лет под ставку 12%. Кредит должен гаситься ежемесячными равными платежами (в конце периода).

Первоначальный ежемесячный платеж рассчитывается по формуле =ПЛТ(12%/12;12*5;-100000)

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

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

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

Предполагается, что досрочное погашение делается в конце периода (за этот период % платится полностью), а размер регулярного платежа меняется только начиная со следующего периода.

Досрочное погашение возможно с 1 месяца, хотя фактически все изменения в расчетах происходят со следующего месяца.

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

В столбцы J:L можно ввести 3 варианта досрочного погашения и последовательно сравнить переплату %.

Примечание: Для того, чтобы рассчитать сумму необходимую для окончательного погашения кредита из тела кредита на конец предыдущего периода вычтите сумму погашения тела кредита в текущем периоде. После этого периода значения во всех столбца обнулятся (кроме столбца Всего погашено тело кредита, конечно).

Источник

Ипотечный кредитный калькулятор в Excel. Как правильно рассчитать кредит в Excel?

Зачем нужен калькулятор?

Когда вы взяли кредит, вы так или иначе думаете о досрочном погашении.
Есть люди которые платят кредит и все. А есть те, которые каждый раз смотрят, сколько осталось платить, какая сумма основного долга. Я отношу себя ко второму типу людей, я смотрю сколько сейчас сумма основного долга, пытаюсь рассчитать, сколько будет платеж, если я сделаю досрочное погашение.
На данный момент у меня есть два калькулятора кредита для своих расчетов. Оба калькулятора сделаны в Excel.

Калькуляторы позволяют достаточно быстро и просто рассчитать ипотеку.
Читайте также: Как рассчитать кредит в Excel самому?

Где Скачать кредитный калькулятор в Excel?

Первый кредитный калькулятор в Excel можно скачать по ссылке.
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.

Для расчета досрочного погашения можно также воспользоваться онлайн версией калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel.

На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Найти и скачать мобильные версии калькуляторов можно с главной страницы сайта.

Достоинства данного калькулятора:

Недостатки калькулятора

Альтернативные калькуляторы

Всех выше названных недостатков лишен кредитный калькулятор для iPad/iPhone. В целом недостатки не сильно критичны и они присущи любому кредитному калькулятору онлайн.

Другой кредитный калькулятор в Excel можно скачать по данной ссылке.

Данный кредитный калькулятор не позволяет рассчитать досрочное погашение. Однако его плюс в том, что он рассчитывает кредит с несколькими процентными периодами. Если сумма процентов по кредиту за данный месяц больше суммы аннуитетного платежа, то график для первого кредитного калькулятора в excel строится некорректно. В графике получаются отрицательные суммы.

Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.

Источник

Расчет кредита в Excel

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

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. «Помассажировать числа» заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.

Вариант 1. Простой кредитный калькулятор в Excel

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

Вариант 2. Добавляем детализацию

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:

Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку («») в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:

=ЕСЛИ(A18<>«»; текущая формула; «»)

Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:

Вариант 3. Досрочное погашение с уменьшением срока или выплаты

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

Вариант 4. Кредитный калькулятор с нерегулярными выплатами

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

Источник

Калькулятор расчета кредита в Excel и формулы ежемесячных платежей

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).

Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.

Как рассчитать платежи по кредиту в Excel

Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

Расчет аннуитетных платежей по кредиту в Excel

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

Формула коэффициента аннуитета:

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.к. мы эти деньги будем отдавать банку, терять.

Расчет платежей в Excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

Составим график погашения предыдущего кредита по дифференцированной схеме.

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9 Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Формула расчета процентов по кредиту в Excel

Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

Заполним таблицу вида:

Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.

Сумма основного долга = аннуитетный платеж – проценты.

Сумма процентов = остаток долга * месячную процентную ставку.

Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.

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

Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.

Расчет полной стоимости кредита в Excel

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

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

Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).

Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.

Далее находим ЧБП: 365 / 28 = 13.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

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

Источник

Как рассчитать график погашения кредита для калькулятора в Excel

Главный показатель в графике погашения кредита – это сумма ежемесячного платежа. Для расчета размера суммы ежемесячного платежа по кредиту в Excel можно воспользоваться функцией ПЛТ (платеж). В качестве аргументов функции можно указать, конкретные значения, например, сумма кредита и процентная ставка. Но если ввести эти значения в отдельные ячейки, а в соответственных аргументах функции ПЛТ указать ссылки на них, вы получите возможность легко изменять условия кредитования и проверять их влияние на размер ежемесячного платежа по кредиту. Таким образом мы можем сделать кредитный калькулятор и составить график платежей в Excel.

Как сделать кредитный калькулятор в Excel

Ниже на рисунке представлен пример простейшего кредитного калькулятора в Excel, который вычисляет сколько придется платить банку ежемесячно для погашения кредита. Пользователь должен указать соответственные значения условий кредитования в ячейках B2:B4, а в ячейке B6 получим результат вычисления функции ПЛТ. Ежемесячный платеж по кредиту вычисляется следующей формулой:

Функция ПЛТ имеет 3 обязательных аргумента для заполнения и 1 опциональный:

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

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

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

Функция ПЛТ изначально не знает, что значит введенное число – количество: лет, месяцев или дней. Она воспринимает, что ставка дисконтирования и количество лет касаются одного и того же периода.

Как составить график платежей по кредиту в Excel

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

Ниже на рисунке представлен фрагмент графика. Он состоит из следующих столбцов:

В примере указанному на втором рисунке срок кредитования составляет 15 лет, а на первом рисунке – срок 30 лет. Уменьшение периода погашения кредита увеличивает сумму ежемесячного платежа.

Последним шагом будет возможность скрытия ячеек с отрицательными значениями в графике погашения кредита. В строках калькулятора до конца периода полного погашения кредита по условию его срока продолжительности лет. То есть если мы укажем другую продолжительность срока кредитования, на пример не 15 или 30 лет, а 10, то лишние результаты вычислений будут скрыты. Это можно реализовать с помощью условного форматирования, которое будет изменять цвет шрифта значений соответственных ячеек на белый. Устанавливая белый цвет шрифта, отображаемый на белом фоне, можно легко скрыть ненужные данные.

Для этого выделите диапазон ячеек D4:H363 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».

Ниже представлена формула использована в условном форматировании изображена ниже на рисунке:

Источник