Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).
Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.
Как рассчитать платежи по кредиту в Excel
Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:
- Аннуитет предполагает, что клиент вносит каждый месяц одинаковую сумму.
- При дифференцированной схеме погашения долга перед финансовой организацией проценты начисляются на остаток кредитной суммы. Поэтому ежемесячные платежи будут уменьшаться.
Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.
Расчет аннуитетных платежей по кредиту в Excel
Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:
А = К * S
где:
- А – сумма платежа по кредиту;
- К – коэффициент аннуитетного платежа;
- S – величина займа.
Формула коэффициента аннуитета:
К = (i * (1 + i)^n) / ((1+i)^n-1)
- где i – процентная ставка за месяц, результат деления годовой ставки на 12;
- n – срок кредита в месяцах.
В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:
- Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
- Составим график погашения кредита. Пока пустой.
- В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в 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 руб.), черная – дифференцированный способ.
Формула расчета процентов по кредиту в Excel
Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:
Рассчитаем ежемесячную процентную ставку и платежи по кредиту:
Заполним таблицу вида:
Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.
Сумма основного долга = аннуитетный платеж – проценты.
Сумма процентов = остаток долга * месячную процентную ставку.
Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.
Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:
- взяли кредит 500 000 руб.;
- вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
- переплата составила 184 881, 67 руб.;
- процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
- Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.
Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.
Расчет полной стоимости кредита в Excel
Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:
- ПСК = i * ЧБП * 100;
- где i – процентная ставка базового периода;
- ЧБП – число базовых периодов в календарном году.
Возьмем для примера следующие данные по кредиту:
Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).
Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.
Далее находим ЧБП: 365 / 28 = 13.
Теперь можно найти процентную ставку базового периода:
У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8
Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.
ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.
Скачать кредитный калькулятор в Excel
Таким образом, для расчета аннуитетных платежей по кредиту используется простейшая функция ПЛТ. Как видите, дифференцированный способ погашения несколько сложнее.
Источник
Всем привет.
Как я недавно обнаружил, люди не очень понимают как устроены кредиты.
Попробую объяснить на примере кредита с аннуитетным платежом что откуда и когда берется.
В конце статьи есть ссылка на файл График_платежей.xlsx. Все расчеты и скриншоты для этой статьи сделаны при помощи этого файла.
Расчет стандартного кредита.
Есть такое заблуждение: «Кредит выгодно гасить досрочно только в начале. Вроде как сначала человек выплачивает проценты, а потом начинает выплачивать тело кредита».
Это заявление в корне не верно. На самом деле, все довольно просто. Возьмем, для примера, расчет кредита на 5 лет на сумму в 1 000 000руб.
Картинка автора. Расчет базовых условий по кредиту. Дальше в статье будет развивать варианты этого расчета.
Инструкция:
Заполнению подлежат поля, выделенные желтым. (D1, D2, H6)
Сумма переплаты по графику (F2) считается как сумма ежемесячных платежей минус сумма кредита.
Ежемесячный платеж (F3) считается для начальных условий.
Фактическая сумма переплаты (F4) считается как сумма значений по столбцы «Платеж п процентам»(E).
Экономия на переплате (F5) = Сумма переплаты по графику (F2) — Фактическая сумма переплаты (F4)
Рассмотрим первую строку.
Нас интересует столбец «Платеж по процентам». Его очень просто рассчитать:
Платеж по процентам = [Сумма долга(1 000 000)] х [на ставку (0,12)]/[количество месяцев в году(12)]=10 000руб.
Я для удобства выбрал для расчета ставку 12%, а этом случае набежавшие проценты за месяц равны 1% от суммы долга.
Далее, у нас имеется часть ежемесячного платежа в счет уменьшения тела кредита.
Платеж по телу долга= [Аннуитетный платеж]-[платеж по процентам].
Для расчета аннуитетного платежа в Excel есть специальная функция [ПЛТ]. Эта функция использована для ячейки F3 и всего столбца G.
Соответственно, в следующем месяце тело долга уже меньше 1 000 000руб, платеж по процентам уменьшился и на ту же величину увеличился платеж по телу кредита.
Ну и так далее, до конца выплат.
Про досрочные платежи.
Теперь рассмотрим варианты расчетов с досрочными платежами в разные моменты времени. За основу возьмем все тот же кредит.
Конечно, эффект от досрочного погашения в начале срока и в конце срока будет сильно отличаться. Ниже, для примера, дополнительный взнос в размере 50 000руб осуществляется на 2й месяц и на 40й из 60.
Картинка автора. 50 000 внесено на 2й месяц.
Картинка автора. 50 000 внесено на 40й месяц.
Сумма экономии отличается значительно (ячейка F5).
Но это связано не с тем что «Сначала гасятся проценты» а с эффектом сложного процента.
Как только вы внесли дополнительный платеж, можно условно считать что вы открыли вклад на эту сумму. «Тело вклада» в конце вам ,конечно, не вернут, а проценты вполне начнут капать.
Итак, имеет смысл вносить досрочные платежи в любой момент. С момента внесения платежа на эту сумму перестанет начисляться долг, вот и все.
Варианты досрочных платежей.
Рассмотрим два метода действия после внесения досрочного платежа (вкладка «Кредиты с капитализацией»):
Первый вариант.
Сохранение изначальной суммы ежемесячного платежа.
Во второй вкладке файла (Кредиты с капитализацией) можно наблюдать что будет происходить, если после внесения дополнительного платежа не менять фактическую сумму ежемесячного платежа. То есть, у нас изначально была платеж 22 244,45 руб. Мы внесли досрочно 100 000 руб. Обязательный платеж стал 19 963,87 руб. Но вы продолжаем платить изначальную сумму,22 244,45 руб. В результате у нас обнаруживается второй столбец с досрочными взносами [Разница между минимальным и начальным ежемесячным платежом].
Картинка автора. Размер досрочного платежа ежемесячно растет.
По такой технологии удастся сэкономить на процентах примерно 72 000руб. При этом кредит закроется на 7 месяцев быстрее.
То есть, если бы мы платили по графику, нам бы еще предстояло выплатить 7*22 000руб= 154 000руб.
Результат:
Осуществив досрочное погашение кредита в размере 100 000руб на второй месяц, не меняя ежемесячный платеж, получилось закрыть кредит на 7 месяцев быстрее
Если предположить что 7 месяцев мы продолжаем извлекать из бюджета 22 250руб, к расчетному дате выплаты кредита можно накопить 155 750руб+16 450руб(остаток с последнего месяца фактической выплаты кредита) = 172 200 руб.
Обращаю внимание, что это число равно сумме досрочных платежей и числа из поля «Экономия на переплате».
Второй вариант.
Я считаю думаю что морально реализовать проще, чем на досрочное погашение отправлять 2387,87руб.
Разницу между изначальным и новым ежемесячным платежом направлять на вклад (вкладка «Кредит+вклад»).
Картинка автора. Вместо досрочного погашения как в варианте 1 разницу в платежах направляем на вклад.
Результат.
Тут все просто. Образовавшуюся разницу между изначальным и новым ежемесячным платежом отправляем на вклад. Выплата кредита произойдет в срок. На дату выплаты на вкладе будет 149 000руб (Поле H2).
Из плюсов — В случае каких-то проблем с начислением банком процентов будет проще разбираться если досрочные платежи будут вида 10 000руб, а не 2387,87руб. Будет легче пересчитать и разобраться.
Выводы.
— По возможности надо вносить досрочные платежи в любой момент времени.
— На сумму досрочного платежа начинают «начисляться» проценты, как на вклад.
— Проще разницу между суммами обязательных платежей отправлять на вклад. Не будет сложностей, если придется обращаться в банк для пересчета сумм платежей. И морально будет легче, что деньги копятся. Можно иногда, по достижении некоторой суммы отправлять тоже на досрочные платежи.
Осторожнее с досрочными платежами НЕ В ДАТУ очередного платежа.
При внесении досрочного платежа НЕ В ДАТУ основного платежа надо быть осторожным. Дело в том, что в этот момент гасятся проценты, набежавшие с начала месяца, а потом тело кредита.
Приведу пример:
Дата очередных платежей по кредиту 1 число каждого месяца.
Мы хотим 10го числа сделать дополнительный платеж.
Итого, мы погасим проценты, которые набежали с 1 по 10 число. Погасим тело кредита на сумму [Размер платежа]-[Проценты с 1 по 10]. После этого, проценты с 10 по 30 продолжат капать. Я точно не знаю какую сумму к погашению основного долга выставят в этом случае к дате очередного платежа.
Тут важно понимать, что проценты в следующую дату платежа в любом случае придется вносить. Как точно пересчитывается платеж я не знаю. Надо читать договора и разбираться.
Однозначно можно сказать что очередной платеж будет далеко не 0. Как я слышал, многие на это накалывались и попадали на пени и штрафы.
Именно по этому рекомендуется вносить досрочные платежи в дату списания обязательного платежа.
Скачать файл с расчетами можно по ссылке.
Пользоваться очень просто, подставляете свои значения суммы, срока, ставок, досрочных платежей и получаете результат.
Если есть какие-то замечания, вопросы или пожелания по расчетам, не стесняйтесь, пишите в комментариях.
Источник
Когда вы взяли кредит, вы так или иначе думаете о досрочном погашении.
Есть люди которые платят кредит и все. А есть те, которые каждый раз смотрят, сколько осталось платить, какая сумма основного долга. Я отношу себя ко второму типу людей, я смотрю сколько сейчас сумма основного долга, пытаюсь рассчитать, сколько будет платеж, если я сделаю досрочное погашение.
На данный момент у меня есть два калькулятора кредита для своих расчетов. Оба калькулятора сделаны в Excel. Калькуляторы позволяют достаточно быстро и просто рассчитать ипотеку.
Читайте также: Как рассчитать кредит в Excel самому?
Скачать кредитный калькулятор в Excel
Первый кредитный калькулятор в Excel можно скачать по ссылке.
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.
Для расчета досрочного погашения можно также воспользоваться онлайн версией калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel.
На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Найти и скачать мобильные версии калькуляторов можно с главной страницы сайта.
Достоинства данного калькулятора:
- Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
- Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
- Построение и расчет графика платежей в виде таблицы в Excel. Таблица графика платежей может также редактироваться
- При расчете учитывается високосный и невисокосный год. За счет этого сумма начисленных процентов практически совпадает с значениями, рассчитываемыми ВТБ24 и Сбербанком
- Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
- Калькулятор можно редактировать под себя, задавая разные варианты расчета.
Недостатки калькулятора
- Нет учета возможное изменение процентной ставки во время выплат кредита
- Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
- Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
- Не рассчитывается вариант — первый платеж только проценты. В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
- Расчет производится для процентой ставки с 2мя знаками после запятой.
Всех выше названных недостатков лишен кредитный калькулятор для iPad/iPhone. В целом недостатки не сильно критичны и они присущи любому кредитному калькулятору онлайн.
Другой кредитный калькулятор в Excel можно скачать по данной ссылке. Данный кредитный калькулятор не позволяет рассчитать досрочное погашение. Однако его плюс в том, что он рассчитывает кредит с несколькими процентными периодами. Если сумма процентов по кредиту за данный месяц больше суммы аннуитетного платежа, то график для первого кредитного калькулятора в excel строится некорректно. В графике получаются отрицательные суммы.
Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.
Дмитрий Тачков
Работник банка или другого фин. учреждения
Подробнее
Создатель проекта, финансовый эксперт
Привет, я автор этой статьи и создатель всех калькуляторов данного проекта. Имею более чем 3х летний опыт работы банках Ренессанс Кредит и Промсвязьбанк. Отлично разбираюсь в кредитах, займах и в досрочном погашении. Пожалуйста оцените эту статью, поставьте оценку ниже.
Источник
Кредитный калькулятор позволяет легко и быстро рассчитать график платежей по планируемому кредиту или займу, чтобы оценить свои возможности выполнить обязательства и погасить долг. Зная несколько параметров кредита/займа, вы можете подобрать удобные для вас условия и сравнить их с предложениями кредиторов (банков, микрофинансовых организаций, потребительских кооперативов, ломбардов).
Процентная ставка, % годовых
Порядок погашения
Периодичность погашения
Переносить даты платежей на понедельник
Параметры кредита/займа
- Сумма кредита/займа
- Срок кредита/займа
- Процентная ставка
- Дата выдачи кредита/займа
Результаты расчета
- Всего выплат
Полная стоимость кредита/займа
- Переплата
Расчет
Расчет ПСК произведен в соответствии с Федеральным законом «О потребительском кредите (займе)». Точность результата расчета ПСК зависит от реализации погрешностей вычислений, связанных с приближенным методом нахождения решения, погрешностями вычислений с использованием конкретного программного средства и вычислительной техники, точностью задания входных данных платежей. В ряде случаев значение ПСК может быть ниже номинальной годовой процентной ставки по кредиту/займу, что допускается при соблюдении корректности расчета в соответствии с законодательством.
Данный расчет произведен исключительно в информационных целях. Ссылка на него не может являться единственным (основным) аргументом в споре или при принятии решения.
Посмотрите выгодные предложения от банков и выберите то, что вам подходит
Удобный кредитный калькулятор
Если планируете взять кредит, то Кредитный калькулятор онлайн — то, без чего не обойтись. При помощи такого инструмента Вы без труда свяжете сумму кредита со сроком выплаты и узнаете переплату. Большинство заемщиков знает о том, что, чем меньше срок выплаты, тем больше будет ежемесячный платеж, но ниже переплата. При увеличении срока кредита ежемесячный платеж становится комфортнее, но увеличивается размер переплаты.
В кредитном калькуляторе, все параметры, кроме размера первоначального взноса, являются обязательными для заполнения. Если пропустить графу, это место будет обозначена красным цветом. Вернитесь и заполните пропуск, иначе дальнейший расчет будет невозможен.
Как рассчитать кредит
Предлагая клиенту потребительский кредит, работник банка, как правило, ведет речь о расчете максимально доступной суммы для клиента. Затем, с помощью кредитного калькулятора рассчитывается сумма переплаты и размер ежемесячного платежа. Также, банки дают возможность заемщикам рассчитать сумму займа и график погашения платежей самостоятельно, воспользовавшись кредитным калькулятором. Для того, чтобы рассчитать доступную клиенту сумму, вводится размер ежемесячного дохода заемщика или его семьи. Как правило, кредитные калькуляторы банков предоставляют информацию только о размере ежемесячного платежа по кредиту и сумме переплаты. Рассчитать указанные параметры кредита онлайн можно на этой странице, при помощи отведённых полей кредитного калькулятора.
Все, что требуется с Вашей стороны — вводить исходные данные, а математические вычисления возьмет на себя «кредитный калькулятор онлайн».
Источник
