Практическая работа № 9. Схема погашения кредита

Функция ППЛАТ (ПЛТ) вычисляет величину постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке. Диалоговое окно данной функции отображено на рисунке 3.13.

Синтаксис: ППЛАТ (ставка; кпер; нз; бз; тип).

Все значения аргументов финансовой функции ППЛАТ совпадают со значениями аргументов предыдущих функций.

Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, необходимо умножить возвращаемое функцией ППЛАТ значение на величину «кпер».

Рисунок 3.13 – Диалоговое окно функции ППЛАТ (ПЛТ)

Функция ОСНПЛАТ (ОСПЛТ) возвращает величину выплаты за данный период на основе периодических постоянных платежей и постоянной процентной ставки. Диалоговое окно данной функции представлено на рисунке 3.14.

Рисунок 3.14 – Диалоговое окно функции ОСНПЛАТ (ОСПЛТ)

Синтаксис: ОСНПЛАТ (ставка; период; кпер; нз; бз; тип)

Функция ПЛПРОЦ (ПРПЛТ), представленная на рисунке 3.15, возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.

Синтаксис: ПЛПРОЦ (ставка; период; кпер; нз; бз; тип).

Рисунок 3.15 – Диалоговое окно функции ПЛПРОЦ (ПРПЛТ)

Аргументы функций ПЛПРОЦ и ОСНПЛАТ:

- ставка - процентная ставка за период;

- период - период, за который требуется найти прибыль (должен находиться в интервале от 1 до «кпер»);

- кпер - общее число периодов выплат;

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

- бз - будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент «бз» опущен, он полагается равным 0 (например, будущая стоимость займа равна 0);

- тип - число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 – то в начале периода

Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды размером в 100000 тенге на срок 5 лет при годовой ставке 2 %. Схему погашения кредита можно представить в виде таблицы, приведенной на рисунке 3.16.

Рисунок 3.16 – Схема погашения кредита

Перед решением задачи необходимо присвоить ячейкам B1, B2 и B3 имена «Срок», «Процент» и «Сумма_кредита» соответственно. Имя – это слово или набор символов, представляющих ячейку, диапазон ячеек, формулу или константу. В данном случае срок кредита, процентная ставка и сумма выступают в роли неизменных констант. Поэтому, например, имя «Процент», которому присвоено значение 2 %, можно использовать в любом месте для вычисления процентов. К тому же определенное имя в формуле облегчает понимание назначения формулы.

По умолчанию имена являются абсолютными ссылками на ячейку. Абсолютный адрес ячейки – это часть формулы, являющаяся адресом ячейки и ссылающаяся на данную ячейку независимо от местоположения ячейки с формулой. Абсолютный адрес ячейки имеет формат $A$1. Если же данной ячейке присвоено имя, то оно будет отображаться в формуле вместо адреса ячейки.

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

Присвоение имени ячейке осуществляется с помощью команды Вставка → Имя → Присвоить.

В ячейке B5 ставится сумма кредита на начало первого года – 100000 тенге.

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

Рисунок 3.17 – Заполнение функции ПЛТ

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

В графе «период» ставится ссылка на ячейку A5 с номером текущего периода, причем данная ссылка является относительной, то есть она может изменяться в зависимости от местоположения формулы.

Сумма основного платежа за первый год в ячейке E5 вычисляется с помощью функции ОСПЛТ (ОСНПЛАТ), как показано на рисунке 3.19.

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

Рисунок 3.18 – Определение суммы платежей по процентам

Рисунок 3.19 – Вычисление суммы основного платежа

Сумма кредита на начало второго года равна сумме кредита на конец первого года. Поэтому в ячейке B6 указывается ссылка на ячейку F5. В оставшиеся годы суммы кредита определяются путем протаскивания маркера заполнения ячейки F5 вниз по столбцу.

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

Задача 1. Составить схему погашения кредита при исходных данных, представленных в таблице 3.18.

Таблица 3.18 – Исходные данные

При выполнении задачи необходимо исходным данным присвоить имена (абсолютную адресацию), используя средства «Вставка» → «Имя» →»Присвоить».

Общую сумму платежа необходимо вычислить при использовании финансовой функции ППЛАТ.

Сумма платежа по процентам вычисляется при использовании функции ПЛПРОЦ

Сумма платежа основного долга вычисляется при помощи функции ОСНПЛАТ.

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

Сумма долга на начало периода переносится из ячейки таблицы, которая содержит сумму долга на конец предыдущего периода.

Задача 2. Разработать схему погашения кредита на сумму 250000 выданную на один год под 28% годовых, при ежемесячном погашении.

Задача 3. Разработать схему погашения кредита размером 800000 выданного на 3 года под 20% годовых с ежеквартальным погашением.

Выполняя задачи 2и 3 необходимо присваивать имена исходным данным. Имена клеток не должны содержать пробелы. (пример имени Срок1, Норма2 и т.д.)