Как найти сумму кредита excel

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

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

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

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

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

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

Расчет аннуитетных платежей по кредиту в Excel

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

А = К * S

где:

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

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• где i – процентная ставка за месяц, результат деления годовой ставки на 12;
• n – срок кредита в месяцах.

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

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



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



3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

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



Расчет платежей в Excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

• сумма основного долга распределена по периодам выплат равными долями;
• проценты по кредиту начисляются на остаток.

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

где:

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

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

Входные данные те же:

Составим график погашения займа:

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

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

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9<=$B$4;$B$2/$B$4;0).

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

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

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Формула расчета процентов по кредиту в Excel

Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

Заполним таблицу вида:

Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.

Сумма основного долга = аннуитетный платеж – проценты.

Сумма процентов = остаток долга * месячную процентную ставку.

Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.

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

• взяли кредит 500 000 руб.;
• вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
• переплата составила 184 881, 67 руб.;
• процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
• Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

• ПСК = i * ЧБП * 100;
• где i – процентная ставка базового периода;
• ЧБП – число базовых периодов в календарном году.

Возьмем для примера следующие данные по кредиту:

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

Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.

Далее находим ЧБП: 365 / 28 = 13.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

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

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

Скачать кредитный калькулятор в 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)

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

Воспользуйтесь средством Excel Formula Coach для расчета ежемесячных выплат по ссуде. При этом вы узнаете, как использовать функцию ПЛТ в формуле.

Синтаксис

ПЛТ(ставка; кпер; пс; [бс]; [тип])

Примечание: Более подробное описание аргументов функции ПЛТ см. в описании функции ПС.

Аргументы функции ПЛТ описаны ниже.

• Ставка    Обязательный аргумент. Процентная ставка по ссуде.

• Кпер    Обязательный аргумент. Общее число выплат по ссуде.

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

• Бс    Необязательный. Значение будущей стоимости, то есть желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается значение 0 (например, значение будущей стоимости для займа равно 0).

• Тип    Необязательный аргумент. Число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.

Тип	Когда нужно платить
0 или опущен	В конце периода
1	В начале периода

Замечания

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

• Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте значения 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».

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

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные	Описание
8 %	Годовая процентная ставка
10	Количество месяцев платежей
10 000р.	Сумма займа
Формула	Описание	Результат
=ПЛТ(A2/12;A3;A4)	Ежемесячный платеж по займу в соответствии с условиями, указанными в качестве аргументов в диапазоне A2:A4.	(1 037,03р.)
=ПЛТ(A2/12;A3;A4;;1)	Ежемесячный платеж по займу в соответствии с условиями, указанными в качестве аргументов в диапазоне A2:A4, за исключением платежей, подлежащих оплате в начале периода.	(1 030,16р.)
Данные	Описание
6 %	Годовая процентная ставка
18	Количество месяцев платежей
50 000р.	Сумма займа
Формула	Описание	Оперативный результат
ПЛТ(A9/12;A10*12; 0;A11)	Необходимая сумма ежемесячных платежей для выплаты 50 000р. за 18 лет.	(129,08 ₽)

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

Расчет оплаты

Прежде всего, нужно сказать, что существует два вида кредитных платежей:

• Дифференцированные;
• Аннуитетные.

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

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

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

Этап 1: расчет ежемесячного взноса

Для расчета ежемесячного взноса при использовании аннуитетной схемы в Экселе существует специальная функция – ПЛТ. Она относится к категории финансовых операторов. Формула этой функции выглядит следующим образом:

=ПЛТ(ставка;кпер;пс;бс;тип)

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

Аргумент «Ставка» указывает на процентную ставку за конкретный период. Если, например, используется годовая ставка, но платеж по займу производится ежемесячно, то годовую ставку нужно разделить на 12 и полученный результат использовать в качестве аргумента. Если применяется ежеквартальный вид оплаты, то в этом случае годовую ставку нужно разделить на 4 и т.д.

«Кпер» обозначает общее количество периодов выплат по кредиту. То есть, если заём берется на один год с ежемесячной оплатой, то число периодов считается 12, если на два года, то число периодов – 24. Если кредит берется на два года с ежеквартальной оплатой, то число периодов равно 8.

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

«Бс» — это будущая стоимость. Эта величина, которую будет составлять тело займа на момент завершения кредитного договора. В большинстве случаев данный аргумент равен «0», так как заемщик на конец срока кредитования должен полностью рассчитаться с кредитором. Указанный аргумент не является обязательным. Поэтому, если он опускается, то считается равным нулю.

Аргумент «Тип» определяет время расчета: в конце или в начале периода. В первом случае он принимает значение «0», а во втором – «1». Большинство банковских учреждений используют именно вариант с оплатой в конце периода. Этот аргумент тоже является необязательным, и если его опустить считается, что он равен нулю.

Теперь настало время перейти к конкретному примеру расчета ежемесячного взноса при помощи функции ПЛТ. Для расчета используем таблицу с исходными данными, где указана процентная ставка по кредиту (12%), величина займа (500000 рублей) и срок кредита (24 месяца). При этом оплата производится ежемесячно в конце каждого периода.

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



2. Производится запуск окошка Мастера функций. В категории «Финансовые» выделяем наименование «ПЛТ» и жмем на кнопку «OK».



3. После этого открывается окно аргументов оператора ПЛТ.

   В поле «Ставка» следует вписать величину процентов за период. Это можно сделать вручную, просто поставив процент, но у нас он указан в отдельной ячейке на листе, поэтому дадим на неё ссылку. Устанавливаем курсор в поле, а затем кликаем по соответствующей ячейке. Но, как мы помним, у нас в таблице задана годовая процентная ставка, а период оплаты равен месяцу. Поэтому делим годовую ставку, а вернее ссылку на ячейку, в которой она содержится, на число 12, соответствующее количеству месяцев в году. Деление выполняем прямо в поле окна аргументов.

   В поле «Кпер» устанавливается срок кредитования. Он у нас равен 24 месяцам. Можно занести в поле число 24 вручную, но мы, как и в предыдущем случае, указываем ссылку на месторасположение данного показателя в исходной таблице.

   В поле «Пс» указывается первоначальная величина займа. Она равна 500000 рублей. Как и в предыдущих случаях, указываем ссылку на элемент листа, в котором содержится данный показатель.

   В поле «Бс» указывается величина займа, после полной его оплаты. Как помним, это значение практически всегда равно нулю. Устанавливаем в данном поле число «0». Хотя этот аргумент можно вообще опустить.

   В поле «Тип» указываем в начале или в конце месяца производится оплата. У нас, как и в большинстве случаев, она производится в конце месяца. Поэтому устанавливаем число «0». Как и в случае с предыдущим аргументом, в данное поле можно ничего не вводить, тогда программа по умолчанию будет считать, что в нем расположено значение равное нулю.

   После того, как все данные введены, жмем на кнопку «OK».



4. После этого в ячейку, которую мы выделили в первом пункте данного руководства, выводится результат вычисления. Как видим, величина ежемесячного общего платежа по займу составляет 23536,74 рубля. Пусть вас не смущает знак «-» перед данной суммой. Так Эксель указывает на то, что это расход денежных средств, то есть, убыток.



5. Для того, чтобы рассчитать общую сумму оплаты за весь срок кредитования с учетом погашения тела займа и ежемесячных процентов, достаточно перемножить величину ежемесячного платежа (23536,74 рубля) на количество месяцев (24 месяца). Как видим, общая сумма платежей за весь срок кредитования в нашем случае составила 564881,67 рубля.



6. Теперь можно подсчитать сумму переплаты по кредиту. Для этого нужно отнять от общей величины выплат по кредиту, включая проценты и тело займа, начальную сумму, взятую в долг. Но мы помним, что первое из этих значений уже со знаком «-». Поэтому в конкретно нашем случае получается, что их нужно сложить. Как видим, общая сумма переплаты по кредиту за весь срок составила 64881,67 рубля.

Урок: Мастер функций в Эксель

Этап 2: детализация платежей

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

1. Для определения величины оплаты по телу займа используем функцию ОСПЛТ, которая как раз предназначена для этих целей. Устанавливаем курсор в ячейку, которая находится в строке «1» и в столбце «Выплата по телу кредита». Жмем на кнопку «Вставить функцию».



2. Переходим в Мастер функций. В категории «Финансовые» отмечаем наименование «ОСПЛТ» и жмем кнопку «OK».



3. Запускается окно аргументов оператора ОСПЛТ. Он имеет следующий синтаксис:

   =ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)

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

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



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

   После того, как все данные, о которых мы говорили выше, введены, жмем на кнопку «OK».



5. После этого в ячейке, которую мы ранее выделили, отобразится величина выплаты по телу займа за первый месяц. Она составит 18536,74 рубля.



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



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



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



9. В запустившемся окне Мастера функций в категории «Финансовые» производим выделение наименования ПРПЛТ. Выполняем щелчок по кнопке «OK».



10. Происходит запуск окна аргументов функции ПРПЛТ. Её синтаксис выглядит следующим образом:

    =ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)

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



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



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



13. Теперь нам предстоит рассчитать общий ежемесячный платеж. Для этого вычисления не следует прибегать к какому-либо оператору, так как можно воспользоваться простой арифметической формулой. Складываем содержимое ячеек первого месяца столбцов «Выплата по телу кредита» и «Выплата по процентам». Для этого устанавливаем знак «=» в первую пустую ячейку столбца «Общая ежемесячная выплата». Затем кликаем по двум вышеуказанным элементам, установив между ними знак «+». Жмем на клавишу Enter.



14. Далее с помощью маркера заполнения, как и в предыдущих случаях, заполняем колонку данными. Как видим, на протяжении всего действия договора сумма общего ежемесячного платежа, включающего платеж по телу займа и оплату процентов, составит 23536,74 рубля. Собственно этот показатель мы уже рассчитывали ранее при помощи ПЛТ. Но в данном случае это представлено более наглядно, именно как сумма оплаты по телу займа и процентам.



15. Теперь нужно добавить данные в столбец, где будет ежемесячно отображаться остаток суммы по кредиту, который ещё требуется заплатить. В первой ячейке столбца «Остаток к выплате» расчет будет самый простой. Нам нужно отнять от первоначальной величины займа, которая указана в таблице с первичными данными, платеж по телу кредита за первый месяц в расчетной таблице. Но, учитывая тот факт, что одно из чисел у нас уже идет со знаком «-», то их следует не отнять, а сложить. Делаем это и жмем на кнопку Enter.



16. А вот вычисление остатка к выплате после второго и последующих месяцев будет несколько сложнее. Для этого нам нужно отнять от тела кредита на начало кредитования общую сумму платежей по телу займа за предыдущий период. Устанавливаем знак «=» во второй ячейке столбца «Остаток к выплате». Далее указываем ссылку на ячейку, в которой содержится первоначальная сумма кредита. Делаем её абсолютной, выделив и нажав на клавишу F4. Затем ставим знак «+», так как второе значение у нас и так будет отрицательным. После этого кликаем по кнопке «Вставить функцию».



17. Запускается Мастер функций, в котором нужно переместиться в категорию «Математические». Там выделяем надпись «СУММ» и жмем на кнопку «OK».



18. Запускается окно аргументов функции СУММ. Указанный оператор служит для того, чтобы суммировать данные в ячейках, что нам и нужно выполнить в столбце «Выплата по телу кредита». Он имеет следующий синтаксис:

    =СУММ(число1;число2;…)

    В качестве аргументов выступают ссылки на ячейки, в которых содержатся числа. Мы устанавливаем курсор в поле «Число1». Затем зажимаем левую кнопку мыши и выделяем на листе первые две ячейки столбца «Выплата по телу кредита». В поле, как видим, отобразилась ссылка на диапазон. Она состоит из двух частей, разделенных двоеточием: ссылки на первую ячейку диапазона и на последнюю. Для того, чтобы в будущем иметь возможность скопировать указанную формулу посредством маркера заполнения, делаем первую часть ссылки на диапазон абсолютной. Выделяем её и жмем на функциональную клавишу F4. Вторую часть ссылки так и оставляем относительной. Теперь при использовании маркера заполнения первая ячейка диапазона будет закреплена, а последняя будет растягиваться по мере продвижения вниз. Это нам и нужно для выполнения поставленных целей. Далее жмем на кнопку «OK».



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



20. Помесячный расчет остатков к оплате по кредиту сделан за весь кредитный период. Как и положено, на конец срока эта сумма равна нулю.

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

Урок: Финансовые функции в Excel

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

Аннуитет. Расчет периодического платежа в MS EXCEL. Погашение ссуды (кредита, займа)

​Смотрите также​ использовать похожую конструкцию​ ту сумму, которую​ придется влезть в​ ЧБП * 100;​Выплата процентов: остаток по​ платежа;​ файл примера).​ российские банки применяют​ вычисления и таким​ при помощи обычного​ что он делится​

​ встречаются или встречаются​Решение2​ период; кпер; пс;​ месяца начисляется процент​ Тип=0. Платеж должен​Рассчитаем в MS EXCEL​ с проверкой на​ мы отдаем банку​ это дело, то​где i – процентная​ кредиту в текущем​S – величина займа.​Функция ПРОЦПЛАТ() начисленные​ в расчетах аннуитетную​
​ образом, чтобы значения​ калькулятора. Но чтобы​ на две части.​ крайне редко в​Ежеквартальный платеж может​

Задача1

​ [бс]; [тип]) практически​ за пользование ссудой​ производиться также в​ сумму регулярного аннуитетного​ присутствие номера периода:​ за временно использование​ неплохо бы перед​ ставка базового периода;​

​ периоде умножить на​Формула коэффициента аннуитета:​

1. ​ проценты за пользование​ схему погашения кредита.​ были положительными. С​​ расчет погашения кредита​ Первая – фиксированные​ перечне банковских услуг.​ быть вычислен по​ с теми же​​ в размере (100 000*10%/12),​
2. ​ конце каждого периода;​​ платежа при погашении​​=ЕСЛИ(A18<>»»;​ его денег. Это​ походом в банк​ЧБП – число базовых​ месячную процентную ставку,​К = (i *​ кредитом указывает с​ Сравнение двух графиков​
3. ​ ее помощью банковские​ аннуитетными платежами стал​ проценты по кредиту,​​Виды аннуитетов:​​ формуле =ПЛТ(15%/12; 5*4;​ аргументами, что и​ до этого момента​Процент за пользование заемными​
4. ​ ссуды. Сделаем это​текущая формула​ можно сделать с​ хотя бы ориентировочно​​ периодов в календарном​​ которая разделена на​ (1 + i)^n)​
5. ​ противоположным знаком, чтобы​ погашения кредита приведено​ сотрудники в считанные​ наиболее понятным, рассмотрим​ вторая – часть​
6. ​Фиксированные – неизменные на​​ 100 000; -100 000*10%; 0),​​ ПЛТ() (подробнее см.​ должен быть внесен​ средствами в месяц​ как с использованием​; «»)​ помощью простых формул:​ прикинуть суммы выплат​
7. ​ году.​ 12 месяцев: =E9*($B$3/12).​ / ((1+i)^n-1)​ отличить денежные потоки​
8. ​ в статье Сравнение​ минуты могут сделать​ его на примере.​

​ суммы основного долга.​ протяжении всего срока​ результат -6 851,59р.​ статью Аннуитет. Расчёт​ первый ежемесячный платеж.​ (за период) составляет​

​ функции ПЛТ(), так​​Т.е. если номер периода​Если хочется более детализированного​ по кредиту, переплату,​

​Возьмем для примера следующие​Выплата основного долга: сумму​
​где i – процентная​ (если выдача кредита​
​ графиков погашения кредита​ и распечатать график​Предположим, что заемщик взял​

​ Причем сумма начисленных​​ кредитования.​Все параметры функции​ в MS EXCEL​В случае начисления​ 10%/12 (ставка);​ и впрямую по​ не пустой, то​ расчета, то можно​ сроки и т.д.​ данные по кредиту:​ всего кредита разделить​

​ ставка за месяц,​ – положительный денежный​ дифференцированными аннуитетными платежами​ платежей кредитополучателям, экономя​ в банке ссуду​ процентов обычно превышает​Валютные – размер платежа​
​ ПЛТ() выбираются аналогично​ погашение основной суммы​ процентов в начале​В конце срока задолженность​ формуле аннуитетов. Также​
​ мы вычисляем сумму​ воспользоваться еще двумя​ «Помассажировать числа» заранее,​Для расчета полной стоимости​ на срок: =ЕСЛИ(D9​ результат деления годовой​
​ поток («в карман»​ в MS EXCEL.​ их время.​ на сумму 50​ сумму, идущую на​ может меняться в​ предыдущей задаче, кроме​ долга). Т.к. сумма​ периода, в первом​ должна быть равна​ составим таблицу ежемесячных​ выплат с помощью​ полезными финансовыми функциями​
​ как я это​ кредита нужно составить​Итоговый платеж: сумма «процентов»​ ставки на 12;​ заемщика), то регулярные​Задача​Чтобы заполнить все месяцы,​ 000 рублей сроком​ погашение ссуды.​ зависимости от колебаний​

​ значения БС, которое​​ идущая на погашение​
​ месяце % не​ 0 (БС=0).​ платежей с расшифровкой​ наших формул с​ Excel -​ называю Microsoft​ график платежей (порядок​ и «основного долга»​n – срок кредита​ выплаты – отрицательный​
​. Сумма кредита =150т.р.​ нужно протянуть ячейку​ на 5 лет.​Тело кредита сравнивается с​ на валютном рынке.​ = -100000*10%=-10000р., и​ основной суммы долга​ начисляется, т.к. реального​Расчет суммы выплаты по​ оставшейся части долга​ ПРПЛТ и ОСПЛТ.​ОСПЛТ (PPMT)​ Excel может сильно​ см. выше).​ в текущем периоде:​

​ в месяцах.​
​ поток «из кармана»).​ Срок кредита =2​ с формулой до​ По условиям кредитного​ процентами по нему​Индексируемые – могут корректироваться​ требует пояснения.​ изменяется от периода​ пользования средствами ссуды​ ссуде за один​​ и начисленных процентов.​​ Если же номера​и​ помочь в этом​​Нужно определить базовый период​​ =F8+G8.​

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

​ к периоду, то​ не было (грубо​ период, произведем сначала​При кредитовании банки наряду​
​ нет, то выводим​ПРПЛТ (IPMT)​ вопросе.​ (БП). В законе​Внесем формулы в соответствующие​

​ специальная функция, которая​ суммы процентов, начисленных​ кредиту = 12%.​ Но поскольку аннуитетный​ ставка по кредиту​ общего срока кредитования.​ актуальный уровень инфляции.​ к предыдущей задаче,​ необходим еще один​ говоря % должен​ с помощью финансовой​ с дифференцированными платежами​ пустую текстовую строку:​. Первая из них​Для быстрой прикидки кредитный​ сказано, что это​ столбцы. Скопируем их​ считает аннуитетные платежи.​ за определенное количество​ Погашение кредита ежемесячное,​ платеж со временем​ равна 20 %.​ Далее сумма, которая​Переменные – платежи меняются​

​ где ПС =​

​ аргумент​ быть начислен за​

​ функции MS EXCEL​ часто используют аннуитетную​Реализованный в предыдущем варианте​ вычисляет ту часть​ калькулятор в Excel​ стандартный временной интервал,​ на всю таблицу.​ Это ПЛТ:​ периодов с даты​ в конце каждого​ не меняется, цифры​Исходя из формулы, для​ идет на погашение​ в зависимости от​ 100000, а БС=0.​период​ 0 дней пользования​ ПЛТ().​ схему погашения. Аннуитетная​​ калькулятор неплох, но​​ очередного платежа, которая​ можно сделать за​ который встречается в​

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

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

​ договора. Запишем суммы​Решение. Сначала вычислим часть​ одинаковые.​ ежемесячную процентную ставку.​ расти, постепенно превышая​ инструмента.​ платежа обладает тем​ какому периоду относится​

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

​ помощью всего одной​​ всего. В примере​ погашения кредита:​ по кредиту. Это​ процентов начисленных в​ (долю) основной суммы​Перепроверить полученные данные можно​ Банки редко указывают​ размер выплат за​

​Механизм расчета аннуитетных платежей​ свойством, что сумма​ сумма.​ идет в погашение​ аннуитета в статье​ платежами (как правило,​ реальной жизни вы,​ кредита), а вторая​ функции и пары​ БП = 28​Красная цифра – аннуитет​ сумма займа, проценты​ первых периодов (начисление​ кредита, которую заемщик​ с помощью кредитного​ данную цифру в​

Задача2

​ пользование кредитом. Как​ по кредиту зависит​ величин идущих на​Для вычисления ежемесячных сумм​ ссуды (основной суммы​ найдете здесь.​ ежемесячными), которые включают​ скорее всего, будете​ может посчитать ту​ простых формул. Для​

​ дней.​
​ (брали 100 000​ и срок.​ и выплата в​ выплачивает за период:​ калькулятора аннуитетных платежей.​
​ кредитном договоре, поэтому​ уже было сказано​ от их вида.​ погашение тела кредита​ идущих на погашение​ долга).​
​Эта функция имеет такой​ как выплату основного​ вносить дополнительные платежи​ часть, которая придется​ расчета ежемесячной выплаты​Далее находим ЧБП: 365​ руб.), черная –​Составим график погашения кредита.​ конце периода):​ =150т.р./2/12, т.е. 6250р.​ Он есть на​ нужно найти ее​ ранее, это не​ Однако для потребительского​ за все периоды​ процентов за ссуду​
​Решение1​ синтаксис:​ долга, так и​ для досрочного погашения​

excel2.ru

Расчет аннуитетных платежей по кредиту: пример

​ на проценты банку.​ по аннуитетному кредиту​ / 28 =​ дифференцированный способ.​ Пока пустой.​ПС*ставка​ (сумму кредита мы​ сайтах всех банков,​ самостоятельно. Для этого​ выгодно для заемщиков,​ кредитования чаще всего​ выплат равна величине​ используется функция ПРПЛТ​Итак, ежемесячный платеж​ПЛТ(ставка; кпер; пс;​ процентный платеж за​ при удобной возможности.​ Добавим к нашему​ (т.е. кредиту, где​ 13.​Проведем расчет процентов по​

Аннуитетный платеж – это…

​В первую ячейку столбца​(ПС-ПС/кпер)*ставка​ разделили на общее​ которые выдают ссуды​ нужно воспользоваться формулой:​ особенно когда речь​ используются фиксированные аннуитеты.​ займа с противоположным​ (ставка; период; кпер;​ может быть вычислен​ [бс]; [тип])​

​ пользование кредитом. Такой​ Для реализации этого​ предыдущему примеру небольшую​ выплаты производятся равными​Теперь можно найти процентную​ кредиту в Excel​ «Платежи по кредиту»​(ПС-2*ПС/кпер)*ставка​ количество периодов выплат​ с таким способом​пс = П /​ идет о долгосрочном​Расчет аннуитетных платежей по​ знаком. Т.е. справедливо​ пс; [бс]; [тип])​ по формуле =ПЛТ(10%/12;​

Классификация аннуитета

​PMT(rate, nper, pv,​ равновеликий платеж называется​ можно добавить в​ шапку таблицы с​ суммами — таких​ ставку базового периода:​ и вычислим эффективную​ вводиться формула расчета​(ПС-3*ПС/кпер)*ставка​ =2года*12 (мес. в​

​ погашения. Для использования​

• ​ 100 / 12,​ кредитовании.​ кредиту обычно производится​
• ​ равенство: ПС+СУММ(долей ПЛТ,​ с теми же​ 5*12; 100 000; 0;​ [fv], [type]) –​
• ​ аннуитет.​ нашу модель столбец​ подробным расчетом и​
• ​ сейчас большинство) в​У нас имеются все​ процентную ставку, имея​ кредита аннуитетными платежами​

​…​ году)).​ кредитного калькулятора понадобятся​где:​На самом деле, рассчитать​ перед его оформлением.​

Преимущества и недостатки аннуитетных платежей

​ идущих на погашение​ аргументами, что и​ 0), результат -2 107,14р.​ английский вариант.​В аннуитетной схеме​ с дополнительными выплатами,​ номера периодов (месяцев):​ Excel есть специальная​ необходимые данные –​ следующую информацию по​ в Excel: =ПЛТ($B$3/12;​Просуммируем полученные выражения​Каждый период заемщик​

​ те же исходные​

• ​П – годовая процентная​ точный размер платежа​ Поэтому важно знать,​ тела кредита)+БС=0: 100000р.+(-100000р.)+0=0.​
• ​ ОСПЛТ() (подробнее см.​ Знак минус показывает,​Примечание​ погашения предполагается неизменность​
• ​ которые будут уменьшать​Функция​ функция​

​ подставляем их в​ предлагаемому банком кредиту:​ $B$4; $B$2). Чтобы​ и, используя формулу​ выплачивает банку эту​ данные, что и​ ставка.​ достаточно просто. Причем​ стоит ли вообще​То же самое​

​ статью Аннуитет. Расчет​

• ​ что мы имеем​: Функция ПЛТ() входит​ процентной ставки по​ остаток. Однако, большинство​
• ​ОСПЛТ (PPMT)​
• ​ПЛТ (PMT)​ формулу ПСК: =B9*B8​Рассчитаем ежемесячную процентную ставку​

​ закрепить ячейки, используем​ суммы арифметической прогрессии,​ часть основного долга​ для предыдущих способов​Расчет:​ это можно сделать​ заниматься какими-либо вычислениями,​ и для второй​ в MS EXCEL​ разнонаправленные денежные потоки:​ в надстройку «Пакет​ кредиту в течение​ банков в подобных​в ячейке B17​из категории​Примечание. Чтобы получить проценты​ и платежи по​

Из чего состоит платеж по кредиту?

​ абсолютные ссылки. Можно​ получим результат.​ плюс начисленные на​ расчета. Их нужно​пс = 20 /​ сразу несколькими способами.​ или лучше сразу​ задачи: 100000р.+(-90000р.)+БС=0, т.е.​ выплаченных процентов за​ +100000 – это​ анализа». Если данная​ всего периода выплат.​

​ случаях предлагают на​ вводится по аналогии​Финансовые (Financial)​ в Excel, не​ кредиту:​ вводить в формулу​=ПС*Ставка* период*(1 -​ его остаток проценты.​ ввести в отведенные​ 100 / 12​ Используя хотя бы​ выбрать другой банк​ БС=-10000р.​ период).​ деньги, которые​ функция недоступна или​

Способы расчета ежемесячного аннуитетного платежа по кредиту

​Определить величину ежемесячных равновеликих​ выбор: сокращать либо​ с​. Выделяем ячейку, где​ нужно умножать на​Заполним таблицу вида:​ непосредственно числа, а​ (период-1)/2/кпер)​ Расчет начисленных процентов​ поля для заполнения.​ = 0,017.​

​ один из них,​

• ​ для получения ссуды.​
• ​Первое правило при оформлении​Примечание​
• ​банкдал​ возвращает ошибку #ИМЯ?,​

​ выплат по ссуде,​ сумму ежемесячной выплаты,​ПЛТ​ хотим получить результат,​ 100. Достаточно выставить​Комиссия берется ежемесячно со​ не ссылки на​Где, Ставка –​ на остаток долга​ И затем программа​Зная все исходные данные,​

Формула расчета

​ можно сориентироваться в​ Чтобы определиться, нужно​ кредита – нужно​. Для определения суммы​нам, -2107,14 –​ то включите или​ размер которой составляет​ либо срок. Каждый​в предыдущем примере:​ жмем на кнопку​ для ячейки с​

​ всей суммы. Общий​ ячейки с данными.​

​ это процентная ставка​ приведен в таблице​ самостоятельно сделает все​ можно приступать к​

​ предстоящих выплатах и​

​ взвесить все плюсы​ адекватно оценить собственную​

​ переплаты по кредиту​ это деньги, которые​

​ установите и загрузите​ 100 000 руб.,​

​ такой сценарий для​Добавился только параметр​

​fx​ результатом процентный формат.​ платеж по кредиту​ Тогда она примет​ за период (=годовая​ ниже – это​ расчеты в течение​ нахождению аннуитетного платежа​ оценить, насколько «подъемной»​ и минусы аннуитетных​ платежеспособность, чтобы платежи​ (общей суммы выплаченных​ мы​

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

​ эту надстройку (в​ а процентная ставка​ наглядности лучше посчитать​Период​в строке формул,​ПСК по новой формуле​ – это аннуитетный​ следующий вид: =ПЛТ(18%/12;​ ставка / число​

​ и есть график​ нескольких секунд, дав​ по кредиту. Оно​ окажется банковская ссуда.​ платежей.​ по нему со​ процентов) используйте функцию​возвращаем банку​ MS EXCEL 2007/2010​

​ составляет 10% годовых.​ отдельно.​

​с номером текущего​

​ находим функцию​ совпала с годовой​

​ платеж плюс комиссия.​

​ 36; 100000).​ выплат в году),​ платежей.​

​ потенциальному заемщику возможность​ выглядит следующим образом:​Способы расчета аннуитетного платежа:​Преимущества:​ временем не стали​

​ ОБЩПЛАТ(), см. здесь.​.​ надстройка «Пакет анализа»​ Ссуда взята на​В случае уменьшения срока​

Расчет аннуитетных платежей по кредиту в Excel

​ месяца (выплаты) и​ПЛТ​ процентной ставкой по​ Сумма основного долга​Ячейки окрасились в красный​ период – период,​Для расчета начисленных процентов​ оценить полученную сумму​АП = 50 000​вручную при помощи формулы;​неизменность суммы и даты​ обременительными для заемщика.​Конечно, для составления таблицы​Альтернативная формула для расчета​

1. ​ включена по умолчанию).​ срок 5 лет.​ придется дополнительно с​ закрепление знаком $​в списке и​
2. ​ кредиту.​ и сумма процентов​ цвет, перед числами​_{​ до которого требуется​}​ может быть использована​
3. ​ и хорошенько подумать​ * 0,017 /​с использованием программы Microsoft​_{​ внесения платежа на​}​ Но на этом​
4. ​ ежемесячных платежей можно​ платежа (общий случай):​Первый аргумент – Ставка.​

​Разбираемся, какая информация содержится​ помощью функции​

​ некоторых ссылок, т.к.​ жмем​Скачать кредитный калькулятор в​ – составляющие части​ появился знак «минус»,​ найти сумму процентов.​ функция ПРОЦПЛАТ(ставка;период;кпер;пс), где​ о предстоящем оформлении​ 1 — (1​ Excel;​ протяжении всего срока​ этапе иногда возникают​ воспользоваться либо ПРПЛТ()​=-(Пс*ставка*(1+ ставка)^ Кпер​ Это процентная ставка​ в задаче:​ЕСЛИ (IF)​ впоследствии мы эту​ОК​

​ Excel​ аннуитетного платежа.​ т.к. мы эти​Например, сумма процентов,​ Ставка — процентная​ кредита.​ + 0,017)-60 =​на сайте банка с​ кредитования;​

​ сложности, поскольку не​ или ОСПЛТ(), т.к.​ /((1+ ставка)^ Кпер​ именно за период,​Заемщик ежемесячно должен делать​проверять — не​ формулу будем копировать​. В следующем окне​Таким образом, для расчета​Сумма основного долга =​ деньги будем отдавать​ выплаченных за первые​ ставка​Автор: Антонина Савченко​ 1336,47 руб.​ помощью кредитного калькулятора.​доступность практически для всех​ у всех есть​ эти функции связаны​ -1)+​ т.е. в нашем​ платеж банку. Этот​ достигли мы нулевого​ вниз. Функция​

​ нужно будет ввести​

fb.ru

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

​ аннуитетных платежей по​ аннуитетный платеж –​ банку, терять.​

​ полгода пользования кредитом​за период​Составим в MS EXCEL​Программа Excel – это​Каждый из методов расчета​ кредитополучателей вне зависимости​ экономическое образование, чтобы​ и в любой​ставка /((1+ ставка)^​ случае за месяц.​ платеж включает:​ баланса раньше срока:​ПРПЛТ (IPMT)​ аргументы для расчета:​ кредиту используется простейшая​ проценты.​​ (см. условия задачи​; Период – номер​ график погашения кредита​ не просто большая​ при правильном применении​ от их финансового​ правильно произвести необходимые​ период: ПЛТ= ОСПЛТ​ Кпер -1)* Бс)*ЕСЛИ(Тип;1/(ставка​ Ставка =10%/12 (в​сумму в счет погашения​А в случае уменьшения​для вычисления процентной​Ставка​ функция ПЛТ. Как​Сумма процентов = остаток​

​Дифференцированный способ оплаты предполагает,​​ выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р.​ периода, для которого​ дифференцированными платежами.​ таблица. В ней​ даст точную цифру,​ положения;​ вычисления. Для облегчения​ + ПРПЛТ​ +1);1)​ году 12 месяцев).​ части ссудысумму для​ выплаты — заново​ части вводится аналогично.​- процентная ставка​ видите, дифференцированный способ​ долга * месячную​

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

​ что:​​За весь срок​ требуется найти величину​При расчете графика погашения​ можно произвести огромное​ равную предстоящему размеру​возможность снижения платежа с​ задачи здесь собраны​

​Соотношение выплат основной суммы​Если процентная ставка =​Кпер – общее​ оплаты начисленных за​ пересчитывать ежемесячный взнос​ Осталось скопировать введенные​ по кредиту в​ погашения несколько сложнее.​ процентную ставку.​сумма основного долга распределена​
​ будет выплачено =ПС*Ставка*(кпер+1)/2=18750р.​ начисленных процентов; Кпер​ кредита дифференцированными платежами​ количество вычислений, зная​ платежа. Поэтому, если​ поправкой на уровень​ все возможные способы​ долга и начисленных​ 0, то формула​ число периодов платежей​ прошедший период процентов​

​ начиная со следующего​ формулы вниз до​ пересчете на период​Кто как, а я​Остаток основного долга =​​ по периодам выплат​​Через функцию ПРОЦПЛАТ()​ — общее число​ сумма основного долга​ лишь, какие формулы​ есть сомнения в​ инфляции.​ для расчета аннуитетных​ процентов хорошо демонстрирует​ упростится до =(Пс​ по аннуитету, т.е.​ на остаток ссуды​ после досрочной выплаты​ последнего периода кредита​

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

​ график, приведенный в​​ + Бс)/Кпер​ 60 (12 мес.​;​

​ периода:​ и добавить столбцы​​ месяцы. Если годовая​​ Особенно потребительские. Кредиты​ – сумму основного​проценты по кредиту начисляются​ =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ(«1:»&кпер))-1;кпер;-ПС))​ – приведенная стоимость​ части пропорционально сроку​ расчета аннуитетного платежа​ вычислений, можно произвести​ погашения кредита удобен​ которыми можно воспользоваться​ файле примера.​Если Тип=0 (выплата​ в году*5 лет)​
​Сумма ежемесячного платежа (аннуитета)​Существуют варианты кредитов, где​ с простыми формулами​ ставка 12%, то​ для бизнеса -​ долга в предыдущем​ на остаток.​Excel – это универсальный​ на текущий момент​ кредитования. Регулярно, в​ в Excel есть​

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

​ проверку, рассчитав аннуитетный​ тем, что, зная​ для планирования собственного​Примечание​ в конце периода)​Пс — Приведенная​постоянна​ клиент может платить​ для вычисления общей​ на один месяц​
​ другое дело, а​
​ периоде.​
​Формула расчета дифференцированного платежа:​
​ аналитическо-вычислительный инструмент, который​
​ (для кредита ПС​
​ течение всего срока​ специальная функция –​ платеж другим возможным​ точный размер ежемесячного​
​ бюджета.​. В статье Аннуитет.​
​ и БС =0,​ стоимость всех денежных​и не меняется​ нерегулярно, в любые​ суммы ежемесячных выплат​ должно приходиться по​ для обычных людей​Опираясь на таблицу ежемесячных​
​ДП = ОСЗ /​ часто используют кредиторы​ — это сумма​ погашения кредита, заемщик​ ПЛТ. Чтобы правильно​
​ способом.​ взноса, проще планировать​
​Перед практической частью изучения​ Расчет периодического платежа​ то Формула 2​

excel2.ru

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

​ потоков аннуитета. В​ на протяжении всего​ произвольные даты внося​ (она постоянна и​ 1% соответственно.​ мышеловка»деньги за 15​ платежей, рассчитаем эффективную​

​ (ПП + ОСЗ​ (банки, инвесторы и​ кредита, для вклада​ выплачивает банку эти​ ей воспользоваться, нужно​

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

​Расчет процентов по кредиту​ семейный бюджет. Однако​ вопроса следует ознакомиться​ в MS EXCEL.​

1. ​ также упрощается:​ нашем случае, это​ срока, так же​
2. ​ любые имеющиеся суммы.​ равна вычисленной выше​Кпер​ минут, нужен только​ процентную ставку:​ * ПС)​

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

Расчет аннуитетных платежей по кредиту в Excel

​ при аннуитетных платежах​ с точки зрения​

​ с теорией. В​

​ Срочный вклад показано как​

• ​Вышеуказанную формулу часто называют​ сумма ссуды, т.е.​
• ​ как и процентная​ Процентная ставка по​
• ​ в ячейке C7)​

​- количество периодов,​

​ паспорт» срабатывает безотказно,​взяли кредит 500 000​где:​

• ​ (предприниматели, компании, частные​ сумма вклада).​ плюс начисленные на​ шагов:​
• ​ вне зависимости от​ экономической выгоды, он​

​ экономической теории аннуитетный​ рассчитать величину регулярной​ формулой аннуитета (аннуитетного​ 100 000.​

1. ​ ставка. Также не​ таким кредитам обычно​ и, ради интереса,​ т.е. срок кредита​ предлагая удовольствие здесь​
2. ​ руб.;​ДП – ежемесячный платеж​
3. ​ лица и т.д.).​Примечание​ его остаток проценты.​Заполнить исходные данные (сумма,​ выбранного способа вычислений​ имеет несколько существенных​ платеж – это​ суммы пополнения вклада,​ платежа) и записывают​Бс — Будущая​ изменяется порядок платежей​ выше, но свободы​ оставшейся сумме долга:​ в месяцах.​ и сейчас, а​

​вернули в банк –​ по кредиту;​Быстро сориентироваться в мудреных​. Не смотря на​ Если кредитным договором​ проценты и срок​

​ производится с помощью​

Расчет платежей в Excel по дифференцированной схеме погашения

​ недостатков.​ один из способов​

• ​ чтобы накопить желаемую​ в виде А=К*S,​ стоимость всех денежных​
• ​ – 1 раз​ выходит больше. Можно​

​Чтобы сделать наш калькулятор​

​Пс​ расплату за него​ 684 881,67 руб.​

​ОСЗ – остаток займа;​

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

​ в месяц;​ даже взять в​ более универсальным и​

​- начальный баланс,​

​ когда-нибудь потом. И​

​ (сумма всех платежей​ПП – число оставшихся​ суммы выплат, переплату​ аргументов совпадают с​ равным месяцу, то​ В2, В3, В4​ калькуляторы, мобильные приложения​общая сумма переплаты гораздо​ кредиту, когда его​График платежей можно рассчитать​ это аннуитетный платеж​ конце срока (по​Сумма для оплаты начисленных​ банке еще денег​ способным автоматически подстраиваться​

​ т.е. сумма кредита.​ главная проблема, по-моему,​ по кредиту);​ до конца срока​ позволяют функции программы​ названиями аргументов функций​

​ из месяца в​ соответственно).​ и другое программное​

​ выше, чем при​ сумма остается неизменной​ без использования формул​ (т.е. ПЛТ), К​

​ истечении числа периодов​ за прошедший период​ в дополнение к​

​ под любой срок​Бс​ даже не в​

​переплата составила 184 881,​ погашения периодов;​ Microsoft Excel.​ аннуитета – ПРОЦПЛАТ()​

Формула расчета процентов по кредиту в Excel

​ месяц сумма основного​Составить график погашения кредита​ обеспечение делает правильные​ дифференцированном способе погашения​ на протяжении всего​ аннуитета. График приведен​

​ — это коэффициент​ Кпер). В нашем​ процентов​

​ имеющемуся кредиту. Для​

​ кредита, имеет смысл​- конечный баланс,​ грабительских процентах или​ 67 руб.;​ПС – процентная ставка​Ежемесячные выплаты зависят от​ не входит в​ долга пропорционально уменьшается.​ по месяцам (А7​

​ расчеты, отталкиваясь именно​ долга;​ срока кредитования.​

​ в столбцах K:P​ аннуитета, а S​ случае Бс =​

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

​процентная ставка – 184​ за месяц (годовую​ схемы погашения кредита.​

• ​ группу этих функций​ Поэтому при дифференцированных​
• ​ -А​ от нее.​сложность досрочного погашения;​При таком способе погашения​
• ​ файла примера лист​ — это сумма​
• ​ 0, т.к. ссуда​каждый период, т.к.​ модели придется рассчитывать​ В ячейке А18​
• ​ которым мы должны​ это «потом» все​ 881, 67 /​

​ ставку делим на​ Различают аннуитетные и​ (не может быть​ платежах основные расходы​n​

Расчет полной стоимости кредита в Excel

​Общий вид данной формулы​невозможность перерасчета при желании​ кредита плательщик одновременно​ Аннуитет (ПЛТ), а​ кредита (т.е. ПС).​ в конце срока​ проценты начисляются только​ проценты и остаток​ лучше использовать формулу​ по идее прийти​

• ​ равно когда-нибудь наступит.​ 500 000 *​
• ​ 12).​ дифференцированные платежи:​
• ​ использована для расчета​ заемщик несет в​).​

​ выглядит следующим образом:​ заемщика досрочно погасить​

​ платит и начисленные​ также на листе​ K=-i/(1-(1+i)^(-n)) или K=(-i*(1+i)^n)/(((1+i)^n)-1),​ должна быть полностью​

​ на непогашенную часть​ с точностью не​ вида:​ к концу срока.​ Кредит убивает мотивацию​ 100, или 37%.​Составим график погашения предыдущего​Аннуитет предполагает, что клиент​ параметров аннуитета).​

​ начале кредитования, размеры​Сделать столбец «Платежи по​АП = О *​

​ ссуду.​ проценты, и часть​

​ Аннуитет (без ПЛТ).​ где i=ставка за​ погашена. Если этот​ ссуды;​

​ до месяца, а​=ЕСЛИ(A17>=$C$7;»»;A17+1)​ Очевидно =0, т.е.​ к росту. Зачем​Безобидная комиссия в 1​ кредита по дифференцированной​

​ вносит каждый месяц​Примечание​ ежемесячных платежей в​ кредиту» (В7 -​

​ пс / 1​Если рассмотреть пример расчета​

​ суммы основного долга.​ Также тело кредита​ период (т.е. Ставка),​ параметр опущен, то​Как следствие п.3 и​ до дня:​

exceltable.com

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

​Эта формула проверяет с​ никто никому ничего​ напрягаться, учиться, развиваться,​ % обошлась кредитополучателю​ схеме.​ одинаковую сумму.​. Английский вариант функции​ этот период самые​ В​ — (1 +​ аннуитетного платежа по​ Но, как правило,​ на начало и​ n — количество​ он считается =0.​ п.1, сумма, уплачиваемая​Предполагается что:​ помощью функции ЕСЛИ​ не должен.​ искать дополнительные источники​ очень дорого.​Входные данные те же:​При дифференцированной схеме погашения​ — ISPMT(rate, per,​ большие. Но постепенно,​n​ пс)-с,​ кредиту, то сразу​ вначале ежемесячный взнос​ конец периода можно​ периодов (т.е. Кпер).​Тип — число​

​ в счет погашения​в зеленые ячейки пользователь​ (IF) достигли мы​Тип​

​ дохода, если можно​Эффективная процентная ставка кредита​Составим график погашения займа:​ долга перед финансовой​ nper, pv)​ с уменьшением остатка​).​где:​ становится понятно, что​ преимущественно состоит из​ рассчитать с помощью​ Напоминаем, что выражение​ 0 или 1,​ основной суммы ссуды,​ вводит произвольные даты​ последнего периода или​- способ учета​

Вариант 1. Простой кредитный калькулятор в Excel

​ тупо зайти в​ без комиссии составит​Остаток задолженности по кредиту:​ организацией проценты начисляются​Функция ПРОЦПЛАТ() предполагает начисление​ ссудной задолженности, уменьшается​Напротив первого месяца в​АП – ежемесячный аннуитетный​ банкам выгодно выдавать​ процентов по ссуде.​ функции ПС и​ для K справедливо​ обозначающее, когда должна​увеличивается​ платежей и их​​ нет, и выводит​​ ежемесячных выплат. Если​​ ближайший банк и​​ 13%. Подсчет ведется​ в первый месяц​ на остаток кредитной​​ процентов​​ и сумма начисленных​ столбце «Платежи по​​ платеж;​​ клиентам подобные ссуды.​ Поэтому при расчете​​ БС (см. файл​​ только при БС=0​ производиться выплата. 0​от месяца к​

• ​ суммы​​ пустую текстовую строку​ равен 1, то​ там тебе за​ по той же​ равняется всей сумме:​ суммы. Поэтому ежемесячные​в начале каждого периода​ процентов по кредиту.​ кредиту» ввести формулу​
• ​О – сумма основного​​ Однако некоторые не​ аннуитетных платежей по​ примера лист Аннуитет​
• ​ (полное погашение кредита​​ – в конце​ месяцу.​
• ​отрицательные суммы — наши​​ («») в том​ выплаты учитываются на​ полчаса оформят кредит​ схеме.​ =$B$2. Во второй​ платежи будут уменьшаться.​(хотя в справке​ Выплаты по кредиту​
• ​= ПЛТ ($В3/12;$В$4;$В$2) и​​ долга;​ так гонятся за​ кредиту можно заметить,​ (ПЛТ), столбцы H:I).​ за число периодов​ периода, 1 –​Заемщик должен сделать 60​ выплаты банку, положительные​ случае, если достигли,​ начало месяца, если​ на кабальных условиях,​Согласно Закону о потребительском​

​ и последующие –​Чаще применяется аннуитет: выгоднее​ MS EXCEL это​ значительно сокращаются и​ нажать Enter.​пс – ежемесячная процентная​ прибылью, поэтому предоставляют​ что данный способ​Ссуда 100 000 руб.​

Вариант 2. Добавляем детализацию

​ Кпер) и Тип=0​ в начале. Если​ равновеликих платежей (12​ — берем дополнительный​ либо номер следующего​​ равен 0, то​​ попутно грамотно разведя​​ кредите для расчета​​ рассчитывается по формуле:​ для банка и​ не сказано). Но,​ становятся не такими​Результат вычислений отобразиться в​ ставка банка;​ заемщикам более выгодные​ возврата средств не​ взята на срок​ (начисление процентов в​ этот параметр опущен,​ мес. в году*5​ кредит к уже​ периода. При копировании​

​ на конец. У​​ на страхование и​​ полной стоимости кредита​ =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10​ удобнее для большинства​​ функцию можно использовать​​ обременительными для заемщика.​

​ таблице красным цветом​​с – количество месяцев​​ условия кредитования. Стоит​ очень выгоден для​ 5 лет. Определить​ конце периода). ​ то он считается​ лет), т.е. всего​ имеющемуся​​ такой формулы вниз​​ нас в России​ прочие допы?​ (ПСК) теперь применяется​ – номер текущего​ клиентов.​ для расчета процентов,​Примечание​ со знаком «-».​ в сроке кредитования.​ отметить, что среди​ заемщиков.​ величину ежеквартальных равновеликих​Таблица ежемесячных платежей​ =0 (наш случай).​

​ 60 периодов (Кпер);​подсчитать точное количество дней​ на большое количество​ абсолютное большинство банков​Так что очень надеюсь,​ новая формула. ПСК​ периода, В4 –​Ежемесячная сумма аннуитетного платежа​ начисляемых и в​

​. При расчете кредита​

​ Это нормально, ведь​Зная формулу, можно запросто​ микрофинансовых организаций таких​В зависимости от дополнительных​ выплат по ссуде,​Составим таблицу ежемесячных платежей​Примечание​Проценты начисляются​ между двумя датами​ строк мы получим​ работает по второму​ что изложенный ниже​ определяется в процентах​ срок кредита; Е9​ рассчитывается по формуле:​ конце периода для​ дифференцированными платежами сумма​ эти деньги заемщик​ произвести необходимые расчеты​ компаний нет. Поэтому​ условий кредитования, аннуитетные​

​ чтобы через 5​​ для вышерассмотренной задачи.​​:​

​в конце​ (и процентов, которые​ номера периодов как​ варианту, поэтому вводим​ материал вам не​ с точностью до​ – остаток по​А = К * S​ это нужно записать​

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

​ переплаты по процентам​ будет отдавать банку,​ самостоятельно. Достаточно лишь​ при оформлении заема​ платежи делятся на​ лет невыплаченный остаток​Для вычисления ежемесячных сумм​В нашем случае​каждого периода (если​ на них приходятся)​ раз до нужного​ 0.​ пригодится.​ третьего знака после​ кредиту в предыдущем​где:​ ее в виде​ будет ниже, чем​ а не получать.​ подставить исходные данные​ всегда используются аннуитетный​ небольшие группы. Их​ составил 10% от​

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

​ запятой по следующей​ периоде; G9 –​А – сумма платежа​ ПРОЦПЛАТ(ставка;период-1;кпер;пс), т.е. «сдвинуть»​ при аннуитетных платежах.​ Формула расчета кредита​

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

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

​ однако некоторые виды​

• ​ составляет 15% годовых.​ используется функция ОСПЛТ(ставка;​ по истечении первого​ это), т.е. аргумент​
• ​ДОЛЯГОДА (YEARFRAC)​ этой строки можно​ и переплату, т.е.​ или вашим близким​ПСК = i *​
• ​ в предыдущем периоде.​К – коэффициент аннуитетного​ период раньше (см.​ сегодня практически все​ Excel позволяет сделать​ необходимые математические вычисления​​ платежа, можно заметить,​

planetaexcel.ru

​ аннуитетов уже не​