1.2.1. Функции для анализа потоков платежей

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

Таблица 2. Функции для анализа потоков платежей

Описание аргументов функций

Ставка — это процентная ставка за период. Например, если вы получили ссуду на автомобиль под 10 % годовых и делаете ежемесячные выплаты, то процентная ставка за месяц составит 10 %/12, или 0,83 %. В качестве значения аргумента ставка нужно ввести в формулу 10 %/12 или 0,83 % ,или 0,0083.

Кпер — это общее число периодов выплат годовой ренты. Например, если вы получили ссуду на 4 года под автомобиль и делаете ежемесячные платежи, то ваша ссуда имеет 4*12 (или 48) периодов. Вы должны ввести число 48 в формулу в качестве значения аргумента кпер.

Выплата — это выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты. Обычно, выплата включает основные платежи и платежи по процентам, но не включает других сборов или налогов. Например, ежемесячная выплата по четырехгодичному займу в 10000 руб. под 12 % годовых составит 233,33 р. Вы должны ввести в формулу число 233,33 со знаком минус в качестве значения аргумента выплата.

Бс — это будущая стоимость или баланс наличности, которого нужно достичь после последней выплаты. Если бс опущено, оно полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если вы хотите накопить 50000 р. для оплаты специального проекта в течение 18 лет, то 50000 р. — это и есть будущая стоимость. Вы можете сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц.

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

Плата — это выплата, производимая в каждый период.

Нз — это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент нз опущен, то он полагается равным 0.

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

Основной_капитал — это текущая стоимость инвестиции.

Ставки — это массив применяемых процентных ставок.

Фактическая_ставка — это фактическая процентная ставка.

Кпер_год — это количество периодов, составляющих год.

Номинальная_ставка — это номинальная годовая процентная ставка.

Периодов_в_году — это количество периодов, составляющих год.

Финансовая функция ППЛАТ

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

Отметим, что очень важно быть последовательным в выборе единиц измерения для задания аргументов ставка и кпер. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 % годовых, то для задания аргумента ставка используйте 12/12, а для задания аргумента кпер — 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента ставка используйте 12 %, а для задания аргумента кпер — 4.

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

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

Приведем пример.

 Пример 1

Рассчитаем размер 30-летней ипотечной ссуды со ставкой 8 % годовых при начальном взносе — 20 % и ежемесячной (ежегодной) выплате с помощью функции ППЛАТ.

Создадим таблицу, согласно рис. 1, на рабочем листе Excel и заполним ее данными в соответствии с табл. 1. Присвоим имя рабочему листу Пример1-ППЛАТ.

Для приведенного на рис. 1 ипотечного расчета в ячейки введены формулы, показанные в табл. 3.

Ячейки с исходными данными окрашены серым цветом (рис. 1).

Рис. 1. Расчет ипотечной ссуды

Таблица 3. Формулы для расчета ипотечной ссуды

Финансовые функции Б3, КПЕР и НОРМА

Функция БЗ вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Функция БЗ подходит для расчета итогов накоплений при ежемесячных банковских взносах.

Синтаксис: Б3 (ставка; кпер; выплата; нз; тип).

Приведем примеры.

 Пример 2

Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. При этом вы собираетесь вложить 1000 усл. ден. ед. при годовой ставке — 6 %. Вы собираетесь вкладывать по 100 усл. ден. ед. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?

Расчет выполняется с помощью формулы

=БЗ(6%/12; 12; -100; -1000; 1)

Ответ: 2301,40 усл. ден. ед.

 Пример 3

Вы берете в долг 1000 усл. ден. ед. при годовой ставке — 1 % и собираетесь выплачивать по 100 усл. ден. ед. ежемесячно. За сколько месяцев вы погасите кредит?

Число выплат вычисляется следующим образом:

=КПЕР(1%; -100; 1000)

Ответ: 11 месяцев.

Функция НОРМА вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов. Следует отметить, что функция НОРМА вычисляет процентную ставку методом итераций, поэтому решение может быть и не найдено. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция НОРМА возвращает значение ошибки #ЧИСЛО!.

Синтаксис: НОРМА (кпер; выплата; нз; бз; тип; нач_прибл).

Рассмотрим следующий пример.

 Пример 4

Вы берете заем в 8000 усл. ден. ед. и можете ежемесячно погашать его по 200 усл. ден. ед. Какая процентная ставка должна быть установлена?

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

=НОРМА(48; -200; 8000)

Ответ: месячная (т. к. период равен месяцу) процентная ставка равна 0,77 %.

Финансовая функция ПЗ

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

Синтаксис: ПЗ (ставка; кпер; выплата; бз; тип).

Рассмотрим действие функции ПЗ на следующем примере.

 Пример 5

Допустим, что у вас просят в долг 10000 усл. ден. ед. и обещают возвращать по 2000 усл. ден. ед. ежегодно в течение 6 лет. Будет ли выгодна эта сделка, если банк может принять ваши деньги под 7 % годовых?

Проведем расчет эффективности капиталовложений с помощью функции ПЗ.

Создадим таблицу, согласно рис. 2, на рабочем листе Excel. Присвоим имя листу Пример5-ПЗ.

В ячейки В1, В2, В3 и В4 введем исходные данные. В ячейку В5 введем формулу =ПЗ(В4;В2;-В3).

Рис. 2. Расчет эффективности капиталовложений

Для оформления таблицы текстовыми надписями в ячейку С2 введем формулу

=ЕСЛИ (В2>4;"лет";ЕСЛИ (В2>1;"года";"год"))

В ячейку В6 введем формулу

=ЕСЛИ (В1<В5; "Выгодно дать деньги в долг"; ЕСЛИ (В5=В1; "Варианты равносильны"; "Выгоднее деньги положить под проценты"))

В данном параграфе была рассмотрена задача с двумя результирующими функциями: числовой — чистым текущим объемом вклада и качественной, оценивающей, выгодна ли сделка. Эти функции зависят от нескольких параметров. Некоторыми из них можно управлять. Например, сроком и суммой ежегодно возвращаемых денег. Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов. Команда Сервис/Сценарии предоставляет такую возможность с одновременным автоматизированным составлением отчета.

Рассмотрим способ применения этой команды для следующих трех комбинаций срока и суммы ежегодно возвращаемых денег: 2000 усл. ден. ед. на 6 лет; 1500 усл. ден. ед. на 12 лет; 1500 усл. ден. ед. на 7 лет.

Выполним команду Сервис/Сценарии. В открывшемся диалоговом окне Диспетчер сценариев для создания первого сценария нажмем кнопку Добавить. Появится окно диалога Добавление сценария.

В поле Название сценария введем с клавиатуры имя первого сценария — с1.

В поле Изменяемые ячейки выполним ссылку на две ячейки В2 и ВЗ.

Имя окна диалога изменится на Изменение сценария (рис. 3).

Рис. 3. Окно диалога Изменение сценария

После нажатия кнопки ОК появится диалоговое окно Значения ячеек сценария (рис. 4), в поля которого введены комбинации срока и суммы ежегодно возвращаемых денег, оговоренных в условии задачи.

Рис. 4. Окно диалога Значения ячеек сценария

С помощью кнопки Добавить последовательно создадим еще два сценария с именами с2 и с3.

После этого появится диалоговое окно Диспетчер сценариев с перечнем созданных сценариев.

Нажмем кнопку Отчет. Откроется диалоговое окно Отчет по сценарию (рис. 5).

Рис. 5. Диалоговое окно Отчет по сценарию

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

После нажатия кнопки ОК создается Отчет. На рис. 6 показан отчет по сценарию типа Структура.

Рис. 6. Отчет по сценарию типа Структура

В рабочем листе Структура сценария выполним следующие действия:

 уменьшим ширину столбцов D, E, F и G;

 выделим строку 10, в меню Формат выберем пункт Ячейки и на вкладке Выравнивание установим флажок Переносить по словам;

 при необходимости изменим высоту десятой строки;

 проанализируем полученный результат.