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


Расчет кредита в 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 лучше использовать формулу вида:

=ЕСЛИ(A17>=$C$7;"";A17+1)

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

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

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

credit6.png

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

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

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

credit8.png

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

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

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

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

credit7.png

Предполагается что:

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

www.planetaexcel.ru

Дифференцированные платежи по кредиту в MS EXCEL. Примеры и методы

Составим в MS EXCEL график погашения кредита дифференцированными платежами.

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

Примечание. При расчете кредита дифференцированными платежами сумма переплаты по процентам будет ниже, чем при аннуитетных платежах. Не удивительно, что сегодня практически все российские банки применяют в расчетах аннуитетную схему погашения кредита. Сравнение двух графиков погашения кредита приведено в статье Сравнение графиков погашения кредита дифференцированными аннуитетными платежами в MS EXCEL.

График погашения кредита дифференцированными платежами

Задача. Сумма кредита =150т.р. Срок кредита =2 года, Ставка по кредиту = 12%. Погашение кредита ежемесячное, в конце каждого периода (месяца).

Решение. Сначала вычислим часть (долю) основной суммы кредита, которую заемщик выплачивает за период: =150т.р./2/12, т.е. 6250р. (сумму кредита мы разделили на общее количество периодов выплат =2года*12 (мес. в году)).Каждый период заемщик выплачивает банку эту часть основного долга плюс начисленные на его остаток проценты. Расчет начисленных процентов на остаток долга приведен в таблице ниже – это и есть график платежей.

Для расчета начисленных процентов может быть использована функция ПРОЦПЛАТ(ставка;период;кпер;пс), где Ставка - процентная ставка за период; Период – номер периода, для которого требуется найти величину начисленных процентов; Кпер - общее число периодов начислений; ПС – приведенная стоимость на текущий момент (для кредита ПС - это сумма кредита, для вклада ПС – начальная сумма вклада).

Примечание. Не смотря на то, что названия аргументов совпадают с названиями аргументов функций аннуитета – ПРОЦПЛАТ() не входит в группу этих функций (не может быть использована для расчета параметров аннуитета).

Примечание. Английский вариант функции - ISPMT(rate, per, nper, pv)

Функция ПРОЦПЛАТ() предполагает начисление процентов в начале каждого периода (хотя в справке MS EXCEL это не сказано). Но, функцию можно использовать для расчета процентов, начисляемых и в конце периода для это нужно записать ее в виде ПРОЦПЛАТ(ставка;период-1;кпер;пс), т.е. «сдвинуть» вычисления на 1 период раньше (см. файл примера).Функция ПРОЦПЛАТ() начисленные проценты за пользование кредитом указывает с противоположным знаком, чтобы отличить денежные потоки (если выдача кредита – положительный денежный поток («в карман» заемщика), то регулярные выплаты – отрицательный поток «из кармана»).

Расчет суммарных процентов, уплаченных с даты выдачи кредита

Выведем формулу для нахождения суммы процентов, начисленных за определенное количество периодов с даты начала действия кредитного договора. Запишем суммы процентов начисленных в первых периодов (начисление и выплата в конце периода):ПС*ставка(ПС-ПС/кпер)*ставка(ПС-2*ПС/кпер)*ставка(ПС-3*ПС/кпер)*ставка…Просуммируем полученные выражения и, используя формулу суммы арифметической прогрессии, получим результат.=ПС*Ставка* период*(1 - (период-1)/2/кпер)Где, Ставка – это процентная ставка за период (=годовая ставка / число выплат в году), период – период, до которого требуется найти сумму процентов.Например, сумма процентов, выплаченных за первые полгода пользования кредитом (см. условия задачи выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р.За весь срок будет выплачено =ПС*Ставка*(кпер+1)/2=18750р.Через функцию ПРОЦПЛАТ() формула будет сложнее: =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ("1:"&кпер))-1;кпер;-ПС))

 

excel2.ru

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

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

Существует два способа платежа:

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

После определения наиболее приемлемого способа можно воспользоваться Excel калькулятором. Кредитный калькулятор Excel позволяет произвести необходимые расчеты. Так как данная программа является продуктом от Microsoft, то подходит не для всех, например она не совместима с Linux или MAC.

Положительные стороны кредитного калькулятора

    • Данный кредитный калькулятор правильно подсчитывает аннуитетный график платежа, а также дифференцированный график платежа.
    • Расчеты кредитов предоставляются в виде таблицы, что очень удобно и наглядно.
    • При досрочном погашении есть возможность подсчитать уменьшение процентов, основного долга, определить стоит ли погашать ранее срока и будет ли от этого выгода.
    • В процессе подсчетов программа автоматически определяет високосные и не високосные года, поэтому графики точно такие же, как и у банков, то есть не будет путаницы.
    • Можно подстроить по своим приоритетам, с помощью использования различных вариантов.
    • Возможность использования именно по своим условиям позволяет получить точные цифры.
    • Есть возможность подсчитывать кредитные средства с несколькими процентными периодами.
    • Кредитный калькулятор Excel легко создать. Чаще всего клиенты банков выбирают аннуитетный вид кредитования. Для того, чтобы появилась возможность рассчитывать платежи по аннуитетному кредитованию нужно воспользоваться категорией – финансовые и выбрать функцию ПЛТ. Далее необходимо выделить одну ячейку и нажать на клавишу – fx, опять найти ПЛТ и ОК. Затем остается ввести некоторые данные для получения информации:- Ставка (проценты по кредиту. Например, если годовая ставка будет равна 24%, то на каждый месяц придется по 2% от основной суммы долга).- Кпер (определение срока кредитования в количестве месяцев).- Бс (первоначальная сумма кредита).- Пс (количество средств, в конце срока, то есть 0).

      — Тип (ведение учета выплат бывают двух видов: 1 – в том случае, если выплаты необходимо произвести в начале месяца, 0 – если выплаты производятся в конце месяца). В РФ чаще всего используется 0.

Для многих важно определить полную переплату, то есть процентов за весь срок. Excel калькулятор предоставляет и такую информацию. Для этого необходимо ввести основную сумму, процентную ставку и желаемый срок погашения.

Нужно выделить ячейку для полной суммы кредита и вписать туда некоторые данные:

=номер ячейки выплаты по месяцам * номер ячейки по сроку кредита.

В результате появится необходимая информация. При необходимости можно ввести детализацию в программу. Большинство экспертов в экономической сфере считают, что 2015 год для РФ будет тяжелым. Так, Ф. Могерини, глава европейской дипломатии, а также главный руководитель МИД в Германии Ф.-В. Штайнмайер прогнозируют банкротство РФ. Свое мнение они объясняют санкциями направленными на Россию. Данную ситуацию невозможно изменить, так как европейские страны дали возможность контролировать свои действия Вашингтону.

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

kreditvbanke.net

Кредитный калькулятор в Excel | СЕМЕЙНЫЙ БЮДЖЕТ и не только...

Поздравления на телефон

Кредитный калькуляторКаждому из нас наверняка рано или поздно приходит мысль о кредите. Кому нужно машину, кому квартиру. И тут  уже надо считать. Когда мы только прицениваемся к кредиту, то достаточно тех калькуляторов, что есть в сети. Хотя и они отвечают не на все вопросы, к ним адресованные. Но когда мы уже берем кредит, то без расчета графика платежей не обойтись.

Вот автор блога «Эксель Практик» и предлагает завести собственный кредитный калькулятор у себя в книге Excel.

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

Итак, любой кредит имеет 4 основных параметра:

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

Так же есть две формы платежей – аннуитетные (когда вы каждый месяц платите одну и ту же сумму) и дифференцированные (когда постоянной остается часть ежемесячного платежа – та, которая погашает основной долг, а вторая часть регулярно пересчитывается).

Если вы знаете 3 показателя, то сможете подобрать четвертый.

Мы сделаем сначала калькулятор. За расчет всех четырех показателей отвечают эти функции:

- Срок – Функция ПС()

- Сумма – Функция КПЕР()

- Ставка – Функция СТАВКА()

- Ежемесячный платеж – Функция ПЛТ()

Параметры функций одни и те же – знаете три из 4-х показателей, соответствующая функция выдаст 4-й. Нагляднее смотрите первый лист файла.

Кредитный (ипотечный) калькулятор

Чтобы подготовить график платежей, нам понадобится дата выдачи кредита.

Небольшое отступление по досрочному погашению. Досрочное погашение уменьшает сумму основного долга, поэтому после него обычно пересчитывается ежемесячный платеж или меняется срок кредита.

Переходим ко второму листу.

График платежей

Первая строчка графика – дата выдачи, поэтому тут будет только первоначальная сумма кредита.

На второй строке:

  1. Дата – определяется как то же число, что и выдача кредита, но следующего месяца. Используем функцию ДАТА, где год и число те же, что и в предыдущем периоде, а месяц на один больше. Но есть нюанс – банк ведь не примет платеж в выходной день. Поэтому делаем корректировку числа с помощью функции ДЕНЬНЕД. Важно: дату можно корректировать вручную, на следующую дату влияния не окажет.
  2. Сумма ежемесячного платежа (которая определяется по функции ПЛТ).
  3. Сумма погашения процентов как умножение величины прошедшего периода на соответствующий процент. Используется функция ДОЛЯГОДА, чтобы убрать последствия високосности. Банки скрупулезно подходят к расчетам, поэтому период считается в днях, иначе можно было бы сделать проще – взять годовой процент, поделить на 12 месяцев и умножить на сумму.
  4. Сумма погашения основного долга – берется как разница ежемесячного платежа и суммы погашения процентов.
  5. Досрочное погашения и его дата ставятся произвольно. Единственное условие – ставится в тот период, где дата или меньше или совпадает с датой досрочного погашения.
  6. Сумма долга после платежа определяется как сумма предыдущего периода за вычетом погашения основной части и суммы досрочного погашения.

И последний нюанс построения графика – на третьей строчке мы меняем немного формулу ежемесячного платежа – ставим условие, что если было досрочное погашение, то сумма пересчитывается по функции ПЛТ, а если нет, остается как в предыдущей строке.

Теперь сделаем такой же график для дифференцированных платежей.

График платежей

Меняем две формулы:

1) Сумму погашения основного долга. Она будет неизменной - сумма долга разделить на количество периодов (месяцев).

2) Ежемесячный платеж определяем как сумму двух частей - погашений основного долга и процентов.

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

Какие еще можно вытащить показатели, которые важны нам, но не учитываются в доступных калькуляторах?

Для меня это была обоснованность взятия кредита. Я тогда снимал квартиру и поэтому мне нужно было рассчитать цену кредита. Цена кредита для меня равнялась сумме выплаченных процентов за минусом арендных платежей за весь период кредита. Если сумма небольшая или вообще отрицательная, то кредит брать стоит. Бонусом для меня было проживание в СВОЕМ (!) доме, где я знал, что могу забить гвоздь в МОЮ стенку, да и вообще психологическое влияние большое.

Если кому нужно более наглядно, то посмотрите видео, где этот файл и формировался:

Делаем кредитный калькулятор

А для тех, кому просто нужен калькулятор и график платежей, вот ссылка на файл:

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

Источник: excelpractic.ru

budget7i.ru


Смотрите также