Горячая линия бесплатной юридической помощи:
Москва и область:
Москва И МО:
+7(499)938-71-58 (бесплатно)
Регионы (вся Россия, добавочный обязательно):
8 (800) 350-84-13 (доб. 215, бесплатно)

Определить сумму переплаты и эффективную ставку для каждоговарианта

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

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

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

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

https://www.youtube.com/watch{q}v=ytcopyrightru

А = К * S

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

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

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

Решение задач на сложные проценты, дисконтирование, аннуитеты и др. [14.06.15]

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

  1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
  2. Условия кредитования.

  3. Составим график погашения кредита. Пока пустой.
  4. График погашения.

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

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

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

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

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

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

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

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

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

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9<=$B$4;$B$2/$B$4;0).

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8 G8.

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

Предлагаем ознакомиться:  Алименты в твердой денежной сумме в 2019 году

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

Примеры финансовых расчетов по функции СТАВКА в Excel

Пример 1. В МФО был взят кредит сроком на 16 дней, сумма которого составляет 1000 долларов. Сумма возврата составляет 1250 долларов. Определить годовую процентную ставку по указанным условиям займа.

=СТАВКА(B4;0;B5;B6;0;0,1)*B3/B2

Определить сумму переплаты и эффективную ставку для каждоговарианта

Описание аргументов:

  • B4 – число периодов выплат (в данном случае – 1);
  • 0 – размер фиксированной выплаты (поскольку в данном примере только один период выплат, указано значение 0);
  • B5 – тело кредита;
  • B6 – сумма на момент погашения долга;
  • 0 – характеризует тип выплат, при котором выплата производится в конце периода;
  • 0,1 – предполагаемое значение процентной ставки (любое число из диапазона от 0 до 1);
  • B3/2 – коэффициент для пересчета полученного значения ставки к годовой процентной ставке.

Пи данных условиях микрокредитования сроком займа на 16 дней процентная ставка составляет 570,31% годовых! Несмотря на это, услуги по микрокредитованию сегодня продолжают набирать популярность.

Задача2 – Накопление суммы вклада

Определим под какую годовую ставку мы можем взять 100 000 руб., выплачивая ежемесячно 3000 руб. в течение 5 лет.

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

В условии задачи содержится следующая информация:

  • Заемщик должен сделать 60 равновеликих платежей (12 мес. в году*5 лет), т.е. всего 60 периодов (Кпер);
  • Проценты начисляются в конце каждого периода (если не сказано обратное, то подразумевается именно это), т.е. аргумент Тип=0;
  • В конце срока задолженность должна быть равна 0 (БС=0).

В результате формула для вычисления годовой ставки будет выглядеть так =12*СТАВКА(12*5;-3000;100000;0;0) или =12*СТАВКА(12*5;-3000;100000)Знак минус у регулярного платежа показывает, что мы имеем разнонаправленные денежные потоки: 100000 – это деньги, которые банкдал нам, -3000 – это деньги, которые мы возвращаем банку.Результат вычисления = 26,10%

https://www.youtube.com/watch{q}v=ytpolicyandsafetyru

Формула может вернуть отрицательные значения ставки. Это происходит, когда сумма всех регулярных платежей недостаточна для погашения кредита даже при 0 ставке. Но, в нашем случае все в порядке: 60*(3000)=180000>100000. Отрицательная ставка означает, что банк выплачивает нам проценты за пользование кредитом, что является абсурдом. Это, конечно, ошибка (попробуйте например, в файле примера на Листе Выплата установить платеж =-1000).

Если задать платеж = 0 или того же знака, что и сумма кредита, то функция СТАВКА() вернет ошибку #ЧИСЛО! Это и понятно, при нулевых платежах погасить кредит невозможно.

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

Предлагаем ознакомиться:  Работница находится в отпуске по уходу за ребенком, но хочет работать по совместительству неполный рабочий день. Законно{q}

Определим, с какой годовой ставкой мы можем накопить 1 000 000 руб., внося ежемесячно по 10 000 руб. в течение 5 лет. (см. файл примера на Лист Накопление)

Примечание. Аннуитетная схема накопления целевой суммы подробно рассмотрена в статье Аннуитет. Расчет периодического платежа в MS EXCEL. Срочный вклад.

Формула для вычисления годовой ставки будет выглядеть так =12*СТАВКА(12*5;-10000;0;1000000) =19,38%

Здесь ПС=0, т.е. начальная сумма вклада =0 (Приведенная Стоимость). Целевой вклад = 1000000 (БС – Будущая Стоимость).

Если суммарное количество взносов будет > целевой стоимости (1000000), то ставка станет отрицательной, чтобы соблюсти наше требование БС=1000000.

Услвоия микрокредитования.

Если задать величину пополнения = 0 или того же знака, что и целевая сумма, то функция СТАВКА() вернет ошибку #ЧИСЛО! Это и понятно, при нулевых взносах накопить ничего не получится. Взнос того же знака, что и целевая сумма, вероятно, означает, что банк платит нам. Но, это не возможно, т.к. начальная сумма вклада =0, поэтому выдается ошибка.

В банк положены 300 тыс. руб., на которые ежемесячно начисляются сложные проценты по ставке 24%. Через 4 месяца сняты 5 тыс. руб., а через 8 месяцев вклад был закрыт. Какая сумма была на счете в момент закрытия вклада (решить задачу при помощи дисконтирования){q}

Замените годовую десятилетнюю ренту с годовым платежом 1000 долл. на ренту с полугодовым платежом по 600 долл. Годовая ставка процентов 8%. n — {q}

Анализ пенсионных отчислений с использованием функции СТАВКА в Excel

Пример 2. Определить темпы роста пенсионных отчислений (процентную ставку), если баланс средств на конец года составляет 12000 долларов, а в начале года – 2400 долларов. Еженедельные платежи на протяжении года составляли 150 долларов (то есть, количество периодов – 52).

=СТАВКА(B2;B3;B4;B5;0;0,1)*52

  • B2 – количество периодов выплат;
  • B3 – сумма платежа (расходная операция, поэтому отрицательное значение);
  • B4 – сумма средств до наступления первого периода выплат;
  • B5 – сумма по окончанию последнего периода выплат;
  • 0 – выплаты в конце периода;
  • 0,1 – произвольное значение из интервала от 0 до 1;
  • 52 – количество периода выплат для пересчета размера ставки в годовых.

То есть, пенсионные отчисления выполняются под 7% годовых.

= СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз])

  • кпер – обязательный аргумент, характеризующий число периодов выплат по аннуитетной схеме.
  • плт – обязательный аргумент, характеризующий фиксированное значение выплаты, производимой в каждый из периодов выплат. Сумма выплаты за каждый период включает две составляющие: тело и проценты без учета прочих комиссий и сборов. Если данный аргумент опущен, следующий аргумент должен быть указан явно.
  • пс – обязательный аргумент, характеризующий текущую стоимость задолженности (либо вознаграждения), эквивалентную общей сумме последующих платежей на данный момент. Если значение неизвестно, необходимо явно указать значение 0 (нуль).
  • [бс] – необязательный аргумент, характеризующий размер желаемого остатка средств после выполнения последней выплаты согласно графика платежей. Если явно не указан, по умолчанию используется значение 0 (нуль), а аргумент пс становится обязательным для заполнения.
  • [прогноз] – необязательный аргумент, характеризующий предполагаемый размер процентной ставки. Если аргумент явно не указан, по умолчанию принимается значение 10%. Если полученное в результате вычислений значение не сходится с указанной прогнозной величиной, величину данного аргумента следует изменить. Рекомендуется выбирать значение для аргумента [прогноз] из диапазона от 0 до 1.
  • [тип] – необязательный аргумент, принимающий значения 0 или 1:
  1. Если введен 0, считается, что выплата производится в конце периода;
  2. Если введен 1, считается, что выплата производится в начале периода.
Предлагаем ознакомиться:  От какой суммы начисляются алименты с безработного

Примечания:

  1. Единицы измерения величин, указанных в качестве аргументов кпер и [прогноз], должны соответствовать друг другу. Например, при расчете ставки по займу, выданному на два года под 16% с ежемесячными выплатами необходимо, в качестве аргумента [прогноз] необходимо использовать значение 16%/12, а кпер – 2*12.
  2. Если хотя бы в качестве одного из аргументов функции было передано текстовое значение, результатом выполнения функции будет являться код ошибки #ЗНАЧ!.
  3. Аннуитетная схема выплаты вознаграждения либо погашения задолженности предполагает выплаты фиксированной суммы, включающей вознаграждение или тело кредита и дополнительных процентов (в зависимости от установленной процентной ставки) на протяжении установленного количества периодов выплат. В отличие от классической схемы, при которой проценты начисляются на остаток вознаграждения или задолженности, в аннуитетной схеме соотношение тело кредита/проценты является изменяющейся величиной.
  4. При выполнении расчетов функция СТАВКА использует итерационный метод. Если после 20 итераций последующие результаты вычислений отличаются друг от друга более, чем на 10-7, результатом вычислений будет являться код ошибки #ЗНАЧ!.

Определение реальной процентной ставки по кредиту

Пример 3. Ноутбук одной и той же модели можно приобрести за 1200 долларов в рассрочку (беспроцентную, судя по рекламе в первом магазине) или за 1050 долларов в другом магазине. Рассрочка выдается на 1 год с 12 периодами выплат. Определить реальный процент «беспроцентной» рассрочки платежей по кредиту.

https://www.youtube.com/watch{q}v=ytaboutru

=СТАВКА(B2;-B3/B2;B4;0;0;0,01)*B2

  • B2 – число периодов выплат;
  • -B3/B2 – выражение для расчета размера ежемесячного платежа;
  • B4 – реальная стоимость ноутбука (используется как начальная стоимость финансового инструмента, цена которого повысится до 1200 к окончанию последнего периода выплат);
  • 0 – остаток по окончанию последнего периода выплат;
  • 0 — выплаты в конце периода;
  • 0,01 — произвольное значение предполагаемой ставки.

То есть, фактически в первом магазине клиенту предложили кредит на ноутбук под 25,4% годовых.

Понравилась статья? Поделиться с друзьями:
Юридическая помощь
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock detector