Кредитный калькулятор с досрочным погашением

Параметры для расчета

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

Стоимость квартиры

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

Первоначальный взнос

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

Продолжительность погашения ссуды варьируется от одного года до 30 лет, минимальная — 1-3 года. С одной стороны, увеличенная длительность погашения гарантирует меньшие платежи, чем короткий срок займа, другой — повышается процент за ссуду денег. Основные заявители ипотеки делают акцент на 10-25 лет. Вот здесь и пригодится формула расчета платежа по ипотеке.

Платежеспособность

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

Процентная ставка

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

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

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

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

Тип платежа

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

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

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

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

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

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

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

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

На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Разработанный ипотечный калькулятор является универсальным, так как подходит включает в себя расчет кредита с учетом комиссий, страховки и графики сравнения кредитов до и после досрочных погашений.

Данный калькулятор идеально подходит для расчета ипотечного кредита и эмуляции наличия досрочных погашений.

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

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

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

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

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

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

Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.

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

Естественно сам файл также можно отредактировать под свои нужды.

Дифференцированные платежи по кредиту в MS EXCEL. Примеры и методы

Составим в MS EXCEL график погашения кредита дифференцированными платежами.

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

Примечание. При расчете кредита дифференцированными платежами сумма переплаты по процентам будет ниже, чем при аннуитетных платежах. Не удивительно, что сегодня практически все российские банки применяют в расчетах аннуитетную схему погашения кредита. Сравнение двух графиков погашения кредита приведено в статье Сравнение графиков погашения кредита дифференцированными аннуитетными платежами в MS EXCEL.

График погашения кредита дифференцированными платежами

Задача. Сумма кредита =150т.р. Срок кредита =2 года, Ставка по кредиту = 12%. Погашение кредита ежемесячное, в конце каждого периода (месяца).

Решение. Сначала вычислим часть (долю) основной суммы кредита, которую заемщик выплачивает за период: =150т.р./2/12, т.е. 6250р. (сумму кредита мы разделили на общее количество периодов выплат =2года*12 (мес. в году)).Каждый период заемщик выплачивает банку эту часть основного долга плюс начисленные на его остаток проценты. Расчет начисленных процентов на остаток долга приведен в таблице ниже – это и есть график платежей.

Для расчета начисленных процентов может быть использована функция ПРОЦПЛАТ(ставка;период;кпер;пс), где Ставка — процентная ставка за период; Период – номер периода, для которого требуется найти величину начисленных процентов; Кпер — общее число периодов начислений; ПС – приведенная стоимость на текущий момент (для кредита ПС — это сумма кредита, для вклада ПС – начальная сумма вклада).

Примечание. Не смотря на то, что названия аргументов совпадают с названиями аргументов функций аннуитета – ПРОЦПЛАТ() не входит в группу этих функций (не может быть использована для расчета параметров аннуитета).

Примечание. Английский вариант функции — ISPMT(rate, per, nper, pv)

Функция ПРОЦПЛАТ() предполагает начисление процентов в начале каждого периода (хотя в справке MS EXCEL это не сказано). Но, функцию можно использовать для расчета процентов, начисляемых и в конце периода для это нужно записать ее в виде ПРОЦПЛАТ(ставка;период-1;кпер;пс), т.е. «сдвинуть» вычисления на 1 период раньше (см. файл примера).Функция ПРОЦПЛАТ() начисленные проценты за пользование кредитом указывает с противоположным знаком, чтобы отличить денежные потоки (если выдача кредита – положительный денежный поток («в карман» заемщика), то регулярные выплаты – отрицательный поток «из кармана»).

Расчет суммарных процентов, уплаченных с даты выдачи кредита

Выведем формулу для нахождения суммы процентов, начисленных за определенное количество периодов с даты начала действия кредитного договора. Запишем суммы процентов начисленных в первых периодов (начисление и выплата в конце периода):ПС*ставка(ПС-ПС/кпер)*ставка(ПС-2*ПС/кпер)*ставка(ПС-3*ПС/кпер)*ставка…Просуммируем полученные выражения и, используя формулу суммы арифметической прогрессии, получим результат.=ПС*Ставка* период*(1 — (период-1)/2/кпер)Где, Ставка – это процентная ставка за период (=годовая ставка / число выплат в году), период – период, до которого требуется найти сумму процентов.Например, сумма процентов, выплаченных за первые полгода пользования кредитом (см. условия задачи выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р.За весь срок будет выплачено =ПС*Ставка*(кпер+1)/2=18750р.Через функцию ПРОЦПЛАТ() формула будет сложнее: =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ(«1:»&кпер))-1;кпер;-ПС))

ипотечный калькулятор эксель, как делать расчеты по кредиту в Excel

О том как делать расчеты по ипотеке.

Каждый из нас рано или поздно подходил к тому, как же ему рассчитать свои платежи и т.п. Здесь
я расскажу о том как рассчитывать АННУИТЕТНЫЕ платежи в Excel. В Интернете
есть масса ипотечных калькуляторов. На сайте практически каждого банка есть свой ипотечный калькулятор.
Но у нас не всегда есть доступ к сети, поэтому научившись считать ипотеку в экселе вам будет
проще принимать решение.

Спасибо Биллу Гейтсу – у нас есть финансовые функции Excel! Так например, самая
полезная функция для расчета ипотечного платежа в экселе оказалась функция
ПЛТ() — платёж Объясняю на пальцах, просто в любой ячейке Excel пишем следующее:

=ПЛТ(ставка;периоды;сумма)

Причем, поскольку платежи в банк мы делаем ежемесячно, то всё должно быть указано в месяцах – и ставка, и количество периодов. Например, если мы берем ипотечный кредит на 10 лет (или 120 месяцев) под 12% (1% в месяц) годовых на сумму 1 000 000р, то в ячейку нужно вписать следущее:

=ПЛТ(1%;120;1000000)

или так:

=ПЛТ(12%/12;10*12;1000000)

Не обращайте внимание на то, что ответ (-14 347,09р) получается красного цвета и со знаком минус — это ведь Вам платить
банку и из своего кошелька, поэтому и минус. Другая полезная при ипотечных рассчетах функция:

Другая полезная при ипотечных рассчетах функция:

ПС(ставка;срок;платёж)

— расчёт максимального кредита (приведенная стоимость).

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

В нашем примере с 12% годовыми давайте предположим что мы бы хотели взять кредит на 20 лет,
то есть на 240 месяцев. Пусть ежемесячно мы хотим платить банку ровно 10 000р. Чтобы
узнать на какую сумму мы можем рассчитывать в Excel в любую ячейку пишем следущее:

=ПС(12%/12;240;10000)

или так:

=ПС(1%;10*12;10000)

Получается та сумма (-908 194,16р) на которую мы можем рассчитывать получить
от банка в качестве ипотечного кредита. Сумма по-прежнему указана красным
цветом со знаком минус, ведь это всё-таки размер вашего будущего долга =)

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

для расчёта процентов:=ПРПЛТ(ставка;номер_платежа;всего платежей;общая_сумма)

для расчёта осн. долга:=ОСПЛТ(ставка;номер_платежа;всего_платежей;общая_сумма)

Где, ставка — рассчитана ежемесячно. Т.е. в нашем примере с кредитом на 12% годовых для расчётов используем 1%.
номер_платежа — это порядковый номер, т.е. если мы будем делать наш первый платёж, то пишем 1, если уже пятый
платёж — то 5. всего_платежей — это общее количество выплат, или периодов выплат как это именует эксель.
Например, если кредит мы берем на 10 лет, то пишем 120, ведь в 10 годах содержатся 120 месяцев. Платим мы
ежемесячно, поэтому и количество платежей будет 120. Если например 20лет, то 240. общая_сумма — это
сумма кредита, который мы берем. Например 1 000 000р, т.е. пишем 1000000.

Пример расчёта второго платежа для ипотечного кредита под 14% годовых, на 25 лет, на 1 500 000р:

Проценты: =ПРПЛТ(14%/12;2;25*12;1500000) = -17 493,51р.

Осн. долг: =ОСПЛТ(14%/12;2;25*12;1500000) = -562,91р.

Платёж: =ПЛТ(14%/12;25*12;1500000) = -18 056,42р.

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

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

Что выгоднее: досрочное погашение с уменьшением срока кредита или досрочное погашение с уменьшением ежемесячного платежа?

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

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

Обратите внимание, что чем раньше вносится дополнительный платеж, тем меньше получается итоговая переплата по кредиту. Давайте обратимся к кредитному калькулятору и проведем эксперимент:К примеру, возьмем потребительский кредит 500 000 рублей на 5 лет под 20 процентов годовых с 1 января 2018 года

Давайте обратимся к кредитному калькулятору и проведем эксперимент:К примеру, возьмем потребительский кредит 500 000 рублей на 5 лет под 20 процентов годовых с 1 января 2018 года.

Рисунок 1. Исходные данные

Без досрочных взносов ежемесячный платеж будет составлять 13 246,94 рублей. Переплата за 5 лет составит 294 816,51 рублей.

Рисунок 2. Переплата без досрочных взносов

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

Рисунок 3. Добавление досрочных взносов

Экономия за счет досрочных взносов составит 52 738,30 рублей, а срок кредита уменьшится на 7 месяцев.

Рисунок 4. Экономия за счет досрочных взносов

Если же мы изменим тип погашения на “досрочное погашение кредита с уменьшением суммы ежемесячного платежа”, с помощью кнопки “Уменьшение платежа”, то цифры будут следующими: размер ежемесячного платежа станет 11 704,19 рублейА экономия за счет досрочных взносов составит 22 509,35 рублей.

Рисунок 5. Переплата в случае досрочных взносов с уменьшением платежа

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

Но что выгоднее, сделать разовый взнос по-больше или делать досрочные взносы чаще, но меньшего размера. Снова обратимся к нашему калькулятору: разделим 50 000 на 5 взносов по 10 000 каждые 2 месяца:

Рисунок 6. Частые досрочные взносы, но меньшего размера

Мы видим, что теперь экономия составляет 64 593,99 рублей, а срок кредита уменьшился на 8 месяцев:

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

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

Как рассчитать аннуитетный платеж в Excel

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

Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.

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

Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.

Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:

Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:

Здесь нам требуется заполнить три поля:

• «Ставка» – годовая процентная ставка по кредиту делённая на 12.
• «Кпер» – общий срок кредитования.
• «Пс» – сумма кредита (указывается со знаком минус).

Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус

Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.

После того как исходные данные будут введены, жмём кнопку «Ок». В результате мы видим в блоке расчетов точное значение ежемесячного аннуитетного платежа:

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

Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках

Можно было просто вписать в строке формул то, что там сейчас вписано

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

Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:

На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.

Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.

Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.

Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:

Основная формула аннуитетного платежа в Excel

Как и говорилось выше, в Microsoft Office Excel можно работать с различными типами платежей по кредитам и ссудам. Аннуитет не является исключением. В общем виде формула, с помощью которой можно быстро вычислить аннуитетные взносы, выглядит следующим образом:  

Основные значения формулы расшифровываются так:

• АП – аннуитетный платеж (название сокращено).
• О – размер основного долга заемщика.
• ПС – процентная ставка, выдвигаемая ежемесячно конкретным банком.
• С – число месяцев, на протяжении которых длится кредитование.

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

Примеры использования функции ПЛТ в Excel

Приведем простое условие задачи. Необходимо посчитать ежемесячный кредитный платеж, если банк выдвигает процент в размере 23%, а общая сумма составляет 25000 рублей. Кредитование продлится на протяжении 3-х лет. Задача решается по алгоритму:

1. Составить общую таблицу в Excel по исходным данным.

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

1. Активировать функцию ПЛТ и ввести для нее аргументы в соответствующее окошко.
2. В поле «Ставка» прописать формулу «В3/В5». Это и будет процентная ставка по взятому кредиту.
3. В строке «Кпер» написать значение в виде «В4*В5». Это будет общее количество выплат за весь срок кредитования.
4. Заполнить поле «Пс». Здесь нужно указать первоначальную сумму, взятую в банке, прописав значение «В2».

Необходимые действия в окне «Аргументы функции». Здесь указан порядок заполнения каждого параметра

1. Удостовериться, что после нажать «ОК» в исходной таблице посчиталось значение «Ежемесячный платеж».

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

Пример расчета суммы переплаты по кредиту в Excel

В этой задаче надо подсчитать сумму, которую переплатит человек, взявший кредит 50000 рублей по процентной ставке 27% на 5 лет. Всего в год заемщик производит 12 выплат. Решение:

1. Составить исходную таблицу данных.

Таблица, составленная по условию задачи

1. Из общей суммы выплат отнять первоначальный размер суммы по формуле «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Ее надо вставить в строку формул сверху главного меню программы.
2. В итоге в последней строке созданной таблички появится сумма переплат. Заемщик переплатит 41606 рублей сверху.

Финальный результат. Практически двукратная переплата

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

Задача с таким условием: клиент зарегистрировал счет в банке на 200000 рублей с возможностью ежемесячного пополнения. Нужно посчитать количество платежа, который человек должен вносить каждый месяц, чтобы через 4 года на его счету оказалось 2000000 рублей. Ставка составляет 11%. Решение:

1. Составить табличку по исходным данным.

Таблица, составленная по данным из условия задачи

1. В строку ввода Эксель ввести формулу «=ПЛТ(B3/B5;B6*B5;-B2;B4)» и нажать «Enter» с клавиатуры. Буквы будут отличаться в зависимости от ячеек, в которых размещена таблица.
2. Проверить, что сумма взноса автоматически посчиталась в последней строке таблицы.

Окончательный результат расчета

Особенности использования функции ПЛТ в Excel

В общем виде данная формула записывается следующим образом: =ПЛТ(ставка; кпер; пс; ; ). У функции есть следующие особенности:

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

Аннуитет или ниспадающий остаток?

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

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

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

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

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

Варианты расчетов c помощью ипотечного калькулятора: так при сумме залога 1 500 000 рублей на 15 лет под 10% годовых регулярная ежемесячная выплата составит 16 119 рублей. И останется такой все последующие годы.

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

Варианты расчетов: при тех же условиях ипотечного кредитования, но с дифференцированными платежами первый взнос составит 20 833 рубля. А последний – 8 403 рубля.

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

1. сумма ипотеки
2. первоначальный взнос (как взять квартиру в ипотеку без первоначального взноса?)
3. процентная ставка,
4. период кредитования (месяцы).

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

График платежей

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

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

X = S * (P + P / (1 + P)n – 1),

при этом:

• под X понимается ежемесячный платеж;
• S – это первоначальная сумма ипотечного кредита;
• P – 1/12 часть процентной ставки;
• n – количество месяцев.

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

Формула выглядит так — Y = S / N, где:

1. Y – сумма основного платёжа;
2. S – размер ипотеки;
3. N – количество месяцев.

Сумму выплаты процентов для каждого месяца необходимо рассчитывать индивидуально, по формуле —

Z = Sn * P / 12, где:

• Z– начисленные проценты;
• Sn — остаток основной задолженности;
• P – годовая процентная ставка по ипотеке.

На десятилетний период придется обсчитать 120 платежей. Поэтому гораздо проще воспользоваться программами-помощниками.

Напоминаем, что вы можете получить быструю бесплатную консультацию по телефону: 8 (800) 350-14-90просто кликните для звонка

Low cost, no risk

SpreadsheetConverter is an add-in for Microsoft Excel in Windows. It appears as an extra menu in Excel. Excel and SpreadsheetConverter are only used when you create the spreadsheet and convert it to web format. We charge a small one-time license fee for each computer on which you install the add-in to perform such conversions.

• There are no additional fees per month.
• There are no additional fees per page or conversion.
• There are no additional fees per web server, page view or visitor.
• There are no additional fees for Excel or plug-ins. The web pages we create have everything in them and do not need or use Excel.

Here is the complete price list:

Edition
	Standard	Professional
License to generate HTML pages	$254	$494
License to generate pages for iPhone/Android	$334	$654
License to generate pages for Node.js	$374	$714
Cost per month	$0	$0
Cost per page	$0	$0
Cost per server	$0	$0
Cost per visitor	$0	$0
* VAT or the equivalent sales tax in your country will be added to the prices above if applicable.

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

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

Итак, любой кредит имеет 4 основных параметра:

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

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

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

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

– Срок – Функция ПС()

– Сумма – Функция КПЕР()

– Ставка – Функция СТАВКА()

– Ежемесячный платеж – Функция ПЛТ()

Параметры функций одни и те же – знаете три из 4-х показателей, соответствующая функция выдаст 4-й. Нагляднее смотрите первый лист файла .

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

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

Переходим ко второму листу.

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

На второй строке –

Дата – определяется как то же число, что и выдача кредита, но следующего месяца. Используем функцию ДАТА, где год и число те же, что и в предыдущем периоде, а месяц на один больше. Но есть нюанс – банк ведь не примет платеж в выходной день. Поэтому делаем корректировку числа с помощью функции ДЕНЬНЕД

Важно : дату можно корректировать вручную, на следующую дату влияния не окажет.
Сумма ежемесячного платежа (которая определяется по функции ПЛТ).
Сумма погашения процентов как умножение величины прошедшего периода на соответствующий процент. Используется функция ДОЛЯГОДА, чтобы убрать последствия високосности

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

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

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

Меняем две формулы:

1) Сумму погашения основного долга. Она будет неизменной – сумма долга разделить на количество периодов (месяцев).

2) Ежемесячный платеж определяем как сумму двух частей – погашений основного долга и процентов.

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

Какие еще можно вытащить показатели, которые важны нам, но не учитываются в доступных калькуляторах?

Для меня это была обоснованность взятия кредита. Я тогда снимал квартиру и поэтому мне нужно было рассчитать цену кредита. Цена кредита для меня равнялась сумме выплаченных процентов за минусом арендных платежей за весь период кредита. Если сумма небольшая или вообще отрицательная, то кредит брать стоит. Бонусом для меня было проживание в СВОЕМ (!) доме, где я знал, что могу забить гвоздь в МОЮ стенку, да и вообще психологическое влияние большое.

Если кому нужно более наглядно, то вот здесь видео , где этот файл и формировался.

«Глаза боятся, а руки делают»

P.S. Я веду рассылку, где идет немного другой формат подачи информации, поэтому если интересно – подписывайтесь на рассылку, форма в правой части страницы. Тем более, что сразу после подписки вы получите бонус – мой видеокурс о 10 полезных инструментах Excel, которые не всякому известны.

P.P.S. Для тех, кто хочет экстренно быстро изучить Excel, я специально сделал практикум-самоучитель, можете посмотреть его описание по вот этой ссылке .