Excel — это универсальная программа, которая позволяет проводить вычисления любой степени сложности. Очень часто она используется кредиторами и заёмщиками для вычисления суммы выплат по кредиту, процентов и остатка. В этом случае наиболее целесообразным будет составление графика платежей. Образец в Excel станет замечательным помощником в этом деле.
Преимущества и недостатки использования Excel
Перед тем как использовать программное обеспечение для составления платёжного графика, необходимо подробно изучить его преимущества и недостатки. Благодаря им можно значительно ускорить процесс и избежать большинства простых ошибок. Основные достоинства использования Excel:
- Способность выполнения дополнительных функций. Помимо платёжного календаря и собственного реестра программа может составлять бюджеты и лимитировать платежи относительно них.
- Возможность организовать обмен информацией с платёжной подсистемой 1С. Благодаря этому преимуществу можно компенсировать необходимость повторного ввода некоторых данных.
- Использование в расчётах простых и понятных формул. Это помогает новичкам быстрее разобраться в процессе составления графика платежей и устранить некоторые ошибки.
Несмотря на эти важные достоинства, у использования Excel есть и несколько недостатков. Их нужно учитывать перед началом проведения расчётов и составлением платёжного графика, в противном случае можно столкнуться с различными трудностями, которые осложнят процесс и увеличат вероятность получения недостоверных данных. Отрицательные характеристики проведения расчётов Excel:
- Невозможность контроля ссылочной ценности. Программа не способна противостоять удалению каких-либо данных из ячеек, даже если на них установлены макросы или специальная защита.
- Трудности с многопользовательским режимом работы. В Excel довольно трудно организовать одновременную работу большого количества людей, так как программа способна функционировать только в единичном режиме. Выходом из такой ситуации будет создание специальной базы данных.
- Конфиденциальность информации и ограничение доступа. Для специалиста не составит труда взломать установленный пароль, поэтому Excel редко используется в тех случаях, когда нужно составить платёжный график для большого количества людей. Из-за отсутствия необходимой защиты доступ к файлу с информацией должен быть строго ограничен.
- Повторный ввод данных. Excel, в отличие от 1С, не способен обмениваться информацией с клиентом банка. Из-за этого необходимо будет дорабатывать используемую базу данных и составлять платёжный календарь в ней.
- Ограничение размера файла. Для ведения некоторых расчётов Excel подойдёт идеально, но для большого количества данных возможностей программы будет недостаточно.
Образцы расчётов по кредиту
Расчёт графика платежей по кредиту в Excel предусматривает необходимость вычисления двух отдельных выплат, процентов и общей стоимости. Кроме этого, нужно учитывать вариант образования задолженностей или досрочного погашения займа.
Аннуитетные платежи
Они представляют собой выплаты по кредиту, когда все платежи являются равными между собой. Это касается общей суммы основной задолженности и предусмотренных процентов. Такой вариант считается наиболее популярным, поэтому большинство банков предпочитает вести расчёт ипотечных и потребительских кредитов именно по нему.
Главное преимущество аннуитетного платежа — необходимость ежемесячно погашать одну и ту же сумму. Это позволяет людям, взявшим деньги у банка в долг на длительный период, не бояться, что сумма увеличится в момент погашения кредита. Среди недостатков следует выделить тот факт, что при большом размере долга заёмщику поначалу нужно будет выплачивать проценты, а не саму задолженность. При этом переплата будет значительно больше, чем в случае проведения других платежей. Другим минусом является то, что совершить платёж по кредиту можно только в строго определённую дату.
Размер ежемесячного аннуитетного платежа определяется по стандартной формуле А=K*S, где S — сумма кредита, а K — коэффициент аннуитетного платежа. Последний параметр вычисляется с учетом продолжительности срока взятого кредита и месячной процентной ставки (1/12 от годовой). Порядок введения данных в Excel:
- В программе создаётся отдельный лист, ему присваивается соответствующее название.
- Создаётся таблица из трех строк и двух столбцов.
- В неё вводятся входные данные (размер кредита, процентная ставка и срок в месяцах).
- После этого формируется пустой платёжный график. Он должен содержать 2 столбца и столько строк, на сколько месяцев взят заём.
- Выделяется первая пустая ячейка в правом столбце.
- В командной строке выбирается раздел «Формулы» и подраздел «Вставить функцию».
- В открывшемся окне выбирается ПЛТ (специальная функция, которая позволяет рассчитать аннуитетные платежи).
- При помощи мышки выделяются числовые значения. Их следует брать из первой таблицы.
- После этого нажимается Enter, и выводится расчётное значение.
В итоге вся таблица заполнится данными с отрицательным знаком, соответствующими сумме, которую нужно ежемесячно платить банку.
Дифференцированные выплаты
Эти платежи являются способом оплаты кредита, при котором вносятся равные суммы по основной задолженности, а проценты начисляются на остаток. Этот вариант не такой популярный, как аннуитетный, применяется он только некоторыми крупными банками. Обусловлено это тем, что размер переплаты, которую вносит заёмщик, значительно меньше.
Другим важным достоинством дифференцированного способа является то, что в заключительные месяцы гашения задолженности переплата будет минимальной. Кроме этого, заёмщик имеет право досрочно выплатить всю сумму кредита и избежать начисления определённой части процентов. Главный недостаток — необходимость переплачивать большие суммы на начальном этапе гашения задолженности.
Расчёт размера дифференцированной выплаты осуществляется по общей формуле ДП=ОСЗ/(ПС*ОСЗ/ПП). В ней присутствуют такие условные обозначения:
- ДП — величина платежа;
- ПС — месячная ставка в процентах, которая равна двенадцатой части годовой;
- ПП — количество месяцев, оставшихся до полного погашения кредита;
- ОСЗ — остаток ссудной задолженности.
- ДП — величина платежа;
- ПС — месячная ставка в процентах, которая равна двенадцатой части годовой;
- ПП — количество месяцев, оставшихся до полного погашения кредита;
- ОСЗ — остаток ссудной задолженности.
Платёжный график можно составить при помощи специального кредитного калькулятора или программы Excel. Последний вариант более сложный, но позволяет получить точные данные. Порядок проведения расчётов в Excel:
- Первым делом создаётся отдельный лист, название которого выбирает сам заёмщик или кредитор.
- Создаётся небольшая таблица из двух столбиков и трех строчек.
- В неё вводятся 3 числовых значения (срок в месяцах, размер ставки в процентах, величина кредита).
- На следующем этапе составляется незаполненный график погашения задолженности. Для этого формируется таблица, состоящая из 5 столбиков и нескольких строчек (количество должно соответствовать числу месяцев).
- Крайний левый столбец отводится для номера месяца.
- Во втором слева определяется остаток задолженности по кредиту. Для этого выделяется верхняя пустая ячейка, и в неё вводится значение, равное сумме взятого кредита.
- Во всех остальных строках ставится формула =ЕСЛИ (D10>$B$4;0;E9-G9). В ней D10 — это номер месяца, B4 — общее количество периодов, на которые выдан заём, G9 — размер основного долга в предыдущем месяце, E9 — остаток по кредиту на прошедший период.
- Средний столбец отведён для выплаты процентов. В верхнюю ячейку вводится формула, которая предусматривает умножение двенадцатой части годовой процентной ставки ($B$3/12) на остаток по кредиту (E9).
- Предпоследний столбик отображает размер выплат основного долга. Во все его ячейки поочерёдно вводится формула =ЕСЛИ (D9<=$B$4;$B$2/$B$4;0).
- Завершается заполнение графика суммой итогового платежа. Она вычисляется путём складывания значений, находящихся в двух соседних столбцах.
- Полученные результаты распространяются на всю таблицу.
Процентная ставка
Чтобы избежать переплат, необходимо предварительно вычислить эффективную ставку в процентах. Эту работу следует проводить с учётом информации о кредите, который предлагает тот или иной банк. Пример заполнения страницы в Excel:
- В предварительно созданный и озаглавленный лист вводятся исходные данные (общая сумма, процентная ставка, ежемесячная комиссия, срок кредита, способ погашения).
- Все эти сведения оформляются в виде таблицы, которая содержит 5 строк и 2 столбика.
- После этого выполняются те же действия, что и для аннуитетных платежей. Итогом вычислений становится определение ежемесячной процентной ставки и ПЛТ.
- На следующем этапе работы оформляется ещё одна таблица. Она должна состоять из 7 столбцов и количества строк, равного числу платёжных периодов.
- Первые 4 столбца заполняются одинаково для всех месяцев (номер периода, сумма аннуитетного платежа, комиссия, размер выплаты по кредиту).
- Пятый столбик предназначен для определения суммы основного долга. Для этого от размера аннуитетной выплаты отнимается комиссия.
- Предпоследняя колонка отводится для суммы процентов, которая вычисляется путём умножения месячной процентной ставки на остаток задолженности.
- В самом конце заполняется остаток основного долга. Он будет равен числовой разнице между остатком от предыдущего периода и размером основного долга.
- Используя полученные значения, вычисляется ставка (процентная). Её получают с помощью деления суммы переплат на размер кредита и умножения полученного числа на 100.
Полная стоимость
Полная стоимость кредита должна рассчитываться в процентах по формуле ПСК=ЧБП*i*100, где ЧБП — число базовых периодов за один календарный год, i — ставка в процентах. Этот показатель можно также рассчитать при помощи программы MS Excel. Для этого нужно выполнить следующие действия:
- На новом листе формируется таблица, которая состоит из двух колонок и четырех строк.
- Во втором столбце указывают сумму займа, ставку в процентах, срок погашения и единовременную комиссию.
- После этого составляется платёжный график по одной из схем (аннуитетной или дифференцированной).
- В отдельную таблицу выводятся расчётные данные (ставка в процентах, число базовых периодов за один календарный год, базовый период и полная стоимость кредита).
- Для определения БП (базового периода) необходимо взять стандартный интервал. В большинстве случаев он составляет 28 дней.
- Затем определяется ЧБП (число базовых периодов за год). Для этого количество дней в году делится на БП.
- Затем определяется i (ставка в процентах за базовый период) путём деления исходной процентной ставки на ЧБП. Для этого в нужной ячейке пишется формула =B3/B8.
- На основании полученного результата вычисляется ПСК (=B9*B8).
Составление графика платежей в Excel — это трудоёмкое мероприятие, которое требует наличия определённых знаний и навыков проведения подобной работы. При правильном подходе к делу и точном соблюдении всей последовательности операций можно значительно упростить задачу и избежать непредвиденных ошибок.
