Главная » Загранпаспорт

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

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

Прежде всего, нужно сказать, что существует два вида кредитных платежей:

  • Дифференцированные;
  • Аннуитетные.

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

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

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

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

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

ПЛТ(ставка;кпер;пс;бс;тип)

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

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

«Кпер» обозначает общее количество периодов выплат по кредиту. То есть, если заём берется на один год с ежемесячной оплатой, то число периодов считается 12 , если на два года, то число периодов – 24 . Если кредит берется на два года с ежеквартальной оплатой, то число периодов равно 8 .

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

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

Аргумент «Тип» определяет время расчета: в конце или в начале периода. В первом случае он принимает значение «0» , а во втором – «1» . Большинство банковских учреждений используют именно вариант с оплатой в конце периода. Этот аргумент тоже является необязательным, и если его опустить считается, что он равен нулю.

Теперь настало время перейти к конкретному примеру расчета ежемесячного взноса при помощи функции ПЛТ. Для расчета используем таблицу с исходными данными, где указана процентная ставка по кредиту (12% ), величина займа (500000 рублей ) и срок кредита (24 месяца ). При этом оплата производится ежемесячно в конце каждого периода.

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

  2. Производится запуск окошка Мастера функций . В категории «Финансовые» выделяем наименование «ПЛТ» и жмем на кнопку «OK» .

  3. После этого открывается окно аргументов оператора ПЛТ .

    В поле «Ставка» следует вписать величину процентов за период. Это можно сделать вручную, просто поставив процент, но у нас он указан в отдельной ячейке на листе, поэтому дадим на неё ссылку. Устанавливаем курсор в поле, а затем кликаем по соответствующей ячейке. Но, как мы помним, у нас в таблице задана годовая процентная ставка, а период оплаты равен месяцу. Поэтому делим годовую ставку, а вернее ссылку на ячейку, в которой она содержится, на число 12 , соответствующее количеству месяцев в году. Деление выполняем прямо в поле окна аргументов.

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

    В поле «Пс» указывается первоначальная величина займа. Она равна 500000 рублей . Как и в предыдущих случаях, указываем ссылку на элемент листа, в котором содержится данный показатель.

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

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

    После того, как все данные введены, жмем на кнопку «OK» .

  4. После этого в ячейку, которую мы выделили в первом пункте данного руководства, выводится результат вычисления. Как видим, величина ежемесячного общего платежа по займу составляет 23536,74 рубля . Пусть вас не смущает знак «-» перед данной суммой. Так Эксель указывает на то, что это расход денежных средств, то есть, убыток.

  5. Для того, чтобы рассчитать общую сумму оплаты за весь срок кредитования с учетом погашения тела займа и ежемесячных процентов, достаточно перемножить величину ежемесячного платежа (23536,74 рубля ) на количество месяцев (24 месяца ). Как видим, общая сумма платежей за весь срок кредитования в нашем случае составила 564881,67 рубля .

  6. Теперь можно подсчитать сумму переплаты по кредиту. Для этого нужно отнять от общей величины выплат по кредиту, включая проценты и тело займа, начальную сумму, взятую в долг. Но мы помним, что первое из этих значений уже со знаком «-» . Поэтому в конкретно нашем случае получается, что их нужно сложить. Как видим, общая сумма переплаты по кредиту за весь срок составила 64881,67 рубля .

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

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

  1. Для определения величины оплаты по телу займа используем функцию ОСПЛТ , которая как раз предназначена для этих целей. Устанавливаем курсор в ячейку, которая находится в строке «1» и в столбце «Выплата по телу кредита» . Жмем на кнопку «Вставить функцию» .

  2. Переходим в Мастер функций . В категории «Финансовые» отмечаем наименование «ОСПЛТ» и жмем кнопку «OK» .

  3. Запускается окно аргументов оператора ОСПЛТ. Он имеет следующий синтаксис:

    ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)

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

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

  4. Но у нас остается ещё один новый аргумент, которого не было у функции ПЛТ . Этот аргумент «Период» . В соответствующее поле устанавливаем ссылку на первую ячейку столбца «Период» . Данный элемент листа содержит в себе число «1» , которое обозначает номер первого месяца кредитования. Но в отличие от предыдущих полей, в указанном поле мы оставляем ссылку относительной, а не делаем из неё абсолютную.

    После того, как все данные, о которых мы говорили выше, введены, жмем на кнопку «OK» .

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

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

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

  8. Теперь нам нужно сделать месячный расчет оплаты по процентам. Для этих целей будем использовать оператор ПРПЛТ . Выделяем первую пустую ячейку в столбце «Выплата по процентам» . Жмем на кнопку «Вставить функцию» .

  9. В запустившемся окне Мастера функций в категории «Финансовые» производим выделение наименования ПРПЛТ . Выполняем щелчок по кнопке «OK» .

  10. Происходит запуск окна аргументов функции ПРПЛТ . Её синтаксис выглядит следующим образом:

    ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)

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

  11. Затем результат расчета суммы оплаты по процентам за кредит за первый месяц выводится в соответствующую ячейку.

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

  13. Теперь нам предстоит рассчитать общий ежемесячный платеж. Для этого вычисления не следует прибегать к какому-либо оператору, так как можно воспользоваться простой арифметической формулой. Складываем содержимое ячеек первого месяца столбцов «Выплата по телу кредита» и «Выплата по процентам» . Для этого устанавливаем знак «=» в первую пустую ячейку столбца «Общая ежемесячная выплата» . Затем кликаем по двум вышеуказанным элементам, установив между ними знак «+» . Жмем на клавишу Enter .

  14. Далее с помощью маркера заполнения, как и в предыдущих случаях, заполняем колонку данными. Как видим, на протяжении всего действия договора сумма общего ежемесячного платежа, включающего платеж по телу займа и оплату процентов, составит 23536,74 рубля . Собственно этот показатель мы уже рассчитывали ранее при помощи ПЛТ . Но в данном случае это представлено более наглядно, именно как сумма оплаты по телу займа и процентам.

  15. Теперь нужно добавить данные в столбец, где будет ежемесячно отображаться остаток суммы по кредиту, который ещё требуется заплатить. В первой ячейке столбца «Остаток к выплате» расчет будет самый простой. Нам нужно отнять от первоначальной величины займа, которая указана в таблице с первичными данными, платеж по телу кредита за первый месяц в расчетной таблице. Но, учитывая тот факт, что одно из чисел у нас уже идет со знаком «-» , то их следует не отнять, а сложить. Делаем это и жмем на кнопку Enter .

  16. А вот вычисление остатка к выплате после второго и последующих месяцев будет несколько сложнее. Для этого нам нужно отнять от тела кредита на начало кредитования общую сумму платежей по телу займа за предыдущий период. Устанавливаем знак «=» во второй ячейке столбца «Остаток к выплате» . Далее указываем ссылку на ячейку, в которой содержится первоначальная сумма кредита. Делаем её абсолютной, выделив и нажав на клавишу F4 . Затем ставим знак «+» , так как второе значение у нас и так будет отрицательным. После этого кликаем по кнопке «Вставить функцию» .

  17. Запускается Мастер функций , в котором нужно переместиться в категорию «Математические» . Там выделяем надпись «СУММ» и жмем на кнопку «OK» .

  18. Запускается окно аргументов функции СУММ . Указанный оператор служит для того, чтобы суммировать данные в ячейках, что нам и нужно выполнить в столбце «Выплата по телу кредита» . Он имеет следующий синтаксис:

    СУММ(число1;число2;…)

    В качестве аргументов выступают ссылки на ячейки, в которых содержатся числа. Мы устанавливаем курсор в поле «Число1» . Затем зажимаем левую кнопку мыши и выделяем на листе первые две ячейки столбца «Выплата по телу кредита» . В поле, как видим, отобразилась ссылка на диапазон. Она состоит из двух частей, разделенных двоеточием: ссылки на первую ячейку диапазона и на последнюю. Для того, чтобы в будущем иметь возможность скопировать указанную формулу посредством маркера заполнения, делаем первую часть ссылки на диапазон абсолютной. Выделяем её и жмем на функциональную клавишу F4 . Вторую часть ссылки так и оставляем относительной. Теперь при использовании маркера заполнения первая ячейка диапазона будет закреплена, а последняя будет растягиваться по мере продвижения вниз. Это нам и нужно для выполнения поставленных целей. Далее жмем на кнопку «OK» .

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

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

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

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

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

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

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

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

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

Сумма кредита составляет 60 000 000 бел.руб., срок кредита - 5 лет (шестьдесят месяцев). Процентная ставка равна 12% годовых.

Данный простой пример показывает отличия между двумя рассматриваемыми схемами. Очевидно, кредитополучателю в большей степени подойдет именно вариант с аннуитетными платежами, поскольку в нем нагрузка на плательщика постоянная на протяжении всего периода погашения кредита. Такой постоянный размер своего платежа (часто банки округляют платежи, например, 1 330 000 бел. руб.) проще и запомнить, и планировать к погашению.

Классическое определение: аннуитет - это поток одинаковых по сумме платежей, которые осуществляются с равной периодичностью.

Традиционная формула для расчета аннуитетного платежа, известная из курса финансовой математики: R = (A*i) / (1*(1+i)^n),

где R - размер аннуитетного платежа;

А - сумма кредита;

i - размер процентной ставки в расчете на один месяц;

n - срок кредитования в месяцах.

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

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

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

Округление аннуитетных платежей. В соответствии с постановлением Правления Национального банка Республики Беларусь от 30.06.2009 № 125 «Об утверждении Инструкции по признанию в бухгалтерском учете доходов и расходов в Национальном банке Республики Беларусь и банках Республики Беларусь» (подпункт 59.2 пункта 59) проценты по кредиту в белорусских рублях округляются до 10 рублей, проценты в иностранной валюте - до двух знаков после запятой.

Таким образом, проценты по кредиту должны быть округлены до 10 бел. руб. и 1000 бел. руб., тогда в примере ежемесячный взнос составит 1 335 000 бел. руб.).

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

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

В результате схема приобретает следующий вид.

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

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

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

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

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

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

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

Рассмотрим еще один пример со следующими условиями.

Сумма кредита равна 60 000 000 бел. руб. при процентной ставке 12% годовых (используется схема расчета процентов с учетом точного количества дней в месяце). Срок кредита составляет 12 месяцев.

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

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

Алгоритм состоит из следующих шагов.

  1. Определение длительности в днях каждого из месяцев погашения с учетом даты выдачи и даты полного погашения кредита. В нашем примере эти данные уже рассчитаны и представлены в таблице (столбец «Число дней пользования кредитом за указанный месяц»).
  2. Для каждого из месяцев, начиная с первого месяца погашения основного долга, рассчитаем, используя длительность данного месяца погашения, коэффициент по формуле: 1 / (1 + Дневная процентная ставка х Число дней пользования за данный месяц).В результате мы рассчитали коэффициент приведения стоимости, взятой на конец месяца, к началу данного месяца.
  3. Для каждого из месяцев, начиная с первого месяца погашения основного долга, производим расчет «множителя при аннуитетном платеже» как произведение всех коэффициентов приведения (рассчитываются на шаге 2), начиная от коэффициента приведения для первого месяца погашения основного долга и заканчивая коэффициентом для данного месяца. При этом для первого месяца погашения основного долга множитель при аннуитетном платеже равен коэффициенту приведения, вычисленному на шаге 2.Вычислив данные множители, получаем возможность привести стоимость, взятую на конец любого из месяцев погашения (а это и есть сумма нашего аннуитетного платежа), к началу первого месяца, за который производится погашение основного долга по кредиту. Поскольку в расчете графика погашения используется установление сезонных платежей, необходимо определить множители при сезонных платежах (равны значению множителя при членах ренты соответствующего месяца) и вычислить приведенную (дисконтированную) стоимость каждого сезонного взноса как произведение суммы сезонного платежа и множителя при соответствующем сезонном платеже. ополнительно вычислена сумма по столбцам «Множитель при аннуитетном платеже», «Множитель при сезонном платеже» и «Дисконтированный сезонный платеж». Эти данные понадобятся при расчете аннуитетного платежа.
    • Вычислим, имея все данные, аннуитетный платеж, определяемый по формуле:Сумма кредита – сумма (Дисконтированный сезонный платеж) / сумма (Множитель при аннуитетном платеже) – сумма (Множитель при сезонном платеже)

      где «сумма) - результат суммирования соответствующего столбца.

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

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

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

Среди них:

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

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

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

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

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

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

В качестве условий по нему используем следующие.

Стоимость объекта лизинга - 72 000 000 бел. руб., в том числе НДС - 12 000 000 бел. руб. Процентная ставка равна 12% годовых (применяется схема расчета процентов с учетом точного количества дней в месяце). Срок погашения лизинга составляет двенадцать месяцев. Отсрочка оплаты контрактной стоимости - три первых месяца.

Передача объекта в лизинг осуществляется 15 декабря 2011 г. Сезонные платежи за апрель и май составляют по 2 000 000 бел. руб., включая НДС; за август - октябрь - по 10 000 000 бел. руб., включая НДС. Проценты рассчитываются исходя из остат-ка на начало операционного дня (за день передачи объекта в лизинг проценты рассчитываться не будут).

Первоначальный платеж составляет 20% от стоимости объекта лизинга - 14 400 000 бел.руб., в том числе НДС - 2 400 000 бел. руб. Выкупной платеж равен 1% от стоимости объекта лизинга - 720 000 бел. руб., в том числе НДС - 120 000 бел. руб.

С учетом того, что календарь погашения в этом примере аналогичен предыдущему (даты начала и окончания погашения, длительность отсрочки погашения основного долга, месяцы с сезонными платежами), смело можно воспользоваться частью данных из таблицы (столбцы 1-7).

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

Размер дисконтированного выкупного платежа рассчитывается исходя из размера выкупного платежа без НДС и множителя при аннуитетном платеже для последнего платежа по графику: 0,91001442 х 600 000 = 546 009 бел.руб.

Таким образом, размер аннуитетного платежа равен частному от деления, где:

Делимое = Общая стоимость объекта лизинга без НДС-Первоначальный взнос без НДС-Сумма дисконтированных сезонных платежей без НДС-Дис-контированный выкупной платеж без НДС (60 000 000-12 000 000-26 563 630-546 009 = 20 890 361 бел. руб.), а делитель = Сумма множителей при аннуитетном платеже-Сумма множителей при сезонном платеже (9,47465464-4,74821863 = 4,726436).

Результат такого деления - 4 419 897, что и является искомым аннуитетным платежом без НДС. Соответственно, аннуитетный платеж с НДС составляет 5 303 876 бел.руб.

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

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

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

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

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

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

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

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

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

Расчет аннуитетных платежей по кредиту в 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. Достаточно выставить для ячейки с результатом процентный формат.

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

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

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

В этой статье:

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

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

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

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

Формула аннуитетного платежа

В соответствии с формулой аннуитетного платежа размер периодических (ежемесячных) выплат будет составлять:

A = K · S

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

Коэффициент аннуитета рассчитывается по следующей формуле:

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

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

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

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

Приведём пример расчета аннуитетного платежа.
Допустим, вы взяли в банке кредит на сумму 30 000 рублей под 18% годовых сроком на 3 года.

Исходные данные:
S = 30 000 рублей
i = 1,5% (18% / 12 мес) = 0,015
n = 36 (3 года х 12 мес)

Подставляем эти значения в формулу и определяем коэффициент аннуитета:

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

ПЛТ(0,015; 36; -30000)

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

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

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

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

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

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

Mp= Sz*(Mps/(1,0-(1,0+ Mps) Sk), где

Mp -величина ежемесячного взноса;

Sz - «тело» кредита;

Sk - срок (в месяцах);

Mps - месячная ставка.

Для расчета годовой процентной ставки применяется следующая формула:

Mps=Годовая процентная ставка /100/12.

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

Mps = 22 / 100 / 12=0,0183.

Mp = 40 000 х (0,0183 / (1 – (1 + 0,0183)-24))=. 2075, 12 руб.

Самостоятельно произведи расчёт достаточно сложно. Упростить задачу можно, воспользовавшись функционалом Microsoft Excel. Для этого необходимо использовать функцию «ПЛТ». Алгоритм действий следующий:

  • Открывается программу:
  • Создается таблица;
  • В ячейке по заданным условиям прописывается формула «=ПЛТ(22%/12; 24; -40 000)», где 22% - проценты, 24 – срок займа. 40 000 – размер кредита;
  • Нажимается кнопка Enter.

Важно! Большое значение имеет простановка знака «=» и точек с запятой. При несоблюдении этих условий вычисления могут быть произведены ошибочно.

Плюсы и минусы аннуитентных платежей

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

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

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