Формирование доходной части бюджета Из каких частей состоят доходы бюджета
Доходы бюджета – денежные средства, поступающие в безвозмездном и безвозвратном порядке в соответствии с...
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. Достаточно выставить для ячейки с результатом процентный формат.
ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.
Таким образом, для расчета аннуитетных платежей по кредиту используется простейшая функция ПЛТ. Как видите, дифференцированный способ погашения несколько сложнее.
Первый кредитный калькулятор в Excel
можно скачать по .
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.
Для расчета досрочного погашения можно также воспользоваться калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel.
На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Найти и скачать мобильные версии калькуляторов можно с .
Всех выше названных недостатков лишен . В целом недостатки не сильно критичны и они присущи любому кредитному калькулятору онлайн.
Другой
кредитный калькулятор в Excel можно скачать по . Данный кредитный калькулятор не позволяет рассчитать досрочное погашение. Однако его плюс в том, что он рассчитывает кредит с несколькими процентными периодами. Если сумма процентов по кредиту за данный месяц больше суммы аннуитетного платежа, то график для первого кредитного калькулятора в excel строится некорректно. В графике получаются отрицательные суммы.
Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.
В настоящее время в интернете можно обнаружить большое количество калькуляторов для расчета платежей по кредиту. Однако они имеют один существенный недостаток: непрозрачность с точки зрения используемых формул. Статья расскажет, как произвести самостоятельные расчеты, используя общедоступные функции в MS Excel .
Основные вопросы, связанные с расчетом кредита, заключаются, как правило, в следующем:
Чтобы ответить на оба вопроса потребуется информация о ставке процента и сроке кредитования. Дополнительно для ответа на первый вопрос необходима информация о сумме платежа, для ответа на второй – данные о размере кредита.
Величина процентной ставки зависит от многих параметров: от кредитной политики конкретного банка, срока займа, вида программы кредитования, обеспечения и т.д.
Срок кредита, как правило, может выбираться заемщиком. Обычно он является кратным 12 месяцам и не превышает 7 лет (по ипотеке – до 30 лет).
Добрый день уважаемый пользователь!
Сегодня я хотел бы поговорить о таком необходимом зле, как кредит. Почему зло, вы и так знаете, особенно это касается потребительского кредитования, когда за вещь вы переплачиваете в 2-3 раза больше ее реальной цены. Это всё необходимо учитывать и просчитывать, поэтому и научитесь создавать свой личный кредитный калькулятор в котором вы реально увидите картинку «мышеловки», в которую попадают обычный обыватель. Хотя есть еще кредиты для бизнеса, но там немного другая история, их берут, чтобы зарабатывать деньги. Главная проблема кредита даже не в «космических» процентах, а в том, что вы получаете удовольствие сейчас, а расплата и проблемы вас ждут в будущем, а это убивает личную мотивацию практически в зародыше. Пропадает желание, что-то делать, развиваться, напрягаться, учиться, создавать источники дохода, когда можно «тупо» взять паспорт и за 15 минут в ближайшем банке вас быстренько возьмут в кабалу и грамотно навешают на вас кучу всего разного и лишнего, лишь бы было, типа страховку и прочее.
Поэтому я очень хочу, чтобы материал, который я дам в своей статье будет вам полезен в принятии ваших решений.
Несмотря на то, что я не являюсь приверженцем кредитов, всё же осознаю их необходимость. Недавно мой ребенок попал в больницу, и я был вынужден, в силу обстоятельств, использовать средства кредитной линии. Ну а потом на протяжении 2 недель полностью закрыл долг, не отлаживая его в долгий ящик. Ну не мог я по-иному, нужны были деньги и срочно, ну и долг я сразу же закрыл и не ждал ни окончания льготного периода, ни начисления процентов.
Вот исходя из этих соображений и всё же возникновению необходимости получения кредита вами или вашими близкими, необходимо, я бы сказал желательно, перед путешествием в банк прикинуть ориентировочно сумму, сроки переплаты и т.п... После того как вы прочувствуете цифры вы или будете готовы оформить кредит или попросту откажетесь от него. И в этом вопросе вам очень поможет Microsoft Excel.
Рассмотрим три самых популярных варианта использования кредитного калькулятора в Excel:
Начнём с простого варианта, быстро прикинем, сколько нам нужно ежемесячно оплачивать по аннуитетному кредиту, это когда выплаты делают одинаковыми суммами, как в большинстве случаев. Это можно произвести одной функцией Excel и несколькими простыми формулами. Для получения результата в Excel существует функция ПЛТ в разделе «Финансовые»
. Указываем, в какую ячейку нужен результат, вызываем «Мастер функций»
ищем функцию ПЛТ, нажимаем кнопочку «ОК»
и в окне мастера вводим необходимые аргументы для нашего расчёта, формула получается следующего вида:
=ПЛТ(B5/12;B6;B4;0;0) , где:
Кроме этого, необходимо рассчитать, сколько составит общая сумма выплаты, и какая переплата получится, когда вы вернёте деньги банку. Это легко осуществить при помощи простых формул.
Теперь давайте немного улучшим и детализируем наш отчёт с помощью функции ОСПЛТ, которая определяет часть основного платежа по телу кредита и функции ПРПЛТ, которая посчитает всё, что касается процентов банку за использование кредита. Видоизменим ваш расчёт следующей таблицей:
Теперь в поле «Тело кредита» в ячейку Е2 вводим формулу функции ОСПЛТ следующего вида:
=ОСПЛТ($B$4/12;D2;$B$5;$B$3;0)
Как видите, ее орфография практически аналогична функции ПЛТ, добавился только аргумент «Период»
, который указывает на номер текущего месяца, и дополнительно рассматривать ее я не буду. Единственное, на что обращу ваше внимание, это то, что формула будет растянута на диапазон, а значит, аргументы необходимо закрепить . Следующим шагом для столбика «Проценты»
будем использовать возможности функции ПРПЛТ. Вводится она аналогично вышеописанной и с теми же условиями и будет иметь такой вид:
=ПРПЛТ($B$4/12;D2;$B$5;$B$3;0)
Теперь в оставшиеся столбики будем вводить простые формулы, для получения суммы выплаты нам нужна формула: =E2+F2
, а для определения суммы остатка кредита используем формулу: =$B$3+СУММ($E$2:E2)
.
При необходимости, возможно, немножко улучшить и автоматизировать ваш кредитный калькулятор в Excel для уменьшения количества .
Для начала пропишем формулу в ячейку D3 для того чтобы она подстраивала и отслеживала срок кредита:
=ЕСЛИ(D2>=$B$5;"«;D2+1)
Следующим шагом с помощью для поля «Тело кредита» , сделаем автоматическую проверку достигли ли вы последнего срока выплат или нет. Если период, достигнут, получаем пустую ячейку «», а если нет, то функцией ОСПЛТ выводим необходимый расчёт:
=ЕСЛИ(D3<>»";ОСПЛТ($B$4/12;D3;$B$5;$B$3;0);"")
Этот вариант рассмотрим, когда вы будете не удовлетворены суммами платежей или сроками погашения и вам захочется закрыть кредит, досрочно, используя дополнительные платежи при наличии свободных средств.
Для реализации этого добавляем дополнительный столбик «Доп.платёж» в котором будут указываться сумма платежей уменьшающий остаток кредита. Но у банков есть два варианта развития событий:
Для лучшей наглядности будем рассматривать каждый случай в отдельности.
Рассмотрим расчеты, когда происходит погашения кредита раньше срока, в этом случае будем использовать функционал функции ЕСЛИ и проверим, достигли ли мы нулевой задолженности раньше указанного срока: В другом случае, когда у вас происходит уменьшение суммы выплат по кредиту, формула пересчитывает ваш ежемесячный платёж сразу же после внесённого дополнительного платежа.
Последний из рассматриваемых вариантов будет расчёт кредита с нерегулярными платежами, это когда на повышенную процентную ставку вам предоставляют лояльную программу вносить платежи на нерегулярной основе и без определений сумм взносов. Согласно таким кредитным программам банк может вам выделять еще дополнительно денег на ваши нужды, но для расчётов такой структуры кредитования производить расчёты нужно с точностью до , а не до . Ну вот в принципе и всё, единственно что хочу сказать, что подсчёт сколько точно дней находится между двумя указанными датами, лучше производить при помощи функции ДОЛЯГОДА.
А на этом у меня всё! Я очень надеюсь, что всё о создании кредитного калькулятора в Excel вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!