как составить график платежей по кредиту в excel
Расчет кредита в Excel
Так что очень надеюсь, что изложенный ниже материал вам не пригодится.
Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. «Помассажировать числа» заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.
Вариант 1. Простой кредитный калькулятор в Excel
Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:
Вариант 2. Добавляем детализацию
Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:
Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:
Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку («») в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:
=ЕСЛИ(A18<>«»; текущая формула; «»)
Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:
Вариант 3. Досрочное погашение с уменьшением срока или выплаты
Реализованный в предыдущем варианте калькулятор неплох, но не учитывает один важный момент: в реальной жизни вы, скорее всего, будете вносить дополнительные платежи для досрочного погашения при удобной возможности. Для реализации этого можно добавить в нашу модель столбец с дополнительными выплатами, которые будут уменьшать остаток. Однако, большинство банков в подобных случаях предлагают на выбор: сокращать либо сумму ежемесячной выплаты, либо срок. Каждый такой сценарий для наглядности лучше посчитать отдельно.
Вариант 4. Кредитный калькулятор с нерегулярными выплатами
Существуют варианты кредитов, где клиент может платить нерегулярно, в любые произвольные даты внося любые имеющиеся суммы. Процентная ставка по таким кредитам обычно выше, но свободы выходит больше. Можно даже взять в банке еще денег в дополнение к имеющемуся кредиту. Для расчета по такой модели придется рассчитывать проценты и остаток с точностью не до месяца, а до дня:
Как рассчитать график погашения кредита для калькулятора в Excel
Главный показатель в графике погашения кредита – это сумма ежемесячного платежа. Для расчета размера суммы ежемесячного платежа по кредиту в Excel можно воспользоваться функцией ПЛТ (платеж). В качестве аргументов функции можно указать, конкретные значения, например, сумма кредита и процентная ставка. Но если ввести эти значения в отдельные ячейки, а в соответственных аргументах функции ПЛТ указать ссылки на них, вы получите возможность легко изменять условия кредитования и проверять их влияние на размер ежемесячного платежа по кредиту. Таким образом мы можем сделать кредитный калькулятор и составить график платежей в Excel.
Как сделать кредитный калькулятор в Excel
Ниже на рисунке представлен пример простейшего кредитного калькулятора в Excel, который вычисляет сколько придется платить банку ежемесячно для погашения кредита. Пользователь должен указать соответственные значения условий кредитования в ячейках B2:B4, а в ячейке B6 получим результат вычисления функции ПЛТ. Ежемесячный платеж по кредиту вычисляется следующей формулой:
Функция ПЛТ имеет 3 обязательных аргумента для заполнения и 1 опциональный:
Примечание: Если функция ПЛТ должна возвращаться положительное число, тогда для этого можно указать отрицательное число в третьем аргументе ПС. В таком случае вычисление будет выполнено с перспективы банка: кредит идет на расход, а платежи по кредиту с процентами ставятся на приход банку.
Наиболее частой ошибкой в финансовых формулах является путаница с периодами капитализации и частотой платежей по кредиту. В данном примере годовая процентная ставка кредита разделена на число 12 с целью вычисления ежемесячной процентной ставки для начисления капитализации. В тоже время период платежа умножается на 12, так как платежи платятся ежемесячно. Оба аргумента приспособлены до ежемесячных периодов капитализации и платежей, только тогда результат вычисления будет правильным.
Если пользователь функции забыл разделить ставку дисконтирования на 12. Excel принял бы введенное значение как ежемесячное дисконтирование и платеж был бы значительно больше. Аналогично, если бы количество платежей указать количеством лет вместе с ежемесячным дисконтированием, Excel принял бы, что платежи будут платится каждый год.
Функция ПЛТ изначально не знает, что значит введенное число – количество: лет, месяцев или дней. Она воспринимает, что ставка дисконтирования и количество лет касаются одного и того же периода.
Как составить график платежей по кредиту в Excel
Имея вычисленную сумму платежа по кредиту, можно составить график платежей по кредиту содержащую информацию о части суммы тела кредита и суммы процентов для каждого платежа. А также остаток долга по кредиту после каждого уплаченного платежа.
Ниже на рисунке представлен фрагмент графика. Он состоит из следующих столбцов:
В примере указанному на втором рисунке срок кредитования составляет 15 лет, а на первом рисунке – срок 30 лет. Уменьшение периода погашения кредита увеличивает сумму ежемесячного платежа.
Последним шагом будет возможность скрытия ячеек с отрицательными значениями в графике погашения кредита. В строках калькулятора до конца периода полного погашения кредита по условию его срока продолжительности лет. То есть если мы укажем другую продолжительность срока кредитования, на пример не 15 или 30 лет, а 10, то лишние результаты вычислений будут скрыты. Это можно реализовать с помощью условного форматирования, которое будет изменять цвет шрифта значений соответственных ячеек на белый. Устанавливая белый цвет шрифта, отображаемый на белом фоне, можно легко скрыть ненужные данные.
Для этого выделите диапазон ячеек D4:H363 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
Ниже представлена формула использована в условном форматировании изображена ниже на рисунке:
Калькулятор расчета кредита в Excel и формулы ежемесячных платежей
Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).
Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.
Как рассчитать платежи по кредиту в Excel
Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:
Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.
Расчет аннуитетных платежей по кредиту в Excel
Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:
Формула коэффициента аннуитета:
В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:
Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.к. мы эти деньги будем отдавать банку, терять.
Расчет платежей в Excel по дифференцированной схеме погашения
Дифференцированный способ оплаты предполагает, что:
Формула расчета дифференцированного платежа:
ДП = ОСЗ / (ПП + ОСЗ * ПС)
Составим график погашения предыдущего кредита по дифференцированной схеме.
Входные данные те же:
Составим график погашения займа:
Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.
Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).
Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9 Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.
Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.
Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:
Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.
Формула расчета процентов по кредиту в Excel
Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:
Рассчитаем ежемесячную процентную ставку и платежи по кредиту:
Заполним таблицу вида:
Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.
Сумма основного долга = аннуитетный платеж – проценты.
Сумма процентов = остаток долга * месячную процентную ставку.
Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.
Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:
Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.
Расчет полной стоимости кредита в Excel
Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:
Возьмем для примера следующие данные по кредиту:
Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).
Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.
Далее находим ЧБП: 365 / 28 = 13.
Теперь можно найти процентную ставку базового периода:
У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8
Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.
ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.
Таким образом, для расчета аннуитетных платежей по кредиту используется простейшая функция ПЛТ. Как видите, дифференцированный способ погашения несколько сложнее.
Образец составления графика платежей по кредиту в Excel
Excel — это универсальная программа, которая позволяет проводить вычисления любой степени сложности. Очень часто она используется кредиторами и заёмщиками для вычисления суммы выплат по кредиту, процентов и остатка. В этом случае наиболее целесообразным будет составление графика платежей. Образец в Excel станет замечательным помощником в этом деле.
Преимущества и недостатки использования Excel
Перед тем как использовать программное обеспечение для составления платёжного графика, необходимо подробно изучить его преимущества и недостатки. Благодаря им можно значительно ускорить процесс и избежать большинства простых ошибок. Основные достоинства использования Excel:
Несмотря на эти важные достоинства, у использования Excel есть и несколько недостатков. Их нужно учитывать перед началом проведения расчётов и составлением платёжного графика, в противном случае можно столкнуться с различными трудностями, которые осложнят процесс и увеличат вероятность получения недостоверных данных. Отрицательные характеристики проведения расчётов Excel:
Образцы расчётов по кредиту
Расчёт графика платежей по кредиту в Excel предусматривает необходимость вычисления двух отдельных выплат, процентов и общей стоимости. Кроме этого, нужно учитывать вариант образования задолженностей или досрочного погашения займа.
Аннуитетные платежи
Они представляют собой выплаты по кредиту, когда все платежи являются равными между собой. Это касается общей суммы основной задолженности и предусмотренных процентов. Такой вариант считается наиболее популярным, поэтому большинство банков предпочитает вести расчёт ипотечных и потребительских кредитов именно по нему.
Главное преимущество аннуитетного платежа — необходимость ежемесячно погашать одну и ту же сумму. Это позволяет людям, взявшим деньги у банка в долг на длительный период, не бояться, что сумма увеличится в момент погашения кредита. Среди недостатков следует выделить тот факт, что при большом размере долга заёмщику поначалу нужно будет выплачивать проценты, а не саму задолженность. При этом переплата будет значительно больше, чем в случае проведения других платежей. Другим минусом является то, что совершить платёж по кредиту можно только в строго определённую дату.
Размер ежемесячного аннуитетного платежа определяется по стандартной формуле А=K*S, где S — сумма кредита, а K — коэффициент аннуитетного платежа. Последний параметр вычисляется с учетом продолжительности срока взятого кредита и месячной процентной ставки (1/12 от годовой). Порядок введения данных в Excel:
В итоге вся таблица заполнится данными с отрицательным знаком, соответствующими сумме, которую нужно ежемесячно платить банку.
Дифференцированные выплаты
Эти платежи являются способом оплаты кредита, при котором вносятся равные суммы по основной задолженности, а проценты начисляются на остаток. Этот вариант не такой популярный, как аннуитетный, применяется он только некоторыми крупными банками. Обусловлено это тем, что размер переплаты, которую вносит заёмщик, значительно меньше.
Другим важным достоинством дифференцированного способа является то, что в заключительные месяцы гашения задолженности переплата будет минимальной. Кроме этого, заёмщик имеет право досрочно выплатить всю сумму кредита и избежать начисления определённой части процентов. Главный недостаток — необходимость переплачивать большие суммы на начальном этапе гашения задолженности.
Расчёт размера дифференцированной выплаты осуществляется по общей формуле ДП=ОСЗ/(ПС*ОСЗ/ПП). В ней присутствуют такие условные обозначения:
Платёжный график можно составить при помощи специального кредитного калькулятора или программы Excel. Последний вариант более сложный, но позволяет получить точные данные. Порядок проведения расчётов в Excel:
Полная стоимость
Полная стоимость кредита должна рассчитываться в процентах по формуле ПСК=ЧБП*i*100, где ЧБП — число базовых периодов за один календарный год, i — ставка в процентах. Этот показатель можно также рассчитать при помощи программы MS Excel. Для этого нужно выполнить следующие действия:
Составление графика платежей в Excel — это трудоёмкое мероприятие, которое требует наличия определённых знаний и навыков проведения подобной работы. При правильном подходе к делу и точном соблюдении всей последовательности операций можно значительно упростить задачу и избежать непредвиденных ошибок.
Расчет аннуитетных платежей по кредиту в Excel
В наш век высоких технологий и автоматизации как-то неприлично вручную выполнять сложные расчёты. Хоть аннуитетные платежи рассчитать не так и трудно, но как говорит Юрий Ашер:
«Не надо напрягать свой мозг там, где это могут сделать за вас другие!»
В нашей ситуации к вам на помощь придут: компьютер и программа Microsoft Excel.
Хотим предупредить, что команда портала temabiz.com поставила перед собой цель не просто дать вам «халяву» в виде «экселевского» файла с готовыми расчетами. Нет, в этой публикации мы вас научим самостоятельно рассчитывать аннуитетные платежи, а также составлять в программе Excel графики погашения аннуитетных кредитов. Ну а для ленивых мы, конечно же, выложим готовые файлы кредитных калькуляторов.
Как рассчитать аннуитетный платеж в Excel
Те, кто читал предыдущую публикацию, наверняка ещё долго будут с ужасом вспоминать формулу аннуитетного платежа. Но сейчас вы, дорогие друзья, можете облегчённо вздохнуть, ибо все расчёты за вас сделает программа Microsoft Excel.
Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.
Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:
Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.
Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:
Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:
Здесь нам требуется заполнить три поля:
Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.
После того как исходные данные будут введены, жмём кнопку «Ок». В результате мы видим в блоке расчетов точное значение ежемесячного аннуитетного платежа:
Итак, в данный момент сумма нашего аннуитетного платежа составляет 4680 руб (на рисунке он обведён и указан под номером 1). Если вы будете менять сумму кредита, процентную ставку и общий срок кредитования, то автоматически будет меняться значение вашего аннуитетного платежа.
Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках. Можно было просто вписать в строке формул то, что там сейчас вписано.
Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:
На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.
Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.
Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.
Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:
Расчет в Excel суммы кредита для заданного аннуитетного платежа
В чём «фишка» аннуитетной схемы погашения кредита? Правильно! Основная «фишка» в том, что заёмщик выплачивает кредит равными суммами на протяжении всего срока кредитования. С такой схемой очень удобно планировать свой бюджет. Например, вы готовы ежемесячно выделять на погашение кредита 5000 рублей. По вашим скромным подсчётам, такая нагрузка будет для вас не слишком обременительной. Естественно, у вас возникает закономерный вопрос: «А на какую сумму кредита я могу рассчитывать?» В общем, нам нужен новый кредитный калькулятор, у которого в исходных данных будет не сумма кредита, а величина аннуитетного платежа.
Что же, друзья, не будем терять время! Открываем программу Microsoft Excel и приступаем к разработке нашего кредитного калькулятора!
Итак, структура нового кредитного калькулятора почти не изменилась. Здесь также есть блок с исходными данными и блок с расчётами. Единственное изменение, это то, что в исходных данных мы вводим ежемесячный аннуитетный платёж, который готовы выплачивать, а в расчётах получаем сумму кредита, на которую мы можем рассчитывать. Собственно, она на нашем рисунке обведена и отмечена под номером 1.
Чтобы рассчитать сумму ожидаемого кредита надо воспользоваться функцией ПС, предварительно кликнув по ячейке, в которой мы хотим видеть свой расчёт (в нашем калькуляторе это ячейка с координатой C11). Вызвать функцию ПС можно нажав на знакомую вам кнопку «fx», которая находится слева от строки формул. В появившемся окне выбираем «ПС» и жмём «Ок». В открывшейся таблице вводим следующие данные:
Жмём «Ок» и в ячейке С11 появилась сумма 53 422 руб. – именно на такой размер кредита может рассчитывать заёмщик, который готов на протяжении 12 месяцев ежемесячно выплачивать по 5000 руб.
Кстати, обратите внимание на данные в строке формул (на рисунке они обведены и указаны под номером 2). Вы всё правильно поняли, друзья! Да, это те данные, которые необходимы для расчёта суммы кредита в нашем калькуляторе: =ПС(C5/12;C6;-C4). Те самые параметры, которые мы вводили в таблице функции ПС.
Расчёт остальных показателей выполняется по такому же принципу, как и в предыдущем калькуляторе:
Кстати, интересный момент. Вот в нашем примере, выплачивая ежемесячно в течение года по 5000 рублей, мы можем рассчитывать на сумму кредита равную 53 422 рубля. А что делать, если надо больше денег? Как вариант, можно увеличить срок кредитования. Если вместо 12 месяцев поставить 24, то сумма кредита увеличится до 96 380 рублей. Эти данные нам мгновенно выдал наш кредитный калькулятор, который вы можете скачать ссылке ниже:
Кредитный калькулятор в Excel по расчету графика аннуитетных платежей
Два предыдущих кредитных калькулятора очень удобны, но они выполняют краткие (общие) расчёты. А иногда заёмщику нужна расширенная информация – график ежемесячных аннуитетных платежей с детальной расшифровкой каждой выплаты (с указанием сумм, идущих на погашение процентов, и сумм, погашающих тело кредита). В общем, сейчас мы сделаем в программе Excel ещё один кредитный калькулятор, который будет автоматически рассчитывать график аннуитетных платежей. Щёлкаем мышкой по рисунку:
Перед вами расширенная и доработанная версия нашего первого кредитного калькулятора (того, который рассчитывает размер ежемесячного аннуитетного платежа по кредиту). Здесь кроме стандартных блоков с исходными данными и расчётами, появилась таблица, в которой детально расписаны все наши будущие ежемесячные выплаты. Таблица имеет пять колонок:
Вот так легко и непринуждённо мы разработали кредитный калькулятор по расчёту графика аннуитетных платежей. Скачать его можно ссылке ниже:
Итак, друзья, теперь у вас есть целых три кредитных калькулятора по расчёту аннуитетных платежей, разработанных в программе Microsoft Excel. В следующей публикации мы расскажем о досрочном погашении аннуитетного кредита.