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

Подбор параметра для банковских депозитов

На протяжении 10-ти лет мы хотим накопить 20 000$. Свои сбережения будем откладывать на банковский депозит по 5% годовых. Деньги будем вносить на банковский депозитный счет ежегодно и одинаковыми частями взносов. Какой должен быть размер ежегодного взноса, чтобы за 10 лет собрать 20 000$ при 5-т и процентах годовых?

Для решения данной задачи в Excel воспользуемся инструментом «Подбор параметра»:

1. Составьте таблицу как показано на рисунке:

В ячейку B5 введите функцию: =БС(B1;B2;B3;)
Оставаясь на ячейке B5, выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра»

В появившемся окне заполните поля, так как на рисунке и нажмите ОК.

Результат вычисления получился с отрицательным числом – это правильно в соответствии со стандартом финансовых функций Excel. Регулярные взносы должны отображаться отрицательным значением, так как это категория расходных операций. А по истечению 10 лет мы получим на приход 20 000$.

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

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

Как использовать калькулятор ДЧП

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

Для использования калькулятора не требуется устанавливать или скачивать какое-либо программное обеспечение или файлы. Не требуется наличие установленного Microsoft Excel или аналогичных программных продуктов.

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

• Новую сумму ежемесячного платежа для последующих платежей.
• Итоговую сумму процентов по каждому месяцу и по кредиту в целом.

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

Можно, например, подобрать сумму ДЧП, чтобы уменьшить ежемесячный платеж до какого-то определенного уровня.

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

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

• ПСК = i * ЧБП * 100;
• где i – процентная ставка базового периода;
• ЧБП – число базовых периодов в календарном году.

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

Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).

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

Далее находим ЧБП: 365 / 28 = 13.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

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

Функция ПЛТ в Excel входит в категорию «Финансовых». Она возвращает размер периодического платежа для аннуитета с учетом постоянства сумм платежей и процентной ставки. Рассмотрим подробнее.

Для других банков

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

• Установите флаг — Первый платеж только проценты и введите дату выдачи
• Платеж в последний день месяца

Следует также понимать, что некоторые режимы расчета не сочетаются. Например: Учет досрочного погашения в дату платежа и учитывать выходные. При этих параметрах расчет может быть неверен

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

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

Стратегия для построения производственного плана выпуска продукта:

1. В текущем году продукт должен быть продан в количестве 10 000шт.
2. Производственные расходы 1-ой штуки: 7,5 руб.
3. Расходы на реализацию: 450 000 руб.

Какую установить розничную цену, чтобы рентабельность производства сохранялась на уровне 20%?

Рентабельность определяется как соотношение дохода к прибыли (прибыль разделить на доход) и выражается только в процентах!

Снова решим поставленную задачу в Excel с помощью подбора параметра:

Составьте таблицу с исходными данными и формулами, так как указано на рисунке ниже

Обратите внимание! В столбце D указаны, какие именно нужно вводить формулы в соответствующие ячейки столбца B. А в ячейке B1 указана цена 1 руб

чтобы избежать ошибок в формуле B3 и B10 (вероятная ошибка деления на 0). Не забудьте отформатировать все ячейки соответствующим форматам: денежный, общий, процентный.

Перейдите в ячейку B10 и выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра»

Заполните поля в появившемся диалоговом окне как на рисунке и нажмите ОК.

Как видно розничную цену (B1) нужно устанавливать в 2 раза выше производственных расходов на 1-ну штуку продукции. Только тогда мы сможем удержать рентабельность производства на уровне 20% при таких расходах на реализацию. В реальности бывает и еще хуже.

Задача2

Требуется накопить за 5 лет сумму 1 000 000 руб. Определить величину регулярных пополнений вклада, если процентная ставка составляет 10% годовых, пополнение вклада производится ежеквартально, капитализация процентов также производится ежеквартально, на счету уже содержится 100 000 руб.

Решение2

Накопить за счет взносов нам потребуется всего 900 000руб. (1 000 000-100 000). Ежеквартальный платеж может быть вычислен по формуле

=ПЛТ(10%/4; 5*4; -100000;1000000; 0)

, результат -32732,42р.

Все параметры функции

ПЛТ()

выбираются аналогично предыдущей задаче, кроме значения ПС = -100000р., который требует пояснения. Вспомним, что для

аннуитета

справедливо тождество: ПС СУММ(ОСПЛТ()) БС=0, т.е. ПС (-900000р.) 1 000 000=0. Отсюда получим ПС = -100000р.

Примечание

. В

файле примера

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

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

За вычисление аннуитета в Excel отвечает функция ПЛТ. Принцип вычисления в общем виде заключается в выполнении следующих шагов:

1. Составить исходную таблицу данных.
2. Построить график погашения долга для каждого месяца.
3. Выделить первую ячейку в столбике «Платежи по кредиту» и ввести формулу расчета «ПЛТ ($В3/12;$В$4;$В$2)».
4. Получившееся значение растянуть для всех столбцов таблички.

Результат работы функции ПЛТ

Расчет в MS Excel погашение основной суммы долга

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

Вычисление остатка суммы основного долга (при БС=0, тип=0)

Предположим, что кредит на 100000 рублей берется на 10 лет под 9%. Необходимо рассчитать сумму основного долга в 1 месяце 3-го года. Решение:

1. Составить таблицу данных и вычислить ежемесячный платеж по приведенной выше формуле ПС.
2. Рассчитать долю платежа, необходимую для погашения части долга, по формуле «=-ПМТ-(ПС-ПС1)*ставка=-ПМТ-(ПС +ПМТ+ПС*ставка)».
3. Посчитать сумму основного долга за 120 периодов по известной формуле.
4. Используя оператор ПРПЛТ найти количество процентов, выплаченных за 25 месяц.
5. Проверить результат.

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

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

• =«-БС(ставка; кон_период; плт; ; ) /(1+тип *ставка)».
• = «+ БС(ставка; нач_период-1; плт; ; ) /ЕСЛИ(нач_период =1;1; 1+тип *ставка)».

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

Если потребуется уменьшить срок кредитования, то придется производить дополнительные вычисления с помощью оператора ЕСЛИ. Так можно будет контролировать нулевой баланс, который не должен быть достигнут раньше окончания сроков выплаты.

Досрочное погашение с уменьшением срока

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

Уменьшение выплат кредитования

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

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

1. Ввести числа месяца, по которым вносятся платежи, и указать их количество.
2. Проконтролировать отрицательные и положительные суммы. Отрицательные предпочтительнее.
3. Посчитать дни между двумя датами, в которые вносились деньги.

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

Как рассчитать размер аннуитетного платежа?

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

А = К х S

В этой формуле:

A – размер платежа

K – коэффициент аннуитета

S – сумма полученного кредита

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

Здесь i – это месячная ставка процентов за пользование кредитом, которая рассчитывается путем деления годовой ставки на 12 месяцев

n – количество месяцев, на протяжении которого кредит необходимо погасить.

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

Примеры функции ПЛТ в Excel

Для корректной работы функции необходимо правильно внести исходные данные:

Размер займа указывается со знаком «минус», т.к. эти деньги кредитная организация «дает», «теряет». Для записи значения процентной ставки необходимо использовать процентный формат. Если записывать в числовом, то применяется десятичное число (0,08).

Нажимаем кнопку fx («Вставить функцию»). Откроется окно «Мастер функций». В категории «Финансовые» выбираем функцию ПЛТ. Заполняем аргументы:

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

Обратите внимание! В поле «Ставка» значение годовых процентов поделено на 12: платежи по кредиту выполняются ежемесячно. Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб

Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб.

Чтобы найти общую сумму, которую нужно выплатить за весь период (основной долг плюс проценты), умножим ежемесячный платеж по займу на значение «Кпер»:

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

Для этого в качестве аргумента «Тип» нужно указать значение 1.

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

Для подробного расчета составим таблицу:

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

В поле «Период» указываем номер периода, для которого рассчитывается основной долг.

Заполняем аргументы функции ПРПЛТ аналогично:

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

Рассчитываем остаток по основному долгу. Получаем таблицу следующего вида:

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

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

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

Формула для расчета процентной части

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

Для примера возьмем расчет кредита в полмиллиона рублей на два года с процентной ставкой 12% и ежемесячной комиссией 1% (снимается ежемесячно со всей суммы) при аннуитетных платежах. Основная задолженность рассчитывается в разнице аннуитетных платежей и процентов. Сумма процентов будет произведением месячной ставки на остаток долга, который равен разнице от остатка предыдущего периода и суммы основного долга в прошлом периоде.

В итоге по расчетам нужно будет вернуть банкирам от полумиллиона 684881,67 руб. Соответственно разница – 184 881, 67 руб. Тогда эффективная процентная ставка окажется на уровне 37%. Если же вести расчет без учета однопроцентной комиссии, то выйдем на эффективную процентную ставку в 13%.

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

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

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

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

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

Расчёт доли тела кредита в аннуитетных платежах

Зная долю процентов в аннуитетном платеже, можно легко посчитать долю тела кредита. Формула расчёта проста и понятна:

Как видите, здесь нет ничего сложного. По сути, аннуитетный платёж содержит в себе две составляющие:

1. 1. Долю процентов по кредиту.
2. 2. Долю тела кредита.

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

Расчёт доли тела кредита в нашем первом платеже выглядит так:

Надеемся, теперь всем понятно, откуда в графе «Погашение тела кредита» нашего графика аннуитетных платежей в выплатах за первый месяц взялась сумма 3763 руб. Да-да, это именно то, что осталось после того, как мы из суммы аннуитетного платежа (4680 руб.) вычли сумму процентов по кредиту (917 руб.). Аналогичным образом рассчитаны значения этой графы за последующие месяцы.

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

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

Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:

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

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

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

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

А = К * S

где:

• А – сумма платежа по кредиту;
• К – коэффициент аннуитетного платежа;
• S – величина займа.

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• где i – процентная ставка за месяц, результат деления годовой ставки на 12;
• n – срок кредита в месяцах.

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
2. Составим график погашения кредита. Пока пустой.
3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

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



Расчет платежей в Excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

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

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

где:

• ДП – ежемесячный платеж по кредиту;
• ОСЗ – остаток займа;
• ПП – число оставшихся до конца срока погашения периодов;
• ПС – процентная ставка за месяц (годовую ставку делим на 12).

Составим график погашения предыдущего кредита по дифференцированной схеме.

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

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Как использовать калькулятор ДЧП локально

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

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

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

При необходимости можно сохранить несколько вариантов расчёта в виде отдельных файлов, чтобы потом можно было сравнить расчёты.

Расчет ЭКС (эффективной кредитной ставки)

Есть несколько методов:

• с помощью специальной формулы;
• в программе Excel;
• с помощью кредитного калькулятора.

Рассмотрим каждый из них.

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

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

Последний показатель (ССК) определяют по дополнительным формулам в зависимости от типа погашения кредита.

При классической схеме погашения ССК определяют по формуле:

При аннуитетной схеме погашения ССК определяют по такой формуле:

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

Проведем пример расчета.

Клиент хочет оформить кредит на сумму 50 тыс. руб. на срок 12 месяцев. Ему нужно заплатить при выдаче займа страховку в размере 1000 руб., за оформление кредита — 250 руб., Процентная ставка по кредиту — 18,5% годовых. Размер платежей рассчитывается по классической схеме.

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

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

Итак, полная сумма кредитных расходов (СКР) составит 10500 руб.

Теперь определяем ССК (средневзвешенную сумму кредита) по вышеуказанной формуле:

Можно переходить к расчету эффективной кредитной ставки по формуле:

Теперь эту сумму умножаем на 100%. Получается 1,88% в месяц, так как мы использовали в формуле временной промежуток в 12 месяцев. Если клиент будет погашать кредит на протяжении всего срока действия, ЭКС составит 22,56% годовых, а не заявленные 18,5% годовых.

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

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

Давайте рассмотрим все на примере.

Клиент оформляет кредит на сумму 100 000 руб. Срок кредитования 24 месяца. Заявленная банком процентная ставка составляет 17% годовых. Клиент должен единоразово внести комиссию в размере 15 000 руб.

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

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

Месяц	Дата погашения	Сумма ежемесячного платежа
1	22.09.2016	-85000 (15000 — комиссия)
2	22.10.2016	4944,22
3	22.11.2016	4944,22
4	22.12.2016	4944,22

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

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

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

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

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

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

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

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

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

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

Справка: аннуитетные и дифференцированные платежи

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

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

См. также: Где взять заем на открытие бизнеса? Где получить кредит на развитие? Как выбрать банк: тонкости принятия решения

Кредитный калькулятор в Excel | СЕМЕЙНЫЙ БЮДЖЕТ и не только…

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

Вот автор блога «Эксель Практик» и предлагает завести собственный кредитный калькулятор у себя в книге Excel.

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

Итак, любой кредит имеет 4 основных параметра:

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

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

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

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

— Срок – Функция ПС()

— Сумма – Функция КПЕР()

— Ставка – Функция СТАВКА()

— Ежемесячный платеж – Функция ПЛТ()

Параметры функций одни и те же – знаете три из 4-х показателей, соответствующая функция выдаст 4-й. Нагляднее смотрите первый лист файла.

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

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

Переходим ко второму листу.

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

На второй строке:

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

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

Сумма погашения процентов как умножение величины прошедшего периода на соответствующий процент. Используется функция ДОЛЯГОДА, чтобы убрать последствия високосности. Банки скрупулезно подходят к расчетам, поэтому период считается в днях, иначе можно было бы сделать проще – взять годовой процент, поделить на 12 месяцев и умножить на сумму. Сумма погашения основного долга – берется как разница ежемесячного платежа и суммы погашения процентов. Досрочное погашения и его дата ставятся произвольно. Единственное условие – ставится в тот период, где дата или меньше или совпадает с датой досрочного погашения. Сумма долга после платежа определяется как сумма предыдущего периода за вычетом погашения основной части и суммы досрочного погашения.

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

Теперь сделаем такой же график для дифференцированных платежей.

Меняем две формулы:

1) Сумму погашения основного долга. Она будет неизменной — сумма долга разделить на количество периодов (месяцев).

2) Ежемесячный платеж определяем как сумму двух частей — погашений основного долга и процентов.

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

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

Для меня это была обоснованность взятия кредита. Я тогда снимал квартиру и поэтому мне нужно было рассчитать цену кредита. Цена кредита для меня равнялась сумме выплаченных процентов за минусом арендных платежей за весь период кредита. Если сумма небольшая или вообще отрицательная, то кредит брать стоит. Бонусом для меня было проживание в СВОЕМ (!) доме, где я знал, что могу забить гвоздь в МОЮ стенку, да и вообще психологическое влияние большое.

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

Делаем кредитный калькулятор

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

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

2.1 Постановка задачи:

1. Рассчитать
   аннуитетные платежи по кредиту суммой
   250 000 рублей, сроком на 1 год и под 17% годовых.
   Составить график платежей, с подробным
   описанием платежей непосредственно по
   кредиту, по процентам и оставшейся суммой
   платежа. (Использование функций ПС(), ПЛТ(),
   ПРПЛТ(), ОСПЛТ()).
   Рассчитать
   сумму ежемесячного вложения под 10% годовых,
   которое через 15 лет составит сумму вклада
   в 5000 рублей. Выплата производится в начале
   периода. (Использование функции ПЛТ()).
   Рассчитать
   сумму ежемесячного вложения под 10% годовых,
   которое через 15 лет составит сумму вклада
   5000 рублей, при первоначальном взносе
   1000 рублей. (Использование функции ПЛТ()).
   Рассчитать
   величину вложений под 18 % годовых, которые
   будут приносить ежегодно в течение 5 лет
   20 000 рублей. (Использование функции ПС()).
   Рассчитать
   величину первоначальных вложений, под
   15% годовых, которое через 10 лет принесет
   доход 1000 рублей, при условии внесении
   раз в год на счет 2000 рублей. (Использование
   функции ПС()).
   Вычислить
   выплаты по процентам за первый месяц
   для трехгодичного займа в 100 000 рублей
   из расчета 10% годовых. (Использование
   функции ПРПЛТ()).
   Вычислить
   доход за последний год от трехгодичного
   займа в 1000 рублей из расчета 10% годовых
   при ежегодных выплатах. (Использование
   функции ПРПЛТ()).
   Вклад размером
   в 5000 рублей положен с 10.01.2010 по 03.04.2010 под
   20% годовых. Найти величину капитала на
   03.04.2010 при начислении простых процентов.
   (Использование функции БС()).
   Определить
   сумму капитала, если изначально вложена
   сумма в размере 10 000 рублей, в банк на
   3 года под 15% годовых, далее в течение
   всего периода раз в месяц вносится сумма
   1000 рублей. Проценты начисляются раз в
   месяц, в начале. (Использование функции
   БС()).
   Определить
   будущую стоимость капитала 15000 рублей,
   помещенных в банк под 18% годовых, сроком
   на 5 лет. Проценты начисляются раз в квартал.
   (Использование функции БС()).
   Взята сумма
   в размере 9000 рублей сроком на 2 года под
   15% годовых. Рассчитать сумму остаточных
   платежей для каждого года займа. (Использование
   функции ОСПЛТ()).
   С кредитно-дебетовой
   карты взята сумма в размере 7000 рублей
   сроком на 3 года под 17% годовых. Рассчитать
   сумму остаточных платежей для каждого
   квартала займа, при условии, что конец
   периода на счету должна быть накоплена
   сумма 8000 рублей. (Использование функции
   ОСПЛТ()).
   Рассчитать
   через сколько лет сумма вклада в размере
   15 000 рублей достигнет 5000 рублей, при процентной
   ставке 15% годовых. (Использование функции
   КПЕР()).
   Начиная
   с 30 лет каждый год на счет в банк вносится
   1000 рублей. К какому возрасту человек станет
   миллионером, при условии, что процентная
   ставка равна 18% годовых. (Использование
   функции КПЕР()).
   Рассчитать
   через сколько лет произойдет полное погашение
   займа размером 25000 рублей, если выплаты
   5000 рублей производятся в конце каждого
   квартала, а процентная ставка равна 17%
   годовых. (Использование функции КПЕР()).