Расчет эффективной процентной ставки в Excel

В Excel эффективные ежегодные процентные ставки, если заданные номинальные процентные ставки и количество периодов, составляющих год, рассчитывает функция ЭФФЕКТ:

Синтаксис ЭФФЕКТ (номинальная_ставка; колпер). (2.17)

Аргументы функции означают:

Номинальная_ставкаэто номинальная годовая процентная ставка; Кол_пер — это количество периодов в году, за которые начисляются сложные проценты.

Пример 2.32. Заем 1 млн. Грн. с номинальной нормой процента 12% будет оплачена в конце 3 лет единой суммой с начисленными процентами. Какая сумма будет уплачена, если проценты начисляются каждые полгода, ежеквартально, ежемесячно, ежедневно.

Решение : Задачу можно решить несколькими способами:

1) по формуле (1.30):

2) используя функцию Excel БС: БС (12% / 2; 2-3;; — 1000) = 1418,52; БС (12% / 4; 4-3;; — 1000) = 1425,76; БС (12% / 12; 12-3;; -1000) = 1430,77; БС (12% / 365; 365-3;; -1000) = 1433,24;

3) используя эффективную процентную ставку — формула (2.17). Будущую стоимость займа можно рассчитать, используя эффективную процентную ставку. Вычислим эффективные ставки в ячейках А1: А4:

А1 = зффект (12% 2) = 0,1236; А2 = зффект (12%, 4) = 0,1255; A3 = зффект (12%, 12) = 0,1268; А4 = зффект (12%; 365) = 0,1275.

В ячейку В1 введем формулу для вычисления будущей стоимости займа

В1 = БС (А1, 3,; -1000) и скопируем ее в В2: В4. Результаты расчетов пе я в ячейках В1: В4 соответственно: 1418,52; 1425,76; 1430,77 и 1433,24.

4) введением математических формул в Excel в строку формул = (1 + 0,12 / 2) л2 — 1;

= (1 + 0,12 / 4) л4 — 1; = (1 + 0,12 / 12) Л12 — 1;

= (1 + 0,12 / 365) Л365 — 1.

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

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

Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.

Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:

Делаем калькулятор расчета аннуитетных платежей в Excel - Шаг 1

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

Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:

Делаем калькулятор расчета аннуитетных платежей в Excel - Шаг 2

Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:

Делаем калькулятор расчета аннуитетных платежей в Excel - Шаг 3

Здесь нам требуется заполнить три поля:

  • «Ставка» – годовая процентная ставка по кредиту делённая на 12.
  • «Кпер» – общий срок кредитования.
  • «Пс» – сумма кредита (указывается со знаком минус).

Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.

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

Делаем калькулятор расчета аннуитетных платежей в Excel - Шаг 4

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

Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках. Можно было просто вписать в строке формул то, что там сейчас вписано.

Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:

Делаем калькулятор расчета аннуитетных платежей в Excel - Шаг 5

На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.

Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.

Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.

Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:

Использование формул Excel для определения объемов платежей и сбережений

​Аргумент​ увеличения величины выплат​ кредиту. Приведем пример​Поскольку в примере не​ наших формул с​ по идее прийти​ суммы​ вниз. Функция​ помочь в этом​Аргумент ПЛТ составляет -150.​ в году.​ которые предлагается решить в​

​ в которых содержатся​​. Затем кликаем по​ выплаты по телу​ тела займа, выплата​ после полной его​«Тип»​

​ по телу кредита.​​ с другой функцией.​ предусмотрено дополнительных комиссий​ ПРПЛТ и ОСПЛТ.​ к концу срока.​отрицательные суммы — наши​

​ПРПЛТ (IPMT)​​ вопросе.​Аргумент ПС (приведенная стоимость)​Аргумент КПЕР 2*12 — это​ ВУЗах, техникумах, школах​ числа. Мы устанавливаем​ двум вышеуказанным элементам,​ займа за первый​ процентов, общий ежемесячный​

​ оплаты. Как помним,​​определяет время расчета:​ Таким образом, с​Входные данные:​ и сборов, определим​ Если же номера​

​ Очевидно =0, т.е.​ выплаты банку, положительные​для вычисления процентной​

​Для быстрой прикидки кредитный​ составляет 2500.​ общее количество периодов​ и других обучающих​ курсор в поле​ установив между ними​ месяц. Она составит​ платеж, который является​

​ это значение практически​

​ в конце или​

​ течением времени удельный​Можно пойти по уже​ годовую эффективную ставку​ нет, то выводим​ никто никому ничего​

​ — берем дополнительный​ части вводится аналогично.​ калькулятор в Excel​Расчет суммы первого взноса​ выплат по кредиту.​ заведениях по теме​«Число1»​ знак​

​18536,74 рубля​ суммой предыдущих двух​ всегда равно нулю.​

​ в начале периода.​ вес процентов в​ проторенному пути: рассчитать​

​ с помощью функции​ пустую текстовую строку:​

​ не должен.​ кредит к уже​ Осталось скопировать введенные​ можно сделать за​

​Допустим, вы хотите приобрести​

​Аргумент ПС или приведенной​

​ MS EXCEL. Желающие​. Затем зажимаем левую​«+»​.​ колонок, а также​

​ Устанавливаем в данном​ В первом случае​ общем ежемесячном платеже​

​ внутреннюю ставку доходности,​ ЭФФЕКТ.​Реализованный в предыдущем варианте​Тип​ имеющемуся​ формулы вниз до​

​ пару минут с​ автомобиль стоимостью 19​ стоимости составляет 5400​

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

​. Жмем на клавишу​Затем, как уже говорилось​ оставшаяся сумма к​ поле число​ он принимает значение​ падает, а удельный​

​ а потом умножить​

​Вызываем «Мастер функций». В​

​ калькулятор неплох, но​- способ учета​подсчитать точное количество дней​ последнего периода кредита​ помощью всего одной​ 000 долларов США​

​ долларов США.​ воспользоваться решением приведенном​ выделяем на листе​Enter​

​ выше, нам следует​ выплате.​«0»​«0»​

​ вес оплаты по​ результат на 12.​ группе «Финансовые» находим​ не учитывает один​

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

​ под 2,9% годовых​Расчет ежемесячных платежей по​ в файле примера.​ первые две ячейки​.​ скопировать данную формулу​Для определения величины оплаты​. Хотя этот аргумент​, а во втором​ телу растет. При​ Но мы используем​ функцию ЭФФЕКТ. Аргументы:​ важный момент: в​

​ (и процентов, которые​

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

​ этом сам общий​ функцию ЧИСТВНДОХ (возвращает​«Номинальная ставка» — годовая​ реальной жизни вы,​

​ выплаты учитываются на​ на них приходятся)​ для вычисления общей​ расчета ежемесячной выплаты​

​ Вы хотите сохранить​Представьте дом стоимостью 180 000​

​ лишь решение конкретных​«Выплата по телу кредита»​

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

​ ставка по кредиту,​

​ скорее всего, будете​

​ начало месяца, если​ лучше с помощью​ суммы ежемесячных выплат​ по аннуитетному кредиту​

​ ежемесячные платежи на​ долларов США под​ контрольных заданий, а​

​ в предыдущих случаях,​ маркера заполнения. Для​

​«Тип»​. Большинство банковских учреждений​ протяжении всего срока​ для графика денежных​ указанная в договоре​ вносить дополнительные платежи​ равен 0, то​ функции​ (она постоянна и​ (т.е. кредиту, где​ уровне 350 долларов​ 5% годовых на​ основные разделы, посвященные​ видим, отобразилась ссылка​ заполняем колонку данными.​ этого устанавливаем курсор​, которая как раз​

​указываем в начале​

​ используют именно вариант​

​ кредитования не меняется.​ потоков).​ с банком. В​

Читайте также:  Как выделить повторяющиеся значения в Excel разными цветами?

​ для досрочного погашения​ на конец. У​ДОЛЯГОДА (YEARFRAC)​ равна вычисленной выше​ выплаты производятся равными​ США. Таким образом,​

​ 30 лет.​ финансовой тематике можно​

​ на диапазон. Она​ Как видим, на​ в нижний правый​ предназначена для этих​

​ или в конце​ с оплатой в​Как раз на расчете​Аргументы функции:​

​ примере – 18%​

​ при удобной возможности.​ нас в России​Кто как, а я​ в ячейке C7)​ суммами — таких​ вам необходимо рассчитать​С помощью функции ПЛТ(ставка;КПЕР;ПС)​ найти здесь:​ состоит из двух​

​ протяжении всего действия​

​ угол ячейки, в​

​ целей. Устанавливаем курсор​ месяца производится оплата.​ конце периода. Этот​

​ аннуитетного платежа мы​

​Эффективная ставка по лизингу​ (0,18).​

​ Для реализации этого​

​ абсолютное большинство банков​ считаю кредиты злом.​

См. также

​ и, ради интереса,​

​ сейчас большинство) в​

​ начальный платеж. В​

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

​ не вводить, тогда​ указана процентная ставка​Для расчета ежемесячного взноса​ 16,7% гдовых делится​ кредиту. Приведем пример​ предусмотрено дополнительных комиссий​ (сумма всех платежей​

​ абсолютные ссылки. Можно​ MS EXCEL. Желающие​ на ВСД() (используется​ ставка меньше, тот​ по кредиту отражает​

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

​ их рассчитать в​ на последнюю. Для​23536,74 рубля​ до конца таблицы.​

  1. ​Мастер функций​ программа по умолчанию​ по кредиту (​
  2. ​ при использовании аннуитетной​ на число дней​ с другой функцией.​ и сборов, определим​ по кредиту);​ вводить в формулу​

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

Расчет аннуитетных платежей по кредиту в Excel

​ MS EXCEL.​ того, чтобы в​

​. Собственно этот показатель​

​В итоге все ячейки​

  • ​. В категории​ будет считать, что​
  • ​12%​ схемы в Экселе​
  • ​ в году (365​

​ годовую эффективную ставку​переплата составила 184 881,​ непосредственно числа, а​

  • ​ воспользоваться решением приведенном​ внутренней доходности, IRR),​ выгоден заемщику.​ с точки зрения​
  • ​В MS EXCEL есть​ будущем иметь возможность​

​ мы уже рассчитывали​ столбца заполнены. Теперь​«Финансовые»​ в нем расположено​

  1. ​), величина займа (​ существует специальная функция​ или 366), на​Можно пойти по уже​ с помощью функции​Условия кредитования.
  2. ​ 67 руб.;​ не ссылки на​График погашения.
  3. ​ в файле примера.​ в которой используется​Но, что за​ заёмщика, то есть​ функция ЭФФЕКТ(номинальная_ставка, кол_пер),​ скопировать указанную формулу​ ранее при помощи​ мы имеем график​отмечаем наименование​ значение равное нулю.​500000 рублей​ –​ ближайшие три года​ проторенному пути: рассчитать​ ЭФФЕКТ.​

​процентная ставка – 184​ ячейки с данными.​В этой статье приведено​ аналогичное дисконтирование регулярных​ смысл имеет 72,24%?​ учитывает все дополнительные​

​ которая возвращает эффективную​

Расчет платежей в Excel по дифференцированной схеме погашения

​ посредством маркера заполнения,​ПЛТ​

  • ​ выплаты тела займа​«ОСПЛТ»​После того, как все​
  • ​) и срок кредита​ПЛТ​

​ внутреннюю ставку доходности,​Вызываем «Мастер функций». В​ 881, 67 /​

​ Тогда она примет​

  • ​ лишь решение конкретных​ платежей, но на​
  • ​ Может быть это​
  • ​ выплаты, непосредственно связанные​ (фактическую)​ делаем первую часть​
  • ​. Но в данном​ помесячно. Как и​и жмем кнопку​ данные введены, жмем​

​ (​. Она относится к​Далее у разных​

​ а потом умножить​

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

​ группе «Финансовые» находим​

Структура графика.

​ 500 000 *​ следующий вид: =ПЛТ(18%/12;​ контрольных заданий, а​ основе номера периода​ соответствующая ставка по​ с кредитом (помимо​годовую​ ссылки на диапазон​ случае это представлено​ говорилось уже выше,​«OK»​ на кнопку​24 месяца​ категории финансовых операторов.​ кредиторов по разному​

​ результат на 12.​ функцию ЭФФЕКТ. Аргументы:​ 100, или 37%.​ 36; 100000).​ основные разделы, посвященные​ выплаты, а не​

​ простым процентам? Рассчитаем​ платежей по самому​процентную ставку, если​

​ абсолютной. Выделяем её​ более наглядно, именно​ величина оплаты по​.​

​«OK»​). При этом оплата​ Формула этой функции​

Таблица.

​ — есть ежедневное​ Но мы используем​«Номинальная ставка» — годовая​

Переплата.

​Безобидная комиссия в 1​Ячейки окрасились в красный​ финансовой тематике можно​ от количества дней.​

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

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

Условия по кредиту.

​Запускается окно аргументов оператора​.​ производится ежемесячно в​

Процентная ставка.

​ выглядит следующим образом:​

График платежей.

​ начисление (капитализация) процентов​ функцию ЧИСТВНДОХ (возвращает​ ставка по кредиту,​ % обошлась кредитополучателю​ цвет, перед числами​ найти здесь:​Представим себе ситуацию, когда​ делали в предыдущих​ выплатами являются банковские​

​ процентная ставка и​ функциональную клавишу​ по телу займа​

​ каждым новым периодом​ ОСПЛТ. Он имеет​После этого в ячейку,​

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

​ очень дорого.​ появился знак «минус»,​Аннуитет в Excel​

  • ​ в 2-х разных​ разделах:​
  • ​ комиссии — комиссии​количество периодов в году​F4​ и процентам.​
  • ​ увеличивается.​ следующий синтаксис:​
  • ​ которую мы выделили​Выделяем элемент на листе,​Как видим, указанная функция​ бывает по сроку​
  • ​ для графика денежных​ с банком. В​Эффективная процентная ставка кредита​

​ т.к. мы эти​Расчет будущей и приведенной​ банках нам предлагают​Мы переплатили 80,77т.р.​ за открытие и​

Расчет полной стоимости кредита в Excel

​, в которые начисляются​. Вторую часть ссылки​Теперь нужно добавить данные​Теперь нам нужно сделать​=ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)​ в первом пункте​ в который будет​ обладает довольно большим​ выплат — либо​ потоков).​

  • ​ примере – 18%​ без комиссии составит​
  • ​ деньги будем отдавать​ стоимости в Excel​
  • ​ взять в кредит​ (в виде процентов​ ведение счёта, за​

​ сложные проценты. Под​ так и оставляем​

Условия 3.

​ в столбец, где​ месячный расчет оплаты​Как видим, аргументы данной​ данного руководства, выводится​

График2.

​ выводиться результат расчета,​ количеством аргументов. Правда,​ 30 дней, либо​Аргументы функции:​ (0,18).​ 13%. Подсчет ведется​ банку, терять.​Расчет кредитов и вкладов​ одинаковую сумму на​

​ и дополнительных платежей)​ приём в кассу​ номинальной ставкой здесь​

​ относительной. Теперь при​ будет ежемесячно отображаться​

Ставка.

​ по процентам. Для​ функции почти полностью​ результат вычисления. Как​ и щелкаем по​

​ последние два из​ календарный месяц.​Эффективная ставка по лизингу​«Количество периодов» — число​ по той же​​

​ в Excel​ одинаковых условиях, но​ взяв кредит в​ наличных денег и​

​ понимается, годовая ставка,​ использовании маркера заполнения​

​ остаток суммы по​ этих целей будем​ совпадают с аргументами​ видим, величина ежемесячного​ пиктограмме​ них не являются​

Расчет дифференцированных платежей в программе MO Excel

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

В основу снова ляжет пример. Клиент взял в банке 180 тысяч рублей на 3 года. Ставка – 13% годовых. Погашение предполагается каждый месяц, в конце периода.

Для расчетов необходимо узнать ежемесячную базовую сумму, подлежащую выплате. Каждый месяц клиент обязан возвращать банку равную сумму – часть долга. В рассматриваемом случае это 180000 / 3 / 12 = 5000 рублей. Каждый месяц на остаток начисляются прописанные в договоре проценты. Соответственно, уменьшается остаток – меньше становится и сумма, начисляемая банком.

Расчет основывается на функции ПРОЦПЛАТ. Через точку с запятой в ней обозначаются четыре показателя:

Расчет процентов по кредиту

Расчет процентов по кредиту

  • ставка за период (13%/12)
  • номер периода, за который будет считаться величина
  • число периодов начисления суммы долга к уплате
  • приведенная стоимость (сумма кредита)

Функция ПРОЦПЛАТ совпадает по аргументам с предыдущей формулой, однако не имеет с ней ничего схожего, подменять их друг другом нельзя. В англоязычной версии наименование функции – ISPMT, аргументы в ней такие же.

В ПРОЦПЛАТ предполагается начисление суммы процентов в начале периода. Сдвинуть эту функцию на конец месяца можно, если сместить вычисления на период раньше (не «период», а «период-1»). Итоги будут отображены с противоположным знаком, то есть минусом. Таким образом отличаются расчеты при начислении процентов по кредиту и вкладу.

Как рассчитать сумму переплаты при досрочном погашении займа

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

формула расчета аннуитетного платежа по кредиту в Excel

Обратите внимание! При уменьшении выплаты приходится перерасчитывать месячные взносы со следующего периода после досрочного погашения.

Расчет эффективной ставки по кредиту в Excel

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

Заемщик взял кредит на сумму 150 000 рублей. Срок – 1 год (12 месяцев). Номинальная годовая ставка – 18%. Выплаты по кредиту укажем в таблице:

Поскольку в примере не предусмотрено дополнительных комиссий и сборов, определим годовую эффективную ставку с помощью функции ЭФФЕКТ.

Вызываем «Мастер функций». В группе «Финансовые» находим функцию ЭФФЕКТ. Аргументы:

  1. «Номинальная ставка» — годовая ставка по кредиту, указанная в договоре с банком. В примере – 18% (0,18).
  2. «Количество периодов» — число периодов в году, за которые начисляются проценты. В примере – 12 месяцев.

Эффективная ставка по кредиту – 19,56%.

Усложним задачу, добавив единовременную комиссию при выдаче кредита в размере 1% от суммы 150 000 рублей. В денежном выражении – 1500 рублей. Заемщик на руки получит 148 500 рублей.

Мы внесли в столбец с ежемесячными платежами 148 500 со знаком «-», т.к. эти деньги банк сначала отдает. Платежи, которые вносит заемщик в кассу впоследствии, являются для банка положительными. Внутреннюю ставку доходности считаем с точки зрения банка: он выступает в качестве инвестора.

Функция дала эффективную ежемесячную ставку 1,69%. Для расчета номинальной ставки результат умножим на 12 (срок кредитования): 1,69% * 12 = 20,28%. Пересчитаем эффективную процентную ставку:

Единовременная комиссия в размере 1% повысила фактическую годовую процентную ставку на 2,72%. Стало: 22,28%.

Добавим в схему выплат по кредиту ежемесячный сбор за обслуживание счета в размере 300 рублей. Ежемесячная эффективная ставка будет равна 2,04%.

Номинальная ставка: 2,04% * 12 = 24,48%. Эффективная годовая ставка:

Читайте также: 

Ежемесячные сборы увеличили ее до 27,42%. Но в кредитном договоре по-прежнему будет стоять цифра 18%. Правда, новый закон обязует банки указывать в кредитном договоре эффективную годовую процентную ставку. Но заемщик увидит эту цифру после одобрения и заключения договора.

Итоги расчета эффективной процентной ставки

  • Мы взяли кредит на $100 000.
  • Выплатили $122015,99.
  • Переплата составила $22015,99.
  • Ставка 22015,99 / 100000 = 22%.

Ежемесячная комиссия в 1% обошлась нам в 12000 долларов, что превышает основную сумму выплат по кредиту! Почему так? Потому что комиссию за обслуживание мы каждый месяц платили из рассчета от основной суммы кредита ($100’000), а проценты с каждым месяцем уменьшались ввиду того, что ежемесячно уменьшалась сумма кредита. Как видите, такая, безобидная, на первый взгляд, хитрость банка, как процент за обслуживание при ближайшем рассмотрении обходится очень дорого.

Что такое эффективная процентная ставка

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

Сейчас же заёмщика при получении кредита ожидает полный «трэш». Вот он сидит дома на унитазе и мирно читает какую-то рекламную газетку. Но вдруг его лобик сморщился, затем глазки забегали, и на лице появилась безумная улыбка. Через минуту «пациент» выбегает из туалета с криком: «Нашёл! Я нашёл банк с самыми выгодными условиями кредитования! Это банк «Лохотрон-инвест», который выдаёт кредиты под 0 процентов годовых! Люся, где мои кеды? Срочно погладь шнурки от них!»

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

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

В итоге получается, что заёмщики компании «Лохотрон-инвест» в реальности переплачивают за кредиты гораздо больше, чем клиенты других банков.

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

Эффективная процентная ставка – это реальная переплата по кредиту, выраженная в процентах годовых.

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

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

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

Итоги расчета эффективной процентной ставки

  • Мы взяли кредит на $100 000.
  • Выплатили $122015,99.
  • Переплата составила $22015,99.
  • Ставка 22015,99 / 100000 = 22%.

Ежемесячная комиссия в 1% обошлась нам в 12000 долларов, что превышает основную сумму выплат по кредиту! Почему так? Потому что комиссию за обслуживание мы каждый месяц платили из рассчета от основной суммы кредита ($100’000), а проценты с каждым месяцем уменьшались ввиду того, что ежемесячно уменьшалась сумма кредита. Как видите, такая, безобидная, на первый взгляд, хитрость банка, как процент за обслуживание при ближайшем рассмотрении обходится очень дорого.

Хомячковый рай. Уйти и потеряться:

Примеры расчёта

Для наглядности приведём реальный пример из жизни. Клиент банка взял потребкредит (потребительский кредит) на сумму 200 000 долларов США на неотложные нужды. Годовая ставка по такому виду банковского займа составила 19%, а банковская комиссия за пользование кредитом составляет 2% от всей суммы банковской ссуды. При выборе схемы оплаты заёмщик выбирает аннуитетные платежи. Таким образом, погашение займа будет происходить в течение всего оговоренного в договоре кредитного срока равными суммами. Для расчета эффективной процентной ставки по кредиту нам понадобиться предварительно, рассчитать размер платежа по кредиту, который заёмщик и будет оплачивать ежемесячно. Воспользуемся формулой расчёта аннуитетных платежей, напоминаем, как она выглядит: A = K*S

  • S – общая сумма кредита (согласно данным нашего примера, она равна S = 200 000);
  • K — коэффициент аннуитета (он зависит напрямую от других величин n и i) и рассчитывается по следующей формуле:

 K = \frac<i \cdot (1+i)^n> <(1+i)^n -1>» /> Подставив значения в формулу, получаем: <strong>i</strong> = 0,016 (19%/12месяцев), соответственно <strong>n</strong> (период кредитования) согласно нашему примеру составляет 12 месяцев. Далее находим коэффициент аннуитета: <strong>K</strong>=0,092252 Следовательно <strong>А</strong>=0,092252*200 000, отсюда <strong>А</strong>=18 450.41 долларов.</p>
<h2>Кредитный калькулятор в Excel</h2>
<p><img src=

  • Считать оба вида платежей.
  • Показывать полный график платежей.
  • Разбивать сумму выплаты на «погашение долга» и «погашение процентов».
  • Учитывать досрочные возвраты (отдельно для уменьшения срока и уменьшения размера выплат).

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

Расчет эффективной процентной ставки по кредиту в Excel

угу, все точно
ВНДОХ() — дает расчет эффективной ставки для случая периодических платежей в погашение кредита

ЧИСТВНДОХ() — для любого расписания денежных потоков

Насчет понятия эффективной ставки и расчета:
Г-н Меликьян в Письме ЦБР от 29 декабря 2006 г. № 175-Т официально подтвердил использование этой функции для расчета эффективной ставки, однако в дальнейшем не без влияния АРБ (http://www.arb.ru/site/docs/docs.php?doc=383) действие этого письма было отменено (Письмо ЦБ РФ от 03.06.2008 N 64-Т).
В настоящее время действует другой документ — Указание ЦБ от 13 мая 2008 года № 2008-У (http://www.cbr.ru/Publ/Vestnik/Ves080604028.zip)с подменой понятий — вместо эффективной процентной ставки использовано понятие полной стоимости кредита (тоже в % годовых ). Расчет производится по формуле, идентичной для той, что описана функцией ЧИСТВНДОХ().

Таким образом термины — эффективная процентная ставка (ЦБР до 2008 г.), полная стоимость кредита (ЦБР сейчас), внутренняя норма доходности (общепринятый термин), внутренняя ставка доходности (Excel) оказываются приравнены друг к другу

В приведенном Дмитрием примере ставка составила около 15%, а без страхового сбора — около 7% (вот где грабители )

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

​ когда-нибудь потом. И​ значение ошибки #ЧИСЛО!​ банку по прошествии​Аргумент «Ставка» составляет 2,9%,​ разделенных на 12​ПЛТ​ периодов начислений; ПС​После нажатия кнопки ОК,​.​ взяв кредит в​ файла в MS​ по определению, одинаков​ долг и планирует​ вкладчик сможет забрать​ процентной ставкой 23,5%.​ присутствие номера периода:​ выплаты учитываются на​ главная проблема, по-моему,​ если:​ 2-х лет.​ разделенных на 12.​ месяцев — количество месяцев​: возвращает сумму периодического​ – приведенная стоимость​ в ячейке​Функция ЧИСТВНДОХ() похожа​ размере 250т.р. Если​ EXCEL воспользуемся Указаниями​ для обоих случаев,​ получить прибыль, величина​ 310000 рублей.​ Определить значение номинальной​

​=ЕСЛИ(A18<>»»;​ начало месяца, если​ даже не в​»Ставка» ≤ 0,​

​Решение1. =ОБЩПЛАТ(10%/12; 5*12;​Аргумент КПЕР составляет 3*12​ в году.​ платежа для аннуитета​ на текущий момент​I18​ на ВСД() (используется​ рассчитать ставку по​ Центробанка РФ от​ приравниваем оба уравнения​ которой зависит от​Таблица начисления процентов по​ ставки, если по​текущая формула​ равен 0, то​ грабительских процентах или​ «кол_пер» ≤ 0​

Вариант 1. Простой кредитный калькулятор в Excel

​ 1 000 000; 1;​ (или двенадцать ежемесячных​Аргумент КПЕР составляет 3*12​ на основе постоянства​ (для кредита ПС​будет рассчитана Эффективная​ для расчета ставки​ методу простых процентов,​ 13 мая 2008​ и после преобразования​ следующих факторов: сумма​ условиям второго банка:​ условию договора выплаты​; «»)​ на конец. У​​ в том, что​​ или «нз» ≤​​ 2*12; 0)​​ платежей за три​ для двенадцати ежемесячных​ сумм платежей и​​ — это сумма​​ ставка совпадающая, естественно,​ внутренней доходности, IRR),​​ то она составит​​ года № 2008-У​ получим формулу, приведенную​​ средств, которая предоставляется​​В данном случае проценты​ по кредиту необходимо​Т.е. если номер периода​

Расчет кредита в Excel функцией ПЛТ

  • ​ нас в России​​ это «потом» все​ 0,​Задача2. Предположим, что заем​ года).​ платежей за три​ процентной ставки.​ кредита, для вклада​ с результатом формулы​ в которой используется​
  • ​ 80,77/250*100%=32,3% (срок кредита​​ «О порядке расчета​ в справке MS​ в долг; длительность​
  • ​ не являются фиксированной​​ проводить ежемесячно.​ не пустой, то​
  • ​ абсолютное большинство банков​​ равно когда-нибудь наступит.​»нач_период» < 1,​ 2 млн. был​Аргумент ПЛТ составляет -350​ года.​КПЕР​ ПС – начальная​ ЧИСТВНДОХ().​
  • ​ аналогичное дисконтирование регулярных​​ =1 год). Это​ и доведения до​ EXCEL для функции​ периода кредитования (использования​ величиной и зависят​Исходная таблица данных:​ мы вычисляем сумму​ работает по второму​ Кредит убивает мотивацию​ «кон_период» < 1​ выдан на 3​ (необходимо будет выплачивать​

​Аргумент ПС (приведенная стоимость)​: возвращает количество периодов​ сумма вклада).​Составим в MS EXCEL​ платежей, но на​ значительно больше 15%​ заемщика — физического​ ЭФФЕКТ()​ предоставленных средств); начисляемые​

Вычисление переплаты по кредиту

Вариант 2. Добавляем детализацию

​ от итоговой суммы​Связь между значениями эффективной​ выплат с помощью​ варианту, поэтому вводим​ к росту. Зачем​​»нач_период» > «кон_период»​​ года. Годовая ставка​​ по 350 долларов​​ составляет 0, поскольку​ выплаты для инвестиции​Примечание​ график погашения кредита​ основе номера периода​ (ставка по кредиту),​ лица полной стоимости​iэфф =((1+i/m)^m)-1​ проценты за использование.​ накоплений за предыдущий​ и номинальной ставок​ наших формул с​ 0.​ напрягаться, учиться, развиваться,​

Подробный расчет выплат по кредиту

​»тип» является любым​​ = 7%. Начисление​​ США в месяц).​ отсчет начинается с​ на основе регулярных​​. Не смотря на​​ дифференцированными платежами.​

Расчет выплаты тела кредита

​ выплаты, а не​​ и гораздо меньше​​ кредита» (приведена Формула​Примечание​Проценты могут начисляться различными​ период (поэтому ссылка​ описывается следующей формулой:​ ПРПЛТ и ОСПЛТ.​Также полезно будет прикинуть​​ искать дополнительные источники​​ числом, отличным от​ процентов и погашение​Оценка динамики увеличения сбережений​ нуля.​ постоянных выплат и​ то, что названия​При расчете графика погашения​ от количества дней.​ 72,24%. Значит, это​ и порядок расчета​. Если задана эффективная​ способами: базовая сумма​ на ячейку L2​=(СТЕПЕНЬ(B3+1;1/B2)-1)*B2​

Читайте также:  Копирование форматов ячеек в таблицах Excel

Подробный кредитный калькулятор

​ Если же номера​ общий объем выплат​ дохода, если можно​ 0 и 1​ займа происходит ежеквартально​Начиная с 500 долларов​Аргумент БС (будущая стоимость),​ постоянной процентной ставки.​ аргументов совпадают с​

​ кредита дифференцированными платежами​

​Представим себе ситуацию, когда​ не тот подход,​ эффективной процентной ставки),​ годовая процентная ставка,​ остается неизменной (простые​ – абсолютная):​Полученный результат:​ нет, то выводим​ и переплату, т.е.​ тупо зайти в​Альтернативная формула:​ в начале месяца​ США на счету,​ которую необходимо достичь,​ПВ​ названиями аргументов функций​ сумма основного долга​ в 2-х разных​ чтобы разобраться в​ а также разъяснительным​ то величина соответствующей​

​ проценты) и база​​=L3*$E$3/$E$4​​Проверим полученный результат, проведя​

​ пустую текстовую строку:​ ту сумму, которую​ ближайший банк и​Из анализа альтернативной формулы​ (тип=1). Определить сколько​ сколько можно собрать​ составляет 8500 долларов​: возвращает приведенную (к​ аннуитета – ПРОЦПЛАТ()​

Формула расчета процентов вȎxcel по кредиту

Вариант 3. Досрочное погашение с уменьшением срока или выплаты

​ делится на равные​ банках нам предлагают​ сути эффективной ставке​ письмом ЦБ РФ​ ей годовой номинальной​ изменяется при наступлении​При расчете суммы за​ пересчет эффективной ставки​Реализованный в предыдущем варианте​ мы отдаем банку​ там тебе за​ ясно, что функция​ процентов будет выплачено​ за 10 месяцев,​ США.​ текущему моменту) стоимость​ не входит в​ части пропорционально сроку​ взять в кредит​ по кредиту. ​ № 175-Т от​ процентной ставки рассчитывается​ каждого последующего периода​

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

Формула расчета процентов вȎxcel по кредиту

​ если класть на​Теперь допустим, вы хотите​ инвестиции. Приведенная (нынешняя)​ группу этих функций​ кредитования. Регулярно, в​ одинаковую сумму на​

Кредитный калькулятор с уменьшением выплаты

Вариант 4. Кредитный калькулятор с нерегулярными выплатами

​Теперь вспомним принцип​ 26 декабря 2006​ по формуле​ выплат (сложные). При​ текущему значению необходимо​Описание аргументов:​ не учитывает один​ его денег. Это​ на кабальных условиях,​ только если БС=0,​ 1-го года.​ депозит по 200​ собрать 8500 долларов​ стоимость представляет собой​ (не может быть​ течение всего срока​ одинаковых условиях, но​ временной стоимости денег:​ года, где можно​

Формула расчета процентов вȎxcel по кредиту

​или с помощью функции​

  • ​ использовании сложных процентов​ прибавить проценты за​B4 – полученное выше​ важный момент: в​
  • ​ можно сделать с​ попутно грамотно разведя​ т.е. когда предполагается,​Решение2. =ОБЩПЛАТ(7%/4; 3*4;​ долларов США в​
  • ​ США на отпуск​ общую сумму, которая​ использована для расчета​ погашения кредита, заемщик​ выплата кредита в​ всем понятно, что​​ найти примеры расчета​

Что включает в себя эффективная процентная ставка по кредиту?

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

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

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

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

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

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

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

ЭПС помогает оценить реальную итоговую стоимость кредита. Процесс расчета эффективной процентной ставки осуществляется автоматически.

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

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

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

Проиллюстрируем живым примером:

  • Заемщик планирует взять в кредит 90 000 рублей для покупки тура
  • Срок кредита – двенадцать месяцев
  • Первый взнос 0%
  • Ставка – 23,5% годовых

Исходя из имеющихся данных, проиллюстрируем пример: Ежемесячный платеж для заемщика составит 9 432 руб., общая переплата по кредиту– 13 181 руб. При условии, что за обслуживание кредита плата уже включена в процентную ставку, полная стоимость кредита составит 113 181 руб., из этой суммы проценты по кредиту составят всего 13 181 руб.

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

Расчет полной стоимости кредита

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

Самостоятельно рассчитать ПСК можно в программе Microsoft Excel, используя функцию — «ЧИСТВНДОХ». Но на правильные самостоятельные расчеты уйдет много времени, к тому же, при самостоятельном расчете нельзя гарантировать сто процентный результат — разумнее довериться в расчете кредитному специалисту.

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

Какие факторы влияют на полную стоимость кредита?

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию