Как рассчитать кредит в Excel: держите расходы под контролем. Кредит в excel


Таблица погашения кредита в excel. Делаем кредитный калькулятор в Excel.

Что выгоднее непосредственно для получателя заемных средств аннуитетный или дифференцированный вид платежа? Небольшой сравнительный анализ показывает основные отличия между двумя схемами:

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

Впрочем, окончательный выбор графика и схемы погашений остается за потенциальным заемщиком.

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

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

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

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

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

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

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

Для расчета величины аннуитетного платежа необходимо знать:

1. Сумму кредита.

2. Срок кредита.

3. Величину процента по кредиту.

4. Периодичность начисления процентов по кредиту (ежемесячно, еженедельно и так далее), а вернее количество платежных периодов для вида процентов.

Рассмотрим пример: нам необходимо рассчитать величину ежемесячного аннуитетного платежа по кредиту на сумму 100 000.00 рублей, сроком на 2 года, по ставке 18 процентов годовых . В данном случае количество платежных периодов будет равно 12 так как в году 12 месяцев (по условию задачи у нас рассматривается годовая процентная ставка).

Расчет величины аннуитетного платежа при помощи формулы

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

В английской версии Excel функция называется PMT .

Она имеет 5 параметров, из которых нам интересны первые 3 (ставка, кпер, пс ), остальные параметры не являются обязательными, поэтому их мы указывать не будем.

Описание параметров:

ставка – процентная ставка, приведенная к одному платежному периоду. Для нашего примера она будет равна / =. Обратите внимание, что процентная ставка должна быть указана в долях от 1. Итого получаем 0,015.

кпер – количество выплат по ссуде. В нашем примере выплаты по кредиту осуществляются ежемесячно, поэтому в качестве значения этого параметра указываем срок кредита, то есть 24 месяца.

пс – сумма кредита.

В результате получаем следующую формулу: =ПЛТ(0,015;24;100000) .

Полученное через формулу значение будет отрицательным, вносим небольшое исправление =-ПЛТ(0,015;24;100000) .

В итоге получаем величину аннуитетного платежа равной 4 992,41 рубль .

Расчет величины аннуитетного платежа на VBA

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

Расчет аннуитетного платежа на VBA по вышеуказанному примеру:

Annuitet = -WorksheetFunction.Pmt(0.015, 24, 100000)

Аннуитет – наиболее распространенная форма платежей по обязательствам. Аннуитетный платеж практически повсеместно используется коммерческими банками при расчете возвратных платежей по кредитам.

Что такое аннуитетные платежи

Аннуитет (лат. annuus - годовой, ежегодный) – понятие, описывающее схему погашения кредитных задолженностей. Сущность термина сводится к равномерным платежам в течение всего периода как по основной сумме долга, так и по процентам, начисляемым на основную задолженность.

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

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

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

Существуют два типа аннуитетного платежа – постнумерандо (первая выплата происходит в конце начального периода) и пренумерандо (первая выплата происходит в начале нулевого периода). Постнумерандо является более распространенным и удобном способом, поэтому используется коммерческими банками при кредитовании.

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

Расчет аннуитетного платежа

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

Обычно аннуитет рассчитывается на месяц, так как платежи происходят равномерными суммами.

Формула расчета аннуитетного платежа

Аннуитетный платеж находится по следующей формуле:

А П = К А *S М ,

где А П – сумма аннуитета, К А – коэффициент аннуитета, S М – величина ежемесячного возврата средств.

Существует несколько формул для расчета коэффициента аннуитетного платежа. Наиболее распространена данная формула:

К А = (j*(1+j) m) / ((1+j) m – 1),

где К А – коэффициент аннуитетного платежа, j – ставка кредитования (месячная), m – число периодов, в которые осуществляется платеж.

j = i / 12, где i – годовая ставка по кредиту.

Для более точного результата целесообразно использовать эту формулу:

К А = (j*(1+j) k) / ((1+j) k – 1),

где К А – коэффициент аннуитетного платежа, j – ставка кредитования (месячная), k – количество платежных периодов (k=m*n), n – число периодов (месяцев или кварталов), m – число лет, в которые осуществляется платеж.

Аннуитетный платеж в большинстве коммерческих банков рассчитывается по следующей формуле:

А п = S k * (j / (1 – (1+j) -m)),

где S k – общая сумма по кредиту.

Пример расчета

Представим, что предполагается взятие кредита в размере 100 000 рублей. Ставка по кредиту составляет 24%. Планируется вернуть денежные средства с процентами за 1 год, то срок платежа составит 12 месяцев.

К А = (0,02*(1+0,02) 12) / ((1+0,02) 12 – 1) = (0,02 * 1,27) / (1,27 — 1) = 0,09407

А П = 0,09407 * 100000 = 9407 рублей.

А п год = 9407 * 12 = 112884 рубля. Таким образом, плата за использование кредитных средств составила 12884 рубля, эффективная ставка по кредиту 12,88%.

А п = 100000 * (0,02 / (1 – (1+0,02) -12)) = 100000 * (0,02 / (1 – 1/1,27) = 100000 * 0,09412 = 9412 рублей.

А п год = 9412 * 12 = 112944 рубля. Таким образом, плата за использование кредитных средств составила 12944 рубля, эффективная ставка по кредиту 12,94%.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

 

biznesto.ru

Кредитный калькулятор загрузить бесплатно в excel

exe.

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

После установки Вы сможете использовать программу "Кредитный калькулятор" запустив CreditCalculator. ). Кредитный калькулятор скачать бесплатно в excel Нет учета возможное изменение процентной ставки во время выплат кредита Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным Не рассчитывается вариант — первый платеж только проценты.

к.

Попробуйте посчитать к примеру кредит 1 млн.

это продукт Microsoft.

 

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

е.

Расчет кредита в excel: скачайте готовые формулы и калькуляторы.

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

Читайте также: Как рассчитать кредит в Excel самому? Первый кредитный калькулятор в Excel можно скачать по ссылке.

Умеет: Считать оба вида платежей.

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

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

  Всех выше названных недостатков лишен кредитный калькулятор для iPad/iPhone.

 В графике получаются отрицательные суммы.

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

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

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

Однако он делит кредит на 2 периода, т.

За счет этого сумма начисленных процентов практически совпадает с значениями,  рассчитываемыми ВТБ24 и Сбербанком Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка Калькулятор можно редактировать под себя, задавая разные варианты расчета.

Построенный при расчете график совпадает с моим графиком платежей.

 

Но Excel есть не на всех компьютерах.

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.

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

Формула коэффициента аннуитета: К = (i * (1 + i)^n) / ((1+i)^n-1) где i – процентная ставка за месяц, результат деления годовой ставки на 12; n – срок кредита в месяцах.

 Дифференцированный способ оплаты предполагает, что: сумма основного долга распределена по периодам выплат равными долями; проценты по кредиту начисляются на остаток.

Это ПЛТ: Заполним входные данные для расчета ежемесячных платежей по кредиту.

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

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

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

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

Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле: А = К * S где: А – сумма платежа по кредиту; К – коэффициент аннуитетного платежа; S – величина займа.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B/12).

Составим график погашения предыдущего кредита по дифференцированной схеме.

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

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

Естественно сам файл также можно отредактировать под свои нужды.

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

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9 Программа "Кредитный калькулятор" это Windows — приложение, являющееся off-line версией кредитного калькулятора, расположенного в разделе "Сервисы" на главной странице данного сайта.

Пользователи MAC и Linux не пользуются Excel обычно, т.

Составим график погашения кредита.

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

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

п.   Чтобы закрепить ячейки, используем абсолютные ссылки.

Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

д.

Калькуляторы позволяют достаточно быстро и просто рассчитать ипотеку.

  Это сумма займа, проценты и срок.   Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными.

Одна - это программа, специально созданная для расчетов кредитов.

Есть люди которые платят кредит и все.

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

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

. Разработчик: Anton RybinКатегории: Приложения для Андроид, Бизнес, Финансы, Недвижимость, Кредитный КалькуляторФинансирование бизнеса » Анализ кредитных предложений банков ».

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

  В предлагаемом ниже архиве 2 программы.

  После распаковки архива запустите установочный файл CreditCalculatorSetup.

Программа позволяет рассчитывать графики платежей по кредитам, суммы переплат и сроки.

) и заемщики (предприниматели, компании, частные лица и т.

Данный калькулятор идеально подходит для расчета ипотечного кредита и эмуляции наличия досрочных погашений.

  Когда вы взяли кредит, вы так или иначе думаете о досрочном погашении.

Дисковое свободное пространство — минимум 1 MB Объем оперативной памяти — минимум 256 MB Операционные системы — Microsoft Windows XP/Vista, Microsoft Windows 7 Для установки программы "Кредитный калькулятор" скачайте архив CreditCalculator.

мы эти деньги будем отдавать банку, терять.

Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

.

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B;0;E9-G9).

Пока пустой.

  Ежемесячные выплаты зависят от схемы погашения кредита.

Если это нарушает чьи-то авторские права, то просим сообщить об этом.

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

В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B/12; $B; $B).

Если Вы считаете кредиты очень часто или у Вас дорогой и/или некачественный интернет, то для расчета кредита можно воспользоваться специализированными программами.   Входные данные те же: Составим график погашения займа: Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B.

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

zip по указанной ссылке и сохраните его на свой диск.

jalixu.videogameplayandaddiction.com

Как рассчитать кредит в Excel: держите расходы под контролем

Расчет кредита в ExcelДата: 6 июня 2016 Категория: Excel Поделиться, добавить в закладки или распечатать статью

Кредиты прочно вошли в нашу жизнь, и мы уже не можем представить себя без них. Мы занимаем деньги в банках, чтобы купить автомобиль, квартиру, бытовую технику. Кредиты стремительно набирают популярность. К слову, один из самых популярных и доступных кредитов сегодня — предложение от Альфа-Банка. То есть, благодаря кредитам, мы делаем нашу жизнь лучше. Всё хорошо, только нужно держать выплаты по кредитам под контролем. А для этого, отлично подходит Эксель.

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

Например, у нас кредит на 10 000 у.е. со среднегодовой ставкой 6% и сроком 36 месяцев. Вычислим ежемесячную выплату тела кредита: =10 000/36. Получим 277,78 у.е.

Выплаты процентов по кредиту просчитываем от остатка по телу кредита на данный период. Первый платёж будет от полной суммы в 10 тыс, второй – от величины 10 000 у.е. – 277,78 у.е. Т.е. платёж по процентам во втором месяце составит: =(10 000,00 – 277,78) * 6% / 12. В этой формуле мы добавили деление на двенадцать, поскольку 6% — среднегодовая ставка, а ежемесячная – в 12 раз меньше. Результат вычисления – 48,61 у.е.

Полный платёж составит 277,78 у.е. + 48,61 у.е. = 326,39 грн. Таким образом, можно просчитать все 36 платежей.

Однако, банки предлагают и систему с фиксированной ежемесячной платой. Такой кредит просчитать сложнее. Для этого в Эксель несколько функций.

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

  • Ставка – процентная ставка за один период
  • Период – Порядковый номер периода, для которого рассчитывается выплата. Он должен быть не больше, чем Кпер, иначе формула вернет ошибку
  • Кпер – количество периодов, на которое рассчитан кредит
  • Пс – сумма (тело) кредита. Для кредита это число записываем отрицательным
  • Бс – будущая стоимость – величина остатка по кредиту после окончания срока выплат. Необязательный аргумент, по умолчанию равен нулю
  • Тип – необязательный аргумент. Укажите «0» (значение по умолчанию), если оплаты производим в конце периода, «1» — в начале периода.

Вот какой результат даёт эта функция для рассмотренного выше примера:

Функция ОСПЛТ в ЭксельФункция ОСПЛТ в Excel

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

Функция ПРПЛТ в ExcelФункция ПРПЛТ в Эксель

Чтобы получить полный ежемесячный платеж, нужно сложить результаты функций ОСПЛТ и ПРПЛТ.

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

  • Плт – размер периодической платы (тело кредита плюс процент)
  • Оценка – необязательный аргумент – начальная оценка ожидаемого результата. Обычно его не задают

Если нужно получить годовую ставку – умножьте результат функции на количество периодов в году. Например, на 12 месяцев, 4 квартала, 2 полугодия и т.п. Попробуем просчитать процентную ставку для нашего примера:

Функция СТАВКА в ЭксельФункция СТАВКА в Excel

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

Давайте опять применим эту формулу к нашему примеру:

Функция КПЕР в ExcelФункция КПЕР в Эксель

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

Функция ПС в ExcelФункция ПС в Эксель

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

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

Пользуйтесь, а я жду Вас снова на страницах моего блога – officelegko.com. Кстати, в следующей статье мы будем считать прибыль от депозита!

Поделиться, добавить в закладки или распечатать статью

officelegko.com

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

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

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

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

Чтобы закрепить ячейки, используем абсолютные ссылки.

 Дифференцированный способ оплаты предполагает, что: сумма основного долга распределена по периодам выплат равными долями; проценты по кредиту начисляются на остаток.

Входные данные те же: Составим график погашения займа: Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B. Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.   ).

Всех выше названных недостатков лишен кредитный калькулятор для iPad/iPhone.

В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B/12; $B; $B).

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

  это продукт Microsoft.

  Калькуляторы позволяют достаточно быстро и просто рассчитать ипотеку.

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

Пока пустой.

Формула расчета дифференцированного платежа: ДП = ОСЗ / (ПП + ОСЗ * ПС) где: ДП – ежемесячный платеж по кредиту; ОСЗ – остаток займа; ПП – число оставшихся до конца срока погашения периодов; ПС – процентная ставка за месяц (годовую ставку делим на 12).

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

мы эти деньги будем отдавать банку, терять.

Построенный при расчете график совпадает с моим графиком платежей.

Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

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

.

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

Составим график погашения предыдущего кредита по дифференцированной схеме.

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

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

Есть люди которые платят кредит и все. Составим график погашения кредита.

Данный калькулятор идеально подходит для расчета ипотечного кредита и эмуляции наличия досрочных погашений.

к.

Формула коэффициента аннуитета: К = (i * (1 + i)^n) / ((1+i)^n-1) где i – процентная ставка за месяц, результат деления годовой ставки на 12; n – срок кредита в месяцах.

Комментарии (0).

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

  ) и заемщики (предприниматели, компании, частные лица и т. Пользователи MAC и Linux не пользуются Excel обычно, т.

У второго калькулятора нет данного недостатка.

За счет этого сумма начисленных процентов практически совпадает с значениями,  рассчитываемыми ВТБ24 и Сбербанком Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка Калькулятор можно редактировать под себя, задавая разные варианты расчета.

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

Но Excel есть не на всех компьютерах.  

Читайте также: Как рассчитать кредит в Excel самому? Первый кредитный калькулятор в Excel можно скачать по ссылке. возможно что после деления в графике снова будут отрицательные значения.

Оба калькулятора сделаны в Excel.

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

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

п.

Ежемесячные выплаты зависят от схемы погашения кредита.

Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде. Ежемесячная сумма аннуитетного платежа рассчитывается по формуле: А = К * S где: А – сумма платежа по кредиту; К – коэффициент аннуитетного платежа; S – величина займа.   Расчет производится для процентой ставки с 2мя знаками после запятой.   Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B/12).

При дифференцированной схеме погашения долга перед финансовой организацией проценты начисляются на остаток кредитной суммы.

  Так же может изменяться и размер процентной ставки.

 В графике получаются отрицательные суммы.

д.

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

При желании, можно погасить кредит досрочно, при этом никаких комиссий или штрафов банк не должен требовать.

Естественно сам файл также можно отредактировать под свои нужды.

Это сумма займа, проценты и срок.

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

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

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9 Кредитный калькулятор в Excel – это программа, которая позволяет осуществлять расчеты выплат по кредиту.

к.

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.   е. Полезная информация? Поделитесь с друзьями: Вернуться Комментариев: 0.

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

Это ПЛТ: Заполним входные данные для расчета ежемесячных платежей по кредиту.

руб под 90 процентов на срок 30 лет. Тогда график платежей нужно делить на 3 и более периода.

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

  Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B;0;E9-G9). Однако он делит кредит на 2 периода, т.

.

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

begul.lostgentlesoul.com

Расчет кредита в 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)

 

 

СКАЧАТЬ  задание credit

i-k-t.ru

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

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

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

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

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

Но в случае если уж произойдёт так, что вам либо вашим родным нужно будет влезть в это дело, то хорошо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. Помассажировать числа заблаговременно, как я это именую 🙂 Микрософт 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)

    Источник: www.planetaexcel.ru

    Категория: Ипотека

  • Слепой расчет (1-2 серия из 4) сериал в HD

    Увлекательные записи:
    Похожие статьи, которые вам, наверника будут интересны:

    beautofan.ru

    Расчет кредита в 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)

    www.planetaexcel.ru