Самый надежный способ удвоить свои деньги — это сложить их пополам и сунуть в бумажник
Фрэнк Хаббард



 

 

Отзывы о банках


InfoBank.by – Все банки Беларуси  >  Как вывести банк на чистую воду за пару минут? Excel и несколько нажатий на клавиши

Как вывести банк на чистую воду за пару минут? Excel и несколько нажатий на клавиши

Размер шрифта:    Уменьшить шрифт  Восстановить исходный рзмер  Увеличить шрифт 
6460

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

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

Чтобы самостоятельно создать кредитный калькулятор, заходим в программу и вводим исходные данные. В данном случае, это:
  • сумма кредита — 1 тыс рублей
  • срок кредита — 12 месяцев
  • процентная ставка — 15,49%
— а также создаем «шапку» для будущего калькулятора.
 
Калькулятор для кредита с дифференцированным графиком платежей

Для начала пронумеруем месяцы, в течение которых будем выплачивать кредит. Если ваш кредит не долгосрочный,  это можно сделать вручную, но представьте, что вы оформляете средства на 10 лет — это 120 месяцев. В этом случае, лучше воспользоваться автозаполнением.

Для этого вводим цифры 1 и 2, выделяем их, затем наводим курсор в правый нижний угол выделенной области — должен появиться значок «+». Когда он появился, нажимаем левую кнопку мыши и тащим вниз, пока не увидим нужную цифру. В нашем случае — 12.
 
Калькулятор для кредита с дифференцированным графиком платежей 

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

Если вы не знакомы с вводом формул в Excel:
  • всегда начинаем написание формулы со знака «=»
  • деление обозначается знаком «/»
  • умножение обозначается знаком «*»
Совет – когда рассчитываете какой-то показатель, указывайте адрес ячейки, а не просто вводите цифры

Например, чтобы посчитать сумму основного долга, которую мы будем гасить ежемесячно, можно задать формулу:
 
= 1000 / 12

Или

= A2/B2
 
Результат в обоих случаях будет одинаковый. Однако второй вариант гораздо более удобный. Если вы захотите рассчитать платежи на другую сумму кредита, вам достаточно изменить ее в ячейке «А2», и результат будет пересчитан. А вот если вы вводите данные цифрами, каждый раз, когда вы захотите что-то поменять, придется менять данные во всех формулах. Это правило советуем применять не только к сумме, но и к сроку, процентам и другим показателям.
 
Калькулятор для кредита с дифференцированным графиком платежей

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

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

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

Совет — рассчитайте данные для первых 3 месяцев, дальше можно воспользоваться автозаполнением. В ячейку «В7» мы внесли сумму кредита, формулы там нет, поэтому автозаполнение начинайте со 2 месяца.
 
Калькулятор для кредита с дифференцированным графиком платежей

Теперь считать будем «проценты за пользование кредитом».

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

В принципе для всех 12 периодов эта формула остается неизменной, меняется в ней только сумма остатка (он уменьшается).

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

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

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

Чтобы узнать, сколько именно вы переплачиваете по кредиту, необходимо вычислить сумму всех выплаченных процентов и комиссий. В этом случае удобнее всего пользоваться функцией «СУММ», которая складывает значения в выделенном фрагменте.
 
Калькулятор для кредита с дифференцированным графиком платежей

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

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

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

Ежемесячный платеж считается по формуле:
 
X – ежемесячный платеж
S – сумма кредита
K – коэффициент аннуитета, который рассчитывается по формуле:
 

P– 1/12 процентной ставки
N – срок кредита
 
Попытаемся максимально упростить расчеты и для начала узнаем, чему же будет равняться значение P.
 
Калькулятор для кредита с аннуитетным графиком платежей
 
Затем вычислим коэффициент аннуитета. Для возведения значения 1+Рв n-ую степень используем функцию «СТЕПЕНЬ».
 
Калькулятор для кредита с аннуитетным графиком платежей
 
Чтобы узнать сумму ежемесячного платежа, умножаем сумму кредита на полученный коэффициент аннуитета.
 
Калькулятор для кредита с аннуитетным графиком платежей
 
Дальше действуем следующим образом:
  • вносим комиссии, если их нет, проставляем в колонке нули
  • заполняем первую строчку в колонке «остаток основного долга» так же, как и в случае с дифференцированными платежами, эта сумма равна сумме кредита
Колонку «ежемесячный платеж» заполняем следующим образом — к сумме платежа, которую мы рассчитали по формуле, прибавляем значение комиссии, которая начисляется в соответствующем месяце.
 
Калькулятор для кредита с аннуитетным графиком платежей
 
После этого начинаем считать, какую сумму составят проценты за пользование кредитом в течение 1 месяца. Для этого остаток основного долга в  1 месяце умножаем на процентную ставку и делим на 12. Или же можно поступить проще и использовать значение коэффициента Р.
 
Калькулятор для кредита с аннуитетным графиком платежей
 
Сейчас этот столбец можно заполнить даже не имея данных по остаткам основного долга.
 
В первый месяц проценты рассчитываются по формуле  – «B7»*«H6»

Во второй — «B8»*«H6»

В третий — «B9»*«H6»
 
 — и так далее. Меняются только значения ячеек в столбце B.
 
Сначала значения будут равняться нулю, но по мере заполнения остальной таблицы, программа их подсчитает.
 
Теперь переходим к вычислению значений из столбца «погашение основного долга». Чтобы произвести расчет, вам нужно от суммы платежа отнять сумму комиссий и процентов за пользование кредитом.
 
Калькулятор для кредита с аннуитетным графиком платежей
 
После того, как вы рассчитали значение для первого месяца, можно использовать автозаполнение. Опять же значения пока будут не актуальные, но после окончательного заполнения все  станет на свои места.

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

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

Затем складываем их между собой и получаем итоговую переплату.
 
Калькулятор для кредита с аннуитетным графиком платежей
 
А теперь обратите внимание — в чем прелесть того, что мы задавали адреса ячеек, а не вводили цифры. Например, мы решили рассмотреть другой кредит, ставка по нему 16% и есть комиссия 25 рублей за оформление договора поручительства. Стоит внести изменения в 2 ячейки, как программа все сама рассчитает, исходя из новых данных, и вот мы уже видим новую сумму платежа и переплаты.
 
Калькулятор для кредита с аннуитетным графиком платежей

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

Скачать готовый кредитный калькулятор

 


Источник: www.infobank.by

Понравилось? Отправь друзьям!


Оставить комментарий
  
Комментариев нет
Другие новости