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

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

В экселе, на сайте и самостоятельно

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

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

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

Следите за руками

Из чего состоит ежемесячный платеж

Ежемесячный платеж состоит из платежа по основному долгу и начисленным процентам. Соотношение основного долга и процентов в платеже может быть разным. Поговорим об этом ниже.

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

Какими бывают ежемесячные платежи

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

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

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

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

Источник

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

history 21 июля 2020 г.

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

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

Задача

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

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

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

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

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

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

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

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

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

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

history 2 февраля 2015 г.

Задача1

Определить величину ежемесячных равновеликих выплат по ссуде, размер которой составляет 100 000 руб., а процентная ставка составляет 10% годовых. Ссуда взята на срок 5 лет.

Разбираемся, какая информация содержится в задаче:

Эта функция имеет такой синтаксис: ПЛТ(ставка; кпер; пс; [бс]; [тип]) PMT(rate, nper, pv, [fv], [type]) – английский вариант.

Примечание : Функция ПЛТ() входит в надстройку «Пакет анализа». Если данная функция недоступна или возвращает ошибку #ИМЯ?, то включите или установите и загрузите эту надстройку (в MS EXCEL 2007/2010 надстройка «Пакет анализа» включена по умолчанию).

Примечание : В нашем случае проценты начисляются в конце периода. Например, по истечении первого месяца начисляется процент за пользование ссудой в размере (100 000*10%/12), до этого момента должен быть внесен первый ежемесячный платеж. В случае начисления процентов в начале периода, в первом месяце % не начисляется, т.к. реального пользования средствами ссуды не было (грубо говоря % должен быть начислен за 0 дней пользования ссудой), а весь первый ежемесячный платеж идет в погашение ссуды (основной суммы долга).

Если процентная ставка = 0, то формула упростится до =(Пс + Бс)/Кпер Если Тип=0 (выплата в конце периода) и БС =0, то Формула 2 также упрощается:

Таблица ежемесячных платежей

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

Для вычисления ежемесячных сумм идущих на погашение процентов за ссуду используется функция ПРПЛТ (ставка; период; кпер; пс; [бс]; [тип]) с теми же аргументами, что и ОСПЛТ() (подробнее см. статью Аннуитет. Расчет в MS EXCEL выплаченных процентов за период ).

Задача2

Ссуда 100 000 руб. взята на срок 5 лет. Определить величину ежеквартальных равновеликих выплат по ссуде, чтобы через 5 лет невыплаченный остаток составил 10% от ссуды. Процентная ставка составляет 15% годовых.

Источник

Формула аннуитетного платежа excel с досрочным погашением

Вычисление аннуитетного платежа в Microsoft Excel

​Смотрите также​ последнего периода кредита​Но если уж случится​Заполним таблицу вида:​Аннуитет предполагает, что клиент​ в конце каждого​ расчета аннуитетного платежа​Если рассматривать составляющие аннуитетного​ кредита – нужно​ K=-i/(1-(1+i)^(-n)) или K=(-i*(1+i)^n)/(((1+i)^n)-1),​ с помощью финансовой​ таблице произойдет автоматический​ у нас уже​ данной статье с​ по телу займа​В поле​

​8​Прежде, чем брать заем,​

Расчет оплаты

​ и добавить столбцы​ так, что вам​Комиссия берется ежемесячно со​

​ в Excel есть​ платежа, можно заметить,​ адекватно оценить собственную​ где i=ставка за​ функции MS EXCEL​ пересчет данных. Поэтому​ идет со знаком​ каждым новым периодом​ используем функцию​«Пс»​.​ неплохо было бы​ с простыми формулами​ или вашим близким​

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

Этап 1: расчет ежемесячного взноса

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

​ проценты по кредиту,​

​ обременительными для заемщика.​ Напоминаем, что выражение​ аннуитета в статье​ случая, а применять​ сложить. Делаем это​ по процентам. Для​

​ целей. Устанавливаем курсор​​. Как и в​​ Говоря простыми словами,​ будущем от различных​ равна вычисленной выше​ походом в банк​ Сумма основного долга​ на остаток кредитной​ =150т.р./2/12, т.е. 6250р.​ шагов:​​ вторая – часть​​ Но на этом​ для K справедливо​ найдете здесь.​ в различных ситуациях​ и жмем на​ этих целей будем​ в ячейку, которая​​ предыдущих случаях, указываем​​ это общая величина​

​ неожиданных неприятностей и​​ в ячейке C7)​ хотя бы ориентировочно​ и сумма процентов​ суммы. Поэтому ежемесячные​ (сумму кредита мы​Заполнить исходные данные (сумма,​ суммы основного долга.​ этапе иногда возникают​​ только при БС=0​​Эта функция имеет такой​ для расчета кредитных​ кнопку​​ использовать оператор​​ находится в строке​ ссылку на элемент​ займа на начало​ разочарований, когда выяснится,​ и, ради интереса,​​ прикинуть суммы выплат​​ – составляющие части​

​ платежи будут уменьшаться.​​ разделили на общее​ проценты и срок​ Причем сумма начисленных​ сложности, поскольку не​ (полное погашение кредита​ синтаксис:​ вариантов по аннуитетной​Enter​ПРПЛТ​«1»​ листа, в котором​

​ кредитования, то есть,​​ что переплата слишком​ оставшейся сумме долга:​ по кредиту, переплату,​ аннуитетного платежа.​Чаще применяется аннуитет: выгоднее​ количество периодов выплат​ кредита в ячейках​ процентов обычно превышает​​ у всех есть​​ за число периодов​ПЛТ(ставка; кпер; пс;​ схеме.​.​. Выделяем первую пустую​и в столбце​ содержится данный показатель.​ та сумма, которую​ большая. Помочь в​

​Чтобы сделать наш калькулятор​​ сроки и т.д.​​Сумма основного долга =​ для банка и​ =2года*12 (мес. в​ В2, В3, В4​ сумму, идущую на​​ экономическое образование, чтобы​​ Кпер) и Тип=0​ [бс]; [тип])​​Урок:​​А вот вычисление остатка​ ячейку в столбце​«Выплата по телу кредита»​В поле​ вы берете взаймы,​ данном расчете могут​ более универсальным и​ «Помассажировать числа» заранее,​ аннуитетный платеж –​

​ удобнее для большинства​ году)).​ соответственно).​ погашение ссуды.​ правильно произвести необходимые​ (начисление процентов в​PMT(rate, nper, pv,​Финансовые функции в Excel​ к выплате после​​«Выплата по процентам»​​. Жмем на кнопку​​«Бс»​​ без учета процентов​ инструменты программы Excel.​​ способным автоматически подстраиваться​​ как я это​ проценты.​ клиентов.​

​ общего срока кредитования.​​ для расчета аннуитетных​​ для вышерассмотренной задачи.​: Функция ПЛТ() входит​ без проблем рассчитать​​ нам нужно отнять​​В запустившемся окне​Мастер функций​​ это значение практически​​— это будущая​ этой программе.​ В ячейке А18​ вопросе.​Остаток основного долга =​где:​

​ его остаток проценты.​​).​​ Далее сумма, которая​ платежей по кредиту,​​Для вычисления ежемесячных сумм​​ в надстройку «Пакет​ общий ежемесячный кредитный​ от тела кредита​Мастера функций​. В категории​

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

​«Финансовые»​отмечаем наименование​ поле число​​ тело займа на​​Прежде всего, нужно сказать,​

​ с теми же​​ эту надстройку (в​

Этап 2: детализация платежей

​ помощи функций​«=»​ кнопке​.​В поле​ равен​Аннуитетные.​ последнего периода или​ простых формул. Для​ процентную ставку:​К = (i *​Для расчета начисленных процентов​ столбце «Платежи по​ ранее, это не​ экономической теории аннуитетный​ аргументами, что и​ MS EXCEL 2007/2010​ОСПЛТ​​во второй ячейке​​«OK»​Запускается окно аргументов оператора​«Тип»​«0»​При дифференцированной схеме клиент​ нет, и выводит​ расчета ежемесячной выплаты​взяли кредит 500 000​ (1 + i)^n)​ может быть использована​ кредиту» ввести формулу​

​ ежемесячно равную долю​

​ Это нормально, ведь​ достаточно просто. Причем​ срока кредитования.​ основной суммы долга​ случае за месяц.​​ указанный период. Применяя​​ её абсолютной, выделив​

​ проценты, и часть​ аргумент​ число периодов платежей​​ кредитный калькулятор, который​​. Затем ставим знак​ОСПЛТ​«Тип»​ случае с предыдущим​«Тип»​ образом и общий​ предела (срока кредита).​. Выделяем ячейку, где​​ 100, или 37%.​​ Это ПЛТ:​ – приведенная стоимость​ аннуитетными платежами в​ можно сориентироваться в​ суммы основного долга.​период​ по аннуитету, т.е.​​ можно будет использовать​​«+»​

​ как с использованием​

​Мастер функций​должна быть относительной,​ функции​ на кнопку​. Большинство банковских учреждений​​ оплаты процентов. Изначально​​ не пустой, то​. В следующем окне​ кредите для расчета​ вводиться формула расчета​ аргументов совпадают с​​ их время.​​Каждый из методов расчета​ очень выгоден для​ с теми же​ стоимость всех денежных​ функции ПЛТ(), так​, в котором нужно​ а во всех​ОСПЛТ​«OK»​ используют именно вариант​ процентные взносы насчитываются​ мы вычисляем сумму​ нужно будет ввести​ полной стоимости кредита​ кредита аннуитетными платежами​ названиями аргументов функций​Чтобы заполнить все месяцы,​​ при правильном применении​​ заемщиков.​ аргументами, что и​ потоков аннуитета в​ и впрямую по​ переместиться в категорию​ других полях координаты​теми самыми данными,​.​ с оплатой в​ на всю сумму​ выплат с помощью​ аргументы для расчета:​ (ПСК) теперь применяется​​ в Excel: =ПЛТ($B$3/12;​​ аннуитета – ПРОЦПЛАТ()​

​ не входит в​ с формулой до​ равную предстоящему размеру​ условий кредитования, аннуитетные​ статью Аннуитет. Расчет​ истечении числа периодов​ составим таблицу ежемесячных​. Там выделяем надпись​ абсолютному виду. После​ для функции​ которую мы выделили​ аргумент тоже является​ мере того, как​ ПРПЛТ и ОСПЛТ.​- процентная ставка​ определяется в процентах​ закрепить ячейки, используем​ группу этих функций​ конца графика погашения.​ платежа. Поэтому, если​ платежи делятся на​ в MS EXCEL​

​ Кпер). В нашем​​ платежей с расшифровкой​

​«СУММ»​ этого щелкаем по​ПЛТ​ в первом пункте​ необязательным, и если​ тело уменьшается, сокращается​ Если же номера​ по кредиту в​​ с точностью до​​ абсолютные ссылки. Можно​ (не может быть​​ Но поскольку аннуитетный​​ есть сомнения в​​ небольшие группы. Их​​ выплаченных процентов за​ случае Бс =​ оставшейся части долга​и жмем на​ кнопке​. Только учитывая тот​ данного руководства, выводится​ его опустить считается,​ и начисление процентов.​ нет, то выводим​ пересчете на период​ третьего знака после​ вводить в формулу​

​ использована для расчета​

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

​ платеж со временем​ правильности уже сделанных​ классификация достаточно разнообразна,​ период).​ 0, т.к. ссуда​ и начисленных процентов.​ кнопку​«OK»​ факт, что в​ результат вычисления. Как​ что он равен​ Но общая сумма​

​ пустую текстовую строку:​ выплаты, т.е. на​ запятой по следующей​ непосредственно числа, а​ параметров аннуитета).​ не меняется, цифры​ вычислений, можно произвести​ однако некоторые виды​Примечание​ в конце срока​При кредитовании банки наряду​«OK»​.​ будущем будет применяться​
​ видим, величина ежемесячного​ нулю.​ оплаты остается неизменной​Реализованный в предыдущем варианте​ месяцы. Если годовая​

Задача1

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

​ с дифференцированными платежами​.​

​ Таким образом, с​ реальной жизни вы,​ 1% соответственно.​ ставка базового периода;​ 36; 100000).​Функция ПРОЦПЛАТ() предполагает начисление​

​ калькулятора аннуитетных платежей.​​ при аннуитетных платежах​Виды аннуитетов:​ ОБЩПЛАТ(), см. здесь.​

​ течением времени удельный​​ скорее всего, будете​Кпер​ЧБП – число базовых​Ячейки окрасились в красный​ процентов​ Он есть на​ вне зависимости от​Фиксированные – неизменные на​Конечно, для составления таблицы​ 0 или 1,​ платежами (как правило,​

​ суммировать данные в​Применив маркер заполнения, производим​ менялись. Для этого​ перед данной суммой.​ используем таблицу с​ вес процентов в​ вносить дополнительные платежи​
​- количество периодов,​ периодов в календарном​ цвет, перед числами​в начале каждого периода​ сайтах всех банков,​
​ выбранного способа вычислений​ протяжении всего срока​ ежемесячных платежей можно​ обозначающее, когда должна​ ежемесячными), которые включают​ ячейках, что нам​
​ копирование формулы в​ требуется поставить знак​ Так Эксель указывает​ исходными данными, где​ общем ежемесячном платеже​ для досрочного погашения​ т.е. срок кредита​ году.​ появился знак «минус»,​(хотя в справке​ которые выдают ссуды​ производится с помощью​ кредитования.​
​ воспользоваться либо ПРПЛТ()​ производиться выплата. 0​ как выплату основного​ и нужно выполнить​ остальные элементы столбца,​ доллара перед каждым​ на то, что​ указана процентная ставка​ падает, а удельный​ при удобной возможности.​

​ в месяцах.​​Возьмем для примера следующие​
​ т.к. мы эти​ MS EXCEL это​ с таким способом​ специальной формулы. Кредитные​Валютные – размер платежа​ или ОСПЛТ(), т.к.​ – в конце​ долга, так и​ в столбце​ таким способом получив​
​ значением координат по​ это расход денежных​ по кредиту (​ вес оплаты по​ Для реализации этого​Пс​ данные по кредиту:​ деньги будем отдавать​ не сказано). Но,​ погашения. Для использования​ калькуляторы, мобильные приложения​ может меняться в​ эти функции связаны​ периода, 1 –​ процентный платеж за​

​«Выплата по телу кредита»​
​ помесячный график оплат​ вертикали и горизонтали.​ средств, то есть,​12%​ телу растет. При​ можно добавить в​- начальный баланс,​Для расчета полной стоимости​ банку, терять.​ функцию можно использовать​​ кредитного калькулятора понадобятся​​ и другое программное​ зависимости от колебаний​ и в любой​​ в начале. Если​​ пользование кредитом. Такой​

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

​ кредита нужно составить​​ для расчета процентов,​ те же исходные​
​ обеспечение делает правильные​ на валютном рынке.​ период: ПЛТ= ОСПЛТ​ этот параметр опущен,​ равновеликий платеж называется​

​ синтаксис:​ заём. Как видим,​ сделать, просто выделив​Для того, чтобы рассчитать​500000 рублей​ ежемесячный платеж на​ с дополнительными выплатами,​Бс​ график платежей (порядок​Дифференцированный способ оплаты предполагает,​ начисляемых и в​ данные, что и​ расчеты, отталкиваясь именно​Индексируемые – могут корректироваться​ + ПРПЛТ​ то он считается​ аннуитет.​=СУММ(число1;число2;…)​ как и было​ координаты и нажав​ общую сумму оплаты​) и срок кредита​ протяжении всего срока​ которые будут уменьшать​

​- конечный баланс,​

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

​ погашения предполагается неизменность​ ссылки на ячейки,​ месяца в месяц​F4​ кредитования с учетом​24 месяца​Как раз на расчете​ банков в подобных​ которым мы должны​ (БП). В законе​ по периодам выплат​ ее в виде​ ввести в отведенные​

​ выглядит следующим образом:​​Переменные – платежи меняются​ процентов хорошо демонстрирует​:​ процентной ставки по​ в которых содержатся​

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

​ равными долями;​ ПРОЦПЛАТ(ставка;период-1;кпер;пс), т.е. «сдвинуть»​ поля для заполнения.​АП = О *​ в зависимости от​

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

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

Задача2

​ конце периода. Например,​
​Определить величину ежемесячных равновеликих​«Число1»​ общий ежемесячный платеж.​ не забываем, что​ ежемесячного платежа (​
​Выделяем элемент на листе,​ так как в​ либо срок. Каждый​ никто никому ничего​ графике погашения чаще​Формула расчета дифференцированного платежа:​
​ файл примера).​ расчеты в течение​ пс)-с,​Механизм расчета аннуитетных платежей​. В статье Аннуитет.​ по истечении первого​ выплат по ссуде,​. Затем зажимаем левую​ Для этого вычисления​ годовую ставку нужно​23536,74 рубля​ в который будет​ настоящее время большинство​ такой сценарий для​ не должен.​ всего. В примере​
​ДП = ОСЗ /​Функция ПРОЦПЛАТ() начисленные​ нескольких секунд, дав​где:​

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

​ по кредиту зависит​ Расчет периодического платежа​ месяца начисляется процент​ размер которой составляет​ кнопку мыши и​ не следует прибегать​ разделить на​) на количество месяцев​ выводиться результат расчета,​ банков используют именно​ наглядности лучше посчитать​Тип​ БП = 28​ (ПП + ОСЗ​ проценты за пользование​ потенциальному заемщику возможность​АП – ежемесячный аннуитетный​ от их вида.​ в MS EXCEL.​ за пользование ссудой​ 100 000 руб.,​

Аннуитетный платеж – это…

​ выделяем на листе​ к какому-либо оператору,​12​ (​ и щелкаем по​ эту схему. Она​ отдельно.​- способ учета​ дней.​ * ПС)​ кредитом указывает с​

​ оценить полученную сумму​ платеж;​ Однако для потребительского​ Срочный вклад показано как​ в размере (100 000*10%/12),​ а процентная ставка​ первые две ячейки​ так как можно​.​24 месяца​ пиктограмме​ удобна и для​В случае уменьшения срока​ ежемесячных выплат. Если​Далее находим ЧБП: 365​где:​

Классификация аннуитета

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

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

Преимущества и недостатки аннуитетных платежей

​ выплаты учитываются на​ 13.​ по кредиту;​ (если выдача кредита​ кредита.​пс – ежемесячная процентная​Расчет аннуитетных платежей по​ чтобы накопить желаемую​ первый ежемесячный платеж.​ срок 5 лет.​. В поле, как​ ячеек первого месяца​ аргумент, которого не​

​ весь срок кредитования​

​ ставку базового периода:​

​ на диапазон. Она​«Выплата по телу кредита»​ПЛТ​ составила​Мастера функций​ Клиенты всегда знают​ достигли мы нулевого​ на конец. У​У нас имеются все​ до конца срока​ заемщика), то регулярные​ график погашения кредита​ в сроке кредитования.​ Поэтому важно знать,​ без использования формул​ периода, в первом​Заемщик ежемесячно должен делать​

Из чего состоит платеж по кредиту?

​ состоит из двух​и​. Этот аргумент​564881,67 рубля​. В категории​ сколько нужно заплатить.​ баланса раньше срока:​ нас в России​ необходимые данные –​ погашения периодов;​ выплаты – отрицательный​ дифференцированными платежами.​

​Зная формулу, можно запросто​ стоит ли вообще​ аннуитета. График приведен​ месяце % не​ платеж банку. Этот​ частей, разделенных двоеточием:​«Выплата по процентам»​«Период»​.​«Финансовые»​Для расчета ежемесячного взноса​А в случае уменьшения​ абсолютное большинство банков​ подставляем их в​ПС – процентная ставка​ поток «из кармана»).​

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

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

​ при использовании аннуитетной​

​ кредита дифференцированными платежами​ самостоятельно. Достаточно лишь​ или лучше сразу​ файла примера лист​ пользования средствами ссуды​сумму в счет погашения​ ячейку диапазона и​ знак​ устанавливаем ссылку на​ переплаты по кредиту.​«ПЛТ»​

Формула расчета

​ схемы в Экселе​ пересчитывать ежемесячный взнос​ варианту, поэтому вводим​Примечание. Чтобы получить проценты​ ставку делим на​ суммы процентов, начисленных​ сумма основного долга​ подставить исходные данные​ выбрать другой банк​ Аннуитет (ПЛТ), а​ не было (грубо​

​ части ссудысумму для​ на последнюю. Для​

​«=»​ первую ячейку столбца​ Для этого нужно​и жмем на​

​ существует специальная функция​

​ начиная со следующего​ 0.​

​ за определенное количество​ делится на равные​

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

​ также на листе​ говоря % должен​ оплаты начисленных за​ того, чтобы в​в первую пустую​«Период»​ отнять от общей​ кнопку​ –​ после досрочной выплаты​Также полезно будет прикинуть​ нужно умножать на​Составим график погашения предыдущего​

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

​ периодов с даты​ части пропорционально сроку​ букв, и произвести​ Чтобы определиться, нужно​ Аннуитет (без ПЛТ).​ быть начислен за​ прошедший период процентов​ будущем иметь возможность​ ячейку столбца​

​. Данный элемент листа​ величины выплат по​«OK»​ПЛТ​ периода:​ общий объем выплат​ 100. Достаточно выставить​ кредита по дифференцированной​ начала действия кредитного​

​ кредитования. Регулярно, в​ необходимые математические вычисления​

​ взвесить все плюсы​

​ Также тело кредита​ 0 дней пользования​

​ скопировать указанную формулу​«Общая ежемесячная выплата»​ содержит в себе​

​ кредиту, включая проценты​.​. Она относится к​Существуют варианты кредитов, где​ и переплату, т.е.​

​ для ячейки с​ схеме.​ договора. Запишем суммы​ течение всего срока​ при помощи обычного​

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

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

​ выплачивает банку эти​ расчет погашения кредита​

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

​ ссуды (основной суммы​и не меняется​ абсолютной. Выделяем её​ знак​ первого месяца кредитования.​ мы помним, что​.​=ПЛТ(ставка;кпер;пс;бс;тип)​ любые имеющиеся суммы.​

​ его денег. Это​ процентной ставкой по​ в первый месяц​ конце периода):​ плюс начисленные на​ наиболее понятным, рассмотрим​ внесения платежа на​ БС (см. файл​ долга).​ на протяжении всего​ и жмем на​«+»​ Но в отличие​ первое из этих​В поле​Как видим, указанная функция​ Процентная ставка по​ можно сделать с​ кредиту.​ равняется всей сумме:​ПС*ставка​ его остаток проценты.​ его на примере.​ протяжении всего срока​

​ примера лист Аннуитет​

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

​Решение1​ срока, так же​ функциональную клавишу​

​. Жмем на клавишу​ от предыдущих полей,​ значений уже со​«Ставка»​ обладает довольно большим​ таким кредитам обычно​ помощью простых формул:​Скачать кредитный калькулятор в​ =$B$2. Во второй​(ПС-ПС/кпер)*ставка​ Если кредитным договором​Предположим, что заемщик взял​ кредитования;​ (ПЛТ), столбцы H:I).​Итак, ежемесячный платеж​ как и процентная​F4​Enter​ в указанном поле​ знаком​следует вписать величину​ количеством аргументов. Правда,​ выше, но свободы​Если хочется более детализированного​ Excel​ и последующие –​(ПС-2*ПС/кпер)*ставка​ период погашения установлен​ в банке ссуду​доступность практически для всех​Ссуда 100 000 руб.​ может быть вычислен​ ставка. Также не​

​. Вторую часть ссылки​​.​ мы оставляем ссылку​«-»​ процентов за период.​ последние два из​ выходит больше. Можно​ расчета, то можно​Таким образом, для расчета​ рассчитывается по формуле:​(ПС-3*ПС/кпер)*ставка​ равным месяцу, то​ на сумму 50​ кредитополучателей вне зависимости​ взята на срок​ по формуле =ПЛТ(10%/12;​ изменяется порядок платежей​

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

​ так и оставляем​​Далее с помощью маркера​ относительной, а не​. Поэтому в конкретно​ Это можно сделать​ них не являются​ даже взять в​ воспользоваться еще двумя​

​ аннуитетных платежей по​ =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10​…​ из месяца в​ 000 рублей сроком​ от их финансового​ 5 лет. Определить​ 5*12; 100 000; 0;​ – 1 раз​ относительной. Теперь при​
​ заполнения, как и​ делаем из неё​ нашем случае получается,​ вручную, просто поставив​ обязательными.​ банке еще денег​ полезными финансовыми функциями​ кредиту используется простейшая​ – номер текущего​Просуммируем полученные выражения​ месяц сумма основного​

​ выплат по ссуде,​​ Знак минус показывает,​Сумма для оплаты начисленных​ первая ячейка диапазона​ заполняем колонку данными.​После того, как все​ сложить. Как видим,​ нас он указан​«Ставка»​ имеющемуся кредиту. Для​ОСПЛТ (PPMT)​

​ видите, дифференцированный способ​​ срок кредита; Е9​ суммы арифметической прогрессии,​ Поэтому при дифференцированных​

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

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

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

​Где, Ставка –​ ежемесячных платежей в​ вычислений необходимо знать​ тем, что, зная​ составляет 15% годовых.​банкдал​ проценты начисляются только​

​ и нужно для​ платеж по телу​«OK»​.​ в поле, а​

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

​ займа и оплату​.​Урок:​ затем кликаем по​

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

​ займу производится ежемесячно,​ до дня:​

​ приходится на выплату​

​Ежеквартальный платеж может​

​ это деньги, которые​ ссуды;​ Далее жмем на​

​ самого кредита (тела​ для обычных людей​ кредиту в текущем​ ставка / число​

​ п.1, сумма, уплачиваемая​«OK»​. Собственно этот показатель​ выделили, отобразится величина​ других операторов Эксель​ у нас в​

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

​ вводит произвольные даты​ может посчитать ту​

​ экономической выгоды, он​

​ процентов по кредиту.​ самостоятельно. Для этого​ имеет несколько существенных​

​Альтернативная формула для расчета​

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

​Все параметры функции​ платежа (общий случай):​увеличивается​ задолженности после второго​ПЛТ​ месяц. Она составит​

​ сколько в конкретном​ период оплаты равен​ аргумента. Если применяется​

​Например, сумма процентов,​ значительно сокращаются и​пс = П /​

​Недостатки:​ ПЛТ() выбираются аналогично​=-(Пс*ставка*(1+ ставка)^ Кпер​

​от месяца к​ месяца выводится в​. Но в данном​18536,74 рубля​

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

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

​ всего кредита разделить​ выплаченных за первые​ становятся не такими​

​общая сумма переплаты гораздо​ предыдущей задаче, кроме​ /((1+ ставка)^ Кпер​ месяцу.​ ячейку. Теперь, начиная​ случае это представлено​.​ по телу займа,​ годовую ставку, а​

​ когда-нибудь потом. И​ на срок: =ЕСЛИ(D9​ полгода пользования кредитом​

​Заемщик должен сделать 60​ с данной ячейки,​ более наглядно, именно​

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

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

​ нужно разделить на​ имеющемуся​ номера периодов (месяцев):​ даже не в​ и «основного долга»​ выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р.​. При расчете кредита​ ставка.​ долга;​ требует пояснения.​

​4​подсчитать точное количество дней​

​Функция​ грабительских процентах или​ в текущем периоде:​За весь срок​

​ дифференцированными платежами сумма​Расчет:​сложность досрочного погашения;​Для этого вернемся​ +1);1)​ лет), т.е. всего​ столбца с помощью​ и процентам.​ на остальные ячейки​

​ в Экселе таблицу,​ число​и т.д.​

​ между двумя датами​ОСПЛТ (PPMT)​

​ в том, что​ =F8+G8.​ будет выплачено =ПС*Ставка*(кпер+1)/2=18750р.​ переплаты по процентам​

​пс = 20 /​невозможность перерасчета при желании​ к предыдущей задаче,​Если процентная ставка =​ 60 периодов (Кпер);​ маркера заполнения.​

​Теперь нужно добавить данные​ столбца с помощью​ которую будем заполнять​12​

​«Кпер»​ (и процентов, которые​

​в ячейке B17​ это «потом» все​Внесем формулы в соответствующие​Через функцию ПРОЦПЛАТ()​ будет ниже, чем​ 100 / 12​

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

​ заемщика досрочно погасить​ где ПС =​ 0, то формула​Проценты начисляются​Помесячный расчет остатков к​ в столбец, где​ маркера заполнения. Для​ данными. Строки этой​, соответствующее количеству месяцев​обозначает общее количество​ на них приходятся)​ вводится по аналогии​ равно когда-нибудь наступит.​ столбцы. Скопируем их​ формула будет сложнее:​ при аннуитетных платежах.​ = 0,017.​ ссуду.​ 100000, а БС=0.​ упростится до =(Пс​в конце​ оплате по кредиту​ будет ежемесячно отображаться​ этого устанавливаем курсор​ таблицы будут отвечать​ в году. Деление​ периодов выплат по​ лучше с помощью​ с​ Кредит убивает мотивацию​ на всю таблицу.​ =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ(«1:»&кпер))-1;кпер;-ПС))​

​ Не удивительно, что​Зная все исходные данные,​Если рассмотреть пример расчета​ Найденное значение регулярного​

​ + Бс)/Кпер​каждого периода (если​ сделан за весь​ остаток суммы по​ в нижний правый​ соответствующему периоду, то​ выполняем прямо в​ кредиту. То есть,​ функции​ПЛТ​ к росту. Зачем​Сравним переплату при аннуитетной​Excel – это универсальный​ сегодня практически все​ можно приступать к​ аннуитетного платежа по​ платежа обладает тем​

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

​Если Тип=0 (выплата​ не сказано обратное,​ кредитный период. Как​ кредиту, который ещё​ угол ячейки, в​ есть, месяцу. Учитывая,​ поле окна аргументов.​ если заём берется​ДОЛЯГОДА (YEARFRAC)​в предыдущем примере:​ напрягаться, учиться, развиваться,​ и дифференцированной схеме​ аналитическо-вычислительный инструмент, который​ российские банки применяют​ нахождению аннуитетного платежа​​ кредиту, то сразу​​ свойством, что сумма​​ в конце периода)​​ то подразумевается именно​ и положено, на​ требуется заплатить. В​​ которой содержится формула.​​ что период кредитования​В поле​​ на один год​​Гэндальф​Добавился только параметр​​ искать дополнительные источники​​ погашения кредита:​ часто используют кредиторы​ в расчетах аннуитетную​

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

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

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

​24​​, если на два​​ расчета (см. вложение)​ некоторых ссылок, т.к.​ полчаса оформят кредит​Проведем расчет процентов по​Быстро сориентироваться в мудреных​ графиков погашения кредита​ + 0,017)-60 =​​ прибылью, поэтому предоставляют​​ знаком. Т.е. справедливо​ платежа) и записывают​ средствами в месяц​ а организовали своеобразный​ величины займа, которая​ тянем его вниз​ столбцах указана выплата​месяцам. Можно занести​ года, то число​ при досрочном погашении​ впоследствии мы эту​ на кабальных условиях,​ кредиту в Excel​ формулах, рассчитать проценты,​

​ дифференцированными аннуитетными платежами​ 1336,47 руб.​ заемщикам более выгодные​ равенство: ПС+СУММ(долей ПЛТ,​ в виде А=К*S,​ (за период) составляет​ кредитный калькулятор. Который​ указана в таблице​ до конца таблицы.​

​ тела займа, выплата​

​ на страхование и​​ процентную ставку, имея​​ позволяют функции программы​

​Задача​ не просто большая​ отметить, что среди​ тела кредита)+БС=0: 100000р.+(-100000р.)+0=0.​ это аннуитетный платеж​В конце срока задолженность​ аннуитетной схеме. Если​ платеж по телу​ столбца заполнены. Теперь​

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

​ платеж, который является​вручную, но мы,​. Если кредит берется​ не изменять, а​ПРПЛТ (IPMT)​ прочие допы?​ следующую информацию по​ Microsoft Excel.​. Сумма кредита =150т.р.​ таблица. В ней​ микрофинансовых организаций таких​То же самое​ (т.е. ПЛТ), К​ должна быть равна​ в исходной таблице​ кредита за первый​ мы имеем график​ суммой предыдущих двух​ как и в​ на два года​ пересчитывать платеж в​для вычисления процентной​Так что очень надеюсь,​

​ 0 (БС=0).​ мы, например, поменяем​ месяц в расчетной​ выплаты тела займа​ колонок, а также​ предыдущем случае, указываем​

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

​ с ежеквартальной оплатой,​ сторону уменьшения. Какой​ части вводится аналогично.​ что изложенный ниже​Рассчитаем ежемесячную процентную ставку​ схемы погашения кредита.​ года, Ставка по​ количество вычислений, зная​ при оформлении заема​ задачи: 100000р.+(-90000р.)+БС=0, т.е.​ аннуитета, а S​Расчет суммы выплаты по​ величину займа и​ таблице. Но, учитывая​ помесячно. Как и​ оставшаяся сумма к​ ссылку на месторасположение​ то число периодов​ д.б. формула, поправьте​

​ Осталось скопировать введенные​

Аннуитетный платеж: формула для досрочного погашения кредита

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

Источник