Как рассчитать эффективную процентную ставку по кредиту в excel • расчет кредита

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

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

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

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

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

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

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

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

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

Кстати, хотим обратить ваше внимание:

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

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

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

Если договор вклада длится, скажем, 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 году, то Эффективная ставка по вкладу = Эффективной (фактической) годовой процентной ставке (См. файл примера ).

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

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

Исходная таблица данных:

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

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

B4 – полученное выше числовое значение номинальной ставки;
B2 – число периодов погашения.

Полученное значение 0,235 соответствует 23,5% (значению эффективной ставки по условию). Расчет номинальной ставки также можно производить с помощью функции НОМИНАЛ.

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

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

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

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

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

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

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

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

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

Идеальным вариантом являются те кредитные программы, где расчет эффективной процентной ставки добавляет к стоимости ссуды не более 2%. Например, у Сбербанка России этот показатель равен примерно 1,5%. Следовательно, здесь минимальное количество переплат.

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

Полезные советы заемщикам

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

Если данная информация недоступна, оформлять документы не стоит.

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

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

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

Задать вопрос о кредитах нашим специалистам!

Какая реальная процентная ставка сложных процентов годовых

Существует 2 направления дисконтирования (пересчет сумм с учетом процентных ставок): номинальный и эффективный.

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

Для расчета эффективной процентной ставки в Excel используется функция ЭФФЕКТ, а для номинальной – НОМИНАЛ. Приведем простой пример на рисунке о выше описанных фактах:

Ниже на рисунке представлено 12 выбранных периодов капитализации для тридцатилетнего кредита. Начальная сумма кредита составляет 165 000. Номинальное дисконтирование с ежемесячной капитализацией 3,75% годовых, а ежегодный платеж составляет 9169,68.

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

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

Сумма процентов – оплата за пользование кредитом.
Тело кредита – часть для закрытия суммы долга кредита.

Ячейка E17 содержит сумму процентов, начисленных на протяжении года. Ее значение разделено на начальную сумму долга и возвращено результатом в ячейке E18. Для перерасчета ставки дисконтирования в Excel нет необходимости строить целую хронологию всех платежей по кредиту за период 30 лет. Программа Excel предлагает специальные функции ЭФФЕКТ и НОМИНАЛ, которые позволяют без графика платежей вычислить номинальную и эффективную процентную ставку:

Обе функции ЭФФЕКТ и НОМИНАЛ имею по 2 аргумента:

Факт сковка – тип дисконтирования который должен быть использован: для функции ЭФФЕКТ следует указать номинальный, а для обратной ей функции НОМИНАЛ – эффективный.
Количество периодов – период капитализации.

В случае с функцией НОМИНАЛ следует указать эффективный метод дисконтирования, а в случае с функцией ЭФФЕКТ – наоборот номинальный. Второй аргумент с количеством периодов определяет число периодов капитализации на протяжении всего срока текущего взноса платежа. В данном примере этот период составляет 1 год, так как был использован термин ежегодный платеж. Год состоит из 12-ти месяцев, номинальное дисконтирование разделено на 12 периодов капитализации. Если бы в условиях кредитования, в котором годовой платеж имел бы ежедневную капитализацию, тогда во втором аргументе функции нужно указывать значение 365.

Формула расчета процентов. базовые понятия

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

Капитал (англ.Capital,Principal) — является базой относительно которого вычисляют процент.
Частота начисления процентов — период выплат процентов на капитал.
Процентная ставка (англ.Rate) — размер процента или доля капитала, который будет выплачен.
Период вложения (англ.Period) — временной интервал передачи капитала банку или другому финансовому институту.

Итак, рассмотрим различные эконометрические задачи с процентами.

Что нужно запомнить

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

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

Тихон Рукавицын

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

Зинаида Шпак

Хорошо, что вы помогаете полезным материалом не попасть впросак! Эти банки и работающие в них специалисты очень хитры, и могут всё так обставить, что потом пожалеешь, что вообще связалась! Особенно точно это про «мелкий шрифт в договоре». Я сама чуть не попалась недавно на такую вот удочку: один из банков огромным ярким шрифтом указывает маленькую процентную ставку по кредиту, не кредит, а мечта! А потом я присмотрелась, да там же через несколько месяцев надо будет платить по процентной ставке в 20/% годовых! И да, я не математик, и мне, в самом деле, проще уж воспользоваться онлайн-калькулятором при расчёте эффективной процентной ставки. Но было интересно заглянуть в формулу по которой будет считать калькулятор эту ставку. Интересно, на мой взгляд, что формула учитывает полную стоимость в годовых процентах. Это, как я думаю, ключевой фактор при расчёте эффективной процентной ставки.

Илья Кондратьев

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

Сергей Ильченко

Всё, что написано в статье, есть в наших банках на самом деле, к большому сожалению. Так и хотят нашего брата обмануть, пишут мелкими буквами, при составлении договора применяют банковские и юридические термины, что реально не понятно о чём в договоре написано. Я один раз минут 20 читал договор, перед тем как подписать, потребовал не накручивать мне лишнего, согласились и договор перепечатали. За свои права, а тем более деньги, нужно всегда бороться!

Александр Нюшков

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

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

Преимущества и недостатки аннуитетных платежей

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

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

Без недостатков не обошлось:

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

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

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

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

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

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

«Номинальная ставка» – годовая ставка по кредиту, указанная в договоре с банком. В примере – 18% (0,18).
«Количество периодов» – число периодов в году, за которые начисляются проценты. В примере – 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

кредитов;
депозитов;
инвестиций в бизнес;
при покупке облигаций, ваучеров, фьючерсов и других финансовых инструментов;
при формировании отчетности по МСФО.

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

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

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

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

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

сумма кредита*((((1+процентная ставка/12)^кол-во месяцев кредитования-1)/(процентная ставка*кол-во месяцев кредитования/12))-(((((1+процентная ставка/12)^ кол-во месяцев кредитования-1)/(процентная ставка/12))-кол-во месяцев кредитования)/(кол-во месяцев кредитования*(1-(1+процентная ставка/12)^(-кол-во месяцев кредитования))))).

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

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

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

=СТАВКА (кпер; плт; пс)*12

И ввести необходимые значения. На место кпер (количество периодов) необходимо подставить общее число месяцев, на которые берется кредит. Плт (от слова «платеж») – размер ежемесячных выплат банку по кредиту. Пс (приведенная стоимость) – в данном случае это сумма кредита. Полученный результат мы умножаем на 12, чтобы получить значение в годовом, а не в месячном выражении.

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

=ПЛТ(ставка;кпер;пс)
.

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

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

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

Главная » Архив новостей » Расчет эффективной процентной ставки по кредиту в Excel. Эффективная ставка по кредиту (расчет, формула)