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

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

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

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

Формулу 4 можно назвать «классической», т.к. она применяется в расчетах, где все платежи аннуитетные, она применяется в большинстве банков, кредитных калькуляторах, в электронных таблицах. Так же она используется в расчетах на сайте Calculator-Credit.ru
Расчет аннуитетных платежей по этой формуле, можно производить с помощью MS Excel и встроенной функции рабочего листа PMT (в русских версиях ППЛАТ или ПЛТ)

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

Калькулятор дифференцированного платежа с досрочным погашением эксель

Делаем кредитный калькулятор в Excel Итак, любой кредит имеет 4 основных параметра: Так же есть две формы – аннуитетные (когда вы каждый месяц платите одну и ту же сумму) и дифференцированные (когда постоянной остается часть ежемесячного платежа – та, которая погашает основной долг, а вторая часть регулярно пересчитывается). Если вы знаете 3 показателя, то сможете подобрать четвертый. Мы сделаем сначала калькулятор.

Аннуитетный и дифференцированный методы по - плюсы и минусы, формулы расчета, расчет в Excel. Формула расчета. АП = ОСЗ х ПС / , где АП - размер ежемесячного аннуитетного платежа ОСЗ - остаток ссудной задолженности ПС - месячная процентная ставка по кредитному договору (равна 1/12 годовой процентной ставки) ПП - количество периодов, оставшихся до погашения кредита.

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

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

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

Аннуитетный и дифференцированный платёж по кредиту: онлайн калькулятор

Например, фраза о каких-то (жалких) 25% в год на четыре года. Кажется, что вроде бы немного, и по логике вещей нужно будет отдать в 2 раза больше, чем взял (но психологически так проще - берёшь здесь и сейчас, а расплачиваешься 4 года несравнимо меньшими платежами).

Можно подумать, что достаточно взять калькулятор, умножить размер кредита на 2 (некоторые так делают) и поделить на 48 (количество месяцев). В результате должна получиться сумма, которую придётся отдавать ежемесячно. И некоторые люди/организации, предлагающие денежные средства, так и могут посчитать.

Кредитный калькулятор, аннуитет и дифференцированные платежи

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

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

Кредитный калькулятор — что такое дифференцированные платежи

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

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

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

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

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

Чем аннуитетные платежи отличаются от дифференцированных

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

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

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

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

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

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

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

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

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

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

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

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

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

А = К * S

А – сумма платежа по кредиту;
К – коэффициент аннуитетного платежа;
S – величина займа.

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

К = (i * (1 + i)^n) / ((1+i)^n-1)

где i – процентная ставка за месяц, результат деления годовой ставки на 12;
n – срок кредита в месяцах.

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

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

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

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

сумма основного долга распределена по периодам выплат равными долями;
проценты по кредиту начисляются на остаток.

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

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

ДП – ежемесячный платеж по кредиту;
ОСЗ – остаток займа;
ПП – число оставшихся до конца срока погашения периодов;
ПС – процентная ставка за месяц (годовую ставку делим на 12).

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

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

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

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

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

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

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

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

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

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

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

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

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

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

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

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

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

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

взяли кредит 500 000 руб.;
вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
переплата составила 184 881, 67 руб.;
процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

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

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

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

ПСК = i * ЧБП * 100;
где i – процентная ставка базового периода;
ЧБП – число базовых периодов в календарном году.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Тип ежемесячного платежа

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

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

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

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

Расчет дифференцированного платежа

В начале срока кредитования больше, а затем постепенно уменьшаются, т.е. регулярные платежи по кредиту не равны между собой. Структура дифференцированного платежа состоит из двух частей: фиксированной на весь период суммы, идущей на погашение суммы задолженности, и убывающей части - процентов по кредиту, которая рассчитывается от суммы остатка заложенности по кредиту. Из-за постоянного уменьшения суммы долга уменьшается и размер процентных выплат, а с ними и ежемесячный платеж.
Для того чтобы вычислить сумму возврата основного долга, необходимо первоначальную сумму кредита разделить на срок кредита (количество периодов):
Формула 1. , где
ОД - возврат основного долга; СК - первоначальная сумма кредита; КП - количество периодов.

На этом сходство в подходах банков заканчивается, и начинаются различия. Состоят они в подходах к вычислению суммы причитающихся процентов. Основных подходов два, разница - в используемой временной базе. Часть банков исходят из того, что «в году 12 месяцев», и тогда размер ежемесячных процентных выплат определяется по формуле:
Формула 2. , где
НП - начисленные проценты; ОК ПС - годовая процентная ставка.

Часть банков исходит из того, что «в году 365 дней» и такой подход называется расчетом точных процентов с точным числом дней ссуды. Размер ежемесячных процентных выплат в данном случае определяется по формуле:
Формула 3. , где
НП - начисленные проценты; ОК - остаток кредита в данном месяце; ПС - годовая процентная ставка; ЧДМ - число дней в месяце (понятно, что это число меняется от 28 до 31).

Пример 1.
В качестве примера приведен график платежей для кредита в размере 1 000 условных единиц на срок 12 месяцев, с ежемесячным возвратом 1/12 части кредита и уплатой процентов. В этом примере, как и на сайте сайт при расчете начисленных процентов используется формула № 2. («в году 12 месяцев»).

Таблица 1.

!

Расчет аннуитетного платежа

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

Формула 4.
, где
АП ПС СК - первоначальная сумма кредита; КП - количество периодов.
! Т.е. если платежи ежемесячные, то КП – срок в месяцах, а ПС месячная процентная ставка (1/12 годовой)

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

Пример 2.
В качестве примера приведен график аннуитетных платежей для кредита в размере 1 000 условных единиц на срок 12 месяцев.

Таблица 2.

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

Другие формулы для расчета аннуитетного платежа

Некоторые кредитные организации применяют формулу, где первый платеж - не аннуитетный:

Формула 5.
, где
АП ПС - процентная ставка за период начисления; СК - первоначальная сумма кредита; КП - количество периодов.

Первый платеж предварительный - не аннуитетный. Он всегда, якобы, меньше АП, т.к. включает в себя только проценты за первый период, который может быть полным или неполным. Но при полном периоде - 31 день, при высоких ПС и долгосрочном кредитовании предварительный платеж может быть больше АП! Оставшиеся (КП -1) платежей – аннуитетные. Эта формула используется в АИЖК.

Также на практике встречается применение формулы, где первый и последний платежи – не аннуитетные:

Формула 6.
, где
АП ПС - процентная ставка за период начисления; СК - первоначальная сумма кредита; КП - количество периодов.

Первый и последний платежи не аннуитетные, первый - только проценты за первый период, а последний - остатки, "хвосты" и т.д.
Оставшиеся (КП - 2) платежей - аннуитетные. Видимо, банки подгоняют АП под целое число рублей или долларов. Поэтому образуется "хвост", который переходит на последний не Аннуитетный Платеж. Далее после каждого досрочного погашения банки подгоняют уже новый уменьшенный АП под целое число денежных единиц. Т.е. "хвост" может уменьшаться или увеличиваться.

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

Что выгоднее аннуитетная или дифференцированная схема платежей?

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

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

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

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

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

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

Расчёт дифференцированного платежа

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

Для расчета начисленных процентов нужно остаток кредита на указанный период умножить на годовую процентную ставку и всё это поделить на 12 (количество месяцев в году).

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

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

Для примера рассчитаем график платежей по кредиту в размере 100000 р. и годовой процентной ставкой 10%. Сроком погашения кредита возьмём 6 месяцев.

Определим размер основного платежа:

100000 / 6 = 16666,67

Определим размер выплаты за каждый месяц периода кредитовния:

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

Что подтверждает правильность наших расчётов.