Самые нужные функции в excel

Простые проценты

Сущность метода начисления по простым процентам состоит в том, что проценты начисляются в течение всего срока инвестиции на одну и ту же сумму (проценты начисленные за предыдущие периоды, не капитализируются, т.е. на них проценты в последующих периодах не начисляются).

В MS EXCEL для обозначения Приведенной стоимости используется аббревиатура ПС (ПС фигурирует как аргумент в многочисленных финансовых функциях MS EXCEL).

Примечание . В MS EXCEL нет отдельной функции для расчета Приведенной стоимости по методу Простых процентов. Функция ПС() используется для расчета в случае сложных процентов и аннуитета. Хотя, указав в качестве аргумента Кпер значение 1, а в качестве ставки указать i*n, то можно заставить ПС() рассчитать Приведенную стоимость и по методу простых процентов (см. файл примера ).

Для определения Приведенной стоимости при начислении простых процентов воспользуемся формулой для расчета Будущей стоимости (FV): FV = PV * (1+i*n) где PV — Приведенная стоимость (сумма, которая инвестируется в настоящий момент и на которую начисляется процент); i — процентная ставка за период начисления процентов (например, если проценты начисляются раз в год, то годовая; если проценты начисляются ежемесячно, то за месяц); n – количество периодов времени, в течение которых начисляются проценты.

Из этой формулы получим, что:

Таким образом, процедура расчета Приведенной стоимости противоположна вычислению Будущей стоимости. Иными словами, с ее помощью мы можем выяснить, какую сумму нам необходимо вложить сегодня для того, чтобы получить определенную сумму в будущем. Например, мы хотим знать, на какую сумму нам сегодня нужно открыть вклад, чтобы накопить через 3 года сумму 100 000р. Пусть в банке действует ставка по вкладам 15% годовых, а процент начисляется только основную сумму вклада (простые проценты). Для того чтобы найти ответ на этот вопрос, нам необходимо рассчитать Приведенную стоимость этой будущей суммы по формуле PV = FV / (1+i*n) = 100000 / (1+0,15*3) = 68 965,52р. Мы получили, что сегодняшняя (текущая, настоящая) сумма 68 965,52р. эквивалентна сумме через 3 года в размере 100 000,00р. (при действующей ставке 15% и начислении по методу простых процентов).

Конечно, метод Приведенной стоимости не учитывает инфляции, рисков банкротства банка и пр. Этот метод эффективно работает для сравнения сумм «при прочих равных условиях». Например, что с помощью него можно ответить на вопрос «Какое предложение банка выгоднее принять, чтобы получить через 3 года максимальную сумму: открыть вклад с простыми процентами по ставке 15% или со сложными процентами с ежемесячной капитализацией по ставке 12% годовых»? Чтобы ответить на этот вопрос рассмотрим расчет Приведенной стоимости при начислении сложных процентов.

Описание функции КПЕР:

Функция КПЕР Рассчитывает общее количество периодов оплаты.

Чтобы использовать функцию рабочего листа Excel NPER, выберите ячейку и введите:

(Обратите внимание, как появляются входные данные формулы)

Синтаксис и входные данные функции NPER:

1	= КПЕР (ставка; PMt; pv; ; )

темп — процентная ставка за каждый период.

pmt — Это оплата за период.

pv — Приведенная стоимость инвестиций, которые соответствуют текущим платежам в будущем.

fv — ДОПОЛНИТЕЛЬНО: будущая стоимость инвестиции или кредита в конце периода платежей.

тип — ДОПОЛНИТЕЛЬНО: аргумент типа показывает, когда производятся платежи: либо в конце периода на 0, либо в начале периода на 1. Его значение по умолчанию — 0.

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

Прежде всего, надо понимать, что именно является вашим долгом по кредиту, и какие выплаты способствуют его уменьшению. В нашем примере вы берёте в кредит 50 000 рублей – это и есть ваш долг. Переплаченные по кредиту проценты (6157 рублей) вашим долгом не являются, это всего лишь вознаграждение банку за предоставленный кредит. Таким образом, можно сделать вывод:

Погашение процентов по кредиту никак не способствует уменьшению вашего долга перед банком.

В кризисные времена банки часто «идут навстречу» своим должникам. Они говорят как-то так: «Мы понимаем, у вас сейчас проблемы! Окей, наш банк готов пойти вам на уступки – можете нам просто погашать проценты, а само тело кредита погашать не надо. Все же люди братья и должны друг другу помогать! Бла-бла-бла…»

На первый взгляд такое предложение может показаться выгодным, а сам банк – «белым и пушистым лапулей». Ага, как бы ни так! Если взять в руки калькулятор и провести простые арифметические расчёты, то сразу становится ясно, что реальное предложение банка выглядит приблизительно так:

«Ребята, вы попали на деньги! Ничего не поделаешь, это жизнь! Предлагаем вам на время (а может и навсегда) стать нашим рабом – будете ежемесячно выплачивать проценты по кредиту, а сам долг погашать не надо (ну, чтобы сумма выплат по процентам не уменьшалась). Ничего личного – это просто бизнес, друзья!»

Теперь запомните главную мысль:

Именно погашение тела кредита вытаскивает вас из долговой ямы. Не процентов, а именно тела кредита.

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

Sn2 – долг на конец месяца по аннуитетному кредиту; Sn1 – сумма текущей задолженности по кредиту; S – сумма в аннуитетном платеже, которая идёт на погашение тела кредита.

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

Давайте для наглядности посчитаем, каким будет долг на конец месяца по нашему кредиту после внесения первого платежа:

Итак, при первом платеже текущая задолженность по кредиту у нас равна всей сумме займа (50 000 руб.). Чтобы посчитать долг на конец месяца, мы отнимаем от этой суммы не весь ежемесячный платёж (4680 руб.), а только ту часть, которая ушла на погашение тела кредита (3763 руб.). В результате наш долг на конец месяца составит 46 237 руб., именно на эту сумму будут начисляться проценты в следующем месяце. Естественно, они будут меньше, так как сумма долга уменьшилась

Теперь вы понимаете, почему важно погашать именно тело кредита?

Итак, друзья, мы с вами разобрались с формулами и расчетами аннуитетных платежей. Надеемся, теперь у вас нет вопросов по этой теме, и вы запросто сможете произвести все необходимые расчеты, а также составить график аннуитетных платежей по кредиту. Единственное, что бы вам, наверное, хотелось, это как-то автоматизировать процесс расчетов. Вы не поверите, но это возможно! Хотите узнать как? Тогда переходим к публикации: Расчет аннуитетных платежей по кредиту в Excel.

⇧

Досрочное погашение аннуитетного кредита

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

• Сократить период выплат в аннуитетной системе. Так, понадобится совершить дополнительный платёж. При этом месячная ставка не возрастёт. Платёж будет представлять собой компенсацию банку в размере процентов, которые он не получит.
• Уменьшение ежемесячных выплат. В этом случае уменьшается аннуитетная ставка, но размер процента не меняется (допустимо только при условии сокращения выплат по основной задолженности).

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

Это интересно: Расчет годовых процентов по кредиту — формула

6.1.2 Решение задачи

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

· ставка = 6%/12;

· пс = -60000;

· бс = 120000.

Значением функции КПЕР является число периодов, необходимое для проведения операции, в данном случае — число месяцев. В результате мы получим число периодов, равное 138,97. То есть почти 139 месяцев.

Для нахождения числа лет полученный результат разделим на 12. В строке формул запишем =139/12, и получим 11,58. То есть около 11,6 лет.

Иллюстрацию к задаче можно рассмотреть на рисунке 4.

Рис. 4: иллюстрация к примеру 1

6.2 Пример 2

Дано

Была взята ссуда на сумму 300 000 руб. под 17% годовых. Объем каждой выплаты равен 7000 руб. Нужно рассчитать количество лет, необходимых для погашения ссуды.

Решение задачи

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

· ставка = 17%/12;

· плт = 7 000;

· пс = -300 000.

Значением функции КПЕР является число периодов, необходимое для погашения ссуды, в данном случае — число месяцев. В результате мы получим число периодов, равное 66,42. То есть около 66,4 месяцев.

Для нахождения числа лет полученный результат разделим на 12. В строке формул запишем =66,4/12, и получим 5,53. То есть около 5,5 лет.

Рис. 5: иллюстрация к примеру 2

Иллюстрацию к задаче можно рассмотреть на рисунке 5.

6.3 Пример 3

Дано

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

Решение задачи

Для этого возьмем имеющиеся данные и составим таблицы зависимости. В таблице зависимости числа периодов от изменения процентной ставки (Рис. 6) мы берем 15 различных значений ставки. Значения платежа и приведенной стоимости остаются неизменными в ячейках A5 и B5 соответственно. Для того, чтобы правильно рассчитать все значения по формуле КПЕР, нужно в строке формул для ячейки B8 изменить аргументы платежа и приведенной стоимости таким образом, чтобы их значения были закреплены в ячейках A5 и B5. Для этого рядом с цифрой 5, означающей строку, где находятся значения аргументов, нужно поставить значок доллара ($), нажав при этом необходимое количество раз клавишу F4 или просто приписав значок перед цифрой. После этого достаточно потянуть за правый нижний угол ячейку с формулой, чтобы заполнить все 15 ячеек таблицы.

Аналогично выполним вычисления зависимости числа периодов от изменения выплаты, производимой в каждый период, результаты которых можно увидеть на рисунке 7, вычисления зависимости числа периодов от изменения общей суммы, результаты которых показаны на рисунке 8.

Рис. 6: зависимость числа периодов от изменения процентной ставки

Рис. 7: зависимость числа периодов от изменения выплаты, производимой в каждый период

Рис. 8: зависимость числа периодов от изменения общей суммы

Примеры можно подробно рассмотреть в файле «Примеры применения функции КПЕР.xlsx»

Список используемой литературы

1. Экономическая информатика; Чистов Д.В.; М.: издательство «КноРус»; 2012г.

2. Практикум по информатике; Землянский А.А. , Кретова Г.А., Стратонович Ю.Р.; М.: издательство «Колос», 2003г.

3. Практикум по основам информатики и вычислительной техники. Учебное пособие для учреждений начального профессионального образования. Гриф МО РФ; Красникова Н.Е. , Силакова Л.А. , Уваров В.М.; М.: издательство «ACADEMIA»; 2012г.

Как рассчитать аннуитетный платеж в Excel

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

Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.

Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:

Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.

Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:

Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:

Здесь нам требуется заполнить три поля:

• «Ставка» – годовая процентная ставка по кредиту делённая на 12.
• «Кпер» – общий срок кредитования.
• «Пс» – сумма кредита (указывается со знаком минус).

Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус

Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.

После того как исходные данные будут введены, жмём кнопку «Ок». В результате мы видим в блоке расчетов точное значение ежемесячного аннуитетного платежа:

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

Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках

Можно было просто вписать в строке формул то, что там сейчас вписано

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

Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:

На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.

Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.

Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.

Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:

Как рассчитать аннуитетный платеж в Excel

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

Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.

Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:

Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.

Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:

Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:

Здесь нам требуется заполнить три поля:

• «Ставка» – годовая процентная ставка по кредиту делённая на 12.
• «Кпер» – общий срок кредитования.
• «Пс» – сумма кредита (указывается со знаком минус).

Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4)

Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус

Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.

После того как исходные данные будут введены, жмём кнопку «Ок». В результате мы видим в блоке расчетов точное значение ежемесячного аннуитетного платежа:

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

Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ»

По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках

Можно было просто вписать в строке формул то, что там сейчас вписано

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

Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:

На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.

Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.

Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.

Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:

Создание графика ссуды

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

В первом столбце периода введите «1» в качестве первого периода и затем перетащите ячейку вниз. В нашем случае нам нужно 120 периодов, так как 10-летний платеж по кредиту, умноженный на 12 месяцев, равен 120.

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

= -PMT (TP; B4 * 12; B3) = -PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)

Третий столбец — это основная сумма, которая будет выплачиваться ежемесячно. Например, за 40-й период мы выплатим 945,51 доллара в качестве основной суммы из нашей ежемесячной общей суммы в 1161,88 доллара.

Для расчета основной суммы выкупа мы используем следующую формулу:

= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)

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

= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)

В пятом столбце указана оставшаяся сумма оплаты. Например, после 40-го платежа нам придется заплатить 83 994,69 доллара на 120 000 долларов.

Формула выглядит следующим образом:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

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

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

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

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

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

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

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

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

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

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

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

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

1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
2. Составим график погашения кредита. Пока пустой.
3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

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

Расчет платежей в Excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

• сумма основного долга распределена по периодам выплат равными долями;
• проценты по кредиту начисляются на остаток.

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

• ДП – ежемесячный платеж по кредиту;
• ОСЗ – остаток займа;
• ПП – число оставшихся до конца срока погашения периодов;
• ПС – процентная ставка за месяц (годовую ставку делим на 12).

Составим график погашения предыдущего кредита по дифференцированной схеме.

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Почему важно уметь рассчитать переплату?

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

Каждый банк предлагает свои банковские продукты — кредиты наличными и кредитные карты. Первые пользуются большей популярностью благодаря большим суммам кредитования

Самое важное в кредите наличными — это переплата по нему

Важно иметь под рукой калькуляторы для расчета процентов банку, особенно при выдаче кредита. Это поможет существенно сэкономить при выборе кредита с минимальной ставкой и переплатой

Кредитка Альфабанка с 100 дневным грейс периодом

• Грейс период 100-365 дней
• Хорошая альтернатива микрозаймам
• Кредитный лимит до 500 000 RUB
• Годовое обслуживание первый год бесплатно, потом 990 р в год

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

• ПСК = i * ЧБП * 100;
• где i – процентная ставка базового периода;
• ЧБП – число базовых периодов в календарном году.

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

Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).

Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.

Далее находим ЧБП: 365 / 28 = 13.

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

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

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

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

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

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

Особенности использования функции КПЕР в Excel

Функция КПЕР используется для решения финансовых задач совместно с функциями ПЛТ, БС, СТАВКА, ПС и имеет следующую синтаксическую запись:

=КПЕР( ставка;плт;пс ;;)

Описание аргументов (первые три аргумента – обязательные для заполнения):

• ставка – числовое значение, характеризующее ставку за 1 период выплат (для ссуд) или капитализации (для депозитных вкладов). Аргумент может быть указан в виде дробного числа или в качестве значения в процентном формате (например, 14,5% или 0,145 – эквивалентные варианты записи). Если в условии задачи указана годовая ставка, необходимо выполнить пересчет по формуле Rп=Rг/12, где Rп – ставка за период, Rg – годовая ставка, 12 – число месяцев в году.
• плт – числовое значение, соответствующее сумме выплаты за период, которая является фиксированной величиной (простые проценты).
• пс – числовое значение, характеризующее текущую стоимость инвестиции (например, сумма, выданная кредитной организацией в долг клиенту, или сумма средств, положенных на депозитный счет в банк).
• – числовое значение, соответствующее будущей стоимости инвестиции. Например, данный аргумент может характеризовать сумму, которую получит вкладчик по окончанию действия договора по депозитному вкладу. Если аргумент явно не указан или принимает значение 0 (нуль), функция КПЕР вернет количество периодов выплат до полного погашения задолженности. Аргумент необязателен для заполнения, по умолчанию принимается значение 0.
• – необязательный аргумент, характеризующий способ выплат (0 – выплата на конец периода, 1 – выплата на начало периода).

Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.

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

Особенности использования функции КПЕР в Excel

Функция КПЕР используется для решения финансовых задач совместно с функциями ПЛТ, БС, СТАВКА, ПС и имеет следующую синтаксическую запись:

=КПЕР( ставка;плт;пс ;;)

Описание аргументов (первые три аргумента – обязательные для заполнения):

• ставка – числовое значение, характеризующее ставку за 1 период выплат (для ссуд) или капитализации (для депозитных вкладов). Аргумент может быть указан в виде дробного числа или в качестве значения в процентном формате (например, 14,5% или 0,145 – эквивалентные варианты записи). Если в условии задачи указана годовая ставка, необходимо выполнить пересчет по формуле Rп=Rг/12, где Rп – ставка за период, Rg – годовая ставка, 12 – число месяцев в году.
• плт – числовое значение, соответствующее сумме выплаты за период, которая является фиксированной величиной (простые проценты).
• пс – числовое значение, характеризующее текущую стоимость инвестиции (например, сумма, выданная кредитной организацией в долг клиенту, или сумма средств, положенных на депозитный счет в банк).
• – числовое значение, соответствующее будущей стоимости инвестиции. Например, данный аргумент может характеризовать сумму, которую получит вкладчик по окончанию действия договора по депозитному вкладу. Если аргумент явно не указан или принимает значение 0 (нуль), функция КПЕР вернет количество периодов выплат до полного погашения задолженности. Аргумент необязателен для заполнения, по умолчанию принимается значение 0.
• – необязательный аргумент, характеризующий способ выплат (0 – выплата на конец периода, 1 – выплата на начало периода).

1. Функция КПЕР возвращает код ошибки #ЧИСЛО! В случае, если сумма платежа за каждый период меньше, чем произведение начальной суммы инвестиции и ставки за период, при этом будущая стоимость инвестиции равна 0 (ситуация при расчете количества периодов для полного возврата задолженности), а выплата производится в конце периода (то есть, аргумент или явно указан как 0 (нуль).
2. Указанная выше особенность работы функции КПЕР вытекает из алгоритма, который она использует для расчета:
3. Все аргументы функции КПЕР должны указываться в виде числовых значений или конвертируемых в числа текстовых срок. Иначе рассматриваемая функция будет возвращать код ошибки #ЗНАЧ!.

1. Фактически, функция КПЕР позволяет определить количество периодов, по окончанию последнего из которых будущая стоимость инвестиции примет указанное значение.
2. В случае с кредитом, считается, что задолженность погашена полностью, если будущая стоимость инвестиции равна 0 (нулю).
3. Также функция КПЕР позволяет вычислить количество периодов капитализации депозитного вклада, необходимых для достижения требуемой суммы накоплений.
4. Для расчета количества периодов выплаты задолженности с нулевой процентной ставкой можно использовать формулу =A1/A2, где A1 – будущая стоимость, A2 – фиксированная сумма выплат за период.

Расчет реальной суммы долга с процентами и переплатой в Excel

Пример 2. Клиенту банка был выдан кредит на сумму 10000 рублей под 23% годовых с ежемесячной оплатой 700 рублей. Сколько всего денег получит банк по окончанию срока кредитного договора?

Формула для расчета:

Общая сумма кредита рассчитывается как произведение фиксированной суммы ежемесячного платежа и количества периодов выплат. В данном случае количество периодов равно 16,85 (нецелое число), значит, последняя выплата должна составить меньше 700 рублей. Найдем целое число периодов:

Чтобы определить, какую часть тела кредита было погашено за 16 целых периодов выплат, воспользуемся следующей функцией:

За последний неполный период необходимо вернуть следующую часть тела кредита:

Рассчитаем оставшиеся проценты к уплате:

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

Общая сумма, которую получит банк, составит 11796 рублей, а размер последнего платежа – 597 рублей.