Расчет кредита в Excel
Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса - другое дело, а для обычных людей мышеловка"деньги за 15 минут, нужен только паспорт" срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это "потом" все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?
Так что очень надеюсь, что изложенный ниже материал вам не пригодится.
Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. "Помассажировать числа" заранее, как я это называю :) Microsoft Excel может сильно помочь в этом вопросе.
Вариант 1. Простой кредитный калькулятор в Excel
Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами - таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial). Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:
- Ставка - процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
- Кпер - количество периодов, т.е. срок кредита в месяцах.
- Пс - начальный баланс, т.е. сумма кредита.
- Бс - конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
- Тип - способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.
Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:
Вариант 2. Добавляем детализацию
Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel - ОСПЛТ (PPMT) и ПРПЛТ (IPMT). Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):
Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:
Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:
Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:
=ЕСЛИ(A17>=$C$7;"";A17+1)
Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку ("") в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:
=ЕСЛИ(A18<>""; текущая формула; "")
Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:
Вариант 3. Досрочное погашение с уменьшением срока или выплаты
Реализованный в предыдущем варианте калькулятор неплох, но не учитывает один важный момент: в реальной жизни вы, скорее всего, будете вносить дополнительные платежи для досрочного погашения при удобной возможности. Для реализации этого можно добавить в нашу модель столбец с дополнительными выплатами, которые будут уменьшать остаток. Однако, большинство банков в подобных случаях предлагают на выбор: сокращать либо сумму ежемесячной выплаты, либо срок. Каждый такой сценарий для наглядности лучше посчитать отдельно.
В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять - не достигли мы нулевого баланса раньше срока:
А в случае уменьшения выплаты - заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:
Вариант 4. Кредитный калькулятор с нерегулярными выплатами
Существуют варианты кредитов, где клиент может платить нерегулярно, в любые произвольные даты внося любые имеющиеся суммы. Процентная ставка по таким кредитам обычно выше, но свободы выходит больше. Можно даже взять в банке еще денег в дополнение к имеющемуся кредиту. Для расчета по такой модели придется рассчитывать проценты и остаток с точностью не до месяца, а до дня:
Предполагается что:
- в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
- отрицательные суммы - наши выплаты банку, положительные - берем дополнительный кредит к уже имеющемуся
- подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)
"- Алло! Вы даете кредиты пациентам психоневрологического диспансера?
— Даем, но под сумасшедший процент."
я лично хотел обратить внимание на вычисление ежемесячной ставки
12% / 12 =...
если брать выплату 0 в месяце то мы за год получим больше чем 112% (игра %, и годовой % другой)
поэтому когда нужно было просчитывать подобные задачи я создавал соответствующие формулы под условия, для пользования данной функции нужно знать механику её вычисления что бы не попасть в неловкую ситуацию
Дано: сумма кредита 3,348,250 тн. ; оформление кредита 12.12.2014г ; дата платежа стоит 10.02.2015г ; первый платеж 128,637тн.; последующие 88,271 ; срок 60 мес ;размер ставки вознаграждения в годовых процентах- фиксированная, 15,50% годовых ; Размер ставки вознограждеия в достоверном, годовом, эффективном, сопоставимом исчислении 22,2% годовых ; общая сумма к погашению 5,336,626
Как задать чтоб он как в системе банковской считал всегда то есть ставлю дату соответственно первый платеж менялся а остальные были равными и просто можно было задать сумму и срок и первоначальный взнос к примеру и он сам считал процентную ставку и т.д. заранее благодарю! с н.п.
Николай, а как сделать аннуитетный график, но не с ежемесячными выплатами, а еженедельными?
Скорей всего по факту ваш платеж не будет совпадать с данными таблицы, хотя разница и будет минимальной. Во многих банках процент в месяц рассчитывается чуть по другому. Не 12% / 12мес, а 12% / 365*(кол-во дней в мес). И т.к. кол-во дней в мес. может быть 28,30,31~ соответственно % будет отличаться.
А в остальном просто шикарно ))) наглядно, красиво, понятно. И еще я бы добавил в центр ячейку с суммой руб. отданных процентов
Не знаю, почему, но эксель-файл вставить не могу
отличная форма для расчета, а подскажите пожалуйста как правильнней связать 2 таблицы, а точнее сделать одну таблицу в которой отражено и можно учитывать как и досрочное погашение 1) на уменьшение срок, так и 2) на уменьшение платежа.
может быть поднимался этот вопрос но пока не нашел в архивах ресурса ни чего(
Вставил между столбцами "Период" и "Выплата кредита" столбец "Дата". А как сделать чтобы даты выплат, которые выпадают на одно и тоже число каждого последующего месяца, также останавливались как и период?
Спасибо
... разобрался...
У меня такой вопрос, как можно посчитать аннуитетный платеж если за период используются несколько процентных ставок. Например выдается кредит на 24 месяца, в первые 12 месяцев ставка 5% годовых, а дальше - 20%.
Заранее спасибо
У меня такой вопрос, как можно посчитать аннуитетный платеж если за период используются несколько процентных ставок
Однако, у банка на этот счет может быть свое мнение - тут надо уточнять.
Необходимо, чтоб перерасчет происходил с последнего значения столбца. То есть если выплачено больше, то последние значения уменьшались. Похоже на "Кредит с досрочным погашением(уменьшение срока)", но перерасчет должен проводиться при каждом отрицательном долге (т.к. есть так же процент неустойки). Вот ссылка на файл.
Помогите пожалуйста сделать калькулятор с остаточным платежем. Функция ПРПЛТ выдает процента плюсом в последние месяца! То есть банк платит проценты по кредиту!!!
Подскажите, пожалуйста, как будет выглядеть формула, если отталкиваться не от суммы и срока, а от платежа и срока? Т.е. я задаю срок, потом задаю ежемесячный платеж и мне формируется максимально возможная сумма, исходя из этих заданных параметров.
Благодарю