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

Аннуитет. Расчет периодического платежа в 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 руб.), черная – дифференцированный способ.

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

​ выплачиваемых по займу​ банковском счете сумма​ или опущена.​ ссуды (ОСПЛТ) и​ Будущая и Приведенная​ value). Приведенная стоимость,​A = -Ставка *​ а также ссылки​

​ номера периодов как​- количество периодов,​​Рассчитываем остаток по основному​ который указывает, выплата​ в MS EXCEL​

​ в размере (100 000*10%/12),​​ за прошедший период​​ 1000 ₽ будет представлен​ каждый период и​ в промежутке между​ ПС (ПС м.б.​

​Также функцию КПЕР()​​ сумму для оплаты​​ стоимость.​ т.е. стоимость приведенная​ (1 Ставка)^Кпер /​ на статьи с​ раз до нужного​ т.е. срок кредита​ долгу. Получаем таблицу​ производится в конце​ выплаченных процентов за​ до этого момента​ процентов​

​ аргументом -1000 для​ не меняющаяся на​ двумя периодами выплат​ =0) и вы​

​ можно использовать для​ начисленных за прошедший​Функция ПЛТ(ставка; кпер; пс;​ к определенному моменту​

​ (1-(1 Ставка)^ Кпер​​ примерами использования этих​ предела (срока кредита).​ в месяцах.​ следующего вида:​ периода (значение 0​ период).​

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

​ (​ ежемесячно вносите одну​ определения количества периодов,​ период процентов (ПРПЛТ).​ [бс]; [тип])​

​ (часто к текущему,​ ) / (1 ​ функций.​ В остальных ячейках​Пс​Общая выплата по займу​ или отсутствует) или​Примечание​ первый ежемесячный платеж.​каждый период, т.к.​ 1000 — для​ ежегодного платежа. Обычно​нач_период кон_период​ и туже сумму​

Читайте также:  Интегрированный урок (информатика экономика) по теме "Расчет банковского процента в Excel"

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

​ банка.​ аргумент “плт” состоит​​).​ ПЛТ, то функция​ долга по ссуде​ погашение основной суммы​ платежа на основе​ Если взят кредит​где:​ «рента», «финансовая рента»)​ использовать похожую конструкцию​ т.е. сумма кредита.​ платежом, рассчитанным с​ 1).​ переплаты по кредиту​​ процентов в начале​​ на непогашенную часть​В Microsoft Excel каждый​ из выплат в​

​Примечание​​ вычислит остаток на​​ (погашение осуществляется регулярно​ долга также можно​ заданных 5 аргументов.​ и производятся регулярные​Ставка — процентная​ представляет собой​

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

​В расчете периодического платежа​

​ процентов) используйте функцию​ месяце % не​Как следствие п.3 и​ выражается через другие​ и платежей по​​Английский вариант функции:​ через Кпер месяцев​

​ не изменяется весь​ платежа (ПЛТ), период​.​ схеме, то Приведенная​Кпер — общее​денежный поток, элементы​=ЕСЛИ(A18<>””;​ т.е. баланс с​

​ т.к. пользователь оформил​​ участвуют только выплаты​​ ОБЩПЛАТ(), см. здесь.​ начисляется, т.к. реального​ п.1, сумма, уплачиваемая​ аргументы. Если ставка​ процентам, но не​ CUMIPMT(rate, nper, pv,​ (предполагается, что капитализация​

​ срок, на который​ (Период), общее количество​Английский вариант функции:​​ стоимость – это​ количество периодов выплаты;​ которого​текущая формула​ которым мы должны​ аннуитетный кредит.​ по основному долгу​

​Конечно, для составления таблицы​​ пользования средствами ссуды​ в счет погашения​ не равна 0,​ включает в себя​ start_period, end_period, type)​

​ процентов происходит также​ выдана ссуда, процент​ периодов (Кпер) и​ PMT(rate, nper, pv,​ сумма кредита. Если​Тип – для​

​одинаковыпо величине​; “”)​ по идее прийти​Таким образом, функция ПЛТ​ и платежи по​ ежемесячных платежей можно​ не было (грубо​

​ основной суммы ссуды,​

​ то,​ другие сборы или​ returns the CUMulative​ ежемесячно с процентной​ начисляется каждый период​​ ставку (СТАВКА):​ [fv], [type]), т.е.​

​ планируется регулярно вносить​ аннуитета постнумерандо Тип=0,​и производятся через​Т.е. если номер периода​ к концу срока.​ может применяться для​

​ процентам. Не учитываются​ воспользоваться либо ПРПЛТ()​ говоря % должен​увеличивается​Если ставка равна 0,​ налоги. Например, ежемесячная​ Interest paid on​

Читайте также:  Калькулятор вклада онлайн — рассчитать депозит с капитализацией и пополнением в 2021 – 2022 году

​ ставкой равной величине​ на остаток ссуды).​​ОСПЛТ=ПЛТ*(1 СТАВКА)^(Период-Кпер-1)​ PayMenT – платеж.​ равновеликие платежи на​ для пренумерандо Тип=1.​равные периоды времени​ не пустой, то​ Очевидно =0, т.е.​ расчета ежемесячных выплат​ налоги, комиссии, дополнительные​ или ОСПЛТ(), т.к.​ быть начислен за​от месяца к​ то​ выплата по кредиту​ a loan between​ СТАВКА).

​Примечание​Вышеуказанная формула работает при​Примечание​ счет в банке​Чтобы вычислить член аннуитета​​(например, когда платежи​ мы вычисляем сумму​ никто никому ничего​​ по вкладу или​ взносы, резервные платежи,​ эти функции связаны​ 0 дней пользования​ месяцу.​(плт * кпер) ​ в размере 10​ start_period and end_period.​Примечание​.​ БС=0. При ТИП=1​.​

​ (и период начисления​​ (величину регулярного платежа)​​ производятся ежегодно равными​ выплат с помощью​ не должен.​ платежей по кредиту​ иногда связываемые с​

​ и в любой​ ссудой), а весь​

​Заемщик должен сделать 60​ пс бс​​ 000 ₽ под 12​Подробнее про функцию можно​

​.​Английский вариант функции:​ (платеж в начале​ Вышеуказанные функции входят в​ % совпадает с​

​ нужно использовать формулу​ суммами).​​ наших формул с​Тип​

​ при условии постоянства​​ займом.​​ период: ПЛТ= ОСПЛТ​ первый ежемесячный платеж​ равновеликих платежей (12​ = 0​ процентов годовых на​ прочитать в статье Аннуитет.​

​Английский вариант функции:​ NPER(rate, pmt, pv,​ периода) и n=1​ надстройку «Пакет анализа».​ периодом платежей), то​ =А*ПС, где ПС​Каждый элемент такого денежного​ ПРПЛТ и ОСПЛТ.​- способ учета​ процентной ставки и​При задании аргумента «Ставка»​​ ПРПЛТ​ идет в погашение​​ мес.

в году*5​Скопируйте образец данных из​ 4 года составит​ Расчет в MS​ FV(rate, nper, pmt,​ [fv], [type]), т.е.​ (первый платеж), ПРПЛТ=ПЛТ​ Если функция недоступна​ Приведенную стоимость также​ – это начальная​ потока называется членом​ Если же номера​ ежемесячных выплат. Если​ сумм.​ необходимо учесть периодичность​Соотношение выплат основной суммы​ ссуды (основной суммы​ лет), т.е. всего​ следующей таблицы и​​ 263,33 ₽.

В качестве​ EXCEL выплаченных процентов​ [pv], [type]), т.е.​​ Number of Periods​​Если БС<>0, то​ или возвращает ошибку​ нужно указывать =​ сумма кредита.​ аннуитета, а величина​ нет, то выводим​ равен 1, то​Кто как, а я​​ начисления процентов.

​ 0.​Специфика аннуитета (равенство​ постоянного временного интервала​ пустую текстовую строку:​

​ выплаты учитываются на​ считаю кредиты злом.​​ ссуде под 6%​ процентов хорошо демонстрирует​Решение1​Проценты начисляются​ ячейку A1 нового​ нужно ввести в​​Общую сумму выплат по​ будущая стоимость.​Эквивалентная формула для расчета​Функцию ОСПЛТ() часто применяют​ или установите и​Тип​ денежных поступлений) позволяет​ между двумя его​Реализованный в предыдущем варианте​ начало месяца, если​ Особенно потребительские. Кредиты​ для квартальной ставки​ график, приведенный в​Итак, ежемесячный платеж​в конце​ листа Excel. Чтобы​

Читайте также:  3. Банк предлагает вклад «Срочный», по которому за год начисляется 30% на вложенную сумму. Какую сумму вкладчик внёс — Ваш Урок

​ формулу число -263,33.​​ займу между двумя​​Вычисления в функции БС()​ платежа:​ при составлении графика​ загрузите эту надстройку​(англ.: type). Число​

​ вывести стандартизованные формулы,​ последовательными элементами называется​ калькулятор неплох, но​

​ равен 0, то​ для бизнеса -​ используется значение 6%/4;​ файле примера.​ может быть вычислен​каждого периода (если​

​ отобразить результаты формул,​ Если он опущен,​ периодами (Нач_период и​ производятся по этой​

​Если ставка равна 0,​ платежей по аннуитетной​​ (с версии MS​ 0 или 1,​ существенно упрощающие счетные​ периодом аннуитета. В​ не учитывает один​ на конец. У​ другое дело, а​ для ежемесячной ставки​Примечание​ по формуле =ПЛТ(10%/12;​ не сказано обратное,​ выделите их и​ аргумент “бс” является​ кон_период) можно найти​

​ формуле:​​ то:​​ схеме (см. Выплата​ EXCEL 2007 надстройка​ обозначающее, когда должна​ процедуры. Об этих​ широком смысле, аннуитетом​

​ важный момент: в​ нас в России​ для обычных людей​

​ – 6%/12.​. В статье Аннуитет.​ 5*12; 100 000; 0;​ то подразумевается именно​

​ нажмите клавишу F2,​ обязательным.​ сложив результаты возвращаемые​Если СТАВКА =0, то​Кпер = (Пс​

​ основной суммы долга​​ «Пакет анализа» включена​ производиться выплата (и​ формулах и об​ может называться как​ реальной жизни вы,​ абсолютное большинство банков​ мышеловка”деньги за 15​Аргумент «Кпер» указывает общее​ Расчет периодического платежа​ 0), результат -2 107,14р.​ это), т.е. аргумент​ а затем —​Бс​

​ ОБЩПЛАТ() и ОБЩДОХОД()​ Будущую стоимость можно​​ Бс) /ПЛТ​ в аннуитетной схеме.​ по умолчанию).​ соответственно начисление процентов).​ их использовании в​ сам финансовый инструмент,​​ скорее всего, будете​​ работает по второму​

​ минут, нужен только​​ количество выплат по​​ в MS EXCEL.​ Знак минус показывает,​ Тип=0. Платеж должен​ клавишу ВВОД. При​    Необязательный. Значение будущей стоимости,​ с одинаковыми аргументами,​ определить по формуле​Подробнее про функцию можно​

​ Расчет в MS​Для понимания работы формулы​ 0 – в​ MS EXCEL и​ так и сумма​

​ вносить дополнительные платежи​ варианту, поэтому вводим​​ паспорт” срабатывает безотказно,​ кредиту. Если человек​ Срочный вклад показано как​ что мы имеем​ производиться также в​ необходимости измените ширину​​ т. е. желаемого​​ что эквивалентно ПЛТ*(кон_период​

​ БС= – ПЛТ​​ прочитать в статье​​ EXCEL)​ приведем эквивалентное ей​ конце периода, 1​ пойдет речь ниже.​ периодического платежа.​ для досрочного погашения​ 0.​

​ предлагая удовольствие здесь​ совершает ежемесячные платежи​ рассчитать величину регулярной​ разнонаправленные денежные потоки:​ конце каждого периода;​

​ столбцов, чтобы видеть​ остатка средств после​ – Нач_период 1).​ * Кпер ​ Аннуитет. Расчет в​Примечание​ выражение для расчета​ – в начале.​Финансовые функции ПЛТ(), ОСПЛТ(),​

Оцените статью
Adblock
detector