17.01.2020      25      0
 

Аннуитетный платж что это такое формула как рассчитать пример расчета в excel


Вычисление суммы процентов, которую необходимо выплатить в промежутке между 2-мя периодами

Чтобы вычислить, сколько процентов нужно будет выплатитьс момента предоставления займа, после истечения заданного количества периодов, используйте формулу: ОБЩПЛАТ(ставка; кол_пер; нз; 1; кон_период; тип).

Задача1. Предположим, что заем 1 млн. был выдан на 5 лет. Годовая ставка = 10%. Начисление процентов и погашение займа происходит ежемесячно в конце месяца (тип=0). Определить сколько процентов будет выплачено банку по прошествии 2-х лет.Решение1. =ОБЩПЛАТ(10%/12; 5*12; 1 000 000; 1; 2*12; 0)

Задача2. Предположим, что заем 2 млн. был выдан на 3 года. Годовая ставка = 7%. Начисление процентов и погашение займа происходит ежеквартально в начале месяца (тип=1). Определить сколько процентов будет выплачено банку по прошествии 1-го года.Решение2. =ОБЩПЛАТ(7%/4; 3*4; 2 000 000; 1; 1*4; 1)

Способ 1. Функция ОБЩПЛАТ()

Функция ОБЩПЛАТ(ставка; кол_пер; нз; нач_период; кон_период; тип) возвращает кумулятивную (нарастающим итогом) величину процентов, выплачиваемых по займу в промежутке между двумя периодами выплат (нач_период и кон_период).

Примечание. Английская версия: CUMIPMT(rate, nper, pv, start_period, end_period, type) returns the CUMulative Interest paid on a loan between start_period and end_period.

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

Начальное значение (чаще всего — сумма кредита).Нач_период — обязательный аргумент. Номер первого периода, включенного в вычисления. Нумерация периодов выплат начинается с 1.Кон_период — обязательный аргумент. Номер последнего периода, включенного в вычисления.Тип — обязательный аргумент, определяющий время платежа. Для аннуитета постнумерандо Тип=0, для пренумерандо Тип=1.

Примечание. Убедитесь, что аргумент «Ставка» соответствуют ставке за период (период не обязательно = году). Например, при ежемесячных выплатах по 4-х летнему займу из расчета 12 процентов годовых используйте значение 12%/12 для аргумента «Ставка». Аргумент «Кол_пер» будет равен 4*12. При ежегодных платежах по тому же займу используйте значение 12% для аргумента «ставка» и 4 — для аргумента «Кол_пер».

Аннуитетный платж что это такое формула как рассчитать пример расчета в excel

При ежеквартальных платежах по тому же займу используйте значение 12%/4 для аргумента «ставка» и 4*4 — для аргумента «Кол_пер».Примечание. Функция ОБЩПЛАТ() возвращает значение ошибки #ЧИСЛО! если: «Ставка» ≤ 0, «кол_пер» ≤ 0 или «нз» ≤ 0, «нач_период» {amp}lt; 1, «кон_период» {amp}lt; 1 «нач_период» {amp}gt; «кон_период» «тип» является любым числом, отличным от 0 и 1

Из анализа альтернативной формулы ясно, что функция ОБЩПЛАТ() может использоваться, только если БС=0, т.е. когда предполагается, что по прошествии количества периодов «Кол_пер» займ полностью погашается. Также обратите внимание, что в определении функции ОБЩПЛАТ() речь идет только о займе. Определить сумму, накопленную за счет процентов в случае срочного вклада, с помощью  функции ОБЩПЛАТ() не получится (для этого см. Способ 2).

Способ 2. Функция ПРПЛТ()

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

Примечание. Английский вариант функции: IPMT(rate, per, nper, pv, [fv], [type]), т.е. Interest Payment – выплата процентов.

Аннуитетный платж что это такое формула как рассчитать пример расчета в excel

Чтобы вычислить сумму процентов, которые были выплачены в промежутке между двумя периодами, нужно использовать не одну, а несколько функций ПРПЛТ(). Например, вычислим сумму долга, выплаченную в 3-м и 4-м периоде:=ПРПЛТ(ставка; 3; кпер; пс; [бс]; [тип]) ПРПЛТ(ставка; 4; кпер; пс; [бс]; [тип])

Чтобы вычислить сумму процентов, которая была выплачена в промежутке между двумя произвольными периодами нач_период и кон_период используйте формулу:=СУММПРОИЗВ(ПРПЛТ(ставка;СТРОКА(ДВССЫЛ(нач_период{amp}amp;»:»{amp}amp;кон_период)); кпер; пс; [бс]; [тип]))

Выражение СТРОКА(ДВССЫЛ(нач_период{amp}amp;»:»{amp}amp;кон_период)) создает массив последовательных чисел, например 2:3:4:5 (если нач_период=2 и кон_период=5). Функция СУММПРОИЗВ() суммирует результаты ПРПЛТ(), т.е. вышеуказанная формула эквивалентна формуле:=ПРПЛТ(ставка; 2; кпер; пс; [бс]; [тип]) ПРПЛТ(ставка; 3; кпер; пс; [бс]; [тип]) ПРПЛТ(ставка; 4; кпер; пс; [бс]; [тип]) ПРПЛТ(ставка; 5; кпер; пс; [бс]; [тип])

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

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

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

Предлагаем ознакомиться:  Расписка возврата денег хотят представить как займ

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

Как это работает на практике, рассмотрим на конкретных примерах.

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

Гражданским Кодексом РФ предусмотрена возможность граждан забрать вклад в любое время вместе с начисленными процентами.

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

  1. Простой. Проценты не причисляются к общей сумме вклада, а переводятся на отдельный счет. Вознаграждение может начисляться ежемесячно, раз в квартал или полгода, а также 1 раз в год или только на момент окончания срока вклада. Расчет годовых процентов в этом случае очень простой и его можно произвести самостоятельно по формуле: S = (P x I x t / 365) / 100%. В этой формуле: Р – сумма депозита в денежных единицах, например в рублях, I – годовая процентная ставка по депозиту, t – срок, на который делался вклад, 365 – количество суток в году.

    Пример расчета: депозитный договор на 600 тыс. рублей открывается сроком на 12 месяцев. Годовая ставка по вкладу составляет 10%. Используя формулу, вычисляем: (600 000 х 10 х 365/365) / 100% = 60 000 руб. Т.е. в данном случае, прибыль равна 60 тыс. рублей, при размещении вышеуказанной суммы на 1 год.

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

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

Аннуитет — это одинаковый по сумме ежемесячный платёж. То есть при аннуитетном платеже вы каждый месяц платите одинаковую сумму (кредит проценты по нему) независимо от оставшейся суммы задолженности.

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

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

Правильно внести исходные данные.

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

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

A = K · S

где А — ежемесячный аннуитетный платёж,К — коэффициент аннуитета,S — сумма кредита.

где i — месячная процентная ставка по кредиту (= годовая ставка / 12),n — количество периодов, в течение которых выплачивается кредит.

Поскольку периодичность платежей по кредиту — ежемесячно, то ставка по кредиту (i) берётся месячная. Если процентная ставка 12% годовых, то месячная ставка:i = 12% / 12 мес = 1%.

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

Аннуитетный платж что это такое формула как рассчитать пример расчета в excel

Что выгоднее, платить ипотеку 10 лет или 30? Как на сумму ежемесячных платежей и общей переплаты влияет повышение процентной ставки на 1%? Когда необходимо выполнить такие расчеты, первым делом приходит на ум услуга банковского калькулятора.

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

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

Если вы хотите узнать, как решить именно Вашу проблему — обращайтесь в форму онлайн-консультанта справа или звоните по телефону 7 (499) 938-51-36. Это быстро и бесплатно!

Скрыть содержание

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

Предлагаем ознакомиться:  Ошибки при монтаже пластиковых окон и их последствия

Аннуитетный платж что это такое формула как рассчитать пример расчета в 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 платежей. Поэтому гораздо проще воспользоваться программами-помощниками.

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

Если у вас возникают проблемы с ручным расчётом аннуитетных платежей — можно вычислить в Excel. В Экселе есть специальная функция ПЛТ. Просто создаёте новую таблицу и в любой ячейке вводите строку.

18% годовых / 12 месяцев / 100 = 0,015

Вкладка досрочного погашения кредита

В любом случае у нас сумма ежемесячных платежей получится 1084,57 рублей.

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

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

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

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

  1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
  2. Расчет ипотеки по формуле: ежемесячные платежи, проценты, полная стоимость кредита, примеры в excel, господдержка, график погашения банку

  3. Составим график погашения кредита. Пока пустой.
  4. Расчет ипотеки по формуле: ежемесячные платежи, проценты, полная стоимость кредита, примеры в excel, господдержка, график погашения банку

  5. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).
Предлагаем ознакомиться:  Как рассчитать процент по кредиту Расчет ПСК страховые платежи и другое

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

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

  • сумма основного долга распределена по периодам выплат равными долями;
  • проценты по кредиту начисляются на остаток.
  1. Формула расчета дифференцированного платежа:
  2. ДП = ОСЗ / (ПП ОСЗ * ПС)
  3. где:
  • ДП – ежемесячный платеж по кредиту;
  • ОСЗ – остаток займа;
  • ПП – число оставшихся до конца срока погашения периодов;
  • ПС – процентная ставка за месяц (годовую ставку делим на 12).

Расчёт аннуитетного кредита

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

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

  • Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).
  • Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9
  • Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8 G8.

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

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

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

  1. Сумма основного долга = аннуитетный платеж – проценты.
  2. Сумма процентов = остаток долга * месячную процентную ставку.
  3. Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.
  4. Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:
  • взяли кредит 500 000 руб.;
  • вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
  • переплата составила 184 881, 67 руб.;
  • процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
  • Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

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

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

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

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

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

  • Далее находим ЧБП: 365 / 28 = 13.
  • Теперь можно найти процентную ставку базового периода:
  • У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

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

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

Скрыть содержание

Размер займа указывается со знаком «минус», т.к. эти деньги кредитная организация «дает», «теряет». Для записи значения процентной ставки необходимо использовать процентный формат. Если записывать в числовом, то применяется десятичное число (0,08).

Расчет ипотеки по формуле: ежемесячные платежи, проценты, полная стоимость кредита, примеры в excel, господдержка, график погашения банку

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

Обратите внимание! В поле «Ставка» значение годовых процентов поделено на 12: платежи по кредиту выполняются ежемесячно.

Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб.

Для этого в качестве аргумента «Тип» нужно указать значение 1.

Детализируем расчет, используя функции ОСПЛТ и ПРПЛТ. С помощью первой покажем тело кредита, посредством второй – проценты.

В поле «Период» указываем номер периода, для которого рассчитывается основной долг.

Расчет ипотеки по формуле: ежемесячные платежи, проценты, полная стоимость кредита, примеры в excel, господдержка, график погашения банку

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

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

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


Об авторе: admin4ik

Ваш комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Adblock detector