Процплат в excel примеры

Здравствуйте, уважаемые читатели блога!

Вам приходилось брать кредит в банке? Тогда эта статья для Вас. При оценке и анализе вариантов займов необходимо получить конечные значения (а сколько же придется заплатить?) для разных наборов исходных данных (в данном случае процентных ставок). Одним из преимуществ табличного процессора MS Excel является возможность быстрого решения подобных задач и автоматического перерасчета результатов при изменении исходных данных. Допустим вы планируете какой-либо проект и для этого берете кредит в банке. В какой срок лучше отдать кредит, какие процентные ставки выбрать? Для решения подобных задач в MS Excel применяется Таблица подстановки. Использование этого средства  MS Excel происходит таким образом.

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

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

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

Допустим, Вы берете кредит в 100 тысяч рублей, сроком на 5 лет и определяете ежемесячные выплаты при различных процентных ставках.

Для решения этой задачи используется Таблица подстановки MS Excel.  Сначала записываем исходные данные – сумму займа, срок, процентная ставка согласно рисунка.

В ячейку D7 вводим формулу периодических постоянных выплат по займу при условии, что сумму необходимо погасить в течении срока займа: = ПЛТ (C4/12;C3*12;C2)

Процентную ставку делим на 12 в случае ежемесячных платежей и формат ячейки выбираем процентный – процентная ставка в этом случае записывается т.о.: 12% – 0,0125 – формат ячейки – процентный.

Кпер – число периодов выплат. Если период в годах, то для вычисления ежемесячных выплат умножаем на 12.

Пс – указываем сумму, которую берем взаймы (в нашем случае – это 100000).

Бс и Тип – необязательные параметры. Бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты; принимается равной 0, если значение не указано. Тип – логическое значение (0 или 1), обозначающее, должна ли производится выплата в конце периода или в начале периода.

Выделяем диапазон ячеек, содержащий значения процентных ставок и формулы для расчета – C7:D18.

Выполните команду Данные – Анализ “что если” – Таблица данных.  На экране появится диалоговое окно Таблица данных. (см.рис). Это окно используется для задания рабочей ячейки, на которую ссылается формула расчета. В нашем примере это ячейка С4, которую необходимо указать в поле Подставлять значения по строкам в:.

Если исходные данные расположены в столбце, то ссылку на рабочую ячейку необходимо ввести в поле Подставлять значения по столбцам в:. После нажатия кнопкиОК программа заполнит колонку результатами. Полученные числа имеют знак “-”.

Допустим, что вам захотелось определить, какая часть платежа идет на погашение процента по кредиту, а какая – проценты по кредиту. Для этого в следующий столбец, в ячейку Е7 необходимо ввести формулу: = ПРОЦПЛАТ (C4/12;1;C3*12;C2) (см.рис).

Затем опять выполните команду Данные – Анализ “что если” – Таблица данных, предварительно выделив необходимый диапазон ячеек. После нажатия кнопки ОКпоявляется таблица Плата по процентам за 1 мес. (см.рис). Если вас не испугают эти цифры, то можете смело отправляться в банк за ссудой.

Удачи в расчетах платежей по процентам 

Функция ПРОЦПЛАТ вычисляет проценты, выплачиваемые за определенный инвестиционный период. Эта функция обеспечивает совместимость с Lotus 1-2-3.

Описание функции ПРОЦПЛАТ

Вычисляет проценты, выплачиваемые за определенный инвестиционный период. Эта функция обеспечивает совместимость с Lotus 1-2-3.

Синтаксис

=ПРОЦПЛАТ(ставка; период; кпер; пс)

Аргументы

Замечания

• Убедитесь, что единицы измерения, выбранные для аргументов «ставка» и «кпер», соответствуют друг другу. При ежемесячных выплатах по четырехгодичному займу из расчета 12 процентов годовых используйте значение 12%/12 для аргумента «ставка» и 4*12 — для аргумента «кпер». При ежегодных платежах по тому же займу используйте значение 12% для аргумента «ставка» и 4 — для аргумента «кпер».
• Все аргументы, которым соответствуют выплачиваемые денежные средства (например, сберегательные вклады), представляются в виде отрицательных чисел, а получаемые (например, дивиденды) — в виде положительных.
• Дополнительные сведения о финансовых функциях см. в описании функции ПС.

Пример

Расчет процентной ставки

Функция СТАВКА используется для
расчета процентной ставки, которая в
зависимости от ситуации может быть либо
нормой прибыли, либо процентом кредита.

Функция СТАВКА вычисляется методом
последовательных приближений и может
не иметь решения или иметь несколько
решений. Если после 20 итераций погрешность
определения ставки превышает 10^-6,
то функция НОРМА возвращает значение
ошибки “#ЧИСЛО!”.

Вызов функции СТАВКА имеет следующий
общий синтаксис:

СТАВКА (Кпер;Плт;Пс;Бс;Тип;Предположение),

где Предположение — предполагаемая
величина процентной ставки (необязательный
аргумент). Если этот аргумент опущен,
то величина процентной ставки полагается
равной 10%. Если функцияСТАВКА не
сходится, следует попытаться использовать
различные значенияПредположение
(обычно между 0 и 1).

Задача №13. Фирме потребуется 1
млн р. через 2 года. Руководство фирмы
готово вложить 50 тыс. р. сразу и по 25 тыс.
р. каждый последующий месяц, чтобы
получить необходимую сумму в конце
второго года. Какой должна быть минимальная
годовая процентная ставка, чтобы фирма
достигла своей цели?

Решение. Для решения задачи необходимо
использовать формулу следующего вида:

=12*СТАВКА(2*12;-25000;-50000;1000000)

В результате будет получено значение
годовой процентной ставки приблизительно
равное 39,36%.

Рис. 11 Расчет значения процентной ставки
с помощью функции СТАВКА

Задача №14. Предположим, что
фирма отказалась от ежемесячных выплат
(см. задачу №13) и готова сегодня положить
на депозит 400 тыс. р. Определить, как в
этом случае изменится минимальная
годовая процентная ставка.

Решение. Для решения задачи необходимо
использовать формулу следующего вида:

=12*СТАВКА(2*12;;-400000;1000000)

В результате будет получено значение
годовой процентной ставки равное 46,7%.

Расчет периодических платежей

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

ПЛТ — вычисление фиксированной
величины периодических платежей,
осуществляемых на основе постоянной
процентной ставки;

ПРОЦПЛАТ — вычисление величины
платежа по процентам за конкретный
период;

ОСПЛТ — вычисление величины основного
платежа за конкретный период. Величина
основного платежа получается как
разность между фиксированным периодическим
платежом и платежом по процентам за
конкретный период.

Вызов функции ПЛТ имеет следующий
общий синтаксис:

ПЛТ(Ставка;Кпер;Пс;Бс;Тип)

Задача №15. Необходимо накопить
4000 р. за три года, откладывая постоянную
сумму в конце каждого месяца. Какой
должна быть эта сумма, если процентная
ставка по вкладу составляет 12% годовых?

Решение. Для решения задачи необходимо
использовать вызов функцииПЛТследующего вида:

ПЛТ (Ставка;Кпер; ;Бс)

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

=ПЛТ(12%/12;3*12;;4000)

В результате будет получена величина
периодического платежа равная -92,82 р.
(знак “минус” показывает направление
потока платежей, т.е. вложение денег в
банк).

Рис. 12 Расчет величины постоянного
платежа с помощью функции ПЛТ

Задача №16. Определить текущую
стоимость обязательных ежемесячных
платежей, чтобы погасить ссуду в 100 тыс.
рублей в течение 5 лет, если процентная
ставка составляет 12% годовых.

Вызов функции ПРОЦПЛАТ имеет
следующий общий синтаксис:

ПРОЦПЛАТ(Ставка;Период;Кпер;Пс)

Задача №17. В начале года банк выдал
клиенту ссуду 80 тыс. р. на срок 3 года под
10% годовых с обязательством ежемесячного
погашения части долга.

Определить величины платежей по процентам
за первый и за предпоследний месяцы
срока погашения ссуды.

Решение. В данном случае используется
функцияПРОЦПЛАТ следующего вида:

ПРОЦПЛАТ(Норма;Период;Кпер;Пс)

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

= ПРОЦПЛАТ(10%/12;1;12*3;80000)

= ПРОЦПЛАТ(10%/12;35;12*3;80000)

Окончательное решение задачи представлено
на рис. 13.

Рис. 13 Расчет величины платежей по
процентам за первый и предпоследний
месяцы срока погашения ссуды

Задача №18. В начале года банк
выдал клиенту кредит в сумме 150 000
руб. на срок 5 лет под 19% годовых с
обязательством ежеквартального погашения
части долга.

Определить величину платежей по процентам
за первый и за предпоследний кварталы
срока погашения кредита.

Вызов функции ОСПЛТ имеет следующий
общий синтаксис:

ОСПЛТ(Ставка;Период;Кпер;Пс;Бс;Тип)

Задача №19. Сделан займ 70 тыс. р.
на срок 3 года под 17% годовых. Рассчитать
размер основных выплат по займу за
каждый год.

Решение. Для значений, заданных в
условии задачи, соответствующие формулы
имеют вид

=ОСПЛТ(17%;1;3;70000)

= ОСПЛТ(17%;2;3;70000)

= ОСПЛТ(17%;3;3;70000)

Окончательное решение задачи представлено
на рис. 14.

Рис. 14 Расчет величины основного платежа
по займу

Задача №20. Использовать условия
задачи №19, однако выплаты по займу
производятся каждое полугодие.

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

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Составим в 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;кпер;-ПС))