1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Ипотечный кредитный калькулятор в Excel. Как правильно рассчитать кредит в Excel?

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

Скачать кредитный калькулятор в Excel

Первый кредитный калькулятор в Excel можно скачать по ссылке.
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.
Для расчета досрочного погашения можно также воспользоваться онлайн версией калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel.

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

Достоинства данного калькулятора:

  1. Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
  2. Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
  3. Построение и расчет графика платежей в виде таблицы в Excel. Таблица графика платежей может также редактироваться
  4. При расчете учитывается високосный и невисокосный год. За счет этого сумма начисленных процентов практически совпадает с значениями, рассчитываемыми ВТБ24 и Сбербанком
  5. Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
  6. Калькулятор можно редактировать под себя, задавая разные варианты расчета.

Недостатки калькулятора

  1. Нет учета возможное изменение процентной ставки во время выплат кредита
  2. Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
  3. Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
  4. Не рассчитывается вариант — первый платеж только проценты. В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
  5. Расчет производится для процентой ставки с 2мя знаками после запятой.

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

Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.

Расчет Эффективной ставки в 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%!).

Читать еще:  Нужно срочно получить кредит в размере 300 000 рублей

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

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

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

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

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

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

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

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

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

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

Кредитный калькулятор в Excel

Добавить калькулятор в Excel

Как быстро посчитать основные параметры кредита (ипотеки) в Excel, как быстро сформировать кредитный калькулятор график платежей в Excel? Как посчитать параметры досрочного погашения кредита в Excel?

Попробуйте наш простой калькулятор для расчета основных параметров при оформлении кредита, который можно встроить непосредственно в ваш Excel!

Калькулятор выводит основные параметры вашего кредита или может сформировать лист с кредитным калькулятором и графиком платежей ! Работает как по схеме «вернуть в конце срока», так и с аннуитетными (равномерными платежами).

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

Читать еще:  Кредит на покупку коммерческой недвижимости под ее залог

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

=сумма кредита*(100%+годовая ставка)^срок кредита(лет)

При аннуитетных (равномерных) платежах:

Для того, чтобы узнать величину ежемесячного платежа используйте функцию: =ПЛТ(годовая проц.ставка/12;кол-во месяцев;сумма кредита)*-1

График платежей по кредиту в Excel

Также вы можете добавить кредитный калькулятор в свой Excel с надстройками SubEx_Fin или SubEx, чтобы он, вместе с другими финансовыми инструментами, был под рукой в любую минуту!

Подробнее в ВКонтакте

Финансовые статьи по Excel

Как в Экселе кредит посчитать

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

Аннуитетный кредит — что это? Это займ с такими условиями погашения, когда вы погашаете одинаковые суммы через равные промежутки времени.

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

ПараметрОписание
СтавкаПроцентная ставка за один период
ПериодПорядковый номер периода, для которого рассчитывается выплата. Он должен быть не больше, чем Кпер, иначе формула вернет ошибку
КперКоличество периодов, на которое рассчитан кредит
ПсСумма (тело) кредита. Для кредита это число записываем отрицательным, а для депозита — положительным
БсБудущая стоимость – величина остатка по кредиту после окончания срока выплат
ТипУкажите «0» (значение по умолчанию), если оплаты производим в конце периода, «1» — в начале периода
ПлтРазмер периодической платы (тело кредита плюс процент)
ОценкаНачальная оценка ожидаемого результата

Как посчитать основной платёж по кредиту

Чтобы посчитать ежемесячные выплаты по телу кредита – используйте функцию =ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип) .

Вот какой результат даёт эта функция:

Как высчитать процент по кредиту

Чтобы узнать переплату (проценты) по кредиту, используем функцию: =ПРПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип) :

Для получения полного ежемесячного платежа, нужно сложить основной платеж и процент, т.е. ОСПЛТ + ПРПЛТ.

Как посчитать процентную ставку по кредиту в Экселе

Чтобы узнать, какая процентная ставка по вашему кредиту – используйте функцию =СТАВКА(Кпер; Плт; Пс; Бс; Тип; Оценка) .

Если нужно получить годовую ставку – умножьте результат функции на количество периодов (платежей) в году. Например, на 12 месяцев, 4 квартала, 2 полугодия и т.п.

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

Как посчитать количество периодов на погашение кредита

Чтобы произвести расчет срока кредита по ежемесячному платежу – используйте функцию =КПЕР(Ставка; Плт; Пс; Бс; Тип) .

Давайте применим эту формулу к нашему примеру:

Как рассчитать сумму кредита

Если вдруг вы забыли, на какую сумму кредит – применяем функцию =ПС(Кпер; Ставка; Плт; Бс; Тип). И снова попробуем вычислить для нашего примера:

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

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

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

5 комментариев

Через какую функцию просчитать сумму фиксированных платежей при сумме займа 100000, прцентной ставке 20% годовых и количестве периодов 24,53 месяца
Функция ОСПЛТ даёт разные платежи в каждом месяце

Тогда у меня ещё один вопрос можно ли вычислить количество периодов по выплате займа через подбор параметров, зная сумму займа(100000 руб), процентной ставке(20% годовых) и сумме ежемесячных платежей(5000). Как не производил подбор мешает дело. Через формулу всё просто =КПЕР(20%/12;-5000;100000). Через подбор вроде можно вычислить процентную ставку, а вот как сроки выплаты по кредиту не получается

Олег, здравствуйте. Отвечаю на два Ваших вопроса. В Экселе нет функции расчета постоянной выплаты, т.к. она и без того просто вычисляется: Плт = Пс * (Ставка + Ставка / (1 + Ставка)^Кпер — 1). Для Вашего примера и периода 24 мес. расчет будет таким: 100000*(1,67% + 1,67% / (1 + 1,67%)^24 — 1) = 5089,58. Здесь 1,67% = 20%/12. Правда, этой схемой пользуются редко, т.к. кредит получается дороже.
В связке с этой формулой работает и подбор параметра, в результате получаются указанные Вами 24,53 мес.

Добрый день!
как в excel рассчитать платеж по кредиту с разной процентной ставкой
если первый год 8%
остальные 19 лет 12%

Здравствуйте, Наталья. Видимо, речь идет о кредите с переменной ежемесячной платой (не аннуитет). Тогда Считаем так: = /240 + * ЕСЛИ(

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector