Расчет частичного досрочного погашения кредита калькулятор сбербанк

Деньги

09 авг 2021, 09:58

Как выгодно рассчитаться с банком

Рассказываем, что такое досрочное и частичное досрочное погашение ипотеки в 2020 году и как правильно производить расчеты с банком

Досрочное погашение ипотеки является общим трендом в России. Средний срок выданных в России ипотечных кредитов в 2020 году составляет 18,1 года, при этом обычно кредиты закрывают за семь — десять лет. Зачастую клиенты опасаются брать ипотеку на небольшой срок из-за высоких ежемесячных платежей, неуверенности в стабильности экономики и своего финансового благосостояния, поэтому они предпочитают перестраховаться и изначально оформить кредит на более долгий срок, говорит руководитель службы ипотечного кредитования компании «Инком-Недвижимость» Ирина Векшина.

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

В этой статье отвечаем на следующий вопросы

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

«Снизить общий объем переплаты можно путем досрочных погашений, что особенно актуально в течение первой трети срока жизни кредита. Банки придерживаются схемы аннуитетных платежей, поэтому заемщик сначала преимущественно погашает проценты, а не само тело кредита», — поясняет генеральный директор агентства недвижимости «БОН ТОН» Наталия Кузнецова.

Согласно закону

Досрочное погашение прописано в ФЗ 284 от 19.10.2011. Сумма займа, предоставленного под проценты заемщику, может быть возвращена досрочно полностью или по частям при условии уведомления об этом займодавца не менее чем за 30 дней до дня такого возврата. Договором может быть установлен более короткий срок уведомления банка. О принятом решении нужно заранее уведомить банк, иначе внесенный платеж не будет засчитан и его оприходуют в следующем месяце.

Варианты досрочного погашения ипотеки

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

Сокращение срока ипотечного кредита

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

Уменьшение платежей по ипотеке

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

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

Комбинированный вариант

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

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

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

Примеры расчета

В компании «БОН ТОН» приводят пример расчета выгоды при различных схемах досрочного погашения ипотечного кредита. Например, при сумме кредита в 3 млн руб. и остатке срока по кредиту в десять лет (120 мес.), под 7,5% с ежемесячным платежом 35,6 тыс. руб. переплата по процентам за десять лет составит 1,273 млн руб.

Представим, что у должника есть 200 тыс. руб. для досрочного погашения в первый месяц. При сокращении срока (но при сохранении ежемесячного платежа) — срок кредита сократится на год, а размер переплаты — на 215 тыс. руб. При уменьшении платежа (и остатке срока 120 месяцев) — платеж снижается до 33,3 тыс. руб. (на 2,48 тыс. руб.), а размер переплаты всего на 85 тыс. руб.

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

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

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

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

– номинальная процентная ставка годовых разделена на число периодов капитализации в году. В данном примере кредит капитализируется ежемесячно, значит процентная ставка указана в ячейке B3 делится на число 12 (месяцев).
(кпер) – количество платежей по кредиту на протяжении всего срока погашения кредита. То есть пользователь кредитного калькулятора должен указать число лет кредитования, а платежи будут проплачиваться ежемесячно. Поэтому число лет указано в ячейке B4 умножается на число 12 (месяцев).
(ПС) – нынешняя сумма долга перед банком. Функции Excel предназначенные для расчетов кредитов, в том числе и функция ПЛТ по принципу действия основываются на расчетах потоков налички. Если текущее значение кредита и взносы платежей будут считаться как приход и расход наличных средств, тогда легко понять почему и когда результаты вычислений этих функций будут положительными или отрицательными числами. В данном примере банк дал вам взаймы сумму 215 000, для вас данная статья потока наличных средств ставиться на приход, а это положительное числовое значение. Результат вычисления функции ПЛТ является отрицательным, так как ежемесячные платежи долга по кредиту для вас это расход.
Тип – определение выплаты в начале (1) или конце(0) периода.

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

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

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

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

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

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

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

Номер – порядковый номер текущего платежа по кредиту. В ячейке D4 введите число 1, а в следующей ячейке D5 введите формулу =D4+1 и скопируйте ее в остальные нижние ячейки аж до D363 (данный график будет содержать 360 платежей).
Сумма платежа – вычисляется м помощью функции ПЛТ в ячейке =$B$7 и округляется до копеек. Хоть Exel выполняет вычисления с точностью до большого количества разрядов после запятой, в графике можно указать только сумму до копеек. Это значит, что в конце периода до полного погашения кредита останется лишь только небольшая сумма для расчета с банком. В ячейке E4 введите формулу =-ОКРУГЛ($B$7;2) и скопируйте ее в нижние ячейки аж до E636.
Тело кредита – часть платежа закрывающая долг кредита без учета процентов. В ячейке F4 введите формулу =E4-G4 и скопируйте ее до F363.
Сумма процентов – часть платежа составляющая проценты начисленные за пользование кредитом. По данному столбцу мы видим сколько зарабатывает банк каждый месяц с данной долгосрочной сделки, то есть его валовая прибыль от выдачи кредита заемщику. Сумма кредита после оплаты предыдущего платежа умножается на годовую процентную ставку, умноженную на 12. Результат вычисления округляется до копеек (двух разрядов после запятой). В ячейке G4 введите следующую формулу =ОКРУГЛ(H3*$B$4/12;2) и скопируйте ее в нижние ячейки аж до G363.
Долг по кредиту – сумма остатка долга по кредиту после каждого платежного взноса заемщика. В ячейке H3 введите формулу =B3, которая возвращает начальную сумму долга кредита. Формула =H3-F4 введена в остальных ячейках данного столбца. Она уменьшает сумму долга только на первую часть (тело кредита) каждого платежа. Вторую часть сумму процентов банк оставляет себе.

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

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

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

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

Данная формула сравнивает текущий порядковый номер платежа в столбце D с количеством лет указанным в ячейке $B$5 умноженным на 12. Если же номер платежа больше, формула возвращает значение ИСТИНА и применяется белый цвет шрифта. Если же вместо этого номер платежа меньше от общего количества платежей или равен ему, не применяется ни каких изменений в форматировании значений для этой группы ячеек.

Почему важно знать, как считается досрочка?

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

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

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

В нем реализован расчет займа с учетом досрочных погашений.
Возможно 2 типа досрочных погашений — с уменьшением суммы платежа и с уменьшением срока кредита.

Формула расчета аннуитетного кредита

Формула для расчета аннуитетных платежей:

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

i = 0.12/12 = 0.01

Это значение и нужно использовать при расчете аннуитетного платежа.
Сумма кредита — сумма выданной ипотеки по договору банка.
Данная формула самая распространенная и используется в таких банках, как ВТб, Сбербанк, Райффайзен. Однако есть другие формулы, об этом ниже.

Пример расчета аннуитетного кредита с досрочными платежами

Теперь давайте попробуем рассчитать ипотеку. Для примера возьмем займ со следующими параметрами

В результате получим следующий график платежей.

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

Аннуитетный платеж = Погашение ОД + Проценты

где Погашение ОД — сумма в погашение тела займа
Проценты — сумма процентов по ссуде за месяц.

Где сумма ОД — сумма основного долга на дату расчета.Ставка — процентная ставка в текущем периоде. Если было изменение процентной ставки, берется новая ставка.Число дней между датами — разность в днях между датами «Дата текущего платежа» и дата предыдущего платежа.
Число дней в году — целое число дней в текущем году. Если мы считаем процентный платеж к примеру с 22 декабря 2011 по 22 января 2012 то формула процентов имеет вид.

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

В нашем примере при первом платеже это делать не нужно.
Рассчитаем первый платеж в уплату процентов по указанному выше займу за сентябрь месяц(разнца между датами 31 день).
Как видно сумма ОД на первый месяц составляет 1 млн. рублей. Подставим даты, ставки и число дней в году.

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

22244.45 — 10191.78 = 12052.67

Теперь рассчитаем сумму основного долга после оплаты первого взноса по ипотеке

1000000 — 12052.67 = 987947.33

Далее проценты будут начисляться на данную сумму. Так можно посчитать график для всех платежей.
Из графика платежей видно, что сумма основного долга на 1 сентября 2012 года составляет 831206.27 рублей.

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

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

831206.27 — 100000 = 731206.27

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

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

Срок кредита = 60 — 13 = 47

Подставим новую сумму в формулу аннуитетного платежа получим новый платеж по займу.

Вот как выглядят промежуточные расчеты

(1 + 0.01)^47 =1,596263443

1,596 — 1 = 0,596263443

731209.72 * 0.01 * 1,596263443 / 0,596263443 = 19575,20374

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

Как видно результат полностью совпадает. Также можно воспользоваться онлайн версией кредитного калькулятора. Там используется указанная выше формула аннуитетного платежа. График кредитного калькулятора может быть использован для сверки расчетов вашего кредита с расчетом банка. Иногда данные могут не совпасть.

Тут есть масса причин. Одна из них — банк использует другую формулу для расчета аннуитетных платежей. На самом деле существует 3 формулы аннуитетных платежей. В знаменателе может стоять разность (n-1), (n-2) или просто n. Саму формулу можно найти в кредитном договоре. Там же указаны и параметры, которые нужно подставлять в формулу.

Пример формул — банк Левобережный

Вот к примеру форумла аннуитета в банке Левобережный

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

Калькулятор «Вклад или досрочное погашение»

Реальная помощь в принятии решения — вкладывать или погашать
Ответ на вопрос — а что если не погашать досрочно, а сделать вклад — сколько можно сэкономить?
Удобное графическое представление вариантов вклада и досрочки
Возможность задать любой вариант вклада
Сохранение ваших расчетов(при регистрации) и возможность экспорта

Работник банка или другого фин. учрежденияПодробнее

Создатель проекта, финансовый эксперт

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

Полезное по теме

Сумма выплат составит

Сумма в конце срока

Платёж по процентам, начисленным на остаток основного долга
Платёж по основному долгу

Таблица выплат

Отзывы о калькуляторе досрочного погашения кредита банка ВТБ

Сосновских Алексей Викторович,

пользуйтесь услугами этого банка

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

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

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

Дату досрочного внесения средств (если платеж единоразовый) или интервал (если вы собираетесь делать платежи на регулярной основе, например раз в 3 месяца)
Сумму досрочного платежа
Выбрать способ перерасчета кредита

Можно задать неограниченное количество частично досрочных погашений.

Особенности частично досрочного погашения кредита

При частично досрочном погашении возможно два типа списаний:

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

После внесения внеочередного платежа меняется график последующих погашений кредита. Сумма основного долга уменьшается и следом за ней изменяется один из двух параметров: сумма ежемесячного платежа или срок кредита. Выбор всегда за клиентом банка. С учетом вашего выбора банк делает перерасчет кредита и формирует новый график платежей. Имейте это ввиду и получайте новый график платежей в офисе банка или в программе интернет-банк (если такую возможность предоставляет банк). Наш онлайн калькулятор также позволяет выбрать любой вариант и производит расчет с учетом выбора. После расчета вам будет представлен подробный график платежей с учетом указанных досрочных погашений.

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

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

Изменяемая процентная ставка

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

Скачайте готовые формулы и калькуляторы

Альтернатива использованию MS Excel — кредитный калькулятор on-line (функционал практически такой же, ничего качать на компьютер не нужно: все операции выполняются на нашем сайте).

Расскажем о файлах подробнее: дадим мини-инструкции.

Считать оба вида платежей.
Показывать полный график платежей.
Разбивать сумму выплаты на «погашение долга» и «погашение процентов».
Учитывать досрочные возвраты (отдельно для уменьшения срока и уменьшения размера выплат).

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

Простая функция для платежей

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

Содержит всего одну функциональную ячейку:

Поменяйте значения на свои (вместо 14 — свою ставку, вместо 12 во втором случае — свой срок кредита в месяцах, вместо 100000 — свою сумму займа).

После смены данных достаточно нажать на «Энтер», чтобы получить результат:

Напоминаем, ссылки на оба xls-файла представлены в начале страницы.

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

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

Для всех подсчетов потребуются следующие данные:

— размер кредита;
— цель кредита;
— размер процентов по кредиту.

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

— функция КПЕР – сумма кредита;
— функция СТАВКА – ставка процентов по кредиту;
— функция ПС – цель кредита;
— функция ПЛТ – ежемесячный платеж по кредиту.

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

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

При желании, можно погасить кредит досрочно, при этом никаких комиссий или штрафов банк не должен требовать.

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

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

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса — другое дело, а для обычных людей мышеловка»деньги за 15 минут, нужен только паспорт» срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это «потом» все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?

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

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

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

Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами — таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial) . Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:

Ставка — процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
Кпер — количество периодов, т.е. срок кредита в месяцах.
Пс — начальный баланс, т.е. сумма кредита.
Бс — конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
Тип — способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.

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

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

Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel — ОСПЛТ (PPMT) и ПРПЛТ (IPMT) . Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):

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

Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:

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

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

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

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

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

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять — не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты — заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

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

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

в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
отрицательные суммы — наши выплаты банку, положительные — берем дополнительный кредит к уже имеющемуся
подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)

Добавить калькулятор в Excel

Как быстро посчитать основные параметры кредита (ипотеки) в Excel, как быстро сформировать кредитный калькулятор в Excel?

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

Калькулятор выводит основные параметры вашего кредита или может сформировать лист с кредитным калькулятором и графиком платежей ! Работает как по схеме «вернуть в конце срока», так и с аннуитетными (равномерными платежами).

При построении графика платежей по кредиту используются функции =ОСПЛТ и =ПРПЛТ для вычисления структуры ежемесячного платежа: сколько в нем составляет основная сумма и сколько проценты.

При возврате полной суммы кредита в конце срока для ее вычисления используется формула:

=сумма кредита*(100%+годовая ставка)^срок кредита(лет)

При аннуитетных (равномерных) платежах:

Также вы можете добавить кредитный калькулятор в свой Excel с надстройкой SubEx, чтобы он, вместе с другими финансовыми инструментами, был под рукой в любую минуту!

Финансовые статьи по Excel

https://youtube.com/watch?v=sS8wy4JN0p4%3Fautoplay%3D0%26loop%3D0%26showinfo%3D0%26theme%3Ddark%26color%3Dred%26controls%3D1%26modestbranding%3D0%26start%3D0%26fs%3D1%26iv_load_policy%3D3%26wmode%3Dtransparent%26rel%3D0

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

Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
Построение и расчет графика платежей в виде таблицы в Excel. Таблица графика платежей может также редактироваться
При расчете учитывается високосный и невисокосный год. За счет этого сумма начисленных процентов практически совпадает с значениями, рассчитываемыми ВТБ24 и Сбербанком
Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
Калькулятор можно редактировать под себя, задавая разные варианты расчета.

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

Нет учета возможное изменение процентной ставки во время выплат кредита
Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
Не рассчитывается вариант — первый платеж только проценты. В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
Расчет производится для процентой ставки с 2мя знаками после запятой.

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