график погашения кредита втб 24 excel
Ипотечный кредитный калькулятор в 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. Достаточно выставить для ячейки с результатом процентный формат.
ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.
Таким образом, для расчета аннуитетных платежей по кредиту используется простейшая функция ПЛТ. Как видите, дифференцированный способ погашения несколько сложнее.
Калькулятор ипотеки ВТБ 24 с досрочным погашением (сокращение срока кредита)
Калькулятор используется как для расчетов ипотеки на первичном рынке, так и для жилья со вторичного рынка. В качестве первоначального взноса по ипотеке возможно указать материнский капитал.
Далее, сам калькулятор и правила досрочного погашения в банке.
Калькулятор:
Возможно использовать расчет Excel прямо на сайте либо скачать его. Подстановка параметров осуществляется в желтые поля (изменяемые ячейки). Расчет графика осуществляется по методу аннуитетных платежей на основе указаний ЦБ России. Модель позволяет планировать досрочное погашение ипотеки с сокращением срока выплаты.
Калькулятор досрочного погашения ипотеки ВТБ 24
Вы можете скачать расчетную модель в формате MS Excel нажатием на СКАЧАТЬ ФАЙЛ
Модель позволяет планировать досрочное погашение ипотеки с сохранением одинаковой суммы ежемесячного платежа (аннуитета), но с сокращением срока кредита. Такая методика погашения сейчас применяется в ВТБ 24 и некоторых других банках по желанию клиента. Альтернативный метод расчета с досрочным погашением предполагает сокращение ежемесячного платежа — модель погашения ипотеки Сбербанка России.
Данный метод погашения доступен также для клиентов банка ВТБ 24 — здесь выбор остается за клиентами (в отличие от Сбербанка, где есть только одна схема). Модель Сбербанка может быть использована для расчета, т.к. она базируется на методике Банка России.
Может ли банк брать комиссию за досрочное погашение ипотеки?
Вступление в силу ФЗ «Об ипотеке (залоге недвижимости)» от 16.07.1998 N 102-ФЗ сделало рынок ипотечного кредитования более клиентоориентированным.
Если ранее банк, предоставивший заемщику ипотечный кредит, был вправе отказать в возможности досрочного погашения (или даже ввести штрафные санкции за таковое), то после принятия закона все, что требуется от заемщика, желающего погасить весь кредит (или его часть) досрочно — это предупредить банк за 30 дней до планируемого перевода денежных средств.
Законом установлен и максимальный срок, за который необходимо написать заявление — 30 дней. Причем банк может изменить этот срок в меньшую сторону. Очевидно, банки не были в восторге от происходящего, ведь досрочное погашение даже части долга приводит к несоответствию запланированной и фактической прибыли по кредиту.
Некоторые из банков, лишившихся части своих доходов, прибегли к крайней мере — введению дополнительных комиссий за досрочное погашение. Начисление подобных комиссий неправомерно, но заемщикам в некоторых случаях приходится прибегать к помощи профессиональных юристов для возврата личных средств, незаконно полученных банком.
Следует сразу подчеркнуть, что подобная практика не относится к ВТБ 24.
Какие бывают схемы платежей по ипотеке?
На сегодняшний день банки предлагают две схемы платежей по ипотечным кредитам — дифференцированную и аннуитетную.
Досрочное погашение ипотеки в банке ВТБ 24
Банк ВТБ 24 точно исполняет прописанные в Федеральном законе «Об ипотеке» требования к банкам. Заемщик может пользоваться законным правом досрочно погасить ипотечный кредит — как полностью, так и частично.
Выполнить досрочное погашение ипотеки ВТБ 24 можно следующими способами:
Как оформляется заявление на досрочное погашение ипотечного кредита
Разные банки представляют различные требования к формату уведомления о намерении сделать досрочное погашение. Некоторые банки, включая Сбербанк, предлагают составить и отправить заявление через Интернет-банк.
До недавних пор банк «ВТБ 24» принимал подобные заявления исключительно в письменном виде. Некоторое время назад банк анонсировал возможность уведомления по телефону.
Согласно приведенным на официальном сайте банка данным, заявление-обязательство о полном или частичном досрочном погашении должно, в числе прочего, включать информацию о сумме досрочного платежа (без учета начисленных процентов), кроме того, оно должно быть подано не позднее, чем за один день до наступления следующего платежного периода.
Частичное досрочное погашение — сокращать срок или сумму кредита?
В заявлении о частичном досрочном погашении заемщик может указать, каким образом желает изменить схему погашения кредита.
Банки предусматривают два варианта.
Оба варианта имеют как преимущества, так и недостатки.
Калькулятор ипотеки ВТБ 24, предлагаемый банком на официальном сайте, не предусматривает возможности пересчета платежей при частичном досрочном погашении.
Если выбран аннуитетный способ погашения кредита, то, очевидно, выгода напрямую связана с количеством средств, уже ушедших на погашение кредита.
Но универсального совета дать нельзя — стоит учитывать насколько значителен для вас платеж по ипотеке и насколько стабильна ваша работа.
Скажем, если вы в настоящий момент хорошо зарабатываете, но у вас есть сомнения в том, что ситуация не изменится — разумнее сперва снизить ежемесячный платеж до комфортного уровня, после чего уже прибегать к пересчету срока кредита.
Мы рекомендуем проводить расчет ипотеки ВТБ 24, изначально принимая во внимание, что платеж по ипотеке не должен слишком значительно бить по семейному бюджету.
Некоторые нюансы досрочного погашения ипотеки в ВТБ 24
Изменение даты ежемесячного платежа по ипотеке в ВТБ 24
Для изменения календарной даты ежемесячного платежа необходимо написать письменное заявление в отделении банка. Но обращаем ваше внимание на то, что у клиентов банка ВТБ 24 периодически возникают недовольства временем, которое может занять данная процедура. Поэтому рекомендуем заблаговременно озаботиться сменой даты платежа, получить новый согласованный сторонами график платежей.
Получение графика платежей после частичного досрочного погашения
После получения банком дополнительных средств в счет частичного досрочного погашения ипотеки производится перерасчет, по итогам которого менеджер банка предлагает возможные варианты дальнейших выплат. После согласования новый график платежей вступает в силу.
Льготные программы ипотечного кредитования ВТБ 24
Банк ВТБ 24 участвует во всех государственных жилищных программах и принимает любые сертификаты (под материнский капитал, по военной ипотеке) на приобретение недвижимости.
Важно учесть, что ряд заемщиков выражал недовольство тем, что материнский капитал не может быть использован для уменьшения срока кредитования. Зато ставка по ипотечному кредиту для молодой семьи составит всего 11%, максимальная сумма ограничена 8 млн. рублей.
Порядок снятия обременения с квартиры
После выплаты всего ипотечного кредита потребуется провести ряд процедур, следствием которых станет освобождение недвижимости от обременения. Для этого вам понадобятся:
Собранные документы необходимо предоставить в Росреестр (через МФЦ), а через пять дней забрать документ, подтверждающий факт снятия с квартиры обременения.
Поздравляем, теперь вы — полноправный собственник приобретенного жилья!
Калькулятор досрочного погашения ВТБ
Как рассчитать досрочное погашение в ВТБ
Для слабовидящих Слабовидящим
Ночная версия Ночной режим
Универсальный банк ВТБ занимает первое место в России по размеру уставного капитала. Крупнейший финансовый институт с государственным участием предлагает своим клиентам широкий выбор программ кредитования, среди которых наиболее востребованными являются потребительские кредиты с удобными условиями погашения. В банке ВТБ можно оформить заем на приобретение недвижимости, покупку авто, оплату обучения в вузе, а также на путешествия и другие крупные расходы. А если возникает необходимость в раннем закрытии кредита, можно применить калькулятор досрочного погашения ВТБ, который быстро рассчитает и спланирует Ваш кредит.
Все кредитные программы ВТБ для частных лиц не запрещают досрочное погашение оформленного продукта, включая частичное (ЧДП). При этом размеры выплат по кредиту пересчитываются в сторону уменьшения – если выбран досрочный способ погашения. Чтобы рассчитать изменения по ключевым параметрам займа, используется калькулятор досрочного погашения кредита ВТБ. С его помощью заемщик сравнивает переплату по займу при плановом и досрочном варианте погашения. Расчеты калькулятора по досрочному закрытию займа помогают адекватно оценить привлекательность программ кредитования ВТБ.
Досрочный метод погашения задолженности невыгоден банку, но финансовое учреждение не может воспрепятствовать этому способу закрытия кредита. В любом случае, перед тем как рассчитать досрочное погашение кредита ВТБ, внимательно прочитайте кредитный договор. В нем могут быть оговорены строгие ограничения по срокам погашения задолженности. В ином случае заемщик вправе выбрать:
Как частичное, так и полное досрочное погашение долга по займу производится без наложения штрафных санкций или дополнительных комиссий. Досрочный вариант закрытия кредитного договора позволяет заемщику существенно сэкономить на комиссии за пользование кредитом.
Чтобы рассчитать досрочное погашение кредита ВТБ, следует ввести в калькулятор ключевые данные по займу, а именно – общую сумму кредита, дату оформления и период действия. Также надо указать тип платежа и значение процентной ставки. В банке ВТБ разрешены полный и частичный досрочный (ЧДП) способы закрытия займа. Какой из вариантов выбрать, заемщик решает сам, соотнося свой бюджет со сроками пользования кредитом и размерами ежемесячных траншей.
Калькулятор досрочного погашения кредита ВТБ удобен для активных заемщиков, которые хотели бы рассчитаться с задолженностью перед банком ранее положенного срока. Также погашение досрочным способом может быть интересно потенциальным клиентам ВТБ. При помощи калькулятора они могут рассчитать переплату по займу и сравнить условия разных кредитных организаций. «Досрочный калькулятор» функционирует в онлайн-режиме, поэтому для предварительных расчетов не нужно посещать представительство ВТБ. А когда заявка на получение кредитных средств будет предварительно одобрена, заемщик должен собрать пакет документов и подать их сотруднику отделения ВТБ на рассмотрение и утверждение.
Калькулятор досрочного погашения ВТБ – это онлайн-инструмент, выполняющий только ознакомительные функции. Не следует принимать результаты расчетов калькулятора за полностью достоверные: их недостаточно для принятия ответственных финансовых решений. Калькулятор досрочного погашения (включая ЧДП) позволяет оценить общие условия сотрудничества с банком, но не учитывает возможные дополнительные услуги. Для получения подробной информации по программам кредитования обратитесь к работнику банка.