Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).
Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.
Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:
Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.
Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:
А = К * S
Формула коэффициента аннуитета:
К = (i * (1 + i)^n) / ((1+i)^n-1)
В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:
Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.к. мы эти деньги будем отдавать банку, терять.
Дифференцированный способ оплаты предполагает, что:
Формула расчета дифференцированного платежа:
ДП = ОСЗ / (ПП + ОСЗ * ПС)
Составим график погашения предыдущего кредита по дифференцированной схеме.
Входные данные те же:
Составим график погашения займа:
Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.
Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).
Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9
Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.
Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.
Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:
Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.
Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:
Рассчитаем ежемесячную процентную ставку и платежи по кредиту:
Заполним таблицу вида:
Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.
Сумма основного долга = аннуитетный платеж – проценты.
Сумма процентов = остаток долга * месячную процентную ставку.
Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.
Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:
Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.
Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:
Возьмем для примера следующие данные по кредиту:
Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).
Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.
Теперь можно найти процентную ставку базового периода:
У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8
Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.
ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.
Таким образом, для расчета аннуитетных платежей по кредиту используется простейшая функция ПЛТ. Как видите, дифференцированный способ погашения несколько сложнее.
Оформить онлайн заявку на кредит можно на сайте практически любого банка. Удобство для клиента здесь очевидно - заполнение заявки на сайте без визита в офис экономит ваше время. Банкам это также выгодно, так как это экономит время сотрудников. Собрать всю необходимую информацию о потенциальном заемщике и принять решение по одобрению кредита банк может без посещения клиентом офиса. Документы и справки можно предоставить в электронном виде. Личный визит будет необходим только для предоставления оригиналов документов и подписания договора.
Кредитный калькулятор с досрочным погашением предназначен для самостоятельного онлайн расчета параметров кредита, таких как сумма ежемесячного платежа и общей переплаты по кредиту на основании желаемой для заемщика суммы и срока кредита, а также процентной ставки. После выполнения расчета вы получите подробный график платежей, содержащий подробную информацию о каждом ежемесячном платеже, а именно: общая сумма платежа, какая часть этой суммы идет на погашение процентов, а какая на погашение основного долга, и остаток основного долга.
Использовать онлайн калькулятор для расчета кредита очень удобно. Можно осуществлять любые расчеты, не прибегая к помощи специалистов.
Процентная ставка - это стоимость кредита, которую предлагает банк. Каждый банк имеет свои программы кредитования населения и предлагает разные процентные ставки. Даже в одном банке процентная ставка может сильно отличаться при различных условиях. Она может зависеть от таких факторов, как возраст заемщика, его кредитная история, цель предоставления кредита, сумма кредита, наличие поручителей. Бывает, что банки предоставляют своим постоянным клиентам (например, владельцам дебетовых карт или лицам, которые уже пользовались кредитом) более выгодные условия кредитования, чем клиентам "с улицы". Актуальные процентные ставки банков вы можете узнать на сайтах этих банков.
Ещё один параметр, влияющий на результат расчета - вид платежа. Аннуитетный - это такой платеж, при котором сумма ежемесячного платежа остается неизменной на протяжении всего срока кредита. Дифференцированный - это тип платежа, при котором сумма ежемесячного платежа уменьшается к концу срока кредитования. Происходит это за счет того, что доля основного долга остается неизменной, а доля процентов с каждым месяцем уменьшается, так как уменьшается общая сумма долга. Наиболее распространён первый вид платежа - аннуитетный.
Кредитный калькулятор удобно применять с целью сравнения результатов при различных исходных значениях, таким образом, выбирая для себя оптимальные условия кредита. Возможность сохранять полученные результаты ещё больше упростит этот процесс.
При составлении графика погашения долга по кредиту может использоваться один из двух способов расчета ежемесячного (ежеквартального) платежа. Первый – аннуитетный метод, когда основной долг вместе с начисленными процентами разбивается на одинаковые суммы в течение всего периода. Второй – дифференцированный, который считается более выгодным, но менее удобным для заемщика . Что представляет собой дифференцированный платеж? Как рассчитывается и какие имеет преимущества и недостатки?
Дифференцированный платеж – вид погашения займа, при котором тело кредита уплачивается равными частями, а проценты насчитываются на остаток долга. Этот способ погашения займа подразумевает ежемесячное уменьшение основного долга, распределенного равномерно на весть период кредитования.
Такая схема достаточно проста, понятна и справедлива по отношению к клиенту. Однако заемщику необходимо быть готовым к большой финансовой нагрузке в первые месяцы погашения займа. При выборе дифференцированного кредита совокупный должен быть примерно на четверть выше, чем при аннуитетной схеме погашения. Не зря этот тип называют платежом для обеспеченных клиентов.
К основным преимуществам дифференцированного расчета платежей по кредиту относятся:
Недостатками такого способа погашения выступают:
Суть расчета кредита дифференцированным способом заключается в том, что он состоит из двух частей:
Если вы хоть раз в жизни брали кредит, то сталкивались с такими понятиями, как и аннуитетный платеж. Что это такое, в чем между ними разница и какова формула расчета? Узнать обо всем этом можно из статьи.
Суть любого займа состоит в том, что берется крупная сумма сразу, а возвращается в течение определенного времени по частям и с процентами. Периодичность погашения стандартно устанавливается один раз в месяц. Вот эту ежемесячную сумму называют платежом.
Считают его по-разному. Если сумма задолженности поделена на равные части, а процент начисляется в зависимости от того, что осталось, то такой способ погашения называют дифференцируемый платеж. Также можно встретить его под названием «классический способ», «коммерческий способ» или метод начисления процентов на остаток.
На первый взгляд кажется, что других вариантов погашения быть просто не может. Но это не так. Существуют аннуитетный и дифференцируемые платежи. Суть аннуитета в том, что погашение производится каждый месяц одинаковой суммой. Для того чтобы установить ее, выполняется расчет процентов за весь период пользования кредитом, полученная сумма прибавляется к основному долгу и делится на количество месяцев.
Суть недостатка заключена в самом названии явления - дифференцируемый платеж, то есть осуществляемый с учетом различий. Так как сумма основного долга делится на равные части, а проценты начисляются в зависимости от остатка, то первая выплата будет самой большой, а последняя – самой маленькой.
Следовательно, заемщику придется особенно сложно в первый год выплат, но с течением времени нагрузка на бюджет будет уменьшаться. В этом заключается недостаток для заемщика, но на самом деле нельзя однозначно сказать, что это плохо. Первый год выплат позволит воспитать дисциплину и наработать навык укладываться в рамки бюджета, что в дальнейшем может привести к образованию свободных денег, которые можно направлять на другие нужды.
Еще один недостаток такой схемы для заемщика заключается в том, что доход для оформления кредита с подобной системой выплат потребуется на четверть больше. Если речь идет о небольших займах, то это может быть и не принципиальным фактором, но если размышлять об ипотеке, то это может стать существенным препятствием к тому, чтобы получить одобрение.
Недостаток такой системы погашения для банка куда более серьезный. Кредитная организация получит меньше процентов. Почему это происходит, вы сможете понять чуть позже, когда мы перейдем к рассмотрению формулы и примеров расчета. Поэтому банки, особенно в случае ипотеки, предпочитают не предоставлять заемщикам выбора, кредитуя только на условиях аннуитетных платежей.
Есть и еще один подводный камень такой схемы выплат для заемщика, который на первый взгляд не кажется очевидным. В долгосрочном кредитовании часть ежемесячного платежа потихоньку съедается инфляцией. Ни для кого не тайна, что 10 тысяч рублей сейчас и 10 тысяч рублей пять лет назад - это совсем не одни и те же деньги. Хотим мы этого или нет, но стоимость денег во времени меняется, особенно стремителен этот процесс в период экономического кризиса.
Дифференцированная нагрузка с постепенным снижением выплаты не дает в полной мере сыграть этому фактору в пользу заемщика в отличие от ситуации, когда гашение происходит равными долями каждый месяц.
Как уже было упомянуто выше, такая система выплаты позволит меньше заплатить банку процентов. Чем больше размер займа, тем ощутимее будет экономия.
Это удобнее и с точки зрения досрочного погашения. Дифференцируемый платеж по кредиту позволяет быстрее гасить основной долг. В первые годы большая часть платежа будет уходить именно на погашение тела займа, в отличие от аннуитетных платежей, когда в первые годы платятся в основном лишь проценты, а основной долг сокращается минимально. Поэтому если возникнет ситуация, что вам нужно срочно закрыть задолженность после нескольких лет выплат, то остаток займа будет значительно ниже, чем в ситуации с аннуитетом.
Даже если вы будете досрочно гасить свой займ частично, это тоже позволит больше сэкономить на процентах, ведь они начисляются только на остаток.
Особенно хорошо преимущества будут ощущаться ближе к окончанию срока кредитования, так как платежи становятся по размеру практически неощутимыми для бюджета, даже в том случае, если вы взяли крупный займ на приобретение жилья.
Платеж включает две части, для расчета каждой из них используется своя формула. Первая часть – это сумма основного долга. Вторая часть – это проценты, которые нужно заплатить за расчетный месяц.
Итак, первая часть считается по формуле: B = S / N.
S – это сумма взятого займа;
N – это количество месяцев в периоде, на который взят кредит.
p = Sn * P / 12, где:
p – сумма начисленных процентов к уплате;
Sn – размер оставшейся заемной суммы;
P – годовая процентная ставка, которая установлена договором кредитования.
b – это размер ежемесячного платежа;
B – первая часть основного платежа;
p – сумма начисленных процентов.
Давайте посчитаем размер платежа для следующих условий. В банке взят 1 000 000 рублей на покупку квартиры. Срок кредитования составляет 10 лет, ставка – 14% годовых.
Чтобы сделать расчет дифференцированного платежа, вычисляем первую часть: 10 лет – это 120 месяцев. Значит, 1 000 000 /120 = 8 333,34 рубля.
Считаем проценты. За первый месяц это будет 1 000 000 * 14% / 12 = 11 666, 66. Платеж за первый месяц составит 20 000 рублей (8 333,34 + 11 666,66).
За второй месяц проценты будут начислены в сумме (1 000 000 – 8 333,34) *14% / 12 = 11 569,44. Платеж за второй месяц составит 19 902,78. Как видите, он уже стал меньше, хоть и ненамного.
Допустим, что заемщик уже год платит по кредиту, каким будет его платеж спустя это время? (1 000 000 – 100 000) * 14% / 12 = 10 500 – это сумма процентов, следовательно, 18 833,34 – это платеж. Разница с платежом первого месяца составляет 1 166,66 рубля. Финальный платеж будет 8 430,56 рубля. Как видите, разница с платежом первого месяца значительная.
Давайте теперь посчитаем сумму переплаты по кредиту на таких условиях. Для этого потребуется сложить между собой ежемесячные платежи за весь период кредитования и вычесть из них сумму займа. Вручную это делать очень долго. Для этого удобнее использовать калькулятор дифференцированных платежей. Его можно написать самостоятельно, используя для этого возможности Excel или рассчитать на специальном сайте, который называется «Ипотечный калькулятор».
В нашем случае сумма переплата по кредиту составит 705 833 рубля, то есть чуть более 70%.
Для того чтобы сравнить, что выгоднее самостоятельно, а не опираться на чьи-то голословные утверждения, нужно рассчитать размер аннуитетного платежа и переплату для аналогичных условий кредитования.
Формула расчета достаточно сложная: x = S * (Р / ((1+Р) n - 1);
Х – ежемесячный платеж,
S – сумма займа,
P – это 1/12 процентной ставки,
N – количество месяцев в периоде кредитования.
В нашем случае (1 000 000 рублей на 10 лет под 14% годовых) ежемесячный платеж составит 15 527 рублей, а сумма переплаты за весь период - 863 197 рублей, то есть больше 86%. Теперь вам должно быть понятно, почему дифференцированный платеж банк предлагать не любит, а в подавляющем большинстве случаев даже и возможности выбора у заемщика нет.
Из примера становится очевидно, по какой схеме нагрузка на семейный бюджет будет ниже, а также наглядно видно, что для дифференцированных платежей при той же сумме займа доход заемщика должен быть на 25% выше.
Закономерно, что для потребительского кредитования или даже автокредитования в большинстве случаев не имеет принципиального значения схема, по которой рассчитаны платежи. Но если речь идет о займе с целью приобретения жилья, то здесь уже очень важно какой будет ипотека, с дифференцированными платежами или аннуитетными.
Большинство кредитных организаций как на территории РФ, так и за границей не дают возможности выбора, предлагая лишь погашение равными долями. Это связано не только с желанием банков заработать как можно больше, но и с заемщиками. Далеко не всех устраивает необходимость платить больше в первые годы, также не каждый человек располагает таким размером дохода, чтобы получить одобрение банка, не всем нравится то, что невозможно точно спланировать свои расходы.
Поэтому даже опытные и хорошо информированные заемщики, взвесив все доводы, выбирают погашение равными частями. Эксперты пишут о том, что востребованность кредитных продуктов с дифференцируемыми платежами крайне низкая, а в условиях экономической нестабильности стала и еще ниже.
Поэтому список банков, предоставляющих выбор, крайне короток. В него входят: "Газпромбанк", "Нордеа Банк" и Петрокоммерц". Раньше такую возможность предоставлял еще и Сбербанк, теперь уже нет.
На данный момент в банках выдается достаточно большое количество кредитов.
Как известно существует два способа погашения кредита — с помощью аннуитетных или дифференцированных платежей.
Кредиты с дифференцированным типом платежей преобладают в Сбербанке. Сбербанк выдает кредиты с дифференцированным типом платежей. К таким кредитам относятся автокредиты и ипотека.
Расчет дифференцированного платежа достаточно прост. Рассмотрим формулы расчета дифференцированных платежей.
Формула для расчета дифференцированных платежей по кредиту:
Остаток основного долга берется на дату платежа.
Количество процентных периодов – срок (в месяцах) до окончания кредита.
Процентная ставка – годовая процентная ставка по кредиту.
Деление на 100 и на 12 производится для перевода ставки в проценты и вычисления процентов за один расчетный период (месяц).
Как видно формула состоит из 2х частей. Первая часть формулы — это константа всегда, т.е для любого месяца. Сумма долга деленная на число месяцев.
Эта часть платежа идет в погашение основного долга.
К примеру если брать кредит 120 тыс на 12 месяцев по ставке 10%, то постоянная часть дифференцированного платежа будет равна
120 000 /12 = 10 000 рублей.
Теперь рассчитаем переменную часть, которая зависит от суммы основного долга. Сумма основного долга также является переменной во времени и зависит от номера платежа.
Вот формула переменной части
Эта часть идет в погашение процентов.
Рассчитаем сумму в погашение процентов для первого платежа
120 000 * 10/(100 * 12) = 1000 рублей.
Первый платеж по дифференцированному займу таким образом будет равен постоянная часть + переменная часть =
10 тыс + 1 тыс = 11 тыс. рублей.
Рассчитаем второй платеж по кредиту.
Постоянная часть у нас получится та же, 10 тыс. рублей.
Рассчитаем сумму основного долга после первого платежа
120 тыс — 10 тыс = 110 тыс.
Исходя из новой суммы основного долга посчитаем проценты по кредиту
110 000 * 10/(100 * 12) = 916,67
Второй платеж по дифференцированному займу равен
10000 + 916.67 = 10916.67
В случае досрочного погашения дифференцированного кредита происходит уменьшение суммы основного долга.
Допустим между датами первого и второго платежа мы сделали досрочное погашение на 20 тыс. рублей.
Произведем рассчет нового платежа по кредиту после досрочного погашения.
Для начала рассчитаем сумму основного долга по кредиту на третий месяц
Сумма ОД = Остаток долга — Сумма в уменьшение ОД — Досрочное погашение
Подставим данные в формулу
Остаток долга= 110 тыс.
Сумма в погашение ОД = 10 тыс.
Досрочные погашения = 20 тыс.
110 000 — 10 000 — 20 000 = 80 000 рублей
Ставка по кредиту осталась той же, 10 процентов
А вот срок кредита изменится.
Новый срок = Число месяцев — Номер месяца досрочного погашения
Посчитаем новый срок
12 — 2 = 10 месяцев
Теперь рассчитаем новый кредит на сумму 80 тыс. на 10 месяцев под 10 процентов годовы
Сумма в погашение ОД = 80 000/10 =8 000 рублей
Рассчитаем платеж по процентам для кредита после досрочного платежа
Проценты по кредиту = 80 000 * 10/(100 * 12) =666,67
Теперь рассчитаем сумму нового платежа после досрочного погашения
Сумма диффер. платежа = 8000 + 666,67 = 8666.67
Если у займа несколько досрочных платежей, то расчет происходит аналогично. Берется сумма долга, отнимаются досрочные погашения, вычисляется новый платеж в уменьшение суммы основного долга.
В конце вычисляется процентный платеж и общий платеж.
На рисунке показан расчет кредита с несколькими досрочными погашениями.
Как видим, расчеты платежа после досрочного погашения сделанные вручную и с помощью кредитного калькулятора совпали.
Рассмотрим входные данные для расчета ипотеки
Входные данные для расчета кредита с дифференцированными платежами.
Пусть мы хотим взять ипотеку на 2 млн. рублей
Процентая ставка = 12.5%
Срок 10 лет или 120 месяцев
Дата выдачи — текущее число.
Вводим эти данные на экран расчет кредита, указываем дифференцированный тип платежей.
Получаем график платежей:
Из графика видно, что ежемесячный платеж все время снижается.
Проценты, которые платим банку, также уменьшаются.
А вот сумма в уплату долга постоянна.
