Как считать кредиты в 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 + * ЕСЛИ(

Расчет кредита в Excel

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

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

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

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

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

Расчет кредита в Excel функцией ПЛТ

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

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

Вычисление переплаты по кредиту

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

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

Подробный расчет выплат по кредиту

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

Расчет выплаты тела кредита

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

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

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

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

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

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

credit6.png

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

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

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

credit8.png

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

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

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

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

credit7.png

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

Ссылка на основную публикацию