Функции Excel для расчета постоянных рент

Вспомним набор базовых аргументов для финансовых функций Excel [2,3]: БС(), КПЕР(), СТАВКА(), ПС() (см. таблица 2.1)и дополним его параметрами аннуитета:

ставка – процентная ставка (норма доходности или цена заемных средств – R);

кпер – срок (число периодов – п) проведения операции;

выплата – величина периодического платежа (pmt);

пс – начальное значение (величина PV);

бс – будущее значение (FV);

[тип] – тип начисления процентов (1 – пренумерандо, 0 – постнумерандо).

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

Таблица 3.1 Функции Excel для расчета постоянных рент

Функция ПЛТ(ставка; кпер; пс;бс;тип) определяет размер периодического платежа по значениям начальной суммы PV₀ (пс) и будущей суммы FV (бс) при условии постоянства платежей и процентной ставки.

Функция ПРПЛТ(ставка;период;кпер;пс;бс;тип) определяет процентную часть платежазаданный период по величине начальной суммы (пс) и будущей суммы (бс) при постоянстве платежей и процентной ставки.

Функция ОСПЛТ (ставка;период;кпер;пс;бс;тип) определяет основную часть платежа, идущую на погашение долга, заданный период по величине начальной суммы (пс) и будущей суммы (бс) при постоянстве платежей и процентной ставки.

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

Задание 3.2. Решить Пример 3.1 с помощью функций Excel. Схема пренумерандо и постнумерандо.

3.3. Примеры расчета периодических платежей .

Пример 3.2. Каким должен быть размер периодического платежа, чтобы внесение пяти одинаковых платежей такого размера по схеме постнумерандо позволило погасить долг 300 тыс. руб. по ставке 8% за квартал? Определить основную и процентную часть платежа.

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

Рис. 3.2.

1. Создаем файл в пакете MS Excel. Вводим данные (см. рис.3.2).

2. При помощи функции ПЛТ() определяем сумму платежа – 75 137 тыс. руб. (ячейка B5).

3. Каждый платеж pmt разбиваем на части следующим образом:

pmt = PPMT + IPMT,

Здесь PPMT – основная часть, долга, IPMT - процентная часть долга

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

1 кв.: 8%*300000=24000 - это процентная часть - IPMT,

из поступившего платежа 75 137 руб. за вычетом этих процентов на погашение долга идет

75137–24000=51137 – это основная часть платежа - PPMT

остаток долга 300000–51137=248863, на эту сумму начисляются проценты.

и далее:

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

Расчет погашения по соотношениям (5) приведен в таблице.

Проделать расчеты по соотношениям (5).

4. Вместо расчета (5) можно использовать функции, ПРПЛТ() и ОСПЛТ() и рассчитывать процентную и основную часть долга за каждый период. Все подробно показано на рисунке.

В ячейке С19 рассчитана основная часть долга за 2 период (функция ОСПЛТ()), в ячейке С20 процентная часть долга за 2 период (функция ПРПЛТ()). В ячейках В19 и В20 показаны формулы.

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

5. Результат продемонстрирован на диаграмме (диаграмма с накоплением), где видно, что постоянный платеж состоит из процентной части долга (темная – падает), и части, идущей на погашение (светлая - растет). Итак, последовательное применение функций к каждому периоду дает план погашения долга.

Построить диаграмму по данным расчета.

Контрольные вопросы.

1. Что такое постоянная рента?

2. Что такое аннуитет?

3. Чем различаются потоки пренумерандо и постнумерандо?

4. Какой тип ренты выгоднее и почему?

5. Какие функции Excel используются для расчета аннуитета?

6. Как определить размер периодического платежа, если известна ставка доходности, срок и конечная сумма?

7. Какие части платежа необходимо различать при погашении долга?

8. Как найти основную часть погашения?

Задания для самостоятельного выполнения.

Создайте файл в Excel, решите задачи, используя финансовые функции.

Задание 1. Проведите расчет ипотеки. Квартира стоит 800000 руб. Первый взнос составляет 20% от суммы. Кредит выдается на 10 лет под 11% годовых. Составьте план погашения кредита равными ежегодными платежами. Рассчитайте сумму выплат и сумму комиссионных. Используйте при выполнении задания функции Excel. Постройте диаграмму платежей. (Указание. Расчет провести с использованием функций ПЛТ(), ПРПЛАТ(), ОСПЛАТ(). Тип диаграммы – гистограмма с накоплением)

Задание 2. Составьте план погашения долга 1750 долл. США 24 равными ежемесячными платежами по ставке 16% годовых. Используйте при выполнении задания функции Excel. Постройте диаграмму платежей.

Задание 3. Составьте план погашения кредита в 300000 руб. до суммы в 100000 руб. Кредит выдан на 10 лет. Погашение производится равными ежемесячными платежами по ставке 16% годовых. Постройте диаграмму платежей.

Задание 4. Рассматриваются два варианта покупки: заплатить сразу 20000 руб. или платить ежемесячно по 1700 руб. в течение года при ставке 12% годовых. Что выгоднее? (Указание: используйте ПС() и сравните полученные значения - суммы, приведенные к началу срока).

Задание 5. Каким должен быть размер периодического платежа, чтобы внесение шести одинаковых платежей такого размера по схеме постнумерандо позволило погасить долг 500 тыс. руб. по ставке 18% за период?

Задание 6. Молодой человек c пятнадцатилетнего возраста в конце каждого месяца регулярно вносит по 15 $ на сберегательный счет в банк, начисляющий на всю растущую сумму сложные проценты по номинальной ставке 15% годовых. В каком возрасте этот человек может стать миллионером?

Задание 7.. Используя данные табл., сравните предложения конкурирующих банков о предоставлении кредита на покупку автомобиля ГАЗ –3110 по уровню годовой доходности. (Указание: Найдите годовые ставки, сравните. Найдите комиссионные выплаты в каждом случае, сравните.)

Таблица.

Поиск по сайту: