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

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

Подробнее про формулу

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

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

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

https://www.youtube.com/watch?v=ytadvertiseru

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

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

Пример вычисления ставки EAR с разной периодичностью начисления процентов.

Используя заявленную ставку 6%, вычислите EAR при полугодовом, ежеквартальном, ежемесячном и ежедневном начислении процентов.

Решение.

EAR при полугодовом начислении процентов =(1 0.03)2 — 1 = 1.06090 — 1 = 0.06090 = 6.090%.

EAR при квартальном начислении процентов =(1 0.015)4 — 1 = 1.06136 — 1 = 0.06136 =6.136%.

EAR при ежемесячном начислении процентов =(1 0.005)12 — 1 = 1.06168 — 1 = 0.06168 = 6.168%.

EAR при ежедневном начислении процентов =(1 0.00016438)365 — 1 = 1.06183 — 1 = 0.06183 = 6.183%.

Обратите внимание на то, что EAR увеличивается с увеличением периодичности начисления.

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

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

Джон планирует инвестировать $2 500 на счет, который будет приносить 8% годовых с ежеквартальным начислением.

Сколько денег будет на счете через два года?

Решение.

8% / 4 = 2%.

2 500 * (1.02)8 = $ 2 929.15.

1,024 — 1 = 0,082432.

2 500 * (1.082432)2 = $2 929.15,

что является тем же результатом.

Алиса хотела бы, чтобы ее банковский вклад на счете вырос до $5 000 за 3 года. Если заявленная доходность вклада составляет 9% годовых с ежемесячным начислением процентов, то какой должна быть сумма первоначального вклада, чтобы Алиса достигла своей финансовой цели через 3 года?

Решение.

9% / 12 = 0,75%.

5 000 / (1.0075)36 = $3 820,74.

1.007512 — 1 = 0,093807.

5,000 / 1.0938073 = $3,820.74,

что дает тот же результат.

Третьим альтернативным показателем доходности является доходность на денежном рынке(англ. ‘money market yield’, также известная как ‘CD equivalent yield’).

Кредит.

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

rMM = (HPY)(360 / t).

rMM = (rBD)(F / P0).

(mathbf{r_{MM} = {360r_{BD} over 360 — (t)(r_{BD})} })   (формула 6)

https://www.youtube.com/watch?v=upload

rMM = (360)(0.048)/[360 — (150) (0.048)] = 4.898%

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

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

Доходность дисконтных векселей Казначейства Германии со сроком погашения менее 1 года и французских векселей BTF рассчитывается по формуле денежного рынка с учетом 360-дневного года.

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

Три наиболее часто используемых показателя урожайности.

Доходность за период владения (HPY)

Эффективная годовая доходность (EAY)

Доходность денежного рынка (CD equivalent yield)

(mathbf{HPY = {P_1 — P_0 D_1 over P_0} })

EAY = (1 HPY)365/t — 1

(mathbf{r_{MM} = {360r_{BD} over 360 — (t)(r_{BD})} })

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

Следующий пример поможет вам закрепить ваши знания об этих показателях доходности.

Вам необходимо найти текущую стоимость (PV) денежного потока в $1,000, который должен быть получен в течение 150 дней.

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

Таблица ниже представляет эту информацию.

Краткосрочная доходность денежного рынка.

Доходность за период владения

2.0408%

Банковская дисконтная доходность

4.8%

Доходность денежного рынка

4.898%

Эффективная годовая доходность

5.0388%

Предлагаем ознакомиться  СП 9.13130.2009 Техника пожарная. Огнетушители. Требования к эксплуатации, СП (Свод правил) от 25 марта 2009 года №9.13130.2009

https://www.youtube.com/watch?v=ytpolicyandsafetyru

Какие показатели доходности подходят для определения текущей стоимости $1,000, которые будут получены через 150 дней?

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

Доходность за период владения (2.0408%). Эта доходность — именно то, что нам нужно.

Поскольку она применяется к 150-дневному периоду, мы можем использовать ее напрямую, чтобы найти текущую стоимость $1,000, которые будут получены в течение 150 дней. (Вспомните принцип, в соответствии с которым ставки дисконтирования должны быть совместимы с периодом времени.)

PV = $1,000 / 1.020408 = $980.00

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

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

Банковская дисконтная доходность (4.8%). Мы не должны использовать этот показатель доходности для определения приведенной стоимости денежного потока. Как упоминалось ранее, банковская дисконтная доходность основана на номинальной стоимости векселя, а не на его цене.

Доходность денежного рынка (4.898%). Чтобы использовать доходность денежного рынка, нам нужно преобразовать ее в доходность за 150-дневный период, разделив ее на (360/150).

После получения HPY = 0.04898/(360/150) = 0.020408 мы используем ее для дисконтирования $1,000, как указано выше.

Эффективная годовая доходность, EAY (5.0388%). Эта доходность также была пересчитана в годовое исчисление, поэтому ее необходимо скорректировать, чтобы она соответствовала срокам движения денежных средств.

(1.050388)150/365 — 1 = 0.020408

Аргументы.

Напомним, что когда мы нашли EAY, экспонента составляла 365/150, или количество 150-дневных периодов в 365-дневном году. Чтобы уменьшить эффективную годовую доходность до 150-дневной доходности, мы используем обратную величину показателя, который мы использовали для пересчета в годовое исчисление.

https://www.youtube.com/watch?v=ytcreatorsru

В предыдущем примере мы преобразовали два краткосрочных показателя годовой доходности в доходность за 150-дневный период владения. Это — один из способов пересчета.

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

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

Если полугодовая доходность к погашению составляет 4%, как мы можем конвертировать в годовую доходность?

Точный подход (с учетом начисления сложного процента) состоит в том, чтобы вычислить (1.04)2 — 1 = 0.0816 или 8.16%. Это то, что мы называем эффективной годовой доходностью.

Однако подход, используемый на рынках облигаций США, заключается в удвоении полугодовой доходности к погашению: 4% * 2 = 8%.

Доходность к погашению, рассчитанная таким образом, без учета начисления процентов, называется доходностью в эквиваленте облигаций (англ. ‘bond equivalent yield’).

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

Банковские предложения по кредитам нельзя сравнивать исключительно по процентной ставке. Этот показатель может оказаться не только не информативным, но и привести к неправильной оценке аналогичных по сути кредитных программ. Не всегда 18% и 20% по займу в двух разных банках будут говорить в пользу первого учреждения. Так происходит из-за того, что:

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

https://www.youtube.com/watch?v=ytcopyrightru

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

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

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

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

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

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

Пример расчета банковской дисконтной доходности.

Предположим, что у нас есть вексель с номинальной стоимостью $100,000 и сроком погашения 150 дней по цене $98,000.

Какова будет банковская дисконтная доходность векселя?

(mathbf{r_{BD} = frac{$2,000}{$100,000} frac{360}{150} = 4.8%})

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

(mathbf {D = r_{BD}F frac{t}{360}})

D = 0.048 * $100,000 * 150/360 = $2,000

F — D = $100,000 — $2,000 = $98,000.

Доходность на основе банковского дисконта не является значимым показателем доходности инвесторов по трем причинам.

  • Во-первых, эта доходность основана на номинальной стоимости, а не на цене покупки инструмента. Доходы от инвестиций должны оцениваться относительно суммы, которая инвестируется.
  • Во-вторых, доходность рассчитывается на основе 360-дневного года, а не 365-дневного.
  • В-третьих, банковская дисконтная доходность аннуализируется с начислением простого процента, то есть игнорирует возможность зарабатывать проценты по процентам (сложный процент).

https://www.youtube.com/watch?v=ytdevru

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

С чем связано это предпочтение правила NPV?

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

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

Предположим, что у компании есть только €30,000 для инвестирования.

Значения.

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

Компания располагает двумя инвестиционными проектами с одинаковым периодом, описанными как A и B в таблице ниже.

IRR и NPV для взаимоисключающих проектов разного размера

Проект

Инвестиции при t = 0

Денежный поток при t = 1

IRR (%)

NPV при ставке 8%

A

-€10,000

€15,000

50

€3,888.89

B

-€30,000

€42,000

40

€8,888.89

Проект А требует немедленных инвестиций в размере €10,000. Этот проект предполагает единовременный денежный платеж в размере €15,000 при t = 1.

Поскольку IRR — это ставка дисконтирования, которая приравнивает текущую стоимость будущего денежного потока к стоимости инвестиций, IRR составляет 50%.

Если мы предположим, что альтернативная стоимость капитала составляет 8%, то NPV проекта А составит €3,888.89. Мы рассчитываем IRR и NPV проекта B как 40% и €8,888.89 соответственно.

Правила IRR и NPV указывают, что мы должны принять оба проекта, но для этого нам потребуется €40,000 — больше денег, чем доступно. Таким образом, мы должны ранжировать эти проекты.

Как рассчитать реальную эффективную ставку

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

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

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

https://www.youtube.com/watch?v=ytaboutru

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

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

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

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

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

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

ЭФФЕКТ.

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

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

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

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

ОВГЗ – облигации внутреннего государственного займа. Их можно сравнить с депозитами в банке. Так как точно также вкладчик получает возврат всей суммы вложенных средств плюс дополнительный доход в виде процентов. Гарантом сохранности средств выступает центральный банк.

Ежемесячная эффективная ставка.

Предположим, что проценты капитализируются ежемесячно. Поэтому 17% делим на 12. Результат в виде десятичной дроби вносим в поле «Ставка». В поле «Кпер» вводим число периодов капитализации. Ежемесячные фиксированные выплаты получать не будем, поэтому поле «Плт» оставляем свободным. В графу «Пс» вносим сумму вложенных средств со знаком «-».

1 – сверху вниз указываются месяцы от 1 до 36;

2 — (В4) вписывается в строку сумма вклада – 50 000 руб.;

3 – (С4) указывается % — 8;

4 – (D4) вставляется формула для расчета ежемесячных %: =B4*$C$4/12, в которой В4 – сумма вклада, С4 -% (нужно проставлять значок $, чтобы формула выбирала данное поле, или путем выделения графы С4 курсором с нажатием клавиши F4 на клавиатуре), 12 – месяцы (% высчитывается в годовых);

5 – (Е4) считается новая сумма вклада, которая будет использована для начисления процента. Нужно написать формулу =B4 D4, в которой В4 – сумма вклада, D4 – сумма %, которые были начислены. Это будет новая сумма вклада, исходя из которой начисляются %.

В графу В5 заносится формула = Е4, в которой Е4 – это сумма вклада на истекший месяц с процентами.

Далее нужно скопировать формулы:

  • подвести курсор к углу ячейки В5, он изменится с белого плюса на черный;
  • потянуть его вниз, произойдет автоматическое копирование формулы из этой ячейки в другие;
  • эту же операцию нужно выполнить с формулами, вписанными в ячейки D4, E4.
  • В итоге, если все выполнено правильно, должен получиться ответ 63 512 руб.

https://www.youtube.com/watch?v=ytpressru

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

  • сумму;
  • срок;
  • дату начала размещения вклада;
  • % ставку;
  • период капитализации;
  • пополнение (если возможно).

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

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

Наверх
Adblock detector