Как посчитать эффективную ставку в 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

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

ЧИСТВНДОХ()

.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



Чем отличается лизинг от кредита

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

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

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

Входные данные:

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

Аргументы функции:

Эффективная ставка по лизингу составила 23,28%.

Расчет эффективной ставки по ОВГЗ в Excel

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

Эффективная ставка позволяет оценить настоящий доход, т.к. учитывает капитализацию процентов. Для примера «приобретем» годичные облигации на сумму 50 000 под 17%. Чтобы рассчитать свой доход, используем функцию БС:

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

Скачать пример расчета эффективной процентной ставки в 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 руб.

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

• первый столбец — нумерация месяцев;
• второй — дата погашения в каждом месяце;
• третий — сумма ежемесячного погашения.

И так до окончания срока действия кредита.

После этого в любой свободной ячейке программы вводим значение: =ЧИСТВНДОХ (значения; даты). Значения — суммы платежей, а даты — расписание погашений в каждом месяце.

После того, как набрали =ЧИСТВНДОХ, выделяем в таблице весь столбец с суммами платежей. Не выделяя при этом название этого столбца. Иначе расчет не получится. Также выделяем столбец с датами. Затем закрываем скобку в формуле, нажимаем на Enter. Полученное значение умножаем на 100%.

В нашем примере сумма получится сумма 0,40244. Умножаем ее на 100%. Получаем 40,2%. Эта и будет эффективная процентная ставка по кредиту.

Специальный калькулятор для расчета ЭКС

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

Вот пример одного из них.

Программа предлагает проводить расчеты по двум схемам:

• классической;
• аннуитетной.

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

Итоги

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

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

​Смотрите также​ получил бы 310899,1​ банк под 16%​ либо срок. Каждый​

​ может посчитать ту​ по кредиту, переплату,​Способ 2. Функция ПРПЛТ()​
​ nper, pv, start_period,​ПС​ США, чтобы можно​ годовых. Пока задолженность​​ это нужно записать​​ ежемесячных платежей в​ погашения (1год) приведен​​ одному моменту времени.​​Сумма кредита -​ рассуждения, что и​Рассчитаем в MS EXCEL​​ рублей, то есть​​ годовых (номинальная ставка),​ такой сценарий для​ часть, которая придется​ сроки и т.д.​Функция ПРПЛТ (ставка; период;​

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

​ end_period, type) returns​БС​ было откладывать по​ не будет погашена​​ ее в виде​​ этот период самые​ расчет для 2-х​ Вспомнив формулу Эффективной​​ 250 тыс. руб.,​​ для годовой ставки:​ эффективную годовую процентную​ больше денег, несмотря​ при этом расчете​ наглядности лучше посчитать​ на проценты банку.​ «Помассажировать числа» заранее,​
​ кпер; пс; [бс];​ the CUMulative Interest​Рассчитаем в MS EXCEL​ 175,00 долларов США​ полностью, вы не​ ПРОЦПЛАТ(ставка;период-1;кпер;пс), т.е. «сдвинуть»​ большие. Но постепенно,​ различных графиков погашения​ ставки по кредитам,​ срок — 1​S = Р*(1+i/m)^(3*m)​ ставку и эффективную​ на более низкую​ производится с использованием​
​ отдельно.​ Добавим к нашему​ как я это​ [тип])​
​ paid on a​ сумму процентов, которую​ в месяц и​ сможете рассчитываться картой​ вычисления на 1​
​ с уменьшением остатка​ (сумма кредита 250​ увидим, что для​

​ год, дата договора​ – для сложных​ ставку по кредиту.​ номинальную процентную ставку.​ сложных процентов (эффективная​В случае уменьшения срока​ предыдущему примеру небольшую​ называю Microsoft​используется для вычисления​ loan between start_period​
​ необходимо выплатить за​

​ собрать 8500 долларов​​ за покупки.​ период раньше (см.​ ссудной задолженности, уменьшается​ т.р., срок =1​ всех платежей по​ (выдачи кредита) –​

​ процентов, где Р​Эффективная ставка возникает, когда​Функция имеет следующий синтаксис:​

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

​ ставка). По условиям​ придется дополнительно с​ шапку таблицы с​ Excel может сильно​ сумм идущих на​ and end_period.​ определенное количество периодов​ США за три​
​С помощью функции ПЛТ(ставка;КПЕР;ПС)​
​ файл примера).​ и сумма начисленных​
​ год, выплаты производятся​ кредитам рассчитывается их​ 17.04.2004, годовая ставка​ – начальная сумма​
​ имеют место Сложные​=ЭФФЕКТ(номинальная_ставка;кол_пер)​ договора вкладчик сможет​ помощью функции​ подробным расчетом и​
​ помочь в этом​ погашение процентов за​Аргументы функции:​ по кредиту. Выплата​ года.​=ПЛТ(17%/12;2*12;5400)​Функция ПРОЦПЛАТ() начисленные​ процентов по кредиту.​
​ ежемесячно, ставка =​ приведенная стоимость к​ – 15%, число​ вклада.​ проценты.​Описание аргументов:​ снять только полученные​

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

​ЕСЛИ (IF)​ номера периодов (месяцев):​ вопросе.​ ссуду используется с​Ставка — обязательный​ кредита производится равными​Аргумент «Ставка» составляет 1,5%/12.​получаем ежемесячный платеж в​ проценты за пользование​ Выплаты по кредиту​ 15%).​ моменту выдачи кредита.​ платежей в году​S = 3*Р*(1+iэфф)​Понятие эффективная ставка​
​номинальная_ставка – обязательный аргумент,​ проценты. Определить сумму​проверять — не​Функция​Для быстрой прикидки кредитный​ теми же аргументами,​ аргумент. Процентная ставка​ ежемесячными платежами (аннуитетная​Аргумент КПЕР составляет 3*12​ размере 266,99 долларов​ кредитом указывает с​ значительно сокращаются и​В случае дифференцированных платежей​ И, если мы​ по аннуитетной схеме​ – для простых​ встречается в нескольких​ характеризующий числовое (десятичная​
​ к получению, если​ достигли мы нулевого​ОСПЛТ (PPMT)​ калькулятор в Excel​ что и ОСПЛТ().​ за период.​ схема). Процентная ставка​ (или двенадцать ежемесячных​ США, который позволит​ противоположным знаком, чтобы​ становятся не такими​ Эффективная ставка по​
​ хотим взять в​ – 12 (ежемесячно).​ процентов (ежегодной капитализации​ определениях. Например, есть​ дробь) или процентное​ размер депозита –​ баланса раньше срока:​в ячейке B17​ можно сделать за​Примечание​Кол_пер — обязательный​ и величина платежа​ платежей за три​ погасить задолженность за​ отличить денежные потоки​ обременительными для заемщика.​ кредиту = 16,243%,​ 2-х банках одну​ Дополнительные расходы –​ не происходит, проценты​ Эффективная (фактическая)​
​ значение номинальной годовой​ 1 млн. рублей,​А в случае уменьшения​ вводится по аналогии​ пару минут с​.​ аргумент (кол_пер –​ — известны, начисление​

​ года).​​ два года.​ (если выдача кредита​Примечание​
​ а в случае​ и туже сумму,​ 1,9% от суммы​ начисляются раз в​годовая​ ставки;​ капитализация – ежемесячная.​ выплаты — заново​ с​ помощью всего одной​Английский вариант функции:​ это аргумент кпер​ процентов за пользование​Аргумент ПЛТ составляет -175​Аргумент «ставка» — это процентная​ – положительный денежный​

​. При расчете кредита​ аннуитета – 16,238%.​ то стоит выбрать​ кредита ежемесячно, разовая​ год (всего 3​
​процентная ставка, есть​кол_пер – обязательный аргумент,​Исходные данные:​ пересчитывать ежемесячный взнос​

​ПЛТ​ функции и пары​ IPMT(rate, per, nper,​ в других функциях​ кредитом – также​ (необходимо откладывать по​

​ ставка на период​ поток («в карман»​ дифференцированными платежами сумма​ Разница незначительная, чтобы​ тот банк, в​ комиссия – 3000р.​ раза) всегда на​ Эффективная ставка​

​ характеризующий числовое значения​Формула для расчета:​ начиная со следующего​​в предыдущем примере:​​ простых формул. Для​ pv, [fv], [type]),​​ аннуитета, например в​​ ежемесячное.​ 175 долларов США​ погашения кредита. Например,​ заемщика), то регулярные​ переплаты по процентам​
​ на ее основании​ котором получается наименьшая​ при открытии банковского​ первоначальную сумму вклада).​по вкладу​ числа периодов за​
​=ЭФФЕКТ(B3;B2)*B4​ после досрочной выплаты​Добавился только параметр​ расчета ежемесячной выплаты​ т.е. Interest Payment​ ПЛТ()). Общее количество​Аннуитетная схема предусматривает погашение​ в месяц).​
​ в данной формуле​ выплаты – отрицательный​ будет ниже, чем​ принимать решение. Необходимо​ приведенная стоимость всех​ счета.​Если срок вклада​(с учетом капитализации),​ год, на протяжении​Описание аргументов:​ периода:​Период​ по аннуитетному кредиту​ – выплата процентов.​ периодов выплат.​ кредита периодическими равновеликими​Аргумент БС (будущая стоимость)​ ставка 17% годовых​
​ поток «из кармана»).​ при аннуитетных платежах.​ определиться какой график​ наших платежей в​Сначала составим График платежей​ =1 году, то​ есть Эффективная процентная​ которых начисляются сложные​B2 – число периодов​Существуют варианты кредитов, где​с номером текущего​ (т.е. кредиту, где​Чтобы вычислить сумму процентов,​Нз — обязательный​ платежами (как правило,​ составляет 8500.​ делится на 12​Выведем формулу для нахождения​ Не удивительно, что​ погашения больше Вам​ погашение кредита. Почему​ по кредиту с​ Эффективная ставка по​ ставка​ проценты.​ капитализации;​ клиент может платить​ месяца (выплаты) и​ выплаты производятся равными​ которые были выплачены​ аргумент (нз –​ ежемесячными), которые включают​Расчет срока погашения потребительского​ — количество месяцев​ суммы процентов, начисленных​ сегодня практически все​ подходит.​ же тогда не​ учетом дополнительных расходов​ вкладу = Эффективной​по потребительским кредитам​Примечания 1:​B3 – номинальная ставка;​ нерегулярно, в любые​ закрепление знаком $​ суммами — таких​ в промежутке между​ это аргумент пс​
​ как выплату основного​ кредита​ в году.​ за определенное количество​ российские банки применяют​При увеличении срока кредита​ сравнивают более понятные​ (см. файл примера​ (фактической) годовой процентной​. Разберемся, что эти​Аргумент кол_пер может принимать​B4 – сумма вклада.​ произвольные даты внося​ некоторых ссылок, т.к.​ сейчас большинство) в​​ двумя периодами, нужно​​ в других функциях​ долга, так и​Представьте, что вы взяли​Аргумент КПЕР 2*12 — это​ периодов с даты​ в расчетах аннуитетную​ разница между Эффективными​ приведенные стоимости, а​ Лист Кредит).​ ставке (См. файл​ ставки из себя​ дробные числа, значения​Результат расчетов:​ любые имеющиеся суммы.​ впоследствии мы эту​ Excel есть специальная​ использовать не одну,​ аннуитета, например в​ процентный платеж за​ потребительский кредит на​

​ общее количество периодов​​ начала действия кредитного​​ схему погашения кредита.​ ставками практически не​ используют Эффективную ставку?​Затем сформируем Итоговый​ примера).​ представляют и как​ которых будут усечены​Для сравнения, доход от​ Процентная ставка по​ формулу будем копировать​

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

​ функция​ а несколько функций​ ПЛТ()). Начальное значение​ пользование кредитом. Такой​ сумму 2500 долларов​ выплат по кредиту.​ договора. Запишем суммы​ Сравнение двух графиков​ изменяется (см. файл​ А для того,​ денежный поток заемщика​Эффективная ставка по вкладу​ их рассчитать в​ до целого числа​ вклада при использовании​ таким кредитам обычно​ вниз. Функция​ПЛТ (PMT)​ ПРПЛТ(). Например, вычислим​

​ (чаще всего -​ равновеликий платеж называется​ США и согласились​Аргумент ПС или приведенной​ процентов начисленных в​ погашения кредита приведено​ примера Лист Сравнение​ чтобы сравнивать разные​ (суммарные платежи на​ и Эффективная годовая​

​ MS EXCEL.​ (в отличие от​ простых процентов составил​ выше, но свободы​ПРПЛТ (IPMT)​из категории​ сумму долга, выплаченную​ сумма кредита).​ аннуитет.​ выплачивать по 150​ стоимости составляет 5400​

​ первых периодов (начисление​ в статье Сравнение​ схем погашения (5лет)).​ суммы кредита: Эффективная​ определенные даты).​ ставка используются чаще​

​В MS EXCEL есть​​ операции округления, при​​ бы 1000000*0,16=160000 рублей,​ выходит больше. Можно​для вычисления процентной​Финансовые (Financial)​ в 3-м и​Нач_период — обязательный​В аннуитетной схеме​ долларов США ежемесячно​ долларов США.​ и выплата в​ графиков погашения кредита​Примечание​ ставка поможет, если​Эффективную ставку по кредиту​ всего для сравнения​ функция ЭФФЕКТ(номинальная_ставка, кол_пер),​ усечении отбрасывается дробная​ поэтому для вкладчика​ даже взять в​

​ части вводится аналогично.​​. Выделяем ячейку, где​ 4-м периоде:​ аргумент. Номер первого​ погашения предполагается неизменность​ под 3% годовых.​

​Расчет ежемесячных платежей по​​ конце периода):​ дифференцированными аннуитетными платежами​.​ в одном банке​ iэфф определим используя​ доходности вкладов в​ которая возвращает эффективную​ часть).​ выгодно использовать предложенный​ банке еще денег​ Осталось скопировать введенные​ хотим получить результат,​=ПРПЛТ(ставка; 3; кпер;​ периода, включенного в​ процентной ставки по​

​С помощью функции КПЕР(ставка;ПЛТ;ПС)​ ипотеке​​ПС*ставка​​ в MS EXCEL.​Эффективная годовая ставка,​ дают 250т.р. на​ функцию ЧИСТВНДОХ (значения,​

excel2.ru

Дифференцированные платежи по кредиту в MS EXCEL

​ различных банках. Несколько​ (фактическую)​Каждый из двух аргументов​

​ вариант со сложными​ в дополнение к​ формулы вниз до​ жмем на кнопку​ пс; [бс]; [тип])+​ вычисления. Нумерация периодов​ кредиту в течение​=КПЕР(3%/12;-150;2500)​Представьте дом стоимостью 180 000​(ПС-ПС/кпер)*ставка​Задача​ рассчитанная с помощью​ одних условиях, а​ даты, [предп]). В​ иной смысл закладывается​годовую​ функции ЭФФЕКТ должен​ процентами.​ имеющемуся кредиту. Для​ последнего периода кредита​fx​ ПРПЛТ(ставка; 4; кпер;​ выплат начинается с​ всего периода выплат.​выясняем, что для погашения​ долларов США под​(ПС-2*ПС/кпер)*ставка​. Сумма кредита =150т.р.​ функции ЭФФЕКТ(), дает​ в другом 300т.р.​ основе этой функции​ при расчете Эффективной​процентную ставку, если​

​ быть представлен числовым​​Пример 3. Два банка​ расчета по такой​ и добавить столбцы​в строке формул,​ пс; [бс]; [тип])​ 1.​ В статье Аннуитет.​ кредита необходимо 17​ 5% годовых на​(ПС-3*ПС/кпер)*ставка​ Срок кредита =2​ значение 16,075%. При​ на других.​ лежит формула:​ ставки по кредитам,​ заданы номинальная годовая​

График погашения кредита дифференцированными платежами

​ (или процентным для​​ предлагают сделать депозитный​ модели придется рассчитывать​ с простыми формулами​ находим функцию​Чтобы вычислить сумму процентов,​Кон_период — обязательный​ Расчет периодического платежа​

​ месяцев и несколько​ 30 лет.​…​ года, Ставка по​ ее расчете не​Итак, у нас​Где, Pi = сумма​ прежде всего по​ процентная ставка и​ аргумента номинальная_ставка) значением​
​ вклад на одинаковую​ проценты и остаток​ для вычисления общей​ПЛТ​ которая была выплачена​ аргумент. Номер последнего​ в MS EXCEL.​ дней.​С помощью функции ПЛТ(ставка;КПЕР;ПС)​Просуммируем полученные выражения​ кредиту = 12%.​

​ используются размеры фактических​ получилось, что сумма​ i-й выплаты заемщиком;​ потребительским. Эффективная процентная​количество периодов в году​​ либо текстовой строкой,​​ сумму (250000 рублей)​ с точностью не​ суммы ежемесячных выплат​в списке и​ в промежутке между​ периода, включенного в​ Погашение ссуды (кредита,​Аргумент «Ставка» составляет 3%/12​=ПЛТ(5%/12;30*12;180000)​ и, используя формулу​ Погашение кредита ежемесячное,​ платежей, а лишь​ всех наших платежей​

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

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

​ номинальная ставка и​ в погашение основной​​ i-й выплаты; d1​​ используется для сравнения​ сложные проценты. Под​ преобразована в число.​ при следующих условиях:​ до дня:​ равна вычисленной выше​ОК​ нач_период и кон_период​Тип — обязательный​ величину регулярной суммы​ год.​ (без учета страховки​ получим результат.​
​ периода (месяца).​ количество периодов капитализации.​ суммы кредита дисконтированных​ = дата 1-й​ различные кредитных предложений​ номинальной ставкой здесь​ При вводе не​Номинальная ставка – 24%,​Предполагается что:​ в ячейке C7)​. В следующем окне​

Расчет суммарных процентов, уплаченных с даты выдачи кредита

​ используйте формулу:​ аргумент, определяющий время​ для погашения кредита​Аргумент ПЛТ составляет -150.​ и налогов) в​=ПС*Ставка* период*(1 -​Решение. Сначала вычислим часть​ Если грубо, то​ по ставке 72,24%​ выплаты (начальная дата,​
​ банков.​
​ понимается, годовая ставка,​
​ преобразуемых к числовым​
​ простые проценты, 12​
​в зеленые ячейки пользователь​
​ и, ради интереса,​ нужно будет ввести​=СУММПРОИЗВ(ПРПЛТ(ставка;СТРОКА(ДВССЫЛ(нач_период&»:»&кон_период)); кпер; пс;​ платежа. Для аннуитета​
​ или ссуды.​Аргумент ПС (приведенная стоимость)​
​ размере 966,28 долларов​ (период-1)/2/кпер)​ (долю) основной суммы​ получается, что в​ равна размеру кредита​ на которую дисконтируются​Эффективная процентная ставка​ которая прописывается, например,​
​ значениям текстовых строк​ периодов капитализации.​ вводит произвольные даты​ оставшейся сумме долга:​ аргументы для расчета:​
​ [бс]; [тип]))​ постнумерандо Тип=0, для​
​В данной статье​ составляет 2500.​ США.​

excel2.ru

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

​Где, Ставка –​ кредита, которую заемщик​ нашем частном случае​ (это из определения​ все суммы).​ по кредиту отражает​ в договоре на​ и имен, а​Номинальная ставка 22%, сложные​ платежей и их​Чтобы сделать наш калькулятор​Ставка​

• ​Выражение СТРОКА(ДВССЫЛ(нач_период&»:»&кон_период)) создает массив​​ пренумерандо Тип=1.​ научимся вычислять сумму​Расчет суммы первого взноса​Аргумент «ставка» составляет 5%,​ это процентная ставка​

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

• ​ также данных логического​​ проценты, начисляемые по​ суммы​ более универсальным и​- процентная ставка​ последовательных чисел, например​Примечание​ процентов, которую необходимо​Допустим, вы хотите приобрести​

• ​ разделенных на 12​​ за период (=годовая​ =150т.р./2/12, т.е. 6250р.​ отличие эффективной ставки​ в другом банке​ денежного потока находятся​

​ с точки зрения​Предположим, что сложные​ типа функция ЭФФЕКТ​

​ итогам каждого периода,​отрицательные суммы — наши​ способным автоматически подстраиваться​ по кредиту в​ 2:3:4:5 (если нач_период=2​. Убедитесь, что аргумент​ выплатитьпосле заданного количества​ автомобиль стоимостью 19​

​ месяцев в году.​

​ ставка / число​

​ (сумму кредита мы​ по кредиту от​ для соблюдения этого​ в диапазоне​ заёмщика, то есть​

• ​ проценты начисляются m​ будет возвращать код​ 4 периода капитализации.​ выплаты банку, положительные​ под любой срок​ пересчете на период​ и кон_период=5).​ «Ставка» соответствуют ставке​

• ​ периодов (а также​ 000 долларов США​Аргумент КПЕР составляет 30*12​

• ​ выплат в году),​ разделили на общее​ номинальной (15%) в​

​ равенства потребуется дисконтировать​G22:G34​

​ учитывает все дополнительные​ раз в год.​ ошибки #ЗНАЧ!.​Определить выгодный вариант, отобразить​

​ — берем дополнительный​

​ кредита, имеет смысл​

​ выплаты, т.е. на​Функция СУММПРОИЗВ() суммирует​ за период (период​ сумму процентов, которую​ под 2,9% годовых​

• ​ для ипотечного кредита​ период – период,​ количество периодов выплат​

• ​ основном обусловлено наличием​ суммы платежей идущих​, а даты выплат​ выплаты, непосредственно связанные​ Эффективная годовая процентная​Аргумент номинальная_ставка принимает значения​

• ​ схему выплат.​ кредит к уже​ немного подправить формулы.​

​ месяцы. Если годовая​ результаты ПРПЛТ(), т.е.​

​ не обязательно =​ необходимо выплатить в​ за три года.​ сроком на 30​ до которого требуется​ =2года*12 (мес. в​

​ периодов капитализации (самой​

​ на обслуживание долга​

​ в​ с кредитом (помимо​ ставка дает возможность​ из диапазона положительных​Исходные данные:​ имеющемуся​

• ​ В ячейке А18​ ставка 12%, то​ вышеуказанная формула эквивалентна​ году). Например, при​

• ​ промежутке между произвольно​ Вы хотите сохранить​ лет с 12​ найти сумму процентов.​

• ​ году)).​ сутью сложных процентов).​ по б​B22:B34​

• ​ платежей по самому​ увидеть, какая годовая​ чисел, а кол_пер​В первом случае таблица​

​подсчитать точное количество дней​ лучше использовать формулу​ на один месяц​ формуле:​ ежемесячных выплатах по​ заданными периодами). Данные​ ежемесячные платежи на​ ежемесячными платежами, оплачиваемыми​Например, сумма процентов,​Каждый период заемщик​Примечание​о​, Эффективная ставка по​

​ кредиту). Такими дополнительными​

​ ставка простых процентов​

​ – из диапазона​ выплат выглядит так:​ между двумя датами​ вида:​ должно приходиться по​=ПРПЛТ(ставка; 2; кпер;​ 4-х летнему займу​ расчеты можно сделать​ уровне 350 долларов​ в течение года.​

• ​ выплаченных за первые​

• ​ выплачивает банку эту​. Сравнение графиков погашения​льшей ставке, то условия​ кредиту для нашего​

• ​ выплатами являются банковские​ позволит достичь такого​ от 1 до​Проценты – постоянная величина,​

• ​ (и процентов, которые​=ЕСЛИ(A17>=$C$7;»»;A17+1)​

​ 1% соответственно.​ пс; [бс]; [тип])+​

​ из расчета 12​ несколькими разными способами​ США. Таким образом,​Аргумент ПС составляет 180​ полгода пользования кредитом​ часть основного долга​ дифференцированными платежами и​

​ кредитного договора в​

​ случая может быть​

​ комиссии — комиссии​ же финансового результата,​ +∞. Если данные​ рассчитываемая по формуле:​

• ​ на них приходятся)​Эта формула проверяет с​Кпер​

• ​ ПРПЛТ(ставка; 3; кпер;​

• ​ процентов годовых используйте​ (см. файл примера).​

​ вам необходимо рассчитать​

​ 000 (нынешняя величина​ (см. условия задачи​ плюс начисленные на​ по аннуитетной схеме​ нем менее выгодны​ вычислена по формуле​ за открытие и​ что и m-разовое​ условия не выполняются,​=$B$2*$B$3/$B$4​ лучше с помощью​ помощью функции ЕСЛИ​- количество периодов,​ пс; [бс]; [тип])​ значение 12%/12 для​Чтобы вычислить, сколько процентов​ начальный платеж. В​

​ кредита).​

​ выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р.​

​ его остаток проценты.​ приведено в этой​ (суммы кредитов могут​

• ​ =ЧИСТВНДОХ(G22:G34;B22:B34). Получим 72,24%.​ ведение счёта, за​ наращение в год​ например, функции =ЭФФЕКТ(0;12)​Описание аргументов (для создания​ функции​

• ​ (IF) достигли мы​ т.е. срок кредита​

• ​ + ПРПЛТ(ставка; 4;​ аргумента «Ставка». Аргумент​ нужно будет выплатитьс​ данной формуле результат​

• ​Расчет суммы ежемесячных сбережений,​За весь срок​ Расчет начисленных процентов​ статье.​

​ быть разными). Поэтому,​

​Значения Эффективных ставок​ приём в кассу​ по ставке i/m,​ или =ЭФФЕКТ(12%;0) вернут​ абсолютной ссылки используйте​ДОЛЯГОДА (YEARFRAC)​ последнего периода или​ в месяцах.​ кпер; пс; [бс];​

​ «Кол_пер» будет равен​

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

​ функции ПС — это​ необходимой для отпуска​ будет выплачено =ПС*Ставка*(кпер+1)/2=18750р.​

• ​ на остаток долга​

• ​Примечание.​ получается, что важнее​

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

• ​ наличных денег и​ где i –​

См. также

​ код ошибки #ЧИСЛО!.​

​ клавишу F4):​

​Функция ЭФФЕКТ в Excel​

​ нет, и выводит​

support.office.com

Аннуитет. Расчет в MS EXCEL выплаченных процентов за период

​Пс​ [тип])+ ПРПЛТ(ставка; 5;​ 4*12. При ежегодных​ после истечения заданного​ сумма кредита, которая​Необходимо собрать деньги на​Через функцию ПРОЦПЛАТ()​ приведен в таблице​Эффективную ставку по​ не само значение​ нескольких кредитов: чья​ т.п., а также​ номинальная ставка.​

​Функция ЭФФЕКТ использует для​$B$2 – начальная сумма​ предназначена для расчета​ пустую текстовую строку​- начальный баланс,​ кпер; пс; [бс];​ платежах по тому​ количества периодов, используйте​ вычитается из цены​ отпуск стоимостью 8500​
​ формула будет сложнее:​ ниже – это​ кредиту можно рассчитать​ Эффективной ставки, а​ ставка меньше, тот​ страховые выплаты.​При сроке контракта​ расчетов формулу, которая​ вклада;​ фактической годовой процентной​ («») в том​ т.е. сумма кредита.​ [тип])​
​ же займу используйте​ формулу: ОБЩПЛАТ(ставка; кол_пер;​ покупки для получения​ долларов США за​ =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ(«1:»&кпер))-1;кпер;-ПС))​ и есть график​ и без функции​ результат сравнения 2-х​ кредит и более​По закону банк​ 1 год по​ может быть записана​

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

​$B$3 – годовая ставка;​ ставки (иное название​ случае, если достигли,​Бс​Кто как, а я​ значение 12% для​ нз; 1; кон_период;​ первого взноса.​

​ три года. Процентная​Управление личными финансами может​ платежей.​ ЧИСТВНДОХ() — с​ ставок (конечно, если​ выгоден заемщику.​ обязан прописывать в​ формуле наращенной суммы​ в Excel в​$B$4 – число периодов​ – эффективная ставка),​ либо номер следующего​
​- конечный баланс,​ считаю кредиты злом.​ аргумента «ставка» и​

​ тип).​С помощью функции ПС(ставка;КПЕР;ПЛТ)​ ставка сбережений составляет​ быть сложной задачей,​Для расчета начисленных процентов​ помощью Подбора параметра.​ эффективная ставка значительно​Но, что за​ договоре эффективную ставку​ имеем:​ виде: =СТЕПЕНЬ(1+(A1/A2);A2)-1, где:​ капитализации вклада.​
​ на основе известных​ периода. При копировании​ т.е. баланс с​

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

​ Особенно потребительские. Кредиты​

​ 4 — для​Задача1. Предположим, что заем​​= 19000-ПС(2,9%/12; 3*12;-350)​ 1,5%.​ особенно если вам​ может быть использована​ Для этого в​ превышает ставку по​​ смысл имеет 72,24%?​​ по кредиту. Но​

​S = Р*(1+i/m)^m​​A1 – номинальная годовая​Сумма накопленных средств за​ данных, таких как​ такой формулы вниз​ которым мы должны​ для бизнеса -​ аргумента «Кол_пер». При​

​ 1 млн. был​
​выясняем, что первый взнос​С помощью функции ПЛТ(ставка;КПЕР;ПС;БС)​ нужно планировать свои​
​ функция ПРОЦПЛАТ(ставка;период;кпер;пс), где​ файле примера на​ кредиту, то это​ Может быть это​ дело в том,​ – для сложных​ ставка;​
​ каждый период рассчитывается​ номинальная годовая ставка,​ на большое количество​ по идее прийти​ другое дело, а​ ежеквартальных платежах по​ выдан на 5​ должен составлять 6946,48​
​=ПЛТ(1,5%/12;3*12;0;8500)​ платежи и сбережения.​ Ставка — процентная​ Листе Кредит создан​ означает, что имеется​ соответствующая ставка по​
​ что заемщик сразу​ процентов, где Р​A2 – число периодов,​ как как сумма​
​ число периодов начисления​ строк мы получим​ к концу срока.​ для обычных людей​ тому же займу​

​ лет. Годовая ставка​​ долларов США.​получаем, что чтобы собрать​ Формулы Excel помогают​ ставка​ столбец I (Дисконтированный​ значительное количество дополнительных​ простым процентам? Рассчитаем​ не видит кредитного​ – начальная сумма​ в которые происходит​ средств на счету​ сложных процентов, и​ номера периодов как​ Очевидно =0, т.е.​ мышеловка»деньги за 15​ используйте значение 12%/4​ = 10%. Начисление​Сначала в формуле указывается​ 8500 долларов США​ вычислить будущую стоимость​за период​ денежный поток (для​ платежей: убрав файле​ ее как мы​ договора и поэтому​
​ вклада.​​ начисление сложных процентов.​ за прошедший период​ возвращает соответствующее числовое​
​ раз до нужного​ никто никому ничего​ минут, нужен только​ для аргумента «ставка»​
​ процентов и погашение​ цена покупки в​
​ за три года,​
​ ваших долгов и​; Период – номер​ Подбора параметра)). В​

​ расчета все дополнительные​

​ делали в предыдущих​ делает свой выбор,​S = Р*(1+iэфф)​Примечания 2:​ и процентов, начисленных​ значение.​ предела (срока кредита).​ не должен.​
​ паспорт» срабатывает безотказно,​ и 4*4 —​ займа происходит ежемесячно​ размере 19 000 долларов​ необходимо откладывать по​ инвестиций, позволяя понять,​ периода, для которого​ окне инструмента Подбор​ платежи получим эффективную​ разделах:​ ориентируясь лишь на​ – для простых​

​Для понимания термина «сложные​

​ за текущий период.​Пример 1. Предприниматель получил​ В остальных ячейках​​Тип​ предлагая удовольствие здесь​ для аргумента «Кол_пер».​ в конце месяца​ США. Результат функции​ 230,99 долларов США​

​ сколько времени потребуется​​ требуется найти величину​​ параметра введите значения​ ставку 16,04% вместо​Мы переплатили 80,77т.р.​ номинальную ставку, указанную​ процентов​

​ проценты» рассмотрим пример.​ В итоге первый​ ссуду в банковской​ этой строки можно​- способ учета​ и сейчас, а​Примечание​ (тип=0). Определить сколько​ ПС будет вычтен​ ежемесячно.​
​ для достижения целей.​ начисленных процентов; Кпер​ указанные на рисунке​ 72,24%!).​

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

​ расплату за него​. Функция ОБЩПЛАТ() возвращает​ процентов будет выплачено​ из цены покупки.​
​Аргумент «ставка» составляет 1,5%,​ Используйте следующие функции:​ — общее число​ ниже.​
​Примечание​ и дополнительных платежей)​Для создания расчетного​ S должен быть,​ денежные средства в​ рублей процентов, и​ год с эффективной​ с проверкой на​ равен 1, то​

excel2.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

planetaexcel.ru

Функция ЭФФЕКТ для расчета годовой процентной ставки в Excel

​ НОМИНАЛ(эффективная_ставка, кол_периодов). См.​ сумма задолженности (прибыли)​ предыдущий период.​ числовое значение номинальной​ реальной жизни вы,​ помощью простых формул:​ на страхование и​ что по прошествии​ 2 000 000; 1;​ месяц под 1,5%​ за три года,​ на данный момент​

Примеры использования функции ЭФФЕКТ в Excel

​ параметров аннуитета).​ выплачивает банку эти​ одном будет осуществляться​ 100т.р. сегодня –​ эффективной ставки (см.​ файл примера.​ увеличивается быстрее при​Для быстрого расчета итоговой​ ставки;​ скорее всего, будете​

​Если хочется более детализированного​

​ прочие допы?​ количества периодов «Кол_пер»​ 1*4; 1)​

​ годовых?​

​ и вам интересно,​

​ равноценна ряду будущих​Примечание​ части основного долга​

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

• ​ это значительно больше,​ здесь ).​Если договор вклада длится,​
• ​ одинаковых сумме и​ суммы используем формулы:​

​B2 – число периодов​

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

​ выплат.​

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

​. Английский вариант функции​ плюс начисленные на​ в другом по​ чем 100т.р. через​Эффективную ставку по​ скажем, 3 года,​ периоде кредитования, в​Первый банк:​ погашения.​ для досрочного погашения​ воспользоваться еще двумя​ что изложенный ниже​Также обратите внимание,​Функция ОБЩПЛАТ(ставка; кол_пер; нз;​=БС(1,5%/12;10;-200;-500)​

​ положить на счет,​

​БС​

​ — ISPMT(rate, per,​

​ его остаток проценты.​

• ​ аннуитетной схеме (равновеликими​ год при 15%​
• ​ кредиту рассчитаем используя​
• ​ с ежемесячным начислением​

​ сравнении с применением​

​Второй банк:​Результат:​ при удобной возможности.​ полезными финансовыми функциями​ материал вам не​ что в определении​ нач_период; кон_период; тип)​получаем, что за 10​

Как посчитать проценты на депозит в Excel для выбора вклада

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

1. ​ функцию ЧИСТВНДОХ(). Для​ по сложным процентам​ простых процентов (особенно,​
2. ​Результаты расчетов:​Полученное значение 0,235 соответствует​ Для реализации этого​ Excel -​

​ пригодится.​ функции ОБЩПЛАТ() речь​

​возвращает кумулятивную (нарастающим​

​ месяцев выйдет сумма​ составлял 175,00 долларов​

​ инвестиции при условии​Функция ПРОЦПЛАТ() предполагает начисление​

​ период погашения установлен​

​ предположим, что дополнительные​ — значительно меньше,​ этого нужно составить​

• ​ по ставке i,​ если периодов начисления​
• ​Несмотря на то, что​
• ​ 23,5% (значению эффективной​ можно добавить в​

​ОСПЛТ (PPMT)​Но если уж случится​ идет только о​ итогом) величину процентов,​ 2517,57 долларов США.​ США. Функция ПС​ периодических равных платежей​ процентов​ равным месяцу, то​ платежи не взимаются.​ если имеется альтернатива​ график платежей по​

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

​ второй банк предлагает​ ставки по условию).​ нашу модель столбец​и​ так, что вам​ займе. Определить сумму,​ выплачиваемых по займу​Аргумент «Ставка» составляет 1,5%/12.​

​ рассчитает размер начального​

​ и постоянной процентной​в начале каждого периода​ из месяца в​ Зависит ли значение​ положить эту сумму​

​ кредиту и включить​ по вкладу вычисляется​

1. ​ много.​
2. ​ расчет с использованием​

​ Расчет номинальной ставки​

​ с дополнительными выплатами,​ПРПЛТ (IPMT)​ или вашим близким​ накопленную за счет​ в промежутке между​Аргумент КПЕР составляет 10​ депозита, который позволит​ ставки.​(хотя в справке​ месяц сумма основного​ эффективной ставки от​ в банк под​ в него все​ по формуле:​

Особенности использования функции ЭФФЕКТ в Excel

​Для получения результата в​

​ сложных процентов, предложение​

​ также можно производить​

• ​ которые будут уменьшать​. Первая из них​ придется влезть в​ процентов в случае​ двумя периодами выплат​
• ​ (месяцев).​ собрать желаемую сумму.​Расчет ежемесячных платежей для​ MS EXCEL это​ долга пропорционально уменьшается.​ графика погашения? Сразу​

​ 15%). Для сравнения​

1. ​ дополнительные платежи.​iэфф =((1+i/12)^(12*3)-1)*(1/3)​ формате процентов необходимо​ первого банка оказалось​ с помощью функции​ остаток. Однако, большинство​ вычисляет ту часть​ это дело, то​
2. ​ срочного вклада, с​ (​Аргумент ПЛТ составляет -200.​С помощью функции ПС(ставка;КПЕР;ПЛТ;БС)​ погашения задолженности по​ не сказано). Но,​ Поэтому при дифференцированных​ даем ответ: зависит,​ сумм, относящихся к​Пример​или через функцию​ установить соответствующий формат​ выгоднее. Если бы​ НОМИНАЛ.​ банков в подобных​ очередного платежа, которая​
3. ​ неплохо бы перед​ помощью  функции ОБЩПЛАТ()​нач_период кон_период​Аргумент ПС (приведенная стоимость)​=ПС(1,5%/12;3*12;-175;8500)​ кредитной карте​ функцию можно использовать​ платежах основные расходы​ но незначительно.​ разным временным периодам​
4. ​. Рассчитаем Эффективную ставку​ ЭФФЕКТ(): iэфф= ЭФФЕКТ(i*3;3*12)/3​ данных в ячейке,​ число периодов капитализации​​

• ​ случаях предлагают на​ приходится на выплату​
• ​ походом в банк​ не получится (для​).​

​ составляет -500.​

• ​мы узнаем, что необходим​Предположим, остаток к оплате​ для расчета процентов,​ заемщик несет в​В файле примера на​ используют дисконтирование, т.е.​ по кредиту со​Для вывода формулы​ в которой будет​ совпадало (12), во​Пример 2. Вкладчику предложили​ выбор: сокращать либо​ самого кредита (тела​
• ​ хотя бы ориентировочно​ этого см. Способ​Примечание.​ПЛТ​ начальный депозит в​ составляет 5400 долларов​ начисляемых и в​ начале кредитования, размеры​ листе Сравнение схем​ приведение их к​ следующими условиями:​ справедливы те же​ введена функция ЭФФЕКТ.​ втором банке вкладчик​ сделать депозит в​ сумму ежемесячной выплаты,​ кредита), а вторая​
• ​ прикинуть суммы выплат​ 2).​Английская версия: CUMIPMT(rate,​КПЕР​ размере 1969,62 долларов​ США под 17%​

exceltable.com

​ конце периода для​

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

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

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

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

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

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

Где применяется понятие эффективной процентной ставки

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

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

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

Расчет эффективной процентной ставки производится в следующих случаях:

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

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

Порядок расчета эффективной процентной ставки

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

1. В первую очередь, необходимо определить сумму процентов, подлежащих к уплате за весь период использования финансового актива. Для этого можно использовать график платежей.
2. Далее, рекомендуется определить все платежи, в том числе, скрытые. К ним могут относиться страховые суммы, комиссии, плата за оценку залога и/или поручительство и иные взносы, производимые пользователем финансового инструмента.
3. Проценты, подлежащие к уплате, и дополнительные расходы комбинируются путем простого арифметического приема – сложения.
4. На следующем этапе необходимо найти дату первого и последнего платежа, а затем определить количество дней пользования активом без учета периода его предоставления.
5. В ходе расчета показателя принимает участие эффективная процентная ставка, определенная компанией. Зачастую, это первичная ставка, которая отражается в договоре.
6. После того, как все показатели найдены, можно приступать к вычислению эффективной процентной ставки с использованием формулы. Просто заменяем буквенные выражения полученными значениями и производим математические действия.

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

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

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

(СП / ((1 + ПС)) ^{((Дкдп – Дндп) / 365))}, где

СП – сумма всех платежей;

ПС – процентная ставка по финансовому инструменту;

Дкдп – дата конечного денежного потока;

Дндп – дата первого финансового оборота.

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

(1 + (ПС / СД)) ^СД – 1, где

ПС – процентная ставка;

СД – срок использования актива по договору.

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

(ПУ + ДЗ) / (СПД – ПУ – КО), где

ПУ – проценты, подлежащие к уплате по договору;

ДЗ – дополнительные затраты, отраженные в соглашении;

СПД – сумма финансового актива по договору;

КО – компенсационный остаток.

Важно! Зачастую, для определения эффективной процентной ставки используется первая формула, которая приведена в методических рекомендация Центробанка РФ.

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

Приведем пример расчета эффективной процентной ставки. Допустим, компания ООО «АгроИнвест» приобрела кредит в сумме 1 млн. рублей под 9% годовых сроком на 1 год под залог производственного помещения. По договору предусмотрено страхование займа в размере 2% и услуги по оценке залогового имущества в размере 56 тыс. рублей.

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

1 млн. руб. * 9% = 90 тыс. рублей.

Теперь найдем сумму дополнительных затрат:

(1 млн. руб. * 2%) + 56 тыс. руб. = 76 тыс. рублей.

Комбинируем все платежи, предусмотренные договором:

90 тыс. руб. + 76 тыс. руб. = 166 тыс. руб.

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

12 – 1 = 11 месяцев.

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

(166 тыс. руб. / (1 млн. руб. – 90 тыс. руб.)) * 100 = 18,24%.

Таким образом, эффективная процентная ставка по кредиту с учетом всех платежей для компании ООО «АгроИнвест» составила 18,24%.

Пример расчета эффективной процентной ставки при покупке векселя

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

165 тыс. руб. – 150 тыс. руб. = 15 тыс. руб.

Далее, найдем процентную ставку за полгода:

15 тыс. руб. / 150 тыс. руб. = 0,1 или 10%.

Теперь можно приступать к определению эффективной или дисконтированной процентной ставки:

(1 + 0,1) ^12/6 – 1 = 21%.

В заключение, определим доходность инвестора от покупки векселя:

150 тыс. руб. * 21% = 31500 руб.

Таким образом, от приобретения векселя инвестор получит доход в размере 21% или в денежном выражении — 31,5 рублей.

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

Для облегчения вычисления эффективной процентной ставки компании используют специализированные программы. Зачастую в этом помогает стандартный сервис Microsoft Office – Microsoft Excel. Для того, чтобы рассчитать эффективную процентную ставку при помощи Microsoft Excel, рекомендуется использовать следующую пошаговую инструкцию:

1. Запускаем Ms Excel, даем название новому файлу. Это необходимо для того, чтобы не перепутать расчеты с другими вычислениями при помощи программы.
2. Формируем таблицу, которая будет включать в себя такие столбцы, как дата платежа, начальная и конечная сумма, объем ежемесячного взноса, сумма основного долга и процентов к уплате, а также дополнительные платежи. Заключительный столбец посвящается итоговой сумме платежа. Таблицу рекомендуется разместить, начиная с 6-й – 8-й строки.
3. Далее, заносим все данные, необходимые для расчета, то есть, даты и суммы по строкам: платеж, основной долг, проценты и дополнительные взносы.
4. Чтобы столбцы, в которых отражается начальная и заключительная суммы, рассчитывались автоматически, вводим формулы.
5. Вычисление итоговой суммы по строкам и столбцам также рекомендуется сделать автоматическим путем привязки арифметического выражения.
6. Чуть выше таблицы необходимо указать значение заявленной и эффективной процентной ставки.

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

Вычисление начального баланса:

СНБпз – СП, где

СНБпз – строка начального баланса по предыдущей дате платежа;

СП – сумма платежа предыдущего периода.

Определение итоговой суммы платежа за период:

СП + ДП, где

СП – сумма ежемесячного платежа, в том числе, процентов, подлежащих к уплате;

ДП – объем дополнительных взносов.

Расчет конечного баланса за период:

СНБ – СП, где

СНБ – начальный баланс;

СП – сумма платежа за рассчитываемый период.

Итоговые значения по столбцам:

А1 + А2 + …Ан, где

А1, А2, Ан – суммы по строкам столбца.

Заключение

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

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

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

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

Итак, начать следует  с воспоминаний школьной программы по математике. Далее следует  вооружится калькулятором, бумагой и ручкой. Ну, а кто предпочитает считать на компьютере, расчет реально произвести и при помощи программы 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 долларов США (получилось, что данная сумма выплаты по кредиту превысила сумму выплат по основной сумме кредита).

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

В наш век высоких технологий и автоматизации как-то неприлично вручную выполнять сложные расчёты. Хоть аннуитетные платежи рассчитать не так и трудно, но как говорит Юрий Ашер:

«Не надо напрягать свой мозг там, где это могут сделать за вас другие!»

В нашей ситуации к вам на помощь придут: компьютер и программа Microsoft Excel.

Хотим предупредить, что команда портала temabiz.com поставила перед собой цель не просто дать вам «халяву» в виде «экселевского» файла с готовыми расчетами. Нет, в этой публикации мы вас научим самостоятельно рассчитывать аннуитетные платежи, а также составлять в программе Excel графики погашения аннуитетных кредитов. Ну а для ленивых мы, конечно же, выложим готовые файлы кредитных калькуляторов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В чём «фишка» аннуитетной схемы погашения кредита? Правильно! Основная «фишка» в том, что заёмщик выплачивает кредит равными суммами на протяжении всего срока кредитования. С такой схемой очень удобно планировать свой бюджет. Например, вы готовы ежемесячно выделять на погашение кредита 5000 рублей. По вашим скромным подсчётам, такая нагрузка будет для вас не слишком обременительной. Естественно, у вас возникает закономерный вопрос: «А на какую сумму кредита я могу рассчитывать?» В общем, нам нужен новый кредитный калькулятор, у которого в исходных данных будет не сумма кредита, а величина аннуитетного платежа.

Что же, друзья, не будем терять время! Открываем программу Microsoft Excel и приступаем к разработке нашего кредитного калькулятора!

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

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

• «Ставка» – годовая процентная ставка по кредиту делённая на 12 (в нашем случае: C5/12).
• «Кпер» – общий срок кредитования (в нашем калькуляторе, это ячейка с координатой C6).
• «Плт» – ежемесячный аннуитетный платёж, перед которым ставим знак минус (в нашем калькуляторе, это ячейка C4, перед данной координатой мы и ставим знак минус).

Жмём «Ок» и в ячейке С11 появилась сумма 53 422 руб. – именно на такой размер кредита может рассчитывать заёмщик, который готов на протяжении 12 месяцев ежемесячно выплачивать по 5000 руб.

Кстати, обратите внимание на данные в строке формул (на рисунке они обведены и указаны под номером 2). Вы всё правильно поняли, друзья! Да, это те данные, которые необходимы для расчёта суммы кредита в нашем калькуляторе: =ПС(C5/12;C6;-C4). Те самые параметры, которые мы вводили в таблице функции ПС.

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

• Общая сумма выплат – это ежемесячный аннуитетный платёж (ячейка С4) умноженный на общий срок кредитования (ячейка С6). В строку формул вводим следующие данные: =C4*C6.
• Переплата (проценты) по кредиту – это общая сумма выплат (ячейка С12) минус сумма кредита (ячейка С11). В строку формул записываем: =C12-C11.
• Эффективная процентная ставка (или полная стоимость кредита) – это общая сумма выплат (ячейка С12) делённая на сумму кредита (ячейка С11) и минус единица. Затем всё это делим на срок кредитования, выраженный в годах (ячейка C6 делённая на 12). В строку формул записываем: = (C12/C11-1)/(C6/12).

Кстати, интересный момент. Вот в нашем примере, выплачивая ежемесячно в течение года по 5000 рублей, мы можем рассчитывать на сумму кредита равную 53 422 рубля. А что делать, если надо больше денег? Как вариант, можно увеличить срок кредитования. Если вместо 12 месяцев поставить 24, то сумма кредита увеличится до 96 380 рублей. Эти данные нам мгновенно выдал наш кредитный калькулятор, который вы можете скачать ссылке ниже:

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

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

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

1. 1. Месяцы. В этой колонке по порядку указаны номера месяцев, в которые будут осуществляться выплаты. Обратите внимание, что речь идёт не о календарных, а о порядковых номерах. То есть, если первая выплата припадает на сентябрь месяц, то ему присваивается порядковый номер «1», как первому месяцу, а не «9», как календарному.
2. 2. Ежемесячный платёж. Это тот самый аннуитетный платёж, который не меняется на протяжении всего срока кредитования. В сноске к одной из ячеек вы можете увидеть данные, которые внесены в строку формул: =ПЛТ(B3/12;B4;-H14). Вы уже знаете, что за расчёт аннуитетного платежа в экселе отвечает функция ПЛТ. Координаты необходимых значений для расчёта можно внести, как через строку формул, так и заполнив таблицу, которая появится при нажатии на кнопку «fx», находящуюся слева от строки формул.
3. 3. Погашение процентов. Здесь рассчитывается доля процентов в аннуитетных платежах (в каждой новой выплате она будет уменьшаться). В программе Excel за расчёт данного показателя отвечает функция ПРПЛТ. Опять же, задать необходимые параметры для расчётов можно либо нажав на кнопку «fx» и заполнив таблицу, либо просто внеся нужную информацию в строку формул. В нашем примере для расчёта доли процентов в первом платеже, в строке формул записано следующее: =ПРПЛТ(A15/12;D15;B15;-C15).
4. 4. Погашение тела кредита. Та самая выплата, которая вытягивает нас из долговой ямы и избавляет от банковского рабства. Мы рассчитали её просто: из суммы аннуитетного платежа вычли долю процентов, которую рассчитали в предыдущей колонке. Собственно, в строке формул по первому платежу так и записано: =E15-F15. Но можно пойти и другим, более изощрённым, путём. В программе Excel за расчёт этого платежа отвечает функция ОСПЛТ. Можете для интереса нажать кнопку «fx», выбрать функцию ОСПЛТ, внести все необходимые данные и получить сумму, идущую на погашение тела кредита в выбранном платеже.
5. 5. Долг на конец месяца. Ну, здесь всё просто! В данной колонке отображается сумма вашего долга перед банком на конец текущего месяца. Из текущего остатка мы отнимаем долю, идущую на погашение тела кредита. А вот уплаченные проценты просто уходят в казну банка и никак не влияют на сумму вашего текущего долга по кредиту.

Вот так легко и непринуждённо мы разработали кредитный калькулятор по расчёту графика аннуитетных платежей. Скачать его можно ссылке ниже:

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

⇧

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

• определение
  наращенной суммы (будущей стоимости),

• определение
  начального значения (текущей стоимости),

• определение
  срока платежа и процентной ставки,

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

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

где
pmt
– фиксированная
(неизменная) периодическая сумма платежа;

n
– общее
число периодов выплат,

r
– процентная
ставка за один период,

type
– число 0
или 1, обозначающее, когда производится
выплата (1 – в начале периода, 0 – в конце
периода),

—
текущая стоимость вклада (займа), по
которому начисляются проценты по ставке
r%
n—ное
число периодов или текущая стоимость
серии фиксированных платежей,

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

Если
процентная ставка за период начисления
r=0, то используется следующая формула:

Эти
формулы используют встроенные функцииБC,
КПЕР, ПC,
ПЛТ, ЭФФЕКТ и
другие.

ПС
— текущий объём вклада

Позволяет
рассчитать текущий объем вклада.

Синтаксис

ПС
(норма;кпер;выплата;бс;тип)

норма
— процентная
ставка за период. Так, если Вы по­лучили
ссуду под станок под 15% годовых и делаете
ежемесяч­ные выплаты, то ставка
процента за месяц составит 15%/12, или
1,25%. Аргумент норма
в данном
случае может принимать значения 15%/12
или 1,25% или 0,0125.

кпер
— общее число
периодов выплат годовых процентов. Так,
если Вы получили ссуду на 3 года под
станок и делаете ежемесячные платежи,
то Ваша ссуда имеет 3*12 (или 36) периодов.
Аргумент кпер
в данном случае принимает значение 36.

выплата
—
размер выплаты, производимой в каждом
периоде и не меняющейся в течение всего
времени выплаты процентов. Обычно,
выплата включает основные платежи и
платежи по процентам без учета других
сборов или налогов. Напри­мер,
ежемесячная выплата по четырехгодичному
займу в 10 000 руб. под 12% годовых составит
308,3 руб. Аргумент выплата
в данном случае принимает значение
-308,3.

бс
— будущая
стоимость или баланс наличности, который
нужно достичь после последней выплаты.
Если бс
опущено, оно полагается равным 0 (будущая
стоимость займа, например, равна 0).
Например, если Вы хотите накопить 50000
руб. в течение18 лет, то 50 000 руб. это и
есть будущая стоимость. Вы можете сделать
предположение о сохранении заданной
процентной ставки и определить, сколько
нужно откладывать каждый месяц.

тип
— число,
определяющее, когда должна производиться
выплата. Может принимать значения 0 или
1: 0 означает — выплата в конце периода,
1 — выплата в начале периода.

• Выбранные
  единицы измерения для аргументов норма
  и кпер
  должны соответствовать друг другу.
  Если Вы делаете ежемесячные выплаты
  по четырехгодичному займу под 12% годовых,
  то используйте 12%/12 для задания аргумента
  норма,
  4*12 для задания аргумента кпер.
  Если Вы делаете ежегодные платежи по
  тому же займу, то используйте 12% для
  задания аргумента норма
  и 4 для задания аргумента кпер.

• В
  функциях, связанных с интервалами
  выплат, выплачи­ваемые деньги
  представляются отрицательным числом,
  а получаемые деньги представляются
  положительным числом. Например, депозит
  в банк на сумму 1 000 руб. представляется
  для вкладчика аргументом -1000, а для
  банка аргументом 1000.

Пример

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

Необходимая
сумма текущего вклада составит:

ПС
(12%;12;;5000)=-1283.38 руб.

БС
— стоимость постоянных платежей в
определенные периоды на основе постоянной
процентной ставки

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

Синтаксис

БС(норма;кпер;выплата;пс;тип)

ставка
— процентная
ставка за период.

кпер
— количество
периодов, в которые производится вы­плата
годовых процентов.

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

пс
— общая сумма
всех будущих платежей с настоящего
момента. Если аргумент пс
опущен,
то он полагается равным 0.

тип
— число,
определяющее когда должна производиться
выплата. Может принимать значения 0 или
1: 0 — выплата в конце периода, 1 — выплата
в начале периода.

Более
подробная информация об аргументах
функции БЗ и о других функциях выплат
по процентным вкладам приведена в
описании функции ПЗ.

• Единицы
  измерения для аргументов норма
  и кпер
  должны быть согласованы. Если производятся
  ежемесячные платежи по четырехгодичному
  займу из расчета 12% годовых, то норма
  должна быть 12%/12 , а кпер
  должно быть 4*12. Если про­изводятся
  ежегодные платежи по тому же займу, то
  норма
  должна быть
  12%, а кпер
  должно быть 4.

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

Пример

Определим,
сколько денег будет на счету через год,
если вы собираетесь вложить 1000 рублей
под 6% годовых (что составит в месяц
6%/12 или 0,5%). Причем вы собираетесь
вкладывать по 100 рублей в начале каждого
следующего месяца в течение года.

Через
год на счете будет:

БС
(0,5%;12;-100;-1000;1)=2301,40 р.

ПРПЛТ
— платежи по процентам за период на
основе постоянных периодических выплат
и постоянной процентной ставки

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

Синтаксис

ПРПЛТ(норма;период;кпер;тс;бс;тип)

норма
—
процентная ставка за период.

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

кпер
— общее число
периодов выплат.

тс
— сумма,
которую составят будущие платежи с
настоящего момента.

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

тип
— число,
определяющее, когда должна производиться
выплата. Может принимать значения 0 или
1: 0 — выплаты в конце периода, 1 — выплаты
в начале периода.

• Единицы
  измерения для аргументов норма
  и кпер
  должны быть согласованы. Если производятся
  ежемесячные платежи по трехгодичному
  займу из расчета 10% годовых, то норма
  должна быть 10%/12 , а кпер
  должно быть 3*12. Если производятся
  ежегодные платежи по тому же займу, то
  норма
  должна быть 10%, а кпер
  должно быть 3.

• Выплачиваемые
  денежные средства представляются
  отрицательным числом, а получаемые
  денежные средства представляются
  положительным числом.

Пример

Требуется
определить доход за первый месяц от
четырех­годичного займа в 1000000 рублей
из расчета 15% годовых:

ПРПЛТ(0,15/12;1;48;1000000)
равняется -12500 рублей.

ПЛТ
— расчет постоянных периодических
выплат

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

Синтаксис

ПЛТ(норма;кпер;нз;бс;тип)

норма
– норма
прибыли за период займа.

кпер
– общее
число периодов выплат годовой ренты

нз
– текущая стоимость: общая сумма всех
будущих платежей с настоящего момента.

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

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

Пример

Предположим,
что необходимо накопить 4000 руб. за 3
года, откладывая постоянную сумму в
конце каждого месяца. Какой должна быть
эта сумма, если норма процента по вкладу
составляет 12% годовых?

Величина
ежемесячных выплат составит:

ПЛТ(12%/12;12*3;;4000)=-92.86
руб.

ОСПЛТ
— расчет основных платежей по займу

Функция
вычисляет величину основного платежа
(выплаты задолженности) по займу, который
погашается равными платежами в конце
или начале каждого расчетного периода,
на указанный период.

Синтаксис

ОСПЛТ(норма;период;кпер;тс;бс;тип)

норма
– норма
прибыли за период.

период
– период,
который должен быть в интервале от 1 до
кпер.

кпер
– общее
число периодов выплат годовой ренты.

тс
– текущая
стоимость: общая сумма всех будущих
платежей с настоящего периода.

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

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

Пример

Банком
выдан кредит в 7000 руб. на 3 года под 17%
годовых, начисляемых один раз в конце
каждого периода.

Определите
размер ежегодных основных выплат по
займу.

Основная
часть платежа на каждый из трех периодов
составит соответственно:

ОСПЛТ(17%;1;3;-70000)=19780.16
руб.

ОСПЛТ(17%;2;3;-70000)=23142.78
руб.

ОСПЛТ(17%;3;3;-70000)=27077.06
руб.

ОБЩПЛТ
— расчет суммы платежей по процентам
по займу между двумя периодами выплат

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

Синтаксис

ОБЩПЛТ(ставка;кол_пер;нз;нач_период;кон_период;тип)

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

кол_пер
– общее
количество периодов выплат.

нз
– текущее
значение.

нач_период
– первый
период.

кон_период
— последний период.

Пример

Пусть
заем под недвижимость сделан на следующих
условиях: процентная ставка –9% годовых;
срок-30 лет, размер ссуды – 125000 руб.,
проценты начисляются ежемесячно.

Найти
сумму выплат по процентам за 2-й год и
за 1-й месяц займа.

Общая
выплата по процентам за второй год
составит:

ОБЩПЛТ(9%/12;30*12;125000;13;24)=-11135.23
руб.

Одна
выплата за первый месяц составит:

ОБЩПЛТ(9%/12;30*12;125000;1;1)=-937.50
руб.

ОБЩДОХОД
— расчет суммы основных выплат по займу
между двумя периодами

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

Синтаксис

ОБЩДОХОД
(ставка;кол_пер;нз;нач_период; нач_период;)

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

кол_пер
– общее
количество периодов выплат.

нз
– текущее
значение.

нач_период
– первый
период.

кон_период
— последний период.

Пример

Выдана
ссуда размером 1000$ сроком на 6 лет под
15% годовых; проценты начисляются
ежеквартально.

Определите
величину основных выплат за 5 год.

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

ОБЩДОХОД(15%/4;6*4;1000;17;20)=-201.43$.

Комплексный
пример

Банком
выдан кредит в 10000$ на 5 лет под 12% годовых,
начисляемых один раз в конце каждого
периода. По условиям договора кредит
должен быть погашен равными долями в
течение указанного срока, выплачиваемыми
в конце каждого периода.

Разработать
план погашения кредита.

Периодический
платеж по данной операции составит:

ПЛТ(12%;5;-10000)=2774.10$.

Процентная
часть платежа на первый период составит:

ПРПЛТ(12%;1;5;-10000)=1200,00$.

Основная
часть платежа, направленная на погашение
долга за первый период, составит:

ОСПЛТ(12%;1;5;-10000)=1574.10$.

Как
видно выполняется тождество:

ПРПЛТ()+ОСПЛТ()=ПЛТ()=2774.10$.

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

ПЛТ(12%;5;-10000)*5=13870.50$.

Накопленная
сумма процентов за весь период составит:

ОБЩПЛТ(12%;5;10000;1;5;0)=-3870,49$.

Накопленная
сумма в счет погашения по займу за 5 лет
составит:

ОБЩДОХОД(12%;5;10;1;5;0)=-10000.00$.

Как
видно, сумма полученных величин равна
общей сумме, выплаченной по данному
займу:

ОБЩДОХОД()+ОБЩПЛАТ()=13870.49$.

(В
силу заложенного алгоритма расчета
функции ОБЩДОХОД() и ОБЩПЛАТ возвращают
отрицательные значения. Для получения
положительных значений можно задать
их со знаком минус).

На
рис. 1 приведена таблица с планом погашения
кредита.

ЭФФЕКТ
— эффективная годовая процентная ставка

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

Синтаксис

ЭФФЕКТ(нoмuнaльнaя_cmaвкa;кол_пep)

номинальная_ставка

—
номинальная годовая процентная ставка.

кол_пер
— количество
периодов в году.

Рис.
1. План погашения кредита

• Число
  периодов в году округляется до целого.

• Функция
  ЭФФЕКТ
  выдает значение ошибки #ЗНАЧ! в случае,
  если один из аргументов — не число либо,
  если номинальная_ставка
  меньше 0 или
  кол_пер
  меньше 1.

Пример

Рассчитаем
эффективную годовую процентную ставку
при став­ке 5,25% и четырех периодах в
году.

ЭФФЕКТ(5,25%;4)
равняется 0,053543 или 5,35%.

КПЕР
— определение срока платежа

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

Синтаксис

КПЕР(норма;выплата;нз;бс;тип)

норма
– норма
прибыли за период.

выплата—
размер выплаты, производимой в каждом
периоде.

нз
— общая сумма
всех будущих платежей с настоящего
момента. Если аргумент нз опущен, то он
полагается равным 0.

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

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

Пример

Ожидается,
что ежегодные доходы от реализации
проекта составят 33 млн. руб. Необходимо
рассчитать срок окупаемости проекта,
если инвестиции к началу поступления
доходов составят 100 млн. руб., норма
дисконтирования 12.11%.

Срок
окупаемости проекта составит:

КПЕР(12.11%;33;-100)=4года.

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

ВНДОХ
— процентная ставка дохода для ряда
последо­вательных периодических
выплат или поступлений

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

Синтаксис

ВНДОХ
(значения;предположение)

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

предположение
—
прогнозируемая величина.

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

• Начиная
  со значения прогноз, функция ВНДОХ
  выполняет
  циклические вычисления, пока не получит
  результат с точностью 0,00001 процента
  (такой метод вычисления на­зывается
  методом итераций).

• Функция
  ВНДОХ выдает значение ошибки #ЧИСЛО! в
  том случае, если не может получить
  результат после 20 попыток.

• Обычно
  нет необходимости задавать аргумент
  прогноз
  для вычислений с помощью функции ВНДОХ.
  Если прогноз
  опущен, то
  он полагается равным 0,1 (10 процентов).
  Если ВНДОХ
  выдает значение ошибки
  #ЧИСЛО!,
  можно попытаться вы­полнить вычисления
  еще раз с другим значением аргумента
  прогноз.

Пример

Предположим,
что затраты по проекту составят 500 тыс.
руб. Ожидаемые доходы составят 50 , 100 ,
300 и 200 тыс. руб. в течение последующих
4 лет. Оценим
экономическую целесообразность проекта
по скорости оборота инвестиций, если
рыночная норма дохода 12%.

Ячейки
А1:А5 содержат следующие значения: -500,
50, 100, 300 и 200 соответственно.

Внутренняя
скорость оборота инвестиций составит:

ВНДОХ(А1:А5)=9.25%.

Это
меньше, чем рыночная норма, поэтому
проект должен быть отвергнут.

В
том случае, если эта какая-либо из
финансовых функций недоступна из Excel,
следует установить надстройку Пакет
Анализа (Analysis
ToolPak),
а затем подключить его с помощью команды
Сервис
— Надстройки…

ЗАДАЧИ