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

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

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

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

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

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

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

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

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

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

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

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

Источник:
http://www.planetaexcel.ru/techniques/11/202/

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

Добрый день уважаемый пользователь!

Сегодня я хотел бы поговорить о таком необходимом зле, как кредит. Почему зло, вы и так знаете, особенно это касается потребительского кредитования, когда за вещь вы переплачиваете в 2-3 раза больше ее реальной цены. Это всё необходимо учитывать и просчитывать, поэтому и научитесь создавать свой личный кредитный калькулятор в котором вы реально увидите картинку «мышеловки», в которую попадают обычный обыватель. Хотя есть еще кредиты для бизнеса, но там немного другая история, их берут, чтобы зарабатывать деньги. Главная проблема кредита даже не в «космических» процентах, а в том, что вы получаете удовольствие сейчас, а расплата и проблемы вас ждут в будущем, а это убивает личную мотивацию практически в зародыше. Пропадает желание, что-то делать, развиваться, напрягаться, учиться, создавать источники дохода, когда можно «тупо» взять паспорт и за 15 минут в ближайшем банке вас быстренько возьмут в кабалу и грамотно навешают на вас кучу всего разного и лишнего, лишь бы было, типа страховку и прочее.

Читайте также  Как построить график по данным таблицы в Excel

Поэтому я очень хочу, чтобы материал, который я дам в своей статье будет вам полезен в принятии ваших решений.

Несмотря на то, что я не являюсь приверженцем кредитов, всё же осознаю их необходимость. Недавно мой ребенок попал в больницу, и я был вынужден, в силу обстоятельств, использовать средства кредитной линии. Ну а потом на протяжении 2 недель полностью закрыл долг, не отлаживая его в долгий ящик. Ну не мог я по-иному, нужны были деньги и срочно, ну и долг я сразу же закрыл и не ждал ни окончания льготного периода, ни начисления процентов.

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

Рассмотрим три самых популярных варианта использования кредитного калькулятора в Excel:

Кредитный калькулятор для расчёта простых кредитов

Начнём с простого варианта, быстро прикинем, сколько нам нужно ежемесячно оплачивать по аннуитетному кредиту, это когда выплаты делают одинаковыми суммами, как в большинстве случаев. Это можно произвести одной функцией Excel и несколькими простыми формулами. Для получения результата в Excel существует функция ПЛТ в разделе «Финансовые». Указываем, в какую ячейку нужен результат, вызываем «Мастер функций» ищем функцию ПЛТ, нажимаем кнопочку «ОК» и в окне мастера вводим необходимые аргументы для нашего расчёта, формула получается следующего вида:

=ПЛТ(B5/12;B6;B4;0;0), где:

  • Ставка (B5/12) – является аргументом, указывающим на процентную ставку по взятому кредиту в разрезе периодов выплат, в нашем случае это месяцы. Если ставка по кредиту в год 18%, то за один месяц будет составлять 1,5%;
  • Кпер (B6) – аргумент, указывающий на количество периодов, то есть, на сколько месяцев взят кредит;
  • Бс (B4) – указываем, какую сумму кредита будем рассчитывать;
  • Пс (0) – это финишная пряма, какой итог кредита должен быть в конце, скорее всего это будет 0, что означает, что вы никому и ничего не должны;
  • Тип (0) – аргумент необходимый для учёта выплат каждый месяц. Если равно 1 – это учитываем выплаты к началу месяца, если 0 – то учитываем на конец. В постсоветском пространстве большинство банков используют последний вариант, а значит вводим 0.

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

Теперь в поле «Тело кредита» в ячейку Е2 вводим формулу функции ОСПЛТ следующего вида:

=ОСПЛТ($B$4/12;D2;$B$5;$B$3;0)

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

=ПРПЛТ($B$4/12;D2;$B$5;$B$3;0) Теперь в оставшиеся столбики будем вводить простые формулы, для получения суммы выплаты нам нужна формула: =E2+F2, а для определения суммы остатка кредита используем формулу: =$B$3+СУММ($E$2:E2). При необходимости, возможно, немножко улучшить и автоматизировать ваш кредитный калькулятор в Excel для уменьшения количества ошибок.

Для начала пропишем формулу в ячейку D3 для того чтобы она подстраивала и отслеживала срок кредита:

=ЕСЛИ(D2>=$B$5;»«;D2+1)

Следующим шагом с помощью логической функции ЕСЛИ для поля «Тело кредита», сделаем автоматическую проверку достигли ли вы последнего срока выплат или нет. Если период, достигнут, получаем пустую ячейку «», а если нет, то функцией ОСПЛТ выводим необходимый расчёт:

=ЕСЛИ(D3<>»»;ОСПЛТ($B$4/12;D3;$B$5;$B$3;0);»»)

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

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

Для реализации этого добавляем дополнительный столбик «Доп.платёж» в котором будут указываться сумма платежей уменьшающий остаток кредита. Но у банков есть два варианта развития событий:

  • во-первых, сокращения суммы выплат по кредиту на каждый месяц;
  • во-вторых, уменьшения срока выплат.

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

Рассмотрим расчеты, когда происходит погашения кредита раньше срока, в этом случае будем использовать функционал функции ЕСЛИ и проверим, достигли ли мы нулевой задолженности раньше указанного срока: В другом случае, когда у вас происходит уменьшение суммы выплат по кредиту, формула пересчитывает ваш ежемесячный платёж сразу же после внесённого дополнительного платежа.

Создаем кредитный калькулятор для кредитов с нерегулярными платежами

Последний из рассматриваемых вариантов будет расчёт кредита с нерегулярными платежами, это когда на повышенную процентную ставку вам предоставляют лояльную программу вносить платежи на нерегулярной основе и без определений сумм взносов. Согласно таким кредитным программам банк может вам выделять еще дополнительно денег на ваши нужды, но для расчётов такой структуры кредитования производить расчёты нужно с точностью до дня, а не до месяца. Ну вот в принципе и всё, единственно что хочу сказать, что подсчёт сколько точно дней находится между двумя указанными датами, лучше производить при помощи функции ДОЛЯГОДА.

А на этом у меня всё! Я очень надеюсь, что всё о создании кредитного калькулятора в Excel вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Источник:
http://topexcel.ru/kak-sozdat-kreditnyj-kalkulyator-v-excel/

Ипотечный кредитный калькулятор в Excel. Как правильно рассчитать кредит в Excel?

Когда вы взяли кредит, вы так или иначе думаете о досрочном погашении.
Есть люди которые платят кредит и все. А есть те, которые каждый раз смотрят, сколько осталось платить, какая сумма основного долга. Я отношу себя ко второму типу людей, я смотрю сколько сейчас сумма основного долга, пытаюсь рассчитать, сколько будет платеж, если я сделаю досрочное погашение.
На данный момент у меня есть два калькулятора кредита для своих расчетов. Оба калькулятора сделаны в Excel. Калькуляторы позволяют достаточно быстро и просто рассчитать ипотеку.
Читайте также: Как рассчитать кредит в Excel самому?

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

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

Читайте также  Автоматическая сетевая диаграмма проекта в EXCEL

На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Найти и скачать мобильные версии калькуляторов можно с главной страницы сайта.

Достоинства данного калькулятора:

  1. Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
  2. Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
  3. Построение и расчет графика платежей в виде таблицы в Excel. Таблица графика платежей может также редактироваться
  4. При расчете учитывается високосный и невисокосный год. За счет этого сумма начисленных процентов практически совпадает с значениями, рассчитываемыми ВТБ24 и Сбербанком
  5. Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
  6. Калькулятор можно редактировать под себя, задавая разные варианты расчета.

Недостатки калькулятора

  1. Нет учета возможное изменение процентной ставки во время выплат кредита
  2. Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
  3. Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
  4. Не рассчитывается вариант — первый платеж только проценты. В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
  5. Расчет производится для процентой ставки с 2мя знаками после запятой.

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

Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.

Источник:
http://mobile-testing.ru/loancalc/loan_calc_excel/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник:
http://excelpractic.ru/delaem-kreditnyj-kalkulyator-2

Как в Экселе кредит посчитать

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

Аннуитетный кредит — что это? Это займ с такими условиями погашения, когда вы погашаете одинаковые суммы через равные промежутки времени.

Читайте также  Как в excel на графике сделать ось сверху?

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

Как посчитать основной платёж по кредиту

Чтобы посчитать ежемесячные выплаты по телу кредита – используйте функцию =ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип) .

Вот какой результат даёт эта функция:

Как высчитать процент по кредиту

Чтобы узнать переплату (проценты) по кредиту, используем функцию: =ПРПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип) :

Для получения полного ежемесячного платежа, нужно сложить основной платеж и процент, т.е. ОСПЛТ + ПРПЛТ.

Как посчитать процентную ставку по кредиту в Экселе

Чтобы узнать, какая процентная ставка по вашему кредиту – используйте функцию =СТАВКА(Кпер; Плт; Пс; Бс; Тип; Оценка) .

Если нужно получить годовую ставку – умножьте результат функции на количество периодов (платежей) в году. Например, на 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 + * ЕСЛИ(

Источник:
http://officelegko.com/2016/06/06/kak-rasschitat-kredit-v-excel-derzhite-rashodyi-pod-kontrolem/

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

Урок по созданию кредитного калькулятора в Microsoft Excel. Расчет ежемесячного платежа с учетом всех процентов и комиссий. Расчет эффективной ставки по кредиту.

В данном уроке будем создавать кредитный калькулятор Аннуитета (оплата кредита равными платежами) в Excelе для расчета по таких параметров как:

  • ежемесячный платеж;
  • сумму оплаты за пользованием кредита;
  • эффективную ставку по кредиту.

Важно понимать, что данные, которые рассчитаем, будут близки к банковским расчетам, но все же могут незначительно отличаться.

Шаг 1. Создаем таблицу значений

В новом документе Excel создаем таблицу с данными, которые будем использовать для расчета:

  • Сумма кредита;
  • Процентная ставка (годовая);
  • Ежемесячная комиссия;
  • Единоразовая комиссия;
  • Срок кредита в месяцах.

Ячейки для ввода данных обозначим желтым.

Данные которые будем рассчитывать:

  • Ежемесячный платеж;
  • Сумма переплаты по кредиту;
  • Процент переплаты;
  • Эффективнаяставка.

Шаг 2. Рассчитываем ежемесячный платеж

Для того, чтобы рассчитать ежемесячный платеж используем функцию «ПЛТ», она находится в категории «Финансовые».

Аргументы функции «ПЛТ»

  • Ставка — Выбираем ячейку процентной ставки и делим ее на 12. Это связано с тем, что процентную ставку указываем годовую, а платеж мы рассчитываем ежемесячный
  • Кпер — срок кредитования;
  • ПС — сумма кредита, обязательно ставим знак «-» перед значением. Так как в параметрах есть Единоразовая комиссия Сумма долга = Сумма кредита + Сумма кредита * Единоразовою комиссию. Все кредитные учреждения Единоразовою комиссию включают в основной долг и насчитывают на них годовую процентную ставку.

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

Шаг 3. Расчет оплаты за кредит.

Расчет суммы оплаты по кредиту производит путем умножение ежемесячного платежа по кредиту на срок кредита и вычитаем основную сумму кредита.

Процент переплаты по кредиту рассчитывается как сумма оплаты деленная на сумму кредита и умноженная на 100.

Шаг 4. Расчет эффективной ставки по кредиту

Эффективная ставка по кредиту включает в себя все проценты и все платежи по кредиту:

  • Процентная ставка;
  • Единоразовая комиссия;
  • Ежемесячная комиссия.

Для расчет эффективной ставки используем функцию «СТАВКА» в категории функций «ФИНАНСОВЫЕ».

  • Кпер — срок кредитования;
  • Плт — рассчитанный ежемесячный платеж, который включает в себя все проценты и комиссии;
  • Пс — сумма кредита, обязательно со знаком «-«.

После использования функции «СТАВКА» необходимо в строке формулы умножить данную функцию на 12, чтобы вычислить годовую эффективную ставку.

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

Источник:
http://wpcalc.com/kreditnyj-kalkulyator-v-excel-s-ravnymi-platezhami/