Формула эксель для расчета кредита

Если вы хотите расчитать платежи по кредиту (аннуитетные или дифференцированные), переплату и график платежей по месяцам в excel, то скачайте следующие xls-файлы:

Альтернатива использованию MS Excel — кредитный калькулятор on-line (функционал практически такой же, ничего качать на компьютер не нужно: все операции выполняются на нашем сайте).

Расскажем о файлах подробнее: дадим мини-инструкции.

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

  • Считать оба вида платежей.
  • Показывать полный график платежей.
  • Разбивать сумму выплаты на "погашение долга" и "погашение процентов".
  • Учитывать досрочные возвраты (отдельно для уменьшения срока и уменьшения размера выплат).

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

Простая функция для платежей

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

Содержит всего одну функциональную ячейку:

Поменяйте значения на свои (вместо 14 — свою ставку, вместо 12 во втором случае — свой срок кредита в месяцах, вместо 100000 — свою сумму займа).

После смены данных достаточно нажать на "Энтер", чтобы получить результат:

Напоминаем, ссылки на оба xls-файла представлены в начале страницы.

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса — другое дело, а для обычных людей мышеловка"деньги за 15 минут, нужен только паспорт" срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это "потом" все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?

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

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

Вариант 1. Простой кредитный калькулятор в Excel

Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами — таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial) . Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:

Читайте также:  Субсидирование лизинга в 2018 году

  • Ставка — процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
  • Кпер — количество периодов, т.е. срок кредита в месяцах.
  • Пс — начальный баланс, т.е. сумма кредита.
  • Бс — конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
  • Тип — способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.

Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:

Вариант 2. Добавляем детализацию

Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel — ОСПЛТ (PPMT) и ПРПЛТ (IPMT) . Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:

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

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

=ЕСЛИ(A18<>""; текущая формула; "")

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

Читайте также:  Тел горячей линии ренессанс кредит

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

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

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять — не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты — заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

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

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

  • в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
  • отрицательные суммы — наши выплаты банку, положительные — берем дополнительный кредит к уже имеющемуся
  • подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)

Статьи по теме

Вычислить процент по кредиту можно с помощью онлайн-калькуляторов или самостоятельно. Расскажем про второй способ.

Как рассчитывается кредит

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

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

Как рассчитать процент по кредиту за год по аннуитетной схеме

Для начала вычислим сумму ежемесячного платежа можно по формуле, приведенной ниже. А после на основе полученных данных вычислим годовой процент.

Ежемесячный платеж = Сумма кредита × Ставка/ 1- (1 + Ставка)^ — Срок кредита

Обратите внимание, что в этой формуле используется ставка в месяц. Ее нужно вычислить отдельно, разделив годовой процент сначала на 100, а затем на 12. Срок кредита в формуле нужно показать в виде количества месяцев (например, три года – 36 месяцев). А знак «^» здесь обозначает возведение в степень.

Пример 1

Разберем на конкретном примере, как работает формула. Узнаем размер ежемесячного платежа, если известны следующие исходные данные:

  • сумма кредита – 800 тыс. руб.;
  • срок кредита – 2 года;
  • ставка – 12 процентов.

Тогда размер процентной ставки за месяц составит 0,01 (12/100/12). А платеж на каждый месяц вычислим по формуле:

800 000 х 0,01 / 1- (1+0,01)^ — 24 = 38 095

Читайте также:  Интернет для терминала эквайринга

Значит, общая сумма к выплате за все два года составит

38 095 х 24 = 914 280

Из этой суммы 114 280 руб. уйдет на выплату процентов. Значит, за один год компания отдаст на уплату процентов 57 140 руб. Теперь очевидно, как рассчитать проценты по кредиту за месяц. 57 140 руб. разделим на 12, получается примерно 4 762 руб.

Как рассчитать годовой процент по кредиту по дифференцированной схеме

Один из способов расчета дифференцированного платежа выглядит так:

Платеж = (Сумма / Срок) + (Остаток × Ставка/12)

Воспользуемся этой формулой для расчета процентов.

Пример 2

  • сумма кредита – 1 млн руб.;
  • срок кредита – 3 года;
  • ставка – 17 процентов.

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

1 месяц: (1 000 000 / 36) + (1 000 000 х 0,17/12) = 27 778 + 14 167 = 41 945

2 месяц: (1 000 000 / 36) + (41 945 х 0,17/12) = 27 778 + 594 = 28 372

3 месяц: (1 000 000 / 36) + (28 372 х 0,17/12) = 27 778 + 402 = 28 179

Сложные проценты по кредиту

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

Эту систему можно встретить редко, но о ее существовании все же стоит знать. Если кредитное учреждение хочет применить эту схему, с ним можно спорить. Ведь закон позволяет начислять только на основную сумму долга (ст. 317.1, 809 и 819 ГК). Схема «проценты на проценты» совсем не выгодна клиентам банка.

Как планировать платежи по кредиту с помощью Excel

Рассчитайте выгодный график погашения кредита.

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

Долг = Первоначальная сумма × (1 + Ставка за расчетный период/100%)^Количество расчетных периодов

Пример 3

Рассчитаем сложные проценты по кредиту, если известно следующее:

сумма кредита — 3 млн руб.

ставка – 20 процентов.

Во-первых, определим ежемесячную процентную ставку 20/100/12. Она составляет 0,0167 процентов.

Далее применим формулу для вычисления сложных процентов:

3 000 000 х (1 + 0,0167/100)^1 = 3 000 501

Тогда размер долга за весь период составит:

3 000 000 х (1 + 0,0167/100)^60 = 3 030 210

Гость, уже успели прочесть в свежем номере?

Какая схема лучше?

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

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

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