Эффективная процентная ставка формула excel

Расчет Эффективной ставки в MS EXCEL

Эффективная процентная ставка формула excel

Рассчитаем в MS EXCEL эффективную годовую процентную ставку и эффективную ставку по кредиту.

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

Например, есть Эффективная (фактическая) годовая процентная ставка, есть Эффективная ставка по вкладу (с учетом капитализации), есть Эффективная процентная ставка по потребительским кредитам. Разберемся, что эти ставки из себя представляют и как их рассчитать в MS EXCEL.

Эффективная (фактическая) годовая процентная ставка

В MS EXCEL есть функция ЭФФЕКТ(номинальная_ставка, кол_пер), которая возвращает эффективную (фактическую) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, в которые начисляются сложные проценты. Под номинальной ставкой здесь понимается, годовая ставка, которая прописывается, например, в договоре на открытие вклада.
Предположим, что сложные проценты начисляются m раз в год. Эффективная годовая процентная ставка дает возможность увидеть, какая годовая ставка простых процентов позволит достичь такого же финансового результата, что и m-разовое наращение в год по ставке i/m, где i – номинальная ставка.
При сроке контракта 1 год по формуле наращенной суммы имеем: S = Р*(1+i/m)m – для сложных процентов, где Р – начальная сумма вклада.

S = Р*(1+iэфф) – для простых процентов

Так как финансовый результат S должен быть, по определению, одинаков для обоих случаев, приравниваем оба уравнения и после преобразования получим формулу, приведенную в справке MS EXCEL для функции ЭФФЕКТ()
iэфф =((1+i/m)m)-1

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

или с помощью функции НОМИНАЛ(эффективная_ставка, кол_периодов). См. файл примера.

Эффективная ставка по вкладу

Если договор вклада длится, скажем, 3 года, с ежемесячным начислением по сложным процентам по ставке i, то Эффективная ставка по вкладу вычисляется по формуле: iэфф =((1+i/12)(12*3)-1)*(1/3)

или через функцию ЭФФЕКТ(): iэфф= ЭФФЕКТ(i*3;3*12)/3

Для вывода формулы справедливы те же рассуждения, что и для годовой ставки: S = Р*(1+i/m)(3*m) – для сложных процентов, где Р – начальная сумма вклада. S = 3*Р*(1+iэфф) – для простых процентов (ежегодной капитализации не происходит, проценты начисляются раз в год (всего 3 раза) всегда на первоначальную сумму вклада).

Если срок вклада =1 году, то Эффективная ставка по вкладу = Эффективной (фактической) годовой процентной ставке (См. файл примера).

Эффективная процентная ставка по потребительским кредитам

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

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

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

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

Для создания расчетного файла в MS EXCEL воспользуемся Указаниями Центробанка РФ от 13 мая 2008 года № 2008-У «О порядке расчета и доведения до заемщика — физического лица полной стоимости кредита» (приведена Формула и порядок расчета эффективной процентной ставки), а также разъяснительным письмом ЦБ РФ № 175-Т от 26 декабря 2006 года, где можно найти примеры расчета эффективной ставки (см. здесь http://www.cbr.ru/publ/VesnSearch.aspx).

Эффективную ставку по кредиту рассчитаем используя функцию ЧИСТВНДОХ(). Для этого нужно составить график платежей по кредиту и включить в него все дополнительные платежи.

Пример. Рассчитаем Эффективную ставку по кредиту со следующими условиями:
Сумма кредита – 250 тыс. руб., срок – 1 год, дата договора (выдачи кредита) – 17.04.2004, годовая ставка – 15%, число платежей в году по аннуитетной схеме – 12 (ежемесячно). Дополнительные расходы – 1,9% от суммы кредита ежемесячно, разовая комиссия – 3000р. при открытии банковского счета.

Сначала составим График платежей по кредиту с учетом дополнительных расходов (см. файл примера Лист Кредит).
Затем сформируем Итоговый денежный поток заемщика (суммарные платежи на определенные даты).

Эффективную ставку по кредиту iэфф определим используя функцию ЧИСТВНДОХ (значения, даты, [предп]). В основе этой функции лежит формула:

Где, Pi = сумма i-й выплаты заемщиком; di = дата i-й выплаты; d1 = дата 1-й выплаты (начальная дата, на которую дисконтируются все суммы).

Учитывая, что значения итогового денежного потока находятся в диапазоне G22:G34, а даты выплат в B22:B34, Эффективная ставка по кредиту для нашего случая может быть вычислена по формуле =ЧИСТВНДОХ(G22:G34;B22:B34). Получим 72,24%.

Значения Эффективных ставок используются при сравнении нескольких кредитов: чья ставка меньше, тот кредит и более выгоден заемщику. Но, что за смысл имеет 72,24%? Может быть это соответствующая ставка по простым процентам? Рассчитаем ее как мы делали в предыдущих разделах: Мы переплатили 80,77т.р.

(в виде процентов и дополнительных платежей) взяв кредит в размере 250т.р. Если рассчитать ставку по методу простых процентов, то она составит 80,77/250*100%=32,3% (срок кредита =1 год). Это значительно больше 15% (ставка по кредиту), и гораздо меньше 72,24%.

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

Теперь вспомним принцип временной стоимости денег: всем понятно, что 100т.р. сегодня – это значительно больше, чем 100т.р. через год при 15% инфляции (или, наоборот – значительно меньше, если имеется альтернатива положить эту сумму в банк под 15%).

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

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

Почему же тогда не сравнивают более понятные приведенные стоимости, а используют Эффективную ставку? А для того, чтобы сравнивать разные суммы кредита: Эффективная ставка поможет, если в одном банке дают 250т.р. на одних условиях, а в другом 300т.р. на других.

Итак, у нас получилось, что сумма всех наших платежей в погашение основной суммы кредита дисконтированных по ставке 72,24% равна размеру кредита (это из определения эффективной ставки). Если в другом банке для соблюдения этого равенства потребуется дисконтировать суммы платежей идущих на обслуживание долга по большей ставке, то условия кредитного договора в нем менее выгодны (суммы кредитов могут быть разными). Поэтому, получается, что важнее не само значение Эффективной ставки, а результат сравнения 2-х ставок (конечно, если эффективная ставка значительно превышает ставку по кредиту, то это означает, что имеется значительное количество дополнительных платежей: убрав файле расчета все дополнительные платежи получим эффективную ставку 16,04% вместо 72,24%!).

Примечание. Функция ЧИСТВНДОХ() похожа на ВСД() (используется для расчета ставки внутренней доходности, IRR), в которой используется аналогичное дисконтирование регулярных платежей, но на основе номера периода выплаты, а не от количества дней.

Использование эффективной ставки для сравнения кредитных договоров с разными схемами погашения

Представим себе ситуацию, когда в 2-х разных банках нам предлагают взять в кредит одинаковую сумму на одинаковых условиях, но выплата кредита в одном будет осуществляться дифференцированными платежами, а в другом по аннуитетной схеме (равновеликими платежами). Для простоты предположим, что дополнительные платежи не взимаются. Зависит ли значение эффективной ставки от графика погашения? Сразу даем ответ: зависит, но незначительно.

В файле примера на листе Сравнение схем погашения (1год) приведен расчет для 2-х различных графиков погашения (сумма кредита 250 т.р., срок =1 год, выплаты производятся ежемесячно, ставка = 15%).

В случае дифференцированных платежей Эффективная ставка по кредиту = 16,243%, а в случае аннуитета – 16,238%. Разница незначительная, чтобы на ее основании принимать решение. Необходимо определиться какой график погашения больше Вам подходит.

При увеличении срока кредита разница между Эффективными ставками практически не изменяется (см. файл примера Лист Сравнение схем погашения (5лет)).

Примечание. Эффективная годовая ставка, рассчитанная с помощью функции ЭФФЕКТ(), дает значение 16,075%.

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

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

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

Примечание. Эффективную ставку по кредиту можно рассчитать и без функции ЧИСТВНДОХ() – с помощью Подбора параметра. Для этого в файле примера на Листе Кредит создан столбец I (Дисконтированный денежный поток (для Подбора параметра)). В окне инструмента Подбор параметра введите значения указанные на рисунке ниже.

После нажатия кнопки ОК, в ячейке I18 будет рассчитана Эффективная ставка совпадающая, естественно, с результатом формулы ЧИСТВНДОХ().

Источник: https://excel2.ru/articles/raschet-effektivnoy-stavki-v-ms-excel

Сутьи и расчет эффективной процентной ставки

Эффективная процентная ставка формула excel

Банки Сегодня Лайв

Статьи, отмеченные данным знаком всегда актуальны. Мы следим за этим

А на комментарии к данной статье ответы даёт квалифицированный юрист а также сам автор статьи.

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

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

  • плата за открытие и ведение счета;
  • плата за внесение наличных через кассу или специальные устройства;
  • комиссия за снятие со счета и прочее.

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

Расчет ЭКС (эффективной кредитной ставки)

Есть несколько методов:

  • с помощью специальной формулы;
  • в программе Excel;
  • с помощью кредитного калькулятора.

Рассмотрим каждый из них.

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

Для удобства расчетов была разработана определенная формула:

ЭКС = СКР / t / ССК, где

ЭКС – эффективная кредитная ставка,
СКР – полная сумма кредитных расходов с учетом дополнительных выплат и комиссий,
t – срок кредитования в годах,
ССК – средневзвешенная сумма кредита.

Последний показатель (ССК) определяют по дополнительным формулам в зависимости от типа погашения кредита.

При классической схеме погашения ССК определяют по формуле:

ССК = СК * (t+1) / (t+2), где

СК – сумма кредита,
t – срок кредита в месяцах.

При аннуитетной схеме погашения ССК определяют по такой формуле:

ССК = СК ((((1+%/12)t-1) / (%*t/12)) — ((((1+%/12)t-1) / (%/12))-t) / (t*(1-(1+%/12))t)))), где

СК – сумма кредита,
t – срок кредита в месяцах.

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

Проведем пример расчета

Клиент хочет оформить кредит на сумму 50 тыс. руб. на срок 12 месяцев. Ему нужно заплатить при выдаче займа страховку в размере 1000 руб., за оформление кредита — 250 руб., Процентная ставка по кредиту — 18,5% годовых. Размер платежей рассчитывается по классической схеме.

Изначально нам нужно определить, сколько клиент должен заплатить за 12 месяцев кредита. Для этого вычисляем:

Способы оплаты госпошлины за водительские права

50 000 * 18,5% годовых = 9250 руб.

Это будет переплата по кредиту за весь период пользования. К этой сумме прибавляем другие расходы:

9250 + 250 + 1000 = 10500 руб.

Итак, полная сумма кредитных расходов (СКР) составит 10500 руб.

Теперь определяем ССК (средневзвешенную сумму кредита) по вышеуказанной формуле:

ССК = 50 000 (СК) * (12+1)/(12+2) = 46428,57 руб.

Можно переходить к расчету эффективной кредитной ставки по формуле:

10500 (СКР)/12(t)/46428,57(ССК) = 0,0188

Теперь эту сумму умножаем на 100%. Получается 1,88% в месяц, так как мы использовали в формуле временной промежуток в 12 месяцев. Если клиент будет погашать кредит на протяжении всего срока действия, ЭКС составит 22,56% годовых, а не заявленные 18,5% годовых.

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

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

Давайте рассмотрим все на примере.

Клиент оформляет кредит на сумму 100 000 руб. Срок кредитования 24 месяца. Заявленная банком процентная ставка составляет 17% годовых. Клиент должен единоразово внести комиссию в размере 15 000 руб.

Строим в Екселе таблицу следующего вида:

  • первый столбец — нумерация месяцев;
  • второй — дата погашения в каждом месяце;
  • третий — сумма ежемесячного погашения.
МесяцДата погашенияСумма ежемесячного платежа
122.09.2016-85000 (15000 — комиссия)
2

Источник: https://bankstoday.net/last-articles/effektivnaya-stavka-po-kreditu-chto-eto-takoe-kak-pravilno-ee-rasschitat

МСФО, Дипифр

Эффективная процентная ставка формула excel

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

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

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

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

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

Такие выплаты могут быть двух типов:

  1. Одноразовые комиссии/сборы, рассчитываемые либо в процентном выражении от суммы кредита, либо в виде фиксированной суммы. Например, комиссия за выдачу кредита, за открытие ссудного счета.
  2. Ежемесячные комиссии/сборы, которые могут рассчитываться как процент от общей суммы кредита или как процент от остатка задолженности. Например, ежемесячная комиссия за обслуживание ссудного счета.

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

Но в одном магазине заявленная цена оказывается существенно ниже: 150 рублей. Это кажется выгодным, покупатель приходит в этот магазин, берет шампанское и идет на кассу. И тут выясняется, что помимо 150 рублей, он должен заплатить 50 рублей за работу кассира, еще 20 рублей за аренду тележки, 30 рублей за услуги охранника и еще 20 рублей за услуги уборщицы.

Но ведь это уже должно быть включено в стоимость товара!

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

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

Эффективная процентная ставка по финансовому инструменту – это ставка, применяемая при точном дисконтировании всех будущих денежных платежей ИЛИ поступлений от финансового инструмента.

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

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

Нужная нам функция называется ВСД или внутренняя норма доходности. Найти ее просто: Формулы—> Финансовые—>ВСД.

Для примера рассмотрим кредит в сумме 100,000 рублей сроком на один год. Номинальная годовая ставка процента 18%, ежемесячные выплаты в этом случае по ставке 18%/12 = 1,5%.

Сумма 9,168 представляет собой ежемесячный платеж, который позволит выплатить 100,000 за 12 месяцев при ежемесячной ставке 1,5% (чтобы в таблице в ячейке E14 получился ноль).

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

Пример 1.

Функция ВСД (Внутренняя Ставка Доходности) подтверждает, что ежемесячная эффективная ставка равна 1,5%.  А вот для того, чтобы определить в данном случае годовую эффективную ставку, можно воспользоваться функцией «эффект», которая находится там же: Формулы—> Финансовые—>Эффект.

Эта функция вычисляет эффективную процентную ставку из номинальной по формуле: (1+Rn)t-1, где Rn — это номинальная ставка. В нашем случае годовая номинальная ставка это 0,18 (18%).

Символом t в этой формуле обозначается количество периодов времени. Так как номинальная ставка годовая, значит, в нашем случае это 12.

В скриншоте ниже годовая эффективная ставка в данном случае получается равной 19,56%.

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

Усложнение А. Единоразовая комиссия при выдаче кредита

В нашем гипотетическом примере возьмем эту комиссию равной 1% от суммы кредита 100,000, то есть 1,000 рублей. Таким образом, заемщик получает на руки не 100,000, а 99,000 рублей.

Пример 2.

Функция ВСД дает эффективную ежемесячную ставку 1,66%. Номинальная ставка в таком случае будет равна 1,66%*12 = 19,92%. А эффективная процентная ставка — 21,83% (см. скриншот ниже). Таким образом, дополнительная комиссия при выдаче кредита повысила эффективную ставку на 2% с 19,56% (см. пример 1) до 21,83%.

Усложнение Б. Ежемесячные сборы (за обслуживание ссудного счета)

Если к нашему кредитному продукту кроме комиссии за выдачу кредита добавить ежемесячный сбор (он может называться как угодно) в сумме 500 (0,05% от тела кредита 100,000), то ежемесячная эффективная ставка вырастет до 2,5%.

В этом случае номинальная годовая ставка будет равна 2,5%*12 = 30%! Эффективная годовая ставка по такому кредиту будет и вовсе заоблачной — 34,48%! Но банк (по крайней мере, раньше) рассказывал своим клиентам, что ставка по такому кредиту равна всего-то 18% годовых в рублях.

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

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

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

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

Алгоритм расчета эффективной процентной ставки по кредиту такой:

  • 1) Зная величину выплат, связанных с кредитом, можно рассчитать эффективную ежемесячную ставку с помощью функции ВСД.
  • 2) Эффективную ежемесячную ставку умножаем на 12 и находим номинальную годовую ставку
  • 3) Номинальную годовую ставку можно перевести в эффективную годовую ставку с помощью функции ЭФФЕКТ

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

Конечно, с помощью Excel трудно обсчитывать кредиты, выдаваемые на большой срок: 15-20 лет. В приведенных примерах срок кредита составлял всего 1 (один) год.

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

Все выплаты — это те, которые вы бы никогда не сделали, если бы не нужно было брать этот кредит.

Напоследок список того, что заемщик сейчас платить не обязан:

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

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

http://ipotekavvalute.ru/post/narushenija-zakonodatelstva-v-kreditnyh-dogovorah.html

Поучительная история

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

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

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

Это называется опыт – на сегодняшний день опыт важнее академической степени.

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

Назавтра в новостях объявили, что банк был ограблен на сумму 100 миллионов. Грабители пересчитали добычу, но насчитали всего 20 миллионов.

Грабители начали ворчать: «Мы рисковали жизнью из-за несчастных 20 миллионов, в то время, как банковское начальство похитило 80 миллионов, не моргнув глазом.

Наверно лучше изучать, как работает система, вместо того, чтобы быть простым грабителем.
Это называется – знание–сила!

Дай человеку пистолет, и он сможет ограбить банк.
Дай человеку банк, и он сможет ограбить всех!»

Другие полезные статьи из рубрики Финансы:

Вернуться на главную страницу

Источник: http://msfo-dipifr.ru/effektivnaya-procentnaya-stavka-po-kreditu-vkladu-eto-vnutrennyaya-norma-doxodnosti-po-finansovomu-instrumentu/

Расчет Эффективной Процентной Ставки. Формула Excel

Эффективная процентная ставка формула excel

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

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

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

Что нужно для правильного расчета ставки

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

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

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

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

Для наглядности приведём реальный пример из жизни. Клиент банка взял потребкредит (потребительский кредит) на сумму 200 000 долларов США на неотложные нужды.

Годовая ставка по такому виду банковского займа составила 19%, а банковская комиссия за пользование кредитом составляет 2% от всей суммы банковской ссуды. При выборе схемы оплаты заёмщик выбирает аннуитетные платежи.

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

Для расчета эффективной процентной ставки по кредиту нам понадобиться предварительно, рассчитать размер платежа по кредиту, который заёмщик и будет оплачивать ежемесячно. Воспользуемся формулой расчёта аннуитетных платежей, напоминаем, как она выглядит: A = K*S

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

Подставив значения в формулу, получаем: i = 0,016 (19%/12месяцев), соответственно n (период кредитования) согласно нашему примеру составляет 12 месяцев. Далее находим коэффициент аннуитета:K=0,092252 Следовательно А=0,092252*200 000, отсюда А=18 450.41 долларов.

Способ второй

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

Для этого в верхней строке после fx вписываете следующие данные: =ПЛТ(0,016;12;-200000) Благодаря встроенной функции ПЛТ расчет происходит автоматически. Проверяем наш предыдущий ответ и получаем такую же сумму — 18 450.

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

Пояснения: 0,015 – размер ежемесячной процентной ставки, i = 19/12/100$ 12 – количество месяцев, входящих в состав кредитного периода = n; -200000 – общая сумма займа = S (пишется со знаком минус). А теперь составляем таблицу:

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

Согласно полученным цифрам в таблице, можно сделать следующие выводы:

  1. Клиент брал кредит в размере 200 000 долларов США, а выплатил 269404,80 долларов США;
  2. Сумма переплаты по кредиту составила – 69404,80 долларов США;
  3. А сумма процентной ставки увеличилась до 34%;
  4. Сумма ежемесячной комиссии за пользование кредитом с 2% выросла до 48000 долларов США (получилось, что данная сумма выплаты по кредиту превысила сумму выплат по основной сумме кредита).

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

Источник: https://www.fd7.ru/raschet-effektivnoj-procentnoj-stavki-formula-excel/

Как рассчитывается эффективная процентная ставка

Эффективная процентная ставка формула excel
30.

12

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

Понятие эффективной ставки

Данная ставка является понятной и простой для расчета и определения.

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

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

К этим дополнительным затратам можно отнести:

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

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

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

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

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

Как осуществляется расчет

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

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

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

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

Например, заемщик оформил займ на 10 тысяч рублей, а ежегодно по нему выплачивается сверх основной суммы 1,5 тысяч рублей.

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

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

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

Использование формулы

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

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

коэффициент аннуитетного займа = месячная ставка по кредиту х (1 + месячная ставка по кредиту х количество периодов, после окончания которых будет полностью погашен займ) / (1+ месячная ставка по кредиту) х количество периодов — 1.

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

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

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

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

В этом случае полная сумма кредита равна 200 тысяч руб., количество периодов — 12 месяцев, месячная ставка по кредиту — 1,5 (ставка процента 18% деленная на срок займа, составляющий 12 месяцев).

В соответствии с имеющимися данными определяется легко коэффициент аннуитетного займа:

0,015*(1+0,015)*12/(1+0,015)*12—1=0,0917.

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

Ежемесячный взнос по кредиту =18336 р.

Дополнительно учитывается комиссия за кассовое обслуживание, равная 1%. В этом случае в год придется уплатить 24 тыс. руб., а в месяц 2 тыс. руб., соответственно, ежемесячный платеж увеличивается на эту сумму и равен 20336 р. В год придется заплатить банку 244 тыс 32 р., а переплата составит 44 тыс. 32 р. Поэтому эффективная ставка равняется 22%.

Дополнительные способы

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

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

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

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

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

Как рассчитывается эффективная ставка по вкладам

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

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

iэфф =((1+ ставка по кредиту /12)(12*число лет вклада)-1)*(1/число лет вклада).

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

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

Источник: https://WseKredity.ru/nalichnymi/vybor-poluchenie/raschet-effektivnoy-protsentnoy-stavki.html

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

Эффективная процентная ставка формула excel
66856 25.06.2014 Скачать пример

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

И главная проблема, по-моему, даже не в грабительских процентах или в том, что это “потом” все равно когда-нибудь наступит. Кредит убивает мотивацию к росту.

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

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

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. “Помассажировать числа” заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.

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

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

кредиту, где выплаты производятся равными суммами – таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial).

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

  • Ставка – процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
  • Кпер – количество периодов, т.е. срок кредита в месяцах.
  • Пс – начальный баланс, т.е. сумма кредита.
  • Бс – конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
  • Тип – способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0. 

Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:

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

Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel – ОСПЛТ (PPMT) и ПРПЛТ (IPMT).

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

Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

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

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

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

=ЕСЛИ(A17>=$C$7;””;A17+1)

Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку (“”) в том случае, если достигли, либо номер следующего периода.

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

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

=ЕСЛИ(A18″”; текущая формула; “”)

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

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

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

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

Каждый такой сценарий для наглядности лучше посчитать отдельно.

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять – не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты – заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

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

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

Предполагается что:

  • в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
  • отрицательные суммы – наши выплаты банку, положительные – берем дополнительный кредит к уже имеющемуся
  • подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)

Источник: https://www.planetaexcel.ru/techniques/11/202/

Юридическое слово
Добавить комментарий