Кредитный калькулятор с нерегулярными выплатами excel. Как создать кредитный калькулятор в Excel

Кредитный калькулятор – это профессиональный кредитный калькулятор предназначенный для расчета потребительских кредитов и ипотеки. Особенность калькулятора – расчет займа с досрочными погашениями
Приложение позволяет рассчитать следующие виды кредитов:
Если вам нужна помощь, напишите ваш вопрос на http://credcons.ru/
Мы обязательно дадим ответ
Приложение позволяет рассчитать:

1. Ипотеку на первичном и вторичном рынке жилья
На первичном рынке есть возможность поменять ставку во время кредита через досрочные погашения, если ставка до получения прав собственности одна, а после меняется.
2. Потребительский кредит
3. Кредит с погашением в виде материнского капитала
4. Автокредит
5. Военную ипотеку
6. Ипотеку на землю и на частный дом, а также займ на гараж
7. Ипотеку на комнату
8. Кредит на отдых и на учебу.
9. Кредит на свадьбу или иное мероприятие

Основные возможности кредитного калькулятора
1. Расчет кредита(тип платежей – дифференцированные и аннуитет)
2. Возможность отправить онлайн заявку на кредит, выбрав самое выгодное предложение банка
3. Расчет графика платежей по кредиту с учетом досрочных взносов. Отображение выходных дней, помеченных звездочкой на графике.
4. Учет комиссий и страховки при вычислении общей переплаты по кредиту.
5. Учет досрочных погашений при построении таблицы платежей по займу.
6. Расчет графика, когда первый платеж идет только в счет уплаты процентов по займу
7. Сохранение ваших расчетов в память телефона и возможность загрузки
8. Экспорт расчетов в html файл при отправке по электронной почте.

Правильность расчетов калькулятора кредита проверена на ипотечных и потребительских займах самых крупных банков России(кредитный калькулятор ВТБ24, Кредитный калькулятор Сбербанка). Приложение разрабатывалось первоначально для собственных нужд. Но благодаря востребованности приложения среди обычных людей принято решение выпустить версию для телефонов на Андроид.

Процесс работы с приложением достаточно прост:
1. Вводим данные по кредиту – ставку, срок, даты, период в месяцах
2. Нажимаем рассчитать – происходит расчет и сохранение кредита в базе
3. Чтобы сбросить кредит – нужно выбрать сброс в меню
4. Для добавления досрочных платежей нужно перейти на вкладку досрочных платежей и в меню выбрать добавить. Возможно добавление досрочных платежей с уменьшением срока займа, платежей в погашение долга, а также комиссий, страховки и изменения процентной ставки по займу.
5. Для загрузки сохраненного кредита нужно в меню на первой вкладке выбрать загрузить и нажать по синей кнопке рядом с нужным кредитом. Займ загрузится и рассчитается.
6. Изменение языка приложения производится в настройках – нужно нажать кнопку настройки.
Здесь же можно задать режим загрузки последнего кредита – при старте всегда будет загружаться последний рассчитанный займ.
7. Экспорт кредита с графиком платежей производится через меню на вкладке "График"
Экспорт происходит в формате html как кредита, так и досрочных платежей.

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

Необходимые данные для расчета графика платежей по кредиту в Excel

Основные вопросы, связанные с расчетом кредита, заключаются, как правило, в следующем:

• какая величина кредита может быть получена, если известен примерный размер платежа;
• каким будет платеж, учитывая предварительно известную сумму займа.

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

Величина процентной ставки зависит от многих параметров: от кредитной политики конкретного банка, срока займа, вида программы кредитования, обеспечения и т.д.

Срок кредита, как правило, может выбираться заемщиком. Обычно он является кратным 12 месяцам и не превышает 7 лет (по ипотеке – до 30 лет).

Формула расчета ежемесячного платежа по кредиту в Excel

1. Самостоятельный расчет ежемесячного платежа по кредиту в Excel носит информативный характер, и может незначительно отличаться от данных банка. Это связано с несколькими причинами: различный учет количества дней в периоде, различающийся подход при округлении значений и т.д.
2. При выборе между аннуитетным и дифференцированным платежом необходимо обратить внимание, что сумма переплаты по аннуитету всегда будет выше. Так, согласно приведенным примерам, при аннуитетных платежах общая сумма переплаты составляет 11 161 р., при дифференцированных – 10 833 р.
3. Для расчетов целесообразно использовать заявленную ставку того банка, в котором планируется взять кредит , либо ее среднерыночное значение.

Кредитный Калькулятор – личный финансовый советник, созданный для Вашего комфорта. Мобильный кредитный специалист с легкостью осуществит предварительный анализ для осознания рисков кредитования. Планируйте финансовое будущее вместе с утилитой, и Вас будет трудно застать врасплох в любой ситуации.

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

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

Возможности Кредитного Калькулятора на Android:

• несколько поддерживаемых языков;
• включение и расчет разных видов кредитования;
• простая настройка и легкий ввод исходных данных;
• быстрый расчет;
• привязка платежей в календарю с поддержкой расписания ежемесячного погашения кредита.

Скачать Кредитный калькулятор для Андроид без смс и без регистрации.

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).

Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.

Как рассчитать платежи по кредиту в Excel

Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:

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

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

Расчет аннуитетных платежей по кредиту в Excel

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

А = К * S

• А – сумма платежа по кредиту;
• К – коэффициент аннуитетного платежа;
• S – величина займа.

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• где i – процентная ставка за месяц, результат деления годовой ставки на 12;
• n – срок кредита в месяцах.

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

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



Расчет платежей в 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 дней.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

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

Первый кредитный калькулятор в Excel можно скачать по .
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.
Для расчета досрочного погашения можно также воспользоваться калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel.

На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Найти и скачать мобильные версии калькуляторов можно с .

Достоинства данного калькулятора:

1. Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
2. Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
3. Построение и расчет графика платежей в виде таблицы в Excel. Таблица графика платежей может также редактироваться
4. При расчете учитывается високосный и невисокосный год. За счет этого сумма начисленных процентов практически совпадает с значениями, рассчитываемыми ВТБ24 и Сбербанком
5. Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
6. Калькулятор можно редактировать под себя, задавая разные варианты расчета.

Недостатки калькулятора

1. Нет учета возможное изменение процентной ставки во время выплат кредита
2. Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
3. Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
4. Не рассчитывается вариант — первый платеж только проценты. В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
5. Расчет производится для процентой ставки с 2мя знаками после запятой.

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

Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.