Как сделать график платежей. Кредитный калькулятор в Excel. Расчет суммарных процентов, уплаченных с даты выдачи кредита
Составим в MS EXCEL график погашения кредита дифференцированными платежами.
При расчете графика погашения кредита дифференцированными платежами сумма основного долга делится на равные части пропорционально сроку кредитования. Регулярно, в течение всего срока погашения кредита, заемщик выплачивает банку эти части основного долга плюс начисленные на его остаток проценты. Если кредитным договором период погашения установлен равным месяцу, то из месяца в месяц сумма основного долга пропорционально уменьшается. Поэтому при дифференцированных платежах основные расходы заемщик несет в начале кредитования, размеры ежемесячных платежей в этот период самые большие. Но постепенно, с уменьшением остатка ссудной задолженности, уменьшается и сумма начисленных процентов по кредиту. Выплаты по кредиту значительно сокращаются и становятся не такими обременительными для заемщика.
Таблицы - это мощные инструменты, которые помогут вам понять, как работает кредит. Они позволяют легко увидеть важные сведения о вашем кредите, а вычисления более или менее автоматизированы. Вы даже можете использовать заранее построенные шаблоны амортизации кредита, которые позволяют вам просто ввести несколько деталей о вашем кредите.
Таблицы доступны от нескольких популярных поставщиков, а инструкции на этой странице будут работать с любым из следующих способов. Мы описываем, как создать собственную таблицу с нуля на этой странице, и вы можете использовать эти инструкции с небольшими изменениями в других программах. Но вам не нужно все это делать самостоятельно, если вы этого не хотите. Шаблоны позволяют вам вставлять несколько подробностей о вашем кредите и делать с ними, а те предварительно созданные шаблоны просты в использовании.
Примечание . При расчете кредита дифференцированными платежами сумма переплаты по процентам будет ниже, чем при . Не удивительно, что сегодня практически все российские банки применяют в расчетах аннуитетную схему погашения кредита. Сравнение двух графиков погашения кредита приведено в статье .
График погашения кредита дифференцированными платежами
Задача . Сумма кредита =150т.р. Срок кредита =2 года, Ставка по кредиту = 12%. Погашение кредита ежемесячное, в конце каждого периода (месяца).
Альтернативный метод получения шаблона амортизации займа. После того, как вы находитесь в шаблоне, заполните информацию, связанную с вашим кредитом. Вам не нужно преобразовывать в десятичный формат, но убедитесь, что скорость отображается правильно. Для ежемесячных платежей введите начальную дату ссуды: это может быть полезно для планирования, но не имеет существенного значения для точных вычислений. Дополнительные доплаты: если вы будете платить дополнительно - или если вы хотите знать, насколько полезно было бы платить дополнительно - используйте это поле. Обратите внимание на то, сколько вы экономите на совокупном проценте и как ссуда будет выплачиваться быстрее, когда вы платите дополнительно.
- Сумма кредита: введите сумму, которую вы заимствуете.
- Обычная процентная ставка: используйте процентную ставку по кредиту.
- Количество платежей в год: как часто вы платите?
Решение. Сначала вычислим часть (долю) основной суммы кредита, которую заемщик выплачивает за период: =150т.р./2/12, т.е. 6250р. (сумму кредита мы разделили на общее количество периодов выплат =2года*12 (мес. в году)).
Каждый период заемщик выплачивает банку эту часть основного долга плюс начисленные на его остаток проценты. Расчет начисленных процентов на остаток долга приведен в таблице ниже – это и есть график платежей.
Расчет суммарных процентов, уплаченных с даты выдачи кредита
Кроме того, вы можете настроить свое содержание. Тем не менее, после того, как вы это сделали несколько раз, вы можете быстрее найти шаблон для запуска процесса, затем «отменить защиту» шаблона и внести свои изменения. Предположим, вам нужна модель, которая показывает вам каждый год или ежемесячный платеж. Начните в верхней строке таблицы и добавьте следующие разделы.
Сумма кредита Количество периодов. . Дайте этим ячейкам зеленый цвет заливки, который говорит вам, что вы можете изменить эти значения, сравнивая ссуды и запускать сценарии «что-если». Теперь вы готовы сделать таблицу данных. Сделайте строку со следующими именами столбцов.
Для расчета начисленных процентов может быть использована функция ПРОЦПЛАТ(ставка;период;кпер;пс), где Ставка - процентная ставка за период ; Период – номер периода, для которого требуется найти величину начисленных процентов; Кпер - общее число периодов начислений; ПС – на текущий момент (для кредита ПС - это сумма кредита, для вклада ПС – начальная сумма вклада).
График погашения кредита дифференцированными платежами
- Номер периода.
- Зачисление ссудного сальдо.
- Платеж.
- Атрибут, применяемый к проценту.
- Применяется к основному балансу займа.
Обратите внимание, что после первой строки вашей таблицы данных вы будете ссылаться на предыдущую строку, чтобы получить остаток по ссуде. Чтобы увидеть пример того, как работает математика и как выглядит ваша таблица, прокрутите список до. Если ваш кредит использует ежемесячные платежи, убедитесь, что вы правильно настроили каждый период в формулах. Например, 30-летний кредит имеет 360 общих периодов. Аналогичным образом, если вы платите годовую ставку 6 процентов, вы должны сделать 5 процентов.
Примечание . Не смотря на то, что названия аргументов совпадают с названиями аргументов – ПРОЦПЛАТ() не входит в группу этих функций (не может быть использована для расчета параметров аннуитета).
Примечание . Английский вариант функции - ISPMT(rate, per, nper, pv)
Функция ПРОЦПЛАТ()
предполагает начисление процентов в начале каждого периода
(хотя в справке MS EXCEL это не сказано). Но, функцию можно использовать для расчета процентов, начисляемых и в конце периода для это нужно записать ее в виде ПРОЦПЛАТ(ставка;период-1;кпер;пс), т.е. «сдвинуть» вычисления на 1 период раньше (см. файл примера
).
Функция ПРОЦПЛАТ()
начисленные проценты за пользование кредитом указывает с противоположным знаком, чтобы отличить денежные потоки (если выдача кредита – положительный денежный поток («в карман» заемщика), то регулярные выплаты – отрицательный поток «из кармана»).
Что вы можете делать со своей таблицей
Если вы не хотите делать всю работу в электронных таблицах, это более простой способ. Это также полезно для двойной проверки вывода вашей электронной таблицы. Как только вы получите свой кредит, вы можете много узнать о своем кредите. Таблица амортизации: электронная таблица, которую вы можете использовать для создания различных линейных диаграмм. Посмотрите, как ваш кредит будет выплачиваться с течением времени, или сколько вы будете обязаны за свой кредит в любую конкретную дату в будущем.
Пример 1: Рассчитать оплату по лизингополучателю
Принципал и интерес: электронная таблица также показывает, как каждый платеж разбивается на основной и интерес. Вы поймете, сколько стоит заимствовать, и как эти издержки меняются со временем. Ваш платеж остается прежним, но вы будете. Рассчитайте платеж, причитающийся за канадский ипотечный кредит, с процентами, усугубляемыми раз в два года. Это будущая стоимость или остаток, который вы хотите оставить после последнего платежа. Если этот параметр опущен, предполагается, что он равен нулю, а платежи должны быть выполнены в конце периода. Используйте 1 в этом аргументе, если платежи должны быть произведены в начале периода.
- Рассчитайте ежемесячный платеж, причитающийся в личном кредите.
- Тип не является обязательным.
Расчет суммарных процентов, уплаченных с даты выдачи кредита
Выведем формулу для нахождения суммы процентов, начисленных за определенное количество периодов с даты начала действия кредитного договора. Запишем суммы процентов начисленных в первых периодов (начисление и выплата в конце периода):
ПС*ставка
(ПС-ПС/кпер)*ставка
(ПС-2*ПС/кпер)*ставка
(ПС-3*ПС/кпер)*ставка
…
Просуммируем полученные выражения и, используя формулу суммы арифметической прогрессии, получим результат.
=ПС*Ставка* период*(1 - (период-1)/2/кпер)
Где, Ставка – это процентная ставка за период (=годовая ставка / число выплат в году), период – период, до которого требуется найти сумму процентов.
Например, сумма процентов, выплаченных за первые полгода пользования кредитом (см. условия задачи выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р.
За весь срок будет выплачено =ПС*Ставка*(кпер+1)/2=18750р.
Через функцию ПРОЦПЛАТ()
формула будет сложнее: =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ("1:"&кпер))-1;кпер;-ПС))
Пример 2: Рассчитать платеж по канадской ипотеке
Ваш браузер не может показывать этот фрейм. Для канадских ипотечных кредитов проценты усугубляются раз в полгода, а не ежемесячно, даже если платежи проводятся ежемесячно. Примечание. Посетите веб-сайт своего банка или обратитесь к своему банкиру, чтобы подтвердить, как ваш банк будет рассчитывать платежи.
Пример 3: Улучшенный калькулятор займа
Вместо простого деления ставки на 12, расчет скорости: ^ -1. Представляет собой полугодовой процент в процентах от годовой ставки. . В предыдущих примерах вам нужно было ввести общее количество причитающихся платежей, после расчета этого числа - количества лет в сроке кредита, умноженного на количество платежей в год.
Каждому из нас наверняка рано или поздно приходит мысль о кредите. Кому нужно машину, кому квартиру. И тут уже надо считать. Когда мы только прицениваемся к кредиту, то достаточно тех калькуляторов, что есть в сети. Хотя и они отвечают не на все вопросы, к ним адресованные. Но когда мы уже берем кредит, то без расчета графика платежей не обойтись.
В файле примера в листе «Списки» имеется таблица поиска частот и количества платежей в год для каждой частоты. В этой книге есть знак минуса перед текущей переменной значения, поэтому ежемесячный платеж отображается как положительное число. Вы можете опустить знак минус, чтобы показать платеж как отрицательное число.
Чтобы просмотреть расширенный кредитный калькулятор, загрузите его. . В отличие от многих других наших калькуляторов ипотеки и займов, наш простой кредитный калькулятор использует только основные встроенные финансовые формулы для расчета либо оплаты, процентной ставки, суммы кредита, либо количества платежей. Полезно как для авто, так и для ипотечных кредитов. Смотрите ниже для получения дополнительной информации.
Чтобы подготовить график платежей, нам понадобится дата выдачи кредита.
Небольшое отступление по досрочному погашению . Досрочное погашение уменьшает сумму основного долга, поэтому после него обычно пересчитывается ежемесячный платеж или меняется срок кредита.
Переходим ко второму листу.
Как использовать таблицу калькулятора займов
Этот калькулятор демонстрирует 4 разных типа расчетов по кредитам. Ниже описаны описания для каждого из полей, а также примеры использования каждого из параметров. Введите 12 для ежемесячных, 52 для еженедельных, 1 для годовых и т.д. сумма займа: это сумма, которую вы заимствовали. Вы также можете ввести свой текущий баланс, если вы также скорректируете срок займа на количество оставшихся лет, чтобы погасить кредит. Годовая процентная ставка: этот калькулятор предполагает фиксированную процентную ставку, и проценты усугубляются каждый период. Оплата: это сумма, которая выплачивается каждый период, включая как основной, так и проценты. Срок займа: Ипотечные кредиты обычно имеют срок от 15 до 30 лет. Автокредиты обычно составляют от 2 до 5 лет. Если вы ввели свой текущий баланс в Сумме займа, то для Срока введите количество лет, которое вы оставили, пока ваш кредит не окупится. Периоды в год: количество платежей в год. . Используйте этот параметр, когда знаете, сколько вам нужно заимствовать и хотите узнать, как процентная ставка или срок влияют на ваш платеж.
Первая строчка графика – дата выдачи, поэтому тут будет только первоначальная сумма кредита.
На второй строке:
- Дата – определяется как то же число, что и выдача кредита, но следующего месяца. Используем функцию ДАТА , где год и число те же, что и в предыдущем периоде, а месяц на один больше. Но есть нюанс – банк ведь не примет платеж в выходной день. Поэтому делаем корректировку числа с помощью функции ДЕНЬНЕД . Важно: дату можно корректировать вручную, на следующую дату влияния не окажет.
- Сумма ежемесячного платежа (которая определяется по функции ПЛТ ).
- Сумма погашения процентов как умножение величины прошедшего периода на соответствующий процент. Используется функция ДОЛЯГОДА , чтобы убрать последствия високосности. Банки скрупулезно подходят к расчетам, поэтому период считается в днях, иначе можно было бы сделать проще – взять годовой процент, поделить на 12 месяцев и умножить на сумму.
- Сумма погашения основного долга – берется как разница ежемесячного платежа и суммы погашения процентов.
- Досрочное погашения и его дата ставятся произвольно. Единственное условие – ставится в тот период, где дата или меньше или совпадает с датой досрочного погашения.
- Сумма долга после платежа определяется как сумма предыдущего периода за вычетом погашения основной части и суммы досрочного погашения.
И последний нюанс построения графика – на третьей строчке мы меняем немного формулу ежемесячного платежа – ставим условие, что если было досрочное погашение, то сумма пересчитывается по функции ПЛТ , а если нет, остается как в предыдущей строке.
Используйте этот параметр, когда вы знаете, сколько вы можете позволить себе платить каждый месяц, и хотите узнать, насколько большой кредит вы можете получить. Имейте в виду, что помимо стандартных платежей по кредиту могут взиматься другие сборы, такие как страхование, налоги и т.д.
Это не так просто решить для процентной ставки, потому что вы можете не иметь никакого контроля над тем, какова может быть ваша процентная ставка. Однако этот вариант может быть полезен для академических целей. Используйте этот вариант, если вы хотите погасить свой кредит раньше, сделав дополнительные платежи. Это предполагает, что для внесения дополнительных платежей никаких штрафов нет. Некоторые люди предпочитают получать кредиты с более длительными сроками и делать регулярные дополнительные платежи.
Теперь сделаем такой же график для дифференцированных платежей.
Меняем две формулы:
1) Сумму погашения основного долга. Она будет неизменной - сумма долга разделить на количество периодов (месяцев).
2) Ежемесячный платеж определяем как сумму двух частей - погашений основного долга и процентов.
Преимущество такого подхода заключается в том, что если вы столкнетесь с трудными временами, вы можете прекратить делать дополнительные платежи. Недостатком является то, что если у вас нет дисциплины для внесения дополнительных выплат, вы в конечном итоге получите больше процентов в целом.
- График амортизации.
- Создайте график амортизации кредита и произвольные дополнительные платежи.
Разница двух форм по сути в том, что вы больше платите в месяц по дифференцированному платежу, но быстрее расплачиваетесь и поэтому в итоге платите меньше процентов.
Какие еще можно вытащить показатели, которые важны нам, но не учитываются в доступных калькуляторах?
Для меня это была обоснованность взятия кредита. Я тогда снимал квартиру и поэтому мне нужно было рассчитать цену кредита. Цена кредита для меня равнялась сумме выплаченных процентов за минусом арендных платежей за весь период кредита. Если сумма небольшая или вообще отрицательная, то кредит брать стоит. Бонусом для меня было проживание в СВОЕМ (!) доме, где я знал, что могу забить гвоздь в МОЮ стенку, да и вообще психологическое влияние большое.
Получить бесплатную ипотечную амортизацию
Вам нужна дополнительная информация о том, как пользоваться этой таблицей? График погашения кредита для вашей ипотеки - это просто таблица ваших ипотечных платежей с течением времени. Каждый платеж разбивается на основную сумму и проценты. Таблица с амортизацией по ипотечным кредитам может помочь вам понять, сколько процентов вы будете платить за весь срок кредита, а также рассчитать, сколько процентов вы будете платить за налоговые цели.
Основные порты платежей со временем
Ниже приведен график графика амортизации по процентам против основных платежей с течением времени. Обратите внимание, что в большей степени ваш платеж начинается с интереса. Затем, со временем, больше вашего платежа идет к основному. Многие кредиторы позволяют вам делать дополнительные платежи по кредиту. Это позволяет вам быстрее выплатить свой основной долг, а это значит, что вы платите меньше процентов с течением времени.
Если кому нужно более наглядно, то посмотрите видео, где этот файл и формировался:
А для тех, кому просто нужен калькулятор и график платежей, вот ссылка на файл.