Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).
Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.
Как рассчитать платежи по кредиту в Excel
Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:
- Аннуитет предполагает, что клиент вносит каждый месяц одинаковую сумму.
- При дифференцированной схеме погашения долга перед финансовой организацией проценты начисляются на остаток кредитной суммы. Поэтому ежемесячные платежи будут уменьшаться.
Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.
Расчет аннуитетных платежей по кредиту в Excel
Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:
А = К * S
где:
- А – сумма платежа по кредиту;
- К – коэффициент аннуитетного платежа;
- S – величина займа.
Формула коэффициента аннуитета:
К = (i * (1 + i)^n) / ((1+i)^n-1)
- где i – процентная ставка за месяц, результат деления годовой ставки на 12;
- n – срок кредита в месяцах.
В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:
- Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
- Составим график погашения кредита. Пока пустой.
- В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в 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
Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =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)
Источник
Всем привет.
Как я недавно обнаружил, люди не очень понимают как устроены кредиты.
Попробую объяснить на примере кредита с аннуитетным платежом что откуда и когда берется.
В конце статьи есть ссылка на файл График_платежей.xlsx. Все расчеты и скриншоты для этой статьи сделаны при помощи этого файла.
Расчет стандартного кредита.
Есть такое заблуждение: «Кредит выгодно гасить досрочно только в начале. Вроде как сначала человек выплачивает проценты, а потом начинает выплачивать тело кредита».
Это заявление в корне не верно. На самом деле, все довольно просто. Возьмем, для примера, расчет кредита на 5 лет на сумму в 1 000 000руб.
Картинка автора. Расчет базовых условий по кредиту. Дальше в статье будет развивать варианты этого расчета.
Инструкция:
Заполнению подлежат поля, выделенные желтым. (D1, D2, H6)
Сумма переплаты по графику (F2) считается как сумма ежемесячных платежей минус сумма кредита.
Ежемесячный платеж (F3) считается для начальных условий.
Фактическая сумма переплаты (F4) считается как сумма значений по столбцы «Платеж п процентам»(E).
Экономия на переплате (F5) = Сумма переплаты по графику (F2) — Фактическая сумма переплаты (F4)
Рассмотрим первую строку.
Нас интересует столбец «Платеж по процентам». Его очень просто рассчитать:
Платеж по процентам = [Сумма долга(1 000 000)] х [на ставку (0,12)]/[количество месяцев в году(12)]=10 000руб.
Я для удобства выбрал для расчета ставку 12%, а этом случае набежавшие проценты за месяц равны 1% от суммы долга.
Далее, у нас имеется часть ежемесячного платежа в счет уменьшения тела кредита.
Платеж по телу долга= [Аннуитетный платеж]-[платеж по процентам].
Для расчета аннуитетного платежа в Excel есть специальная функция [ПЛТ]. Эта функция использована для ячейки F3 и всего столбца G.
Соответственно, в следующем месяце тело долга уже меньше 1 000 000руб, платеж по процентам уменьшился и на ту же величину увеличился платеж по телу кредита.
Ну и так далее, до конца выплат.
Про досрочные платежи.
Теперь рассмотрим варианты расчетов с досрочными платежами в разные моменты времени. За основу возьмем все тот же кредит.
Конечно, эффект от досрочного погашения в начале срока и в конце срока будет сильно отличаться. Ниже, для примера, дополнительный взнос в размере 50 000руб осуществляется на 2й месяц и на 40й из 60.
Картинка автора. 50 000 внесено на 2й месяц.
Картинка автора. 50 000 внесено на 40й месяц.
Сумма экономии отличается значительно (ячейка F5).
Но это связано не с тем что «Сначала гасятся проценты» а с эффектом сложного процента.
Как только вы внесли дополнительный платеж, можно условно считать что вы открыли вклад на эту сумму. «Тело вклада» в конце вам ,конечно, не вернут, а проценты вполне начнут капать.
Итак, имеет смысл вносить досрочные платежи в любой момент. С момента внесения платежа на эту сумму перестанет начисляться долг, вот и все.
Варианты досрочных платежей.
Рассмотрим два метода действия после внесения досрочного платежа (вкладка «Кредиты с капитализацией»):
Первый вариант.
Сохранение изначальной суммы ежемесячного платежа.
Во второй вкладке файла (Кредиты с капитализацией) можно наблюдать что будет происходить, если после внесения дополнительного платежа не менять фактическую сумму ежемесячного платежа. То есть, у нас изначально была платеж 22 244,45 руб. Мы внесли досрочно 100 000 руб. Обязательный платеж стал 19 963,87 руб. Но вы продолжаем платить изначальную сумму,22 244,45 руб. В результате у нас обнаруживается второй столбец с досрочными взносами [Разница между минимальным и начальным ежемесячным платежом].
Картинка автора. Размер досрочного платежа ежемесячно растет.
По такой технологии удастся сэкономить на процентах примерно 72 000руб. При этом кредит закроется на 7 месяцев быстрее.
То есть, если бы мы платили по графику, нам бы еще предстояло выплатить 7*22 000руб= 154 000руб.
Результат:
Осуществив досрочное погашение кредита в размере 100 000руб на второй месяц, не меняя ежемесячный платеж, получилось закрыть кредит на 7 месяцев быстрее
Если предположить что 7 месяцев мы продолжаем извлекать из бюджета 22 250руб, к расчетному дате выплаты кредита можно накопить 155 750руб+16 450руб(остаток с последнего месяца фактической выплаты кредита) = 172 200 руб.
Обращаю внимание, что это число равно сумме досрочных платежей и числа из поля «Экономия на переплате».
Второй вариант.
Я считаю думаю что морально реализовать проще, чем на досрочное погашение отправлять 2387,87руб.
Разницу между изначальным и новым ежемесячным платежом направлять на вклад (вкладка «Кредит+вклад»).
Картинка автора. Вместо досрочного погашения как в варианте 1 разницу в платежах направляем на вклад.
Результат.
Тут все просто. Образовавшуюся разницу между изначальным и новым ежемесячным платежом отправляем на вклад. Выплата кредита произойдет в срок. На дату выплаты на вкладе будет 149 000руб (Поле H2).
Из плюсов — В случае каких-то проблем с начислением банком процентов будет проще разбираться если досрочные платежи будут вида 10 000руб, а не 2387,87руб. Будет легче пересчитать и разобраться.
Выводы.
— По возможности надо вносить досрочные платежи в любой момент времени.
— На сумму досрочного платежа начинают «начисляться» проценты, как на вклад.
— Проще разницу между суммами обязательных платежей отправлять на вклад. Не будет сложностей, если придется обращаться в банк для пересчета сумм платежей. И морально будет легче, что деньги копятся. Можно иногда, по достижении некоторой суммы отправлять тоже на досрочные платежи.
Осторожнее с досрочными платежами НЕ В ДАТУ очередного платежа.
При внесении досрочного платежа НЕ В ДАТУ основного платежа надо быть осторожным. Дело в том, что в этот момент гасятся проценты, набежавшие с начала месяца, а потом тело кредита.
Приведу пример:
Дата очередных платежей по кредиту 1 число каждого месяца.
Мы хотим 10го числа сделать дополнительный платеж.
Итого, мы погасим проценты, которые набежали с 1 по 10 число. Погасим тело кредита на сумму [Размер платежа]-[Проценты с 1 по 10]. После этого, проценты с 10 по 30 продолжат капать. Я точно не знаю какую сумму к погашению основного долга выставят в этом случае к дате очередного платежа.
Тут важно понимать, что проценты в следующую дату платежа в любом случае придется вносить. Как точно пересчитывается платеж я не знаю. Надо читать договора и разбираться.
Однозначно можно сказать что очередной платеж будет далеко не 0. Как я слышал, многие на это накалывались и попадали на пени и штрафы.
Именно по этому рекомендуется вносить досрочные платежи в дату списания обязательного платежа.
Скачать файл с расчетами можно по ссылке.
Пользоваться очень просто, подставляете свои значения суммы, срока, ставок, досрочных платежей и получаете результат.
Если есть какие-то замечания, вопросы или пожелания по расчетам, не стесняйтесь, пишите в комментариях.
Источник
Способ погашения кредита – важный фактор, способный оказывать влияние, как на величину текущего взноса, так и на переплату по займу в целом. Российские банки предлагают два варианта исполнения обязательств по договору – аннуитетный и дифференцированный. Поскольку в большинстве случаев предпочтение отдается первом методу, его мы и рассмотрим в данной статье, а так же разберемся, как правильно рассчитать различные виды платежей по нему с использованием Эксель.
Особенности кредитных платежей по аннуитетной схеме
График аннуитетного погашения задолженности подразумевает регулярное внесение денежных средств на счет банка равными долями. При этом с течением времени сам принцип и структурное содержание текущего платежа меняется. Если в первой половине срока вся сумма направлена на погашение переплаты по процентным ставкам, а само тело кредита остается практически неизменным, то во второй его половине ситуация кардинально меняется. Доля самого займа растет, а величина по процентам меняется в сторону снижения.
Аннуитет имеет следующую специфику:
- для банка такой способ расчета – это высокая прибыль даже тогда, когда клиент готов погасить всю сумму досрочно;
- для должника фиксированный платеж – это всегда удобно, поскольку можно рассчитывать финансовую нагрузку на бюджет, зная планируемые траты;
- размер переплаты – в данном случае он будет на порядок выше, чем при дифференцированном варианте погашения займа.
Кредитный калькулятор в Excel
Каждая финансовая компания, имеющая собственный сайт, предлагает пользователям опцию расчетов по кредитам. Воспользовавшись сервисом, потенциальный заемщик сможет наглядно увидеть размер своего текущего платежа, а так же общую сумму, которую он должен будет вернуть банку. Конечно, пользоваться таким программами легко и довольно удобно, однако желательно все же иметь собственного, домашнего помощника в подсчетах. Идеальный вариант – специальная программа, разработанная в Excel.
Данный сервис – это калькулятор, который даст возможность быстро посчитать различные виды выплат по кредитному соглашению, не требуя при этом прямого доступа к интернет-ресурсу. Человеку просто необходимо скачать приложение и выполнять математические подсчеты в любое удобное для него, время.
Обратите внимание! Программа достаточно популярна в бухгалтерском учете.
Для того чтобы получить данные, потребуется минимум информации:
- величина планируемого займа;
- целевое предназначение сделки;
- процентная ставка, действующая в банке на данный момент времени.
Программа выглядит в виде таблицы, поля которой заполняются данными. При работе с ней используются следующие функции:
- КПЕР – размер кредита в рублях;
- СТАВКА – величина переплаты. Измеряется в процентах на определенный промежуток времени. Чаще всего – за год;
- ПС – целевое предназначение займа;
- ПЛТ – текущий взнос по кредиту.
Если ввести в программу верхние три пункта, сервис в автоматическом режиме подсчитает последний, четвертый.
Справка! В программе так же есть опция составления персонального графика внесения денег на счет банка. Для этого дополнительно потребуется дата, с которой начал свое действие договор по займу. Она указывается в соглашении, подписанном сторонами.
Как рассчитать уровень платежей в Excel
Принцип проведения расчетов зависит от выбранной клиентом схемы погашения долга. В нашей статье мы не будем рассматривать дифференцированный метод внесения средств, поскольку:
- произвести такие расчеты достаточно сложно – при таком методе сумма платежа каждый месяц меняется;
- способ крайне редко применяется банковской системой, что делает рассмотрение здесь математических формулировок не целесообразным.
Рассмотрим более детально, какие формулы следует применять при стандартном, аннуитетном варианте погашения долга.
Какие формулы использовать
Сумма, которую должник обязан исправно перечислять на расчетный счет банка ежемесячно, определяется следующим образом:
А = К х С, где:
- А – искомая величина взноса;
- К – аннуитетный коэффициент;
- С – сумма по договору (тело кредита).
В свою очередь, коэффициент получаем по формуле:
К = (i * (1 + i)^n) / ((1+i)^n-1), где:
- i – ставка в месяц. Она привязана к размеру годовой переплаты по процентам и составляет двенадцатую ее часть;
- n – период актуальности кредитного договора. Данный промежуток времени следует исчислять только в календарных месяцах.
Расчет суммы переплаты при досрочном погашении
Стремление вернуть долг как можно скорее – оправданно, ведь так человек меньше теряет. Если сразу внести досрочно нужную сумму нет возможности, многие прибегают к долевому погашению, стараясь каждую свободную копейку перечислить компании.
Для удобства пользователей в таблице добавлен дополнительный столбец, автоматически уменьшающий остаток на сумму последнего платежа. Это исключит риск переплаты и позволит вовремя закрыть обязательства по кредиту.
Калькулятор по кредиту с нерегулярными оплатами
Все больше становятся популярными те виды займов, в договоре которых прописан пункт о возможности внесения нерегулярных оплат. Это удобно – человек в любую, выбранную им дату, перечисляет определенную, доступную для него, сумму. Конечно, ставка при таком соглашении будет на порядок выше, но зато свобода действий заемщика – больше. Это позволит погасить долг в более короткие сроки, перекрыв, тем самым, переплату по процентам.
Справка! Банки, в свою очередь, видя частое поступление денег на свой счет, охотнее предоставляют клиенту возможность дополнительного кредитования, доверяя ему, как образцовому плательщику.
Для того чтобы рассчитать схему внесения средств и отследить, когда кредит будет полностью погашен, подойдет более развернутая модель калькулятора. В программе Эксель такой сервис имеется. Это расширенная таблица, содержащая дополнительные графы, позволяющие рассчитать взносы с точностью не до месяца, как в стандартной модели, а за каждый день. Принцип получения результата следующий:
- первые два столбца таблицы зеленого цвета – это дата и сумма внесения. После перечисления платежа и добавления его размера в строке, расположенной под ней, появится отрицательное число – это и будет остаточная величина долга;
- следующие два столбца – тело кредита и проценты. Прописанные в них формулы, позволят наглядно увидеть, какая долговая часть пойдет на погашения самого займа, а какая покроет проценты;
- последний, пятый столбец – общий долг к выплате. Для удобства пользователя он окрашен в желтый цвет.
- Расчет полной стоимости ссуды с помощью Эксель
Порядок определения полной суммы долга – исходя из тела ссуды и переплаты по процентам, регламентирован действующим законодательством о потребительском кредитовании населения. Согласно нормативному документу при проведении исчислений используется формула:
ПСК = i х ЧБП х 100, где
- i – переплата по процентной ставке за базовый срок;
- ЧПБ – количество таких сроков в одном календарном году.
Базовый период рассчитывается следующим образом. По закону, это стандартные временные рамки, чаще всего фигурирующие в типовых банковских графиках погашения задолженностей. Большинство отечественных финансовых учреждений применяют 28 дневный срок. Таким образом, базовый период равен 28. Следовательно, ЧПБ = 365 (количество дней в году) : 28. Получаем 13. Примечательно, что этот показатель так же, в большинстве случаев, стандартный.
Теперь, имея на руках все необходимые цифры, можно подставлять их в формулу. Например, клиент берет в долг 400 000 рублей, сроком на 2 года (24 месяца), под ставку 22% годовых.
Выходит:
ПСК = 22/13 х 13 х 100 = 22%
Получаем, что при отсутствии дополнительных или скрытых комиссионных начислений, нередко устанавливаемых банками, ПСК полностью совпала с величиной ставки.
Чтобы узнать, сколько же всего необходимо отдать компании, чтобы кредит считался закрытым, нужно к размеру взятых денежных средств прибавить ПСК – в нашем случае, это 22%.
В этом видео подробно рассказано о том, как правильно подсчитать проценты по кредиту в таблице Эксель:
Таким образом, становится понятно, что пользоваться кредитным калькулятором в программе Excel достаточно просто, а преимуществ – масса. Основное из них – достоверность полученных данных, тогда как многие банки грешат тем, что калькуляторы, которые они предлагают на своих сайтах, содержат заведомо скрытые комиссии. Они автоматически увеличивают общую переплату и величину текущих взносов.
Источник
