Эффективная ставка по кредиту: что это такое, как правильно ее рассчитать

Эффективная ставка по кредиту: что это такое, как правильно ее рассчитать

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

Формы покрытия кредита

Такой серьёзный вопрос, как порядок возвращения заёмных средств, всегда оговаривается кредитно-финансовыми учреждениями заранее. Лишь после разъяснения всех нюансов клиенту кредит предоставляют в пользование.

Существует всего две формы погашения займа:

  • дифференцированный платёж;
  • аннуитетный платёж.

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

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

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

Консультант и клиенты

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

Пример вычисления NPV

Давайте рассмотрим применение данной функции для определения величины NPV на конкретном примере.

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

Переход в Мастер функций в Microsoft Excel

Мастер функций в Microsoft Excel

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

Так как в нашем случае денежные потоки размещены на листе цельным массивом, то вносить данные в остальные поля не нужно. Просто жмем на кнопку «OK».

Аргументы функции ЧПС в Microsoft Excel

Итог расчета функции ЧПС в Microsoft Excel

Добавление первоначального взноса в расчет в Microsoft Excel

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

Добавление адреса первоначального взноса в расчет в Microsoft Excel

Результат расчета NPV в Microsoft Excel

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Функция бз в excel

/>Функция БС в Excel… Очень полезная формула для людей, имеющих сбережения в банке, или располагающие другими инвестиционными формами вложение денежных средств. Думаю, увидев первый раз описание этой формулы в самом Microsoft Excel понять её предназначение довольно сложно. Однако, прочитав эту статью, Вы получите опровержение её непонятности, и подсчёт сложного процента будет решен в считанные секунды.

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

Сколько Вы думаете, у нас накопится денег через 1 год? Некоторые могут моментально ответить 1 120 $ (1000*12%=1120$) Это неправильный ответ. Другие будут ссылаться на ежемесячную капитализацию, т.е. в их понимании в первый месяц будет начислено 10$ (1000*1%), во второй 10,1 (1010*1%) и так далее. При таком подсчёте, который уже изображен на картинке получается 1126,8 $. Но это тоже не точный ответ.


Для продвинутых и любознательных, которые хотят просмотреть формулы, можно открыть / скачать Excel-файл . Всё дело в том, что процент от суммы высчитывается в банке, как правило, каждый день, а не каждый месяц. И рассчитать итоговую сумму таким методом, который указан на картинке, крайне нерационально, но можно. У нас в таком случае получится 365/366 заполненных колонок, что выглядит очень громоздко. А если нужно просчитать сумму вклада за большой период, на 5 или 10 лет!? Всё это решается в одной ячейке, при помощи функции БС.

Начинаем разбираться, как ей пользоваться. Итак, заходим в Microsoft Excel вводим формулу =БС и далее рекомендуется нажать Аргументы функции.


Вот теперь начнём заполнять поля исходя из нашего примера.


Первое поле Ставка. В нашем примере сюда вписываем 12%/365, 12 процентов – это годовая ставка, а 365 количество дней, периодов по которым считается процент. Если процент высчитывается (не путать с выплатами) один раз в месяц, то делить придётся на 12 (месяцев).

Второе поле Кпер. Это количество периодов, отображающих нахождение в банке (для нашего опять же случая). Мы положили на год или 365 дней, соответственно вводим 365.

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

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

Пятое поле Тип. Заполнение этого поля имеет значение, в случае заполнения поля Плт. Если Плт не трогали, то и Тип можно оставить не тронутым. Есть только два варианта заполнения поля 0 или 1. Ноль вводиться при выплате в начале периода, а единица при выплате в конце периода.

На этом настройка формулы БС завершена. При использовании функции БС в нашем примере накопится 1127,47 $ — это точная цифра. Вводите свои данные и получайте достоверные результаты. Успеха в инвестировании и подсчёте прибыли!

#2 Дисконтированный срок окупаемости инвестиций (DPP). Формула расчета

Дисконтированный срок окупаемости (англ. DPP, Discounted Payback Period) – период возврата денежных средств с учетом временной стоимости денег (ставки дисконта). Главное отличие от простой формулы срока окупаемости – это дисконтирования денежных потоков и приведение будущих денежных поступлений к текущему времени.

Формула расчета дисконтированного срока окупаемости инвестиции, проекта и бизнеса

DPP (Discounted Payback Period) – дисконтированный срок окупаемости инвестиций;

IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;

CF (Cash Flow) – денежный поток, создаваемый инвестицией;

r – ставка дисконтирования;

n – срок реализации проекта.

Вставка в Word

Рассмотрим 3 способа:

Напечатайте вместо знака английскую букву « D ». Выделите её и в верхнем меню на вкладке «Главная» установите шрифт «Symbol». Буква автоматически превратится в дельту.

В любом месте документа введите « 0394 » и нажмите горячие клавиши Alt и X . Цифры преобразуются в символ. Раскладка клавиатуры должна быть английской.

На полноценной клавиатуре с правым цифровым блоком клавиш можно воспользоваться следующим методом. Зажимаем Alt и набираем « 916 ». Вводить цифры надо именно на правом цифровом блоке.

Ежегодная капитализация

Д = В х (1 П)^Т, где

В — сумма вклада;

Т — срок вклада в годах.

год без капитализации с капитализацией
Деньги во вкладе Начисленные
проценты
Деньги во вкладе Начисленные
проценты
1 100 000 10 000 100 000 10 000
2 100 000 10 000 110 000 11 000
3 100 000 10 000 121 000 12 100
4 100 000 10 000 133 100 13 310
5 100 000 10 000 146 410 14 641
ИТОГО 50 000 61 051

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

Вклад процент excel

Кроме рассмотренных выше периодов начислений капитализации банки могут предлагать и другие, например, раз в полгода, раз в 10, 20, 100, 200, 400 дней. Здесь условия ограничиваются лишь фантазией банковских работников, отвечающих за депозитные программы.

MIRR Excel Функция

Модифицированная внутренняя норма доходности (MIRR): это встроенная встроенная функция в Excel, которая относится к категории Финансовые функции.

  • Это финансовый показатель инвестиций или указывает на потенциальную прибыль проекта или инвестиций
  • MIRR Функция лучше, чем внутренняя норма доходности (IRR) с точки зрения показателя эффективности инвестиций, и это более точно
  • Наиболее часто используемая функция финансовых аналитиков в инвестиционно-банковской деятельности, инвестициях в акционерный капитал, оценке проектов недвижимости и финансовой оценке
  • Функция MIRR используется как для рабочего листа, так и для функции VBA.

Определение

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

MIRR Формула в Excel

Формула для функции MIRR в Excel выглядит следующим образом:

Синтаксис или формула функции MIRR имеет следующие аргументы:

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

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

  • Finance_rate : это стоимость заимствования или процентная ставка, выплачиваемая за деньги, использованные в денежных потоках (отрицательный денежный поток)

Примечание: он также может быть введен как десятичное значение, то есть 0, 07 для 7%

  • Reinvest_rate : это процентная ставка, которую вы получаете на сумму реинвестированного денежного потока (положительный денежный поток)

Примечание: он также может быть введен как десятичное значение, то есть 0, 07 для 7%

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

MIRR FUNCTION помогает отделить отрицательные и положительные денежные потоки и дисконтировать их по соответствующей ставке.

Как использовать функцию MIRR в Excel

Функция MIRR в Excel очень проста и удобна в использовании. Позвольте понять работу MIRR в Excel на некоторых примерах.

Вы можете скачать этот шаблон Excel функции MIRR здесь — Шаблон Excel функции MIRR

Пример № 1

В приведенном ниже примере таблица содержит нижеприведенные детали.

Начальные инвестиции: 10000, ставка финансирования для MIRR составляет 9%, а ставка реинвестирования для MIRR составляет 7%

Положительный денежный поток: год 1: 4000

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

Применим функцию MIRR в ячейке «D17».

Выберите ячейку «D17», в которой необходимо применить функцию MIRR. Нажмите кнопку вставки функции (fx) под панелью инструментов формулы, появится диалоговое окно. Введите ключевое слово «MIRR» в поле поиска функции, появится функция MIRR. в выберите функциональное поле.

Двойной щелчок по функции MIRR. Появляется диалоговое окно, в котором необходимо заполнить или ввести аргументы для функции max, т.е. = MIRR (values, finance_rate, reinvest_rate

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

finance_rate: это проценты, выплачиваемые на начальную сумму или годовую процентную ставку для 10000 кредитов, т.е. 9% или 0, 09

reinvest_rate: проценты, полученные от реинвестирования чистого дохода или годовой процентной ставки, которую вы получаете за реинвестированную прибыль, т.е. 7% или 0, 07

Нажмите ОК, после ввода трех аргументов. = MIRR (D12: D15, 9%, 7%), т. Е. Возвращает Модифицированную внутреннюю норму прибыли (MIRR) инвестиций через три года. т.е. 10, 52% в клетке D17

Примечание: изначально он вернет значение 11%, но чтобы получить более точное значение, мы должны нажать на увеличение десятичного знака на 2 пункта. Так что это даст точную отдачу от инвестиций, т.е. 10, 52%

Пример № 2 — Выбор проекта на основе наибольшей доходности

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

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

Давайте применим функцию MIRR в ячейке «D34» для проекта 1 и «E34» для проекта 2.

Выберите ячейку «D34», в которой необходимо применить функцию MIRR. Нажмите кнопку вставки функции (fx) под панелью инструментов формул, появится диалоговое окно, введите ключевое слово «MIRR» в поле поиска функции, появится функция MIRR. в выберите функциональное поле.

Вышеуказанный шаг одновременно применяется в ячейке «E34»

Двойной щелчок по функции MIRR. Появляется диалоговое окно, в котором необходимо заполнить или ввести аргументы для функции max = MIRR (values, finance_rate, reinvest_rate) для проекта 1.

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

finance_rate: это проценты, выплачиваемые на начальную сумму или годовую процентную ставку для 10000 кредитов, т.е. 9% или 0, 09

reinvest_rate: проценты, полученные от реинвестирования чистого дохода или годовой процентной ставки, которую вы получаете за реинвестированную прибыль, т.е. 7% или 0, 07

Аналогичная процедура применяется для проекта 2 (ссылка: ниже упомянутый скриншот )

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

Для проекта 1

= MIRR (D27: D32, 9%, 7%), т. Е. Возвращает Модифицированную внутреннюю норму прибыли (MIRR) инвестиций через три года. т.е. 6, 34% в клетке D34

Для проекта 2

= MIRR (E27: E32, 9%, 7%), т. Е. Возвращает Модифицированную внутреннюю норму прибыли (MIRR) инвестиций через три года. т.е. 5, 74% в клетке E34

Вывод: на основании расчета MIRR, проект 1 предпочтительнее, он дал лучшую отдачу по сравнению с проектом 2

Что нужно помнить о MIRR в Excel

  • Значения должны содержать как минимум одно отрицательное значение и одно положительное значение для расчета измененной внутренней нормы прибыли. В противном случае MIRR возвращает # DIV / 0! ошибка
  • Если массив или ссылочный аргумент содержат пустые ячейки, текстовые или логические значения, эти значения игнорируются
  • # ЗНАЧЕНИЕ! ошибка возникает, если любой из предоставленных аргументов не является числовым значением или не числовым
  • Начальные инвестиции должны иметь отрицательное значение, в противном случае функция MIRR вернет значение ошибки ( # DIV / 0! Error)
  • Функция MIRR учитывает как стоимость инвестиций (finance_rate), так и процентную ставку, полученную при реинвестировании денежных средств (reinvest_rate).
  • Основное различие между MIRR и IRR заключается в том, что MIRR учитывает проценты, полученные от реинвестирования денежных средств, тогда как IRR не учитывает их.

Рекомендуемые статьи

Это было руководство к MIRR в Excel. Здесь мы обсуждаем формулу MIRR в Excel и как использовать функцию MIRR в Excel вместе с примером Excel и загружаемыми шаблонами Excel. Вы также можете посмотреть на эти полезные функции в Excel —

Как правильно рассчитать ЭКС

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

В «домашних условиях» не получится подсчитать ЭКС без специальных экономических знаний. В интернете предлагается большое количество онлайн-калькуляторов. Они рассчитывают все необходимые входные параметры (их предоставляет менеджер финансовой организации) и выдают итоговые цифры для сравнения предложений от банков.

Формула

Для ручного подсчета используется одна из несложных формул:

Эфст = (С/Спк — 1)/(полный срок кредитования/12)*100,

где:

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

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

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

Пример 1: сумма кредита — 60 тыс. руб., срок — 12 месяцев, размер ставки — 23% годовых. Заемщик выплатил займ вовремя, не допуская задержек. Общая сумма внесенных платежей составила 73 800 руб. Залог не был предусмотрен в договоре. Банк не брал никаких дополнительных платежей ни за открытие счета, ни за его обслуживание, не было и страховых полисов. Формула будет выглядеть так:

Эфст = (73800/60000 – 1)/(12/12)*100 = 23%

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

Пример 2: условия кредита, как в 1 примере, но банк берет 2% за выдачу первоначальной суммы и еще 3% — за обналичивание. Это приводит к увеличению всех платежей до 76,8 тыс. руб. Теперь расчет выглядит так:

Эфст = (76800/60000 – 1)/(12/12)*100 = 28%

Как видно, ЭКС возросла до 28%, что удорожает пользование кредитными деньгами. Таких «дополнительных» платежей бывает много, что влечет увеличение переплаты. Поэтому обязательно просите дать полный перечень комиссий и только тогда принимайте решение.

Расчет ЭКС в Excel

Для расчета ЭКС в табличном редакторе есть несколько функций:

  • ЭФФЕКТ (EFFECT);
  • ЧИСТВНДОХ (XIRR);
  • ПЛТ (PMT).

Подробное описание этих функций есть в справочной службе Excel и интернете.

Рассмотрим расчет функции ЭФФЕКТ для выяснения эффективности ставки по кредиту.

Клиент взял 70 тыс. руб. на 24 месяца под 20% годовых без процентов за обналичивание и выдачу займа.

Функция ЭФФЕКТ подходит только, когда в договоре не предусмотрены дополнительные сборы. Для ее расчета прописывается формула. Для этого в любой пустой ячейке таблицы ставится знак «=» и в строке ввода выбирается «Мастер функций». Далее в группе «Финансы» используется функция ЭФФЕКТ или в поиске прописывается это значение. Ее выделяют и в поле аргументов вводят следующие параметры:

  • номинальная ставка — годовая ставка, озвученная банком (в нашем случае она равна 20% — указываем 0,2);
  • количество периодов — количество месяцев, на которое берется кредит (проставляем 24).

После нажатия клавиши «Ввод» или Enter на клавиатуре получаем значение 0,22. Это и есть эффективная ставка по кредиту.

Пример расчета прилагается в виде таблицы Excel.

МСФО, Дипифр

per cent_mВ современном мире часто выигрывает не тот, кто может произвести лучший по качеству товар, а тот, кто сможет выдать свой товар за самый качественный и самый выгодный. Ничего не стоит обмануть потребителя, поэтому информация часто подвергается искажению. Так поступают банки, пользуясь финансовой неграмотностью населения, чтобы впарить этому населению кредиты по заоблачным процентным ставкам. Единственный способ не быть обманутым при взаимодействии с банками — это научиться считать эффективную процентную ставку как по кредитам, так и по вкладам. Что такое эффективная процентная ставка, было написано в предыдущей статье. Как ее рассчитать для банковского кредита с помощью Excel, написано ниже.

Как банки маскируют полную стоимость кредита

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

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

Все эти дополнительные выплаты нужны банкам для того, чтобы «понизить» ставку процента по кредиту и выглядеть на рынке банковских услуг более привлекательно. Гипотетический пример такого поведения в розничной торговле можно обрисовать таким образом. Скажем, шампанское в среднем по городу стоит 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.

effective rate

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

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

Effect function

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

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

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

Пример 2.

efrate1_1

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

effect3

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

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

effect4

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

effect5

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чистая приведенная стоимость проекта

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

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

  • cтоимость капитала инвестируемого объекта;
  • кредитная ставка банков в финансовом окружении;
  • среднеотраслевая доходность капитала для инвестируемого объекта;
  • доходность финансовых инструментов на фондовом рынке;
  • внутренняя норма доходности.

Денежные притоки на инвестируемый объект в виде денежных поступлений NV рассчитываются так:

  • CIt – инвестиции за весь жизненный цикл проекта;
  • CFt – денежные поступления за весь жизненный цикл проекта;
  • n – жизненный цикл инвестиций.

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

Рис. 1. Денежный поток инвестиций и доходов.

Для расчетов чистой приведенной стоимости денежные потоки подвергаются дисконтированию по ставке r.

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

  • ICt – приток инвестиций в период от i=0 до T;
  • CFt – денежный поток от инвестиций в t–год;
  • n – длительность жизненного цикла инвестиций;
  • r – норма дисконтирования.

Если вложения делаются одномоментно, то формула приобретает вид:

где ICo – первоначальные инвестиции.

Для упрощения расчетов NPV частное от деления

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

  • служит критерием целесообразности вложений в данный инвестируемый объект,
  • оценочным показателем при выборе вариантов;
  • абсолютным показателем будущей доходности инвестиций.
Читайте также:  Макрос для поиска ячеек в Excel с выпадающим списком и условным форматированием

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

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

  • 1 инвестиционный объект со стоимостью капитала на уровне 25%;
  • 2 инвестиционный объект на уровне 15%;
  • Срок жизни инвестиций – 3 года;
  • Размер первоначальных инвестиций 60 млн. рублей;
  • Среднеотраслевая доходность предприятий данной отрасли 14%.

Поступления дохода от инвестиций:

  1. Для 1 объекта: 1 год – 27 млн.рублей; 2 год – 33 млн. рублей; 3 год 35 млн. рублей.
  2. Для 2 объекта: 1 год – 27 млн.рублей; 2 год – 33 млн. рублей; 3 год 35 млн. рублей.

Для 1 объекта норма дисконтирования в 14% неприемлема, так как инвестиционный проект снизит стоимость его капитала, поэтому она может быть не ниже 25%. Рассчитаем NPV при такой норме дисконта: NPV =-60 +27/1,25 + 33/1,5625 + 35/1,953 = -60 + 21,6 + 21,12 + 18,14 = 0,86.

Для 2 объекта: NPV =-60 +27/1,15 + 33/1,322 + 35/1,52 = -60 + 23,47 + 24,96 + 23,02 = 11,45.

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

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

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

Как при помощи Excel считать величину дисконтированного (приведенного) денежного потока

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

Анализ затрат

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

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

Период окупаемости инвестиций

Период окупаемости инвестиций — это количество времени, которое требуется для того, чтобы окупились первоначальные инвестиции в данный проект. Совокупная стоимость проекта сравнивается с получаемыми доходами и вычисляется время, которое требуется для того, чтобы полученные доходы превысили затраты на реализацию данного проекта. Когда выполняется сравнение двух или большего числа проектов сходного масштаба и сложности, как правило, выбирается проект с наименьшим периодом окупаемости инвестиций. У этого метода нет «универсальной» формулы, которая позволяла бы быстро найти требуемое решение. Если, например, себестоимость проекта равняется 100 000 долл., а ожидаемые доходы составляют 25 000 долл. в квартал, то период окупаемости инвестиций составит один год.

Дисконтированные (приведенные) денежные потоки

Если вам предложат 1 000 долл. сегодня или те же 1 000 долл. через два года, какой вариант вы предпочтете? Ответ предсказуем, поскольку вложив сейчас эту сумму в банк или какое-либо предприятие, через два года вы будете иметь с нее прибыль. Например, под 6% годовых такая инвестиция на двухлетний период составит 1 123,60 долл. (в нынешних долларах, разумеется).

Метод дисконтированного (приведенного) денежного потока сравнивает стоимость будущих денежных потоков с нынешними долларами. Иными словами, он выполняет операцию, противоположную той, которую мы только что объяснили. Зная, что ваш проект принесет через два года сумму, равную 1 123,60 долл. (это так называемая будущая стоимость — Future Value, или FV), вы бы смогли с помощью метода дисконтированного (приведенного) денежного потока определить нынешнюю стоимость этой суммы. Ответ, конечно же, таков: 1 000 долл.

Чтобы иметь представление о дисконтированных денежных потоках, вы должны знать стоимость соответствующих инвестиций в нынешних долларах, иначе говоря, приведенную стоимостью (Present Value, или PV), которая вычисляется следующим образом: PV=FV/(1+i) n . Эта формула говорит о том, что приведенная стоимость равняется будущей стоимости инвестиций, деленной на один, плюс процентная ставка, возведенная в степень, равную количеству периодов, на которые мы инвестируем нашу сумму.

Вам не нравится математика? Но это же так просто! В Excel предусмотрена встроенная функция для вычисления приведенной стоимости (наряду со множеством других функций, позволяющих выполнять финансовые расчеты). На рисунке ниже показана группа Function Library (Библиотека функций), предусмотренная на вкладке Formulas (Формулы), и часть списка финансовых функций, встроенных в Excel.

Рис. 1. Финансовые функции, представленные в библиотеке функций и перечень финансовых функций

Рис. 1. Финансовые функции, представленные в библиотеке функций и перечень финансовых функций

Вернемся, однако, к нашей формуле для вычисления приведенной стоимости инвестиций. Выберите в списке функций элемент PV (в русифицированной версии Excel — ПС (Приведенная стоимость)). На экране появится диалоговое окно Function Arguments (Аргументы функции), показанное на рис. 2.

Рис. 2. Диалоговое окно Function Arguments для функции PV

Рис. 2. Диалоговое окно Function Arguments для функции PV

Диалоговое окно Function Arguments предназначено для ввода значений отдельных элементов выбранной вами функции, которые необходимы для вычисления приведенной стоимости. В текстовом поле Rate (Ставка) этого диалогового окна следует ввести величину процентной ставки за определенный временной период. Вы можете ввести 6% или 0,06 (предполагается, что процент начисляется ежегодно по методу сложных процентов). Если бы процент начислялся ежеквартально (по тому же методу), тогда вам нужно было бы разделить указанную величину процентной ставки на 4, а затем ввести полученный результат в поле Rate (Ставка).

Ниже находится поле Nper (Кпер), в котором вводят количество временных периодов. Мы инвестируем нашу сумму на два года. Величина выплаты (поле Pmt (Плт)) равняется 0, поскольку мы не производим выплат по этой инвестиции, а просто хотим знать величину всей этой суммы в нынешних долларах. Далее находится поле FV (Бс), в котором вводят значение будущей стоимости. В нашем примере будущая стоимость инвестиции равняется -1 123,60 долл. Если в поле FV (Бс) ввести положительное число, то результат вычисления этой функции будет отрицательным. На рис. 3. показано диалоговое окно Function Arguments со значениями аргументов функции PV (Приведенная стоимость), введенных в соответствующие поля.

Рис. 3. Аргументы функции PV

Рис. 3. Аргументы функции PV

Вместо числовых значений в полях диалогового окна Function Arguments (Аргументы функции) можно дать адрес ячейки, в которой введено нужное вам значение. Предположим, например, что в ячейке С1 введено число 0,06. В этом случае в текстовом поле Rate (Процентная ставка) диалогового окна Function Arguments достаточно указать только адрес упомянутой выше ячейки, т.е. С1. Непосредственно под текстовыми полями диалогового окна Function Arguments представлен результат наших вычислений функции PV (Приведенная стоимость). В нашем случае PV=1000. Помимо диалогового окна Function Arguments аргументы данной функции отображены в строке формул программы Excel, а также в активизированной ячейке (А1 в данном случае) (см. рис. 3.).

Как видите, сначала следует значение процентной ставки, затем количество периодов и будущая стоимость. Обратите внимание, что в данной функции отсутствует значение между двумя запятыми. Это означает, что один из аргументов функции равен нулю (в нашем случае величина выплаты (поле Pmt (Плт)). (В русифицированной версии программы Excel аргументы функций следует отделять друг от друга точкой с запятой (;)) Как только вы
щелкнете на кнопке ОК, в ячейке А1 появится результат вычисления функции, в нашем случае — 1 000 долл.

Для того чтобы воспользоваться функцией PV (ПС), не обязательно перебирать ряд интерфейсных элементов программы. Для этого достаточно просто ввести =pv() в ячейке А1. В результате ваших действий на экране появится экранная подсказка, в которой приведен синтаксис данной функции, т.е. сокращенные названия и очередность ее аргументов (рис. 4).

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

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

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

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

Допустим, что ваш комитет по отбору проектов рассматривает три проекта, из которых необходимо выбрать самый подходящий. Ожидается, что проект А принесет через два года 130 000 долл. прибыли; проект В — 140 000 долл. через три года; а проект С — 148 000 долл. через четыре года. Какому из этих проектов должен отдать предпочтение комитет, если свое решение он основывает лишь на использовании метода дисконтированного (приведенного) денежного потока, полагая, что процентная ставка равняется 8%? Самую высокую прибыль обеспечивает проект А. На рис. 5 показаны расчетные формулы по каждому проекту и полученные с их помощью результаты.

Рис. 5. Сравнение проектов с помощью метода дисконтированного (приведенного) денежного потока

Рис. 5. Сравнение проектов с помощью метода дисконтированного (приведенного) денежного потока

Заключение

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

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

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