Содержание

Как рассчитать кредит в Excel: держите расходы под контролем

Дата: 6 июня 2016
Категория: Excel

Поделиться, добавить в закладки или распечатать статью

Кредиты прочно вошли в нашу жизнь, и мы уже не можем представить себя без них. Мы занимаем деньги в банках, чтобы купить автомобиль, квартиру, бытовую технику. Кредиты стремительно набирают популярность. К слову, один из самых популярных и доступных кредитов сегодня — предложение от Альфа-Банка. То есть, благодаря кредитам, мы делаем нашу жизнь лучше. Всё хорошо, только нужно держать выплаты по кредитам под контролем. А для этого, отлично подходит Эксель.

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

Например, у нас кредит на 10 000 у.е. со среднегодовой ставкой 6% и сроком 36 месяцев. Вычислим ежемесячную выплату тела кредита: =10 000/36. Получим 277,78 у.е.

Выплаты процентов по кредиту просчитываем от остатка по телу кредита на данный период. Первый платёж будет от полной суммы в 10 тыс, второй – от величины 10 000 у.е. – 277,78 у.е. Т.е. платёж по процентам во втором месяце составит: =(10 000,00 – 277,78) * 6% / 12. В этой формуле мы добавили деление на двенадцать, поскольку 6% — среднегодовая ставка, а ежемесячная – в 12 раз меньше. Результат вычисления – 48,61 у.е.

Полный платёж составит 277,78 у.е. + 48,61 у.е. = 326,39 грн. Таким образом, можно просчитать все 36 платежей.

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

Чтобы посчитать ежемесячные выплаты по телу кредита – используйте функцию =ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип). Аргументы функции:

  • Ставка – процентная ставка за один период
  • Период – Порядковый номер периода, для которого рассчитывается выплата. Он должен быть не больше, чем Кпер, иначе формула вернет ошибку
  • Кпер – количество периодов, на которое рассчитан кредит
  • Пс – сумма (тело) кредита. Для кредита это число записываем отрицательным
  • Бс – будущая стоимость – величина остатка по кредиту после окончания срока выплат. Необязательный аргумент, по умолчанию равен нулю
  • Тип – необязательный аргумент. Укажите «0» (значение по умолчанию), если оплаты производим в конце периода, «1» — в начале периода.

Вот какой результат даёт эта функция для рассмотренного выше примера:

Функция ОСПЛТ в Excel

Основную часть платежа посчитали, теперь проценты. Для этого используем функцию =ПРПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип). Аргументы у функции те же, что и в предыдущей функции. Результат вычисления для нашего примера такой:

Функция ПРПЛТ в Эксель

Чтобы получить полный ежемесячный платеж, нужно сложить результаты функций ОСПЛТ и ПРПЛТ.

Чтобы узнать, какая процентная ставка по вашему кредиту – используйте функцию =СТАВКА(Кпер; Плт; Пс; Бс; Тип; Оценка). Помимо уже известных Вам аргументов, здесь применяются:

  • Плт – размер периодической платы (тело кредита плюс процент)
  • Оценка – необязательный аргумент – начальная оценка ожидаемого результата. Обычно его не задают

Если нужно получить годовую ставку – умножьте результат функции на количество периодов в году. Например, на 12 месяцев, 4 квартала, 2 полугодия и т.п. Попробуем просчитать процентную ставку для нашего примера:

Функция СТАВКА в Excel

Если нужно узнать сколько периодов понадобится, чтобы погасить кредит – используйте функцию =КПЕР(Ставка; Плт; Пс; Бс; Тип).

Давайте опять применим эту формулу к нашему примеру:

Функция КПЕР в Эксель

А если вы вдруг забыли, на какую сумму взяли кредит – применяем функцию =ПС(Кпер; Ставка; Плт; Бс; Тип). И снова попробуем вычислить для нашего примера:

Функция ПС в Эксель

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

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

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

Поделиться, добавить в закладки или распечатать статью

officelegko.com

Расчет кредита в Excel – ИКТ

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса – другое дело, а для обычных людей мышеловка”деньги за 15 минут, нужен только паспорт” срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это “потом” все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?

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

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. “Помассажировать числа” заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.

Вариант 1. Простой кредитный калькулятор в Excel

Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами – таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial). Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:

  • Ставка – процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
  • Кпер – количество периодов, т.е. срок кредита в месяцах.
  • Бс – начальный баланс, т.е. сумма кредита.
  • Пс – конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
  • Тип – способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.

Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:

Вариант 2. Добавляем детализацию

Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel – ОСПЛТ (PPMT) и ПРПЛТ(IPMT). Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:

Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:

=ЕСЛИ(A17>=$C$7;””;A17+1)

Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку (“”) в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:

=ЕСЛИ(A18<>””; текущая формула; “”)

Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:

Вариант 3. Досрочное погашение с уменьшением срока или выплаты

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

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять – не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты – заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

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

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

Предполагается что:

  • в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
  • отрицательные суммы – наши выплаты банку, положительные – берем дополнительный кредит к уже имеющемуся
  • подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)

 

 

СКАЧАТЬ  задание credit

i-k-t.ru

Дифференцированные платежи по кредиту в MS EXCEL. Примеры и методы

Составим в MS EXCEL график погашения кредита дифференцированными платежами.

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

Примечание. При расчете кредита дифференцированными платежами сумма переплаты по процентам будет ниже, чем при аннуитетных платежах. Не удивительно, что сегодня практически все российские банки применяют в расчетах аннуитетную схему погашения кредита. Сравнение двух графиков погашения кредита приведено в статье Сравнение графиков погашения кредита дифференцированными аннуитетными платежами в MS EXCEL.

График погашения кредита дифференцированными платежами

Задача. Сумма кредита =150т.р. Срок кредита =2 года, Ставка по кредиту = 12%. Погашение кредита ежемесячное, в конце каждого периода (месяца).

Решение. Сначала вычислим часть (долю) основной суммы кредита, которую заемщик выплачивает за период: =150т.р./2/12, т.е. 6250р. (сумму кредита мы разделили на общее количество периодов выплат =2года*12 (мес. в году)).
Каждый период заемщик выплачивает банку эту часть основного долга плюс начисленные на его остаток проценты. Расчет начисленных процентов на остаток долга приведен в таблице ниже – это и есть график платежей.


Для расчета начисленных процентов может быть использована функция ПРОЦПЛАТ(ставка;период;кпер;пс), где Ставка — процентная ставка за период; Период – номер периода, для которого требуется найти величину начисленных процентов; Кпер — общее число периодов начислений; ПС – приведенная стоимость на текущий момент (для кредита ПС — это сумма кредита, для вклада ПС – начальная сумма вклада).

Примечание. Не смотря на то, что названия аргументов совпадают с названиями аргументов функций аннуитета – ПРОЦПЛАТ() не входит в группу этих функций (не может быть использована для расчета параметров аннуитета).

Примечание. Английский вариант функции — ISPMT(rate, per, nper, pv)

Функция ПРОЦПЛАТ() предполагает начисление процентов в начале каждого периода (хотя в справке MS EXCEL это не сказано). Но, функцию можно использовать для расчета процентов, начисляемых и в конце периода для это нужно записать ее в виде ПРОЦПЛАТ(ставка;период-1;кпер;пс), т.е. «сдвинуть» вычисления на 1 период раньше (см. файл примера).
Функция ПРОЦПЛАТ() начисленные проценты за пользование кредитом указывает с противоположным знаком, чтобы отличить денежные потоки (если выдача кредита – положительный денежный поток («в карман» заемщика), то регулярные выплаты – отрицательный поток «из кармана»).

Расчет суммарных процентов, уплаченных с даты выдачи кредита

Выведем формулу для нахождения суммы процентов, начисленных за определенное количество периодов с даты начала действия кредитного договора. Запишем суммы процентов начисленных в первых периодов (начисление и выплата в конце периода):
ПС*ставка
(ПС-ПС/кпер)*ставка
(ПС-2*ПС/кпер)*ставка
(ПС-3*ПС/кпер)*ставка

Просуммируем полученные выражения и, используя формулу суммы арифметической прогрессии, получим результат.
=ПС*Ставка* период*(1 — (период-1)/2/кпер)
Где, Ставка – это процентная ставка за период (=годовая ставка / число выплат в году), период – период, до которого требуется найти сумму процентов.
Например, сумма процентов, выплаченных за первые полгода пользования кредитом (см. условия задачи выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р.
За весь срок будет выплачено =ПС*Ставка*(кпер+1)/2=18750р.
Через функцию ПРОЦПЛАТ() формула будет сложнее: =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ(«1:»&кпер))-1;кпер;-ПС))

 

excel2.ru

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

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

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

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

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

Из чего состоит ежемесячный платеж по кредиту

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

  1. Сумма основного долга — это и есть те деньги, которые вы , а теперь постепенно возвращаете. Если вы заключили договор на 100 тыс. р., то это и есть ваш основной долг. Каждый месяц вы выплачиваете его часть, соответственно, он уменьшается.
  2. Проценты за пользование средствами — это ваше вознаграждение банку за то, что вы пользуетесь его деньгами. Размер этого платежа соответствует процентной ставке. Если вы взяли 100 тыс. р. под 12% годовых, то каждый месяц вам нужно будет уплатить 1 тыс. р. процентов.
  3. Комиссия может влиять на величину ежемесячного платежа. Ее присутствие не обязательно. Банк может взять деньги за свою работу, начисляя комиссию за рассмотрение вашей заявки или оформление кредита, за открытие и обслуживание счета или расчетно-кассовые операции. Банки зачастую устанавливают дополнительную комиссию тогда, когда вы не расплачиваетесь кредитной картой, а снимаете средства в банкомате. Комиссия может начисляться в виде определенной суммы или процентов. Учтите, что если банк берет всего 0,9% за обслуживание вашего счета, то для вас это выльется в 10,8% годовых переплаты по займу. Эти деньги будут списаны с вашего счета помимо указанной процентной ставки.
  4. Штраф и пеня. Начисляются за нарушение кредитного договора, просрочку платежей. Устанавливаются различными банками в разных размерах, и детально прописываются в кредитном договоре.
  5. Дополнительные платежи. Определенные кредитные программы требуют дополнительных финансовых вложений. Например, если вы оформляете ссуду под залог, то вам придется оплатить услуги оценщика. Большинство банков потребует приобрести страховку при покупке недвижимости или автомобиля в кредит.

Как правильно рассчитать сумму ежемесячного платежа

Чтобы вы могли оценить свои финансовые возможности, Центральным Банком России был выпущен указ, который обязывает кредитно-финансовые организации указывать в соглашении эффективную процентную ставку. Этот показатель поможет вам понять, насколько дорого обойдутся заемные средства.

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

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

buhof.ru

Расчет кредита в Excel |

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса — другое дело, а для обычных людей мышеловка»деньги за 15 минут, нужен только паспорт» срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это «потом» все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?

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

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. «Помассажировать числа» заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.

Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами — таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial) . Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:

  • Ставка — процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
  • Кпер — количество периодов, т.е. срок кредита в месяцах.
  • Пс — начальный баланс, т.е. сумма кредита.
  • Бс — конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
  • Тип — способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.

Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:

Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel — ОСПЛТ (PPMT) и ПРПЛТ (IPMT) . Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:

Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:

Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку («») в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:

Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:

Вариант 3. Досрочное погашение с уменьшением срока или выплаты

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

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять — не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты — заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

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

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

  • в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы

argi.su

Как при помощи функции СТАВКА рассчитать в Excel процентную ставку по кредиту

Функция СТАВКА вычисляет процентную ставку по займу или инвестиции, базируясь на величине будущей стоимости. В транзакциях, в которых процентная ставка не задана жестко, эта функция может быть использована для вычисления неявной ставки (ставки, по которой можно было бы получить такой же доход). Синтаксис функции следующий: СТАВКА(клер;ставка;пс;бс;тип;предположение).

Ставки краткосрочных займов

Краткосрочные займы обычно должны погашаться в течение 14 дней и, как правило, предполагают 15 долларов комиссии с каждых ста взятых взаймы долларов. Если вы взяли в кредит 200 долларов и согласились через 14 дней отдать 260 долларов, процентную ставку можно вычислить по следующей формуле: =СТАВКА(1;0;200;-260;0;,01)*365/14. Значение периода равно единице по той причине, что предполагается всего одна проплата. Этот единственный период охватывает 14 дней, поэтому результат нужно разделить на количество дней в году (365) и умножить на 14. Полученный результат (782%) такой большой потому, что заем краткосрочный (рис. 1).

Рис. 1. Вычисление процентной ставки краткосрочного займа

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

Темпы роста

Чаще всего функцию СТАВКА используют для вычисления темпов роста на пенсионном счету. Предположим, что баланс на пенсионном счету составляет 40 тысяч долларов на начало года и 48,5 тысячи — на конец. В течение года с каждой получки (т.е. раз в две недели) вы клали на счет по 200 долларов (т.е. осуществили 26 платежей). Следующая формула показывает, как пополнялись ваши инвестиции (рис. 2): =СТАВКА(26;-200;-40000;48500;,01)*26.

Рис. 2. Вычисление темпов роста

В данном примере функция СТАВКА возвращает темпы роста за каждый период, поэтому для получения годовой процентной ставки следует умножить это число на 26. Результатом будет ставка 7,49%.

Беспроцентные займы

Беспроцентные займы на самом деле редко таковыми являются, так как интерес заимодателя уже учтен в стоимости товара. Предположим, что вы хотите купить кухню за 3 тысячи долларов и оформляете на нее беспроцентную рассрочку на 12 месяцев. Если бы у вас было достаточно наличных, вы смогли бы купить эту же кухню за 2500 долларов — фактически вы переплачиваете за рассрочку 500 долларов. Рассчитанная по следующей формуле приведенная процентная ставка составляет 35,07%: =СТАВКА(12;-3000/12;2500;0;0;,01)*12.

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

Рис. 3. Таблица погашения кредита проверяет результаты расчета процентной ставки

excel2010.ru

Кредитный калькулятор в Excel | Компьютер для чайников

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

Расчет суммы ежемесячных выплат

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

  1. Откройте программу Microsoft Excel и введите в столбик А описание исходных данных: сумма кредита, годовая ставка, срок кредита (в месяцах), а также строку результата – сумма ежемесячных выплат. В столбик В мы будем заносить соответствующие значения.
  2. Ставим курсор на ячейку результата (в нашем примере это ячейка В5) и в меню «Вставка» находим пункт «функция». Откроется окно Мастера функций
  3. Выберите категорию «финансовые» и в окне функций выделите функцию ПЛТ
  4. Далее необходимо указать ячейки, которые будут служить аргументами функции. Первый аргумент Ставка. Чтобы не вводить название ячейки вручную, окно мастера можно свернуть кнопкой в конце строки.
  5. Сверните окно и выделите ячейку, в которой будете указывать ставку (в нашем примере это ячейка В2).
  6. Нажмите на кнопку в конце строки аргумента, чтобы вернуться в окно мастера.
  7. Так как ставку вы буде указывать годовую, а результат выплат нужно получить помесячный, то в строке аргумента заданное значение нужно разделить на 12. Дополните строку аргумента «/12»
  8. Следующий аргумент – количество периодов. Таким же образом сверните окно и укажите ячейку значения «срок кредита» (у нас это ячейка В3)
  9. Аргумент ПС означает сумму кредита, сверните окно мастера и укажите соответствующую ячейку (в нашем примере В1)
  10. Аргумент БС выражает конечный баланс. Очевидно, что наша задача погасить кредит полностью, поэтому введите в строку значение аргумента «0». Аргумент Тип указывает на способ учета зачисления выплат – в начале периода (месяца) или в конце. Большинство кредитов выдается по второму типу учета, поэтому определите значение аргумента равным «0»
  11. Нажмите кнопку ОК и формула готова. Введите исходные значения, и в результативной ячейке вы найдете сумму ежемесячных выплат. Она будет со знаком минус. Это означает, что деньги вам нужно отдавать…

Дополнительные расчеты

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

  1. Всего выплат по кредиту (за весь период) – значение выражается формулой, умножьте сумму ежемесячных выплат на количество месяцев. В нашем примере В5*В3.
  2. Сумма переплаты – сколько денег вы заплатите за пользование кредитными средствами. Для этого нужно сложить сумму всех выплат (эта величина у нас со знаком минус!) и сумму кредита. Формула в нашем случае: В6+В1
  3. Просчитывать разные варианты и сравнивать предложения по кредитованию с таким калькулятором очень легко.

Иногда банки идут на хитрости для повышения привлекательности своих предложений, и вводят различные комиссии и дополнительные платежи (помимо процентов). Будьте внимательны, учитывайте все дополнительные траты на обслуживание кредита при принятии окончательного решения.

Расскажи друзьям о полезной статье с помощью кнопок:


Загрузка…
Опубликовано: 12.08.2015

Кредитный калькулятор в Excel

dontfear.ru

Отправить ответ

avatar
  Подписаться  
Уведомление о