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

Аннуитетный платеж формула excel

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

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

Наиболее распространенный вариант – внесение аннуитетных платежей, то есть выплата кредита равными суммами.

Как рассчитать размер аннуитетного платежа?

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

А = К х S

A – размер платежа

K – коэффициент аннуитета

S – сумма полученного кредита

Есть один неизвестный элемент формулы – коэффициент аннуитета. Его необходимо рассчитать отдельно по соответствующей формуле.

Здесь i – это месячная ставка процентов за пользование кредитом, которая рассчитывается путем деления годовой ставки на 12 месяцев

n количество месяцев, на протяжении которого кредит необходимо погасить.

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

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

Чтобы не утруждать себя расчетами вручную, попробуйте сделать это при помощи таблицы Excel. Там есть специальная функция под названием ПЛТ. Для расчетов следует создать новую таблицу и ввести строку в любой ячейке. Если вам выдали кредит в сумме 30000 руб., под 18% годовых на 36 месяцев, необходимо ввести в ячейку вот такое выражение.

= ПЛТ(18%/12; 36; -30000)

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

Можно внести запись и в таком виде:

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

Получается 1084,57 рублей.

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

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

Справка: аннуитетные и дифференцированные платежи

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

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

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

Источник: biznes-kredit.info

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

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

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

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

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

Задача1

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

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

  1. Заемщик ежемесячно должен делать платеж банку. Этот платеж включает: сумму в счет погашения части ссуды и сумму для оплаты начисленных за прошедший период процентов на остаток ссуды ;
  2. Сумма ежемесячного платежа (аннуитета) постоянна и не меняется на протяжении всего срока, так же как и процентная ставка. Также не изменяется порядок платежей – 1 раз в месяц;
  3. Сумма для оплаты начисленных за прошедший период процентов уменьшается каждый период, т.к. проценты начисляются только на непогашенную часть ссуды;
  4. Как следствие п.3 и п.1, сумма, уплачиваемая в счет погашения основной суммы ссуды, увеличивается от месяца к месяцу.
  5. Заемщик должен сделать 60 равновеликих платежей (12 мес. в году*5 лет), т.е. всего 60 периодов (Кпер);
  6. Проценты начисляются в конце каждого периода (если не сказано обратное, то подразумевается именно это), т.е. аргумент Тип=0. Платеж должен производиться также в конце каждого периода;
  7. Процент за пользование заемными средствами в месяц (за период) составляет 10%/12 (ставка);
  8. В конце срока задолженность должна быть равна 0 (БС=0).

Расчет суммы выплаты по ссуде за один период, произведем сначала с помощью финансовой функции MS EXCEL ПЛТ() .

Примечание . Обзор всех функций аннуитета в статье найдете здесь .

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

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

Первый аргумент – Ставка. Это процентная ставка именно за период, т.е. в нашем случае за месяц. Ставка =10%/12 (в году 12 месяцев). Кпер – общее число периодов платежей по аннуитету, т.е. 60 (12 мес. в году*5 лет) Пс – Приведенная стоимость всех денежных потоков аннуитета. В нашем случае, это сумма ссуды, т.е. 100 000.

Бс – Будущая стоимость всех денежных потоков аннуитета в конце срока (по истечении числа периодов Кпер). В нашем случае Бс = 0, т.к. ссуда в конце срока должна быть полностью погашена. Если этот параметр опущен, то он считается =0. Тип – число 0 или 1, обозначающее, когда должна производиться выплата. 0 – в конце периода, 1 – в начале.

Если этот параметр опущен, то он считается =0 (наш случай).

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

В случае начисления процентов в начале периода, в первом месяце % не начисляется, т.к.

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

Решение1 Итак, ежемесячный платеж может быть вычислен по формуле =ПЛТ(10%/12; 5*12; 100 000; 0; 0) , результат -2 107,14р. Знак минус показывает, что мы имеем разнонаправленные денежные потоки: +100000 – это деньги, которые банкдал нам, -2107,14 – это деньги, которые мы возвращаем банку .

Альтернативная формула для расчета платежа (общий случай): =-(Пс*ставка*(1+ ставка) Кпер /((1+ ставка) Кпер -1)+ ставка /((1+ ставка) Кпер -1)* Бс)*ЕСЛИ(Тип;1/(ставка +1);1)

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

Вышеуказанную формулу часто называют формулой аннуитета (аннуитетного платежа) и записывают в виде А=К*S, где А – это аннуитетный платеж (т.е. ПЛТ), К – это коэффициент аннуитета, а S – это сумма кредита (т.е. ПС).

K=-i/(1-(1+i)(-n)) или K=(-i*(1+i)n)/(((1+i)n)-1), где i=ставка за период (т.е. Ставка), n – количество периодов (т.е. Кпер).

Напоминаем, что выражение для K справедливо только при БС=0 (полное погашение кредита за число периодов Кпер) и Тип=0 (начисление процентов в конце периода).

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

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

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

Расчёт в MS EXCEL погашение основной суммы долга ). Т.к.

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

Источник: https://excelprost.ru/formuly/annuitetnyj-platezh-formula-excel.html

Кредитные продукты. Методы и алгоритмы расчета

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

Планирование финансов в бизнес-планах это прежде всего – подбор финансирования для устранения дефицита денежных средств.

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

В Budget-Plan Express можно легко запланировать кредитные или арендные продукты (лизинговые платежи) любой сложности.

Для простоты работы, для пользователей определены три типа кредитных продуктов, которые выбираются из списка: «Стандартный», «Аннуитетный», «Потребительский».

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

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

    K(t) = Z (1 + pt),

    Где:
    K(t) – выплаты за период t.
    Z – сумма кредита.
    t – количество лет или коэффициент количества лет (t = количество дней / 360 или = количество дней / 365.

    Если шаг расчета месяц, то t = количество месяцев / 12).
    p – процентная ставка.

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

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

Формула для сложных процентов находится из формулы для простых процентов. Например, расчет итоговых выплат, с учетом нарастающих процентов, для 1-го и 2-го года будет следующим:

    K1 = Z (1+ p)
    K2 = Z(1+ p)(1+ p) = Z(1+ p)2

    Где:
    K(1), K(2) – выплаты за 1-й и 2-й годы кредита.

Соответственно, расчет кредитных платежей по схеме сложных процентов, для n лет, исчисляется по формуле:

    Kn Z(1+ p)(1+ p)…(1+ p) = Z( 1+ p )n

    Где:
    n – количество лет или коэффициент количества лет (n = количество дней / 360 или n = количество месяцев / 12).

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

Пример. Z = 1 000 000, p = 0.12 (12%) и n = 1.5 (18 месяцев). Рассчитать варианты кредита – по простым и сложным процентам.

    По схеме простых процентов:

    K(1.5) = Z (1 + pt) = 1 000 000 (1 + 0.12 × 1.5) = 1 180 000

    По схеме сложных процентов:

    K(1.5) = Z( 1+ p )n = 1 000 000 (1 + 0.12)1.5 ≈ 1 185 287

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

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

потребительский кредит, формулы [4], [5]).

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

  1. «Стандартный» кредитный продукт предполагает расчет дифференцированных платежей по формулам простых и сложных процентов.
  2. «Аннуитетный» кредитный продукт – это равные по сумме (как правило, ежемесячные) платежи, которые включают в себя сумму начисленных процентов за кредит и сумму основного долга. Используются две формулы для расчета аннуитетов – с применением простых и сложных процентов.
  3. «Потребительский» кредитный продукт, как и «Стандартный» рассчитывается по стандартным формулам простых и сложных процентов. Однако, кредит выплачивается равными платежами – аннуитетами, которые рассчитываются простым делением суммы всех платежей (долга и процентов) на количество выплат.

  1. Стандартный
  2. «Стандартный» кредитный продукт предполагает расчет дифференцированных платежей по формулам простых и сложных процентов. Это даже не формула, а скорее – алгоритм расчета дифференцированных платежей.

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

    Особенность алгоритма расчета в том, что процентные деньги начисляются в зависимости от остатка долга.

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

    Но он (алгоритм расчета) имеет и недостатки: так как каждый месяц мы имеем дело с разными платежами (с уменьшением долга, суммы выплат уменьшаются), платежи нужно контролировать и пересчитывать каждым месяц. Этот недостаток устранят следующая формула, которая предлагает скорректировать выплаты долга таким образом, чтобы получить аннуитеты – равные по размеру платежи.
    Аннуитет, в широком смысле – денежный поток с равными интервалами и равными поступлениями денежных средств. Аннуитетный платеж – это равный по сумме (например, ежемесячный или ежеквартальный) платеж по кредиту, который включает в себя сумму начисленных процентов за кредит и сумму основного долга. В «Budget–Plan Express» используются две формулы для расчета аннуитетов – с применением простых и сложных процентов.

    1. Аннуитет с применением формулы простых процентов.

    Формула для расчета аннуитетов выводится из формулы ренты для простых процентов. Пусть А – это аннуитет (равный платеж), в разных n периодах:

      Z(1+np) = A [1+(n-1)p] + A [1+(n-2)p] + … A

      отсюда находим аннуитет [*]:

      [1]

      Где:
      A – аннуитетный платеж с применением простых процентов,
      Z – сумма кредита,
      n – коэффициент лет,
      p – коэффициент процентной ставки.
      *[для преобразования формулы [1] используется формула суммы арифметической прогрессии]

    2. Аннуитет с применением формулы сложных процентов.

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

      Z(1+p)(n) = A (1+p)(n-1) + A (1+p)(n-2) + … A

      отсюда находим аннуитет [*]:

      [2]

      Где:
      A – аннуитетный платеж с применением сложных процентов,
      Z – сумма кредита,
      n – коэффициент лет,
      p – коэффициент процентной ставки.
      *[для преобразования формулы [2] используется формула суммы конечной геометрической прогрессии]

    Другой вариант (эквивалентное преобразование [3]) этой же формулы можно получить, если разделить числитель и знаменатель на (1+p)n:

      [3]

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

    По сложившейся практике банки, как правило, считают аннуитетный платеж по этой формуле [2],[3].

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

    Однако, кредит выплачивается равными платежами – аннуитетами, которые рассчитываются простым делением суммы всех платежей (долга и процентов) на количество выплат:

      [4]
      [5]

      Где:

      A – аннуитетный платеж с применением простых [4] и сложных [5] процентов;

      m – количество аннуитетов (платежей).

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

Эта формула для расчета аннуитетов [2] и [3] называется – формула современной (наращенной) стоимости постоянной финансовой ренты. Также эта формула используется для расчета дисконтированных денежных потоков (см.

раздел «Дисконтирование. Present Value»).

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

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

Поэтому, когда объявляется ставка, например, 12.5%, нужно уточнить – по какой формуле считается кредит. Реальная ставка может быть на порядок больше объявленной, если кредит берется более чем на 1 год.

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

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

Максимальный срок расчета кредитов – 10 лет (120 месяцев).
Заметим, так как шкала времени в «Budget-Plan Express» 3 года (36 месяцев), все расчеты, после 36-го месяца, относятся к будущему периоду.

В “общих настройках” указываются общие параметры расчета:

  1. Шаг расчета (в месяцах, днях);
  2. Метод учета годового цикла (ACT/ACT, ACT/360, 360/360);
  3. Предельный процент;
  4. Расчетный процент (простой, сложный);
  5. Расчетная валюта.

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

  1. Периодичность платежей;
  2. Отсрочка по долгу;
  3. Отсрочка по процентам;
  4. Учет прогрессий;
  5. Учет прочих разовых платежей;
  6. Учет прочих периодических платежей;
  7. Коррекция ставок.

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

Все платежи отображаются в «Таблица платежей» в той валюте, к которой они относятся. При этом на момент выплат, в “Таблица платежей” также рассчитываются расходы (доходы), связанные с курсовыми разницами – в системной (основной) валюте.

В тоже время, все расчеты в финансовом плане представлены в системной (основной) валюте.

В отчете о прибылях и убытка курсовые разницы отражены в строке (16): “Прочие внереализационные расходы (доходы)” и не включены в “Расходы по обслуживанию долга”.

При расчете кредита, например, в долларах, в «финансовом плане» они будут пересчитаны в рубли – по прогнозному курсу.

Прогрессивные выплаты используются только для «стандартного» кредитного продукта, когда процентные деньги погашаются в зависимости от остатка долга.

    1. Платежи, изменяющиеся в арифметической прогрессии:

      Z = [2B1 + d (n-1)] n / 2,

      отсюда первая выплата долга:

      B1 = Z / n – d(n-1) / 2

      где:
      Z – сумма долга,
      B1 – первая выплата долга,
      d – разность арифметической прогрессии (сумма).

    2. Платежи, изменяющиеся в геометрической прогрессии:

      Z = B1 [qn – 1] / [q – 1],

      отсюда первая выплата долга:

      B1 = Z [q – 1] / [qn – 1]

      где:
      Z – сумма долга,
      B1 – первая выплата долга,
      q – знаменатель геометрической прогрессии (процент).

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

    t = s / g,

    где числа s и g определяются в зависимости от способа расчета:

    1. “Английский” способ или ACT/ACT. Число s равно точному числу дней ссуды минус один день (деньвыдачи и день погашения ссуды считаются одним днем), число g равно точному числу дней в году (365 или 366). Этот способназывается английским и часто упоминается, как способ 365/365или ACT/ACT.

    2. “Французский” способ или ACT/360. Число s равно точному числу дней ссуды минус один день (деньвыдачи и день погашения ссуды считаются одним днем), числоg равно 360 (в году 12 месяцев по 30 дней). Этот способназывается французским и часто упоминается, как способ365/360 или ACT/360.

    3. “Немецкий” способ или 360/360. Число g равно 360 (в году 12 месяцев по 30 дней), число sсостоит из полного числа месяцев (по 30 дней) плюс точноечисло дней в оставшемся неполном месяце минус один день(день выдачи и день погашения ссуды считаются одним днем).Этот способ называется немецким и часто упоминается, какспособ 360/360.

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

В «Budget-Plan Express» алгоритм определения точного количества дней “зашит” в расчет. Чтобы использовать этот алгоритм, нужно указать шаг расчета в днях (вкладка “настройки”).

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

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

Например, предельная ставка в рублях = ставка рефинансирования * 1,8, предельная ставка в валюте = ставка рефинансирования * 0,8.

Справка о программе “Budget-Plan Express”, www.strategic-line.ru | справки

Источник: https://www.strategic-line.ru/index.files/web-help-114-financial-planning-loan-products-methods.htm

Поделиться:
Нет комментариев

    Добавить комментарий

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