Автоматизировать процесс дифференцированного расчёта кредита можно при помощи кредитного калькулятора, разработанного в программе Microsoft Excel. В этой публикации мы вам расскажем и покажем, как это делается. Давайте приступим!
Где можно бесплатно скачать такой калькулятор
Не удивляйтесь, друзья, но вначале вам действительно надо скачать готовый калькулятор дифференцированных платежей, который мы разработали в Excel. Именно его мы и будем «разбирать на запчасти». Также, при желании, вы сможете его доработать под свои требования.
На примере этого калькулятора вы немного познакомитесь с программой Microsoft Excel, а также автоматизируете расчёт дифференцированных платежей по кредиту. Бесплатно скачать калькулятор можно перейдя по ссылке ниже:
Получилось? Вот и отлично! Приступаем к «разбору полётов»!
Разрабатываем калькулятор дифференцированных платежей в Excel
Прежде всего давайте разберемся, по какому принципу работает наш калькулятор. Откройте скачанный «экселевкий» файл. В верхнем левом углу страницы вы увидите две таблицы. Они называются: «Укажите данные для расчёта» и «Результаты расчёта». Также сверху над всеми столбцами нашей страницы Excel есть буквы A, B, C, D, E, F и т.д., а слева напротив строк – цифры 1, 2, 3, 4, 5, 6 и т. д. Именно эти буквы и цифры определяют координаты каждой ячейки таблицы.
Кликните левой кнопкой мыши по ячейке со значением «5958р.» , которое находится в результатах расчёта в строке «Переплата по кредиту». В нашем калькуляторе эта ячейка имеет координаты B8 . Вот вам картинка для наглядности:
На изображении данную ячейку мы обвели красной линией и обозначили цифрой один. Обратите внимание ещё вот на что. Когда вы кликаете по какой-либо ячейке в таблице Excel, то эта ячейка выделяется чёрной жирной рамкой, а её буквенно-цифровые координаты сверху и слева окрашиваются другим фоном. Например, на нашем изображении буква B сверху и цифра 8 слева изменили цвет фона с серо-голубого на желтоватый. Также в верхней строке формул, слева от которой есть кнопка «fx» (на рисунке она обведена красным и обозначена цифрой два) указано значение или формула, по которой выполняется расчёт данных для выделенной ячейки. В нашем примере для ячейки с координатой B8 выполняется расчёт по следующей формуле: =B7-B2 . В окне с координатой B7 указана общая сумма выплат по кредиту, которая в нашем примере равна 55 958 рублей , а B2 – это сам кредит, который равен 50 000 рублей . Выполнив простое математическое вычисление, наша программа занесла в ячейку B8 значение 5958 (55 958 – 50 000=5958).
Как видите, Microsoft Excel работает достаточно просто. По аналогичному принципу заданы формулы и значения для остальных ячеек нашего кредитного калькулятора дифференцированных платежей. Давайте рассмотрим, как они рассчитаны. Щёлкаем мышкой по изображению:
Итак, правее в оранжевой рамке вы видите график дифференцированных платежей по кредиту. Все значения в этой таблице рассчитываются автоматически по формулам, которые мы рассматривали . Именно эти формулы и прописаны в ячейках нашего калькулятора. Давайте их детально рассмотрим на примере первой строки графика погашения кредита.
- «Ежемесячный платёж» – это ежемесячный дифференцированный платёж по займу. Он состоит из двух частей: суммы, идущей на погашение процентов (ячейка F14 ), и суммы, идущей на погашение тела кредита (ячейка G14 ). Именно потому ежемесячный платёж в первой строке рассчитан по формуле: =F14+ G14 .
- «Погашение процентов» – здесь работает формула расчёта процентов по кредиту за данный период: остаток задолженности (в первом платеже он равен сумме кредита 50 000 руб. , вынесенную в ячейку H13 ) умножить на (она равна 22% и вынесена в ячейку A14 ) и разделить на 12 (мы вынесли это значение в ячейку B14 ). Собственно, эти условия и прописаны в формуле для ячейки F14 : =H13*A14/B14 . Кстати, вместо B14 можно просто указать фиксированную цифру – 12 .
- «Погашение тела кредита» – это фиксированное значение, которое не меняется на протяжении всего срока кредитования. Рассчитывается этот показатель очень просто: сумма кредита (ячейка B2 ) делится на общий срок кредитования (ячейка B4 ). В итоге для ячейки G14 получаем такую формулу: = B2/B4 .
- «Долг на конец месяца» – из суммы долга на конец предыдущего месяца (в первом платеже он у нас равен сумме кредита – 50 000 рублей и вынесен в ячейку H13 ) вычитаем выплату по телу кредита в текущем периоде (4167 рублей – ячейка G14 ). В результате, долг на конец месяца по первому платежу у нас равен 45 833 рубля (50 000 – 4167 = 45 833), что и записано в формуле для ячейки H14 : = H13- G14 .
Вот таким нехитрым способом разработан кредитный калькулятор дифференцированных платежей в Excel. Он рассчитан на кредиты сроком до 12 месяцев. При желании, вы можете его усовершенствовать и расширить данный диапазон до 24, 36 и более месяцев. В общем, теперь всё в ваших руках, друзья. Как говорится, мы вам дали удочку, а вы сами решайте, что с ней дальше делать.
Портал сайт – ваш надёжный информационный помощник в вопросах кредитования. Оставайтесь с нами!
Оформить онлайн заявку на кредит можно на сайте практически любого банка. Удобство для клиента здесь очевидно - заполнение заявки на сайте без визита в офис экономит ваше время. Банкам это также выгодно, так как это экономит время сотрудников. Собрать всю необходимую информацию о потенциальном заемщике и принять решение по одобрению кредита банк может без посещения клиентом офиса. Документы и справки можно предоставить в электронном виде. Личный визит будет необходим только для предоставления оригиналов документов и подписания договора.
Рассчитайте свой кредит самостоятельно
Кредитный калькулятор с досрочным погашением предназначен для самостоятельного онлайн расчета параметров кредита, таких как сумма ежемесячного платежа и общей переплаты по кредиту на основании желаемой для заемщика суммы и срока кредита, а также процентной ставки. После выполнения расчета вы получите подробный график платежей, содержащий подробную информацию о каждом ежемесячном платеже, а именно: общая сумма платежа, какая часть этой суммы идет на погашение процентов, а какая на погашение основного долга, и остаток основного долга.
Использовать онлайн калькулятор для расчета кредита очень удобно. Можно осуществлять любые расчеты, не прибегая к помощи специалистов.
Процентная ставка
Процентная ставка - это стоимость кредита, которую предлагает банк. Каждый банк имеет свои программы кредитования населения и предлагает разные процентные ставки. Даже в одном банке процентная ставка может сильно отличаться при различных условиях. Она может зависеть от таких факторов, как возраст заемщика, его кредитная история, цель предоставления кредита, сумма кредита, наличие поручителей. Бывает, что банки предоставляют своим постоянным клиентам (например, владельцам дебетовых карт или лицам, которые уже пользовались кредитом) более выгодные условия кредитования, чем клиентам "с улицы". Актуальные процентные ставки банков вы можете узнать на сайтах этих банков.
Тип ежемесячного платежа
Ещё один параметр, влияющий на результат расчета - вид платежа. Аннуитетный - это такой платеж, при котором сумма ежемесячного платежа остается неизменной на протяжении всего срока кредита. Дифференцированный - это тип платежа, при котором сумма ежемесячного платежа уменьшается к концу срока кредитования. Происходит это за счет того, что доля основного долга остается неизменной, а доля процентов с каждым месяцем уменьшается, так как уменьшается общая сумма долга. Наиболее распространён первый вид платежа - аннуитетный.
Кредитный калькулятор удобно применять с целью сравнения результатов при различных исходных значениях, таким образом, выбирая для себя оптимальные условия кредита. Возможность сохранять полученные результаты ещё больше упростит этот процесс.
Дифференцированный платеж — вариант ежемесячного платежа по кредиту, когда размер ежемесячного платежа по погашению кредита постепенно уменьшается к концу периода кредитования.
Ежемесячный платёж, при дифференцированной схеме погашения кредита, состоит из двух составляющих. Первая часть называется основным платежом , размер которого не изменяется на всём сроке кредитования. Основной платёж идет на погашения основного долга по кредиту. Вторая часть — убывающая, которая уменьшается к концу срока кредитования. Данная часть платежа идет на погашение процентов по кредиту.
При дифференцированной схеме погашения кредита, ежемесячный платеж рассчитывается как сумма основного платежа и проценты, начисляемые на оставшийся размер долга. Естественно, что оставшийся размер долга уменьшается к концу срока кредитования, отсюда и получается уменьшение размера ежемесячной выплаты.
Для расчёта размера основного платежа и начисленных процентов можно воспользоваться , на сайте , либо воспользоваться обычным калькулятором.
Расчёт дифференцированного платежа
Размер основного платежа вычисляется следующим образом: необходимо сумму кредита разделить на количество месяцев, за который планируется погашение кредита, полученное число и будет являться основным платежом.
Для расчета начисленных процентов нужно остаток кредита на указанный период умножить на годовую процентную ставку и всё это поделить на 12 (количество месяцев в году).
Чтобы рассчитать остаток задолженности на период, т.е. найти величину из приведённой выше формулы, необходимо размер основного платежа умножить на количество прошедших периодов и всё это вычесть из общей суммы платежа.
Пример расчёта графика выплат по дифференцированному кредиту
Для примера рассчитаем график платежей по кредиту в размере 100000 р. и годовой процентной ставкой 10%. Сроком погашения кредита возьмём 6 месяцев.
Определим размер основного платежа:
| 100000 / 6 = 16666,67 |
Определим размер выплаты за каждый месяц периода кредитовния:
Результат подсчётов по нашему примеру на сайте будет выглядеть так:
Что подтверждает правильность наших расчётов.
Как правило, банки используют два способа гашения кредита (аннуитет или дифференцированный). Именно их и рассчитывают различные кредитные калькуляторы. Они позволяют рассчитать ежемесячную сумму выплат на погашение кредита, определить максимальный размер займа, срок кредитования и оптимальную процентную ставку исходя из вашего суммарного семейного дохода. Варьируя различные суммы и сроки кредита, процентные ставки, данные типы платежей и подбирают кредит, а возможно и другой банк.
Чем отличается аннуитет от дифференцированного платежа
Аннуитет рассчитывается следующим образом. На протяжении всего срока погашения (графика) общий ежемесячный платеж (основной долг и проценты) будет постоянной величиной (см. схему 2). Используется формула сложных процентов, в соответствии с которой в начале пользования кредитом вы будете погашать небольшую часть основного долга и проценты на остаток.
К концу же срока пользования кредитом удельный вес основного долга в платеже будет увеличиваться, а проценты уменьшаться (оно и понятно – начисляются на остаток). Формула сложная, но никаких подвохов в ней нет, все правильно, лишних денег с вас не возьмут. Лично мне она даже больше нравится – можно взять кредит на больший срок (почему, расскажу в конце).
Ну а дифференцированный платеж – более простой. Сумма кредита (основного долга) просто делится на равные части на количество периодов (обычно месяцев). К каждой части ежемесячно прибавляются проценты исходя из остатка долга. Таким образом график гашения получается с убыванием – вначале платишь много, потом все меньше, а к концу срока уже почти не замечаешь кредитную нагрузку (см. схему 1).
Кстати, пользуясь кредитным калькулятором, следует иметь в виду, что при его использовании не учитываются возможные дополнительные платежи связанные с кредитом. Это могут быть страховка, комиссии за ведение ссудного счета (что уже незаконно), комиссии за рассмотрение и выдачу, РКО и пр.), которые могут различаться в каждом конкретном случае.
Я лично при выборе кредита стараюсь выбирать следующие условия (возможность) его погашения:
- График с аннуитетными платежами. Ежемесячная сумма платежа меньше, чем при дифференцированном варианте, не так давит на семейный бюджет. Срок по кредиту увеличивается, но это не беда, если (см. п.2)…
- Возможность досрочного гашения кредита. Если появились «лишние» деньги возможно пустить их на погашение. Тут же пересчитываются в меньшую сторону аннуитетные платежи, строится новый график со старым сроком окончательного погашения.
Мне кажется, что это самая оптимальная долговая нагрузка.
Если вы хоть раз в жизни брали кредит, то сталкивались с такими понятиями, как и аннуитетный платеж. Что это такое, в чем между ними разница и какова формула расчета? Узнать обо всем этом можно из статьи.
Что такое дифференцируемый платеж?
Суть любого займа состоит в том, что берется крупная сумма сразу, а возвращается в течение определенного времени по частям и с процентами. Периодичность погашения стандартно устанавливается один раз в месяц. Вот эту ежемесячную сумму называют платежом.
Считают его по-разному. Если сумма задолженности поделена на равные части, а процент начисляется в зависимости от того, что осталось, то такой способ погашения называют дифференцируемый платеж. Также можно встретить его под названием «классический способ», «коммерческий способ» или метод начисления процентов на остаток.
На первый взгляд кажется, что других вариантов погашения быть просто не может. Но это не так. Существуют аннуитетный и дифференцируемые платежи. Суть аннуитета в том, что погашение производится каждый месяц одинаковой суммой. Для того чтобы установить ее, выполняется расчет процентов за весь период пользования кредитом, полученная сумма прибавляется к основному долгу и делится на количество месяцев.
Недостатки дифференцируемых платежей
Суть недостатка заключена в самом названии явления - дифференцируемый платеж, то есть осуществляемый с учетом различий. Так как сумма основного долга делится на равные части, а проценты начисляются в зависимости от остатка, то первая выплата будет самой большой, а последняя – самой маленькой.
Следовательно, заемщику придется особенно сложно в первый год выплат, но с течением времени нагрузка на бюджет будет уменьшаться. В этом заключается недостаток для заемщика, но на самом деле нельзя однозначно сказать, что это плохо. Первый год выплат позволит воспитать дисциплину и наработать навык укладываться в рамки бюджета, что в дальнейшем может привести к образованию свободных денег, которые можно направлять на другие нужды.
Еще один недостаток такой схемы для заемщика заключается в том, что доход для оформления кредита с подобной системой выплат потребуется на четверть больше. Если речь идет о небольших займах, то это может быть и не принципиальным фактором, но если размышлять об ипотеке, то это может стать существенным препятствием к тому, чтобы получить одобрение.
Недостаток такой системы погашения для банка куда более серьезный. Кредитная организация получит меньше процентов. Почему это происходит, вы сможете понять чуть позже, когда мы перейдем к рассмотрению формулы и примеров расчета. Поэтому банки, особенно в случае ипотеки, предпочитают не предоставлять заемщикам выбора, кредитуя только на условиях аннуитетных платежей.
Роль инфляции
Есть и еще один подводный камень такой схемы выплат для заемщика, который на первый взгляд не кажется очевидным. В долгосрочном кредитовании часть ежемесячного платежа потихоньку съедается инфляцией. Ни для кого не тайна, что 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% выше.
Ипотека
Закономерно, что для потребительского кредитования или даже автокредитования в большинстве случаев не имеет принципиального значения схема, по которой рассчитаны платежи. Но если речь идет о займе с целью приобретения жилья, то здесь уже очень важно какой будет ипотека, с дифференцированными платежами или аннуитетными.
Большинство кредитных организаций как на территории РФ, так и за границей не дают возможности выбора, предлагая лишь погашение равными долями. Это связано не только с желанием банков заработать как можно больше, но и с заемщиками. Далеко не всех устраивает необходимость платить больше в первые годы, также не каждый человек располагает таким размером дохода, чтобы получить одобрение банка, не всем нравится то, что невозможно точно спланировать свои расходы.
Поэтому даже опытные и хорошо информированные заемщики, взвесив все доводы, выбирают погашение равными частями. Эксперты пишут о том, что востребованность кредитных продуктов с дифференцируемыми платежами крайне низкая, а в условиях экономической нестабильности стала и еще ниже.
Поэтому список банков, предоставляющих выбор, крайне короток. В него входят: "Газпромбанк", "Нордеа Банк" и Петрокоммерц". Раньше такую возможность предоставлял еще и Сбербанк, теперь уже нет.
