Финансовые ренты

6.7 Используем Excel

Как следует из материала, изложенного ранее в этой лекции, количественный анализ рент сводится к вычислению следующих их характеристик:

• приведенная ценность потока платежей,
• будущая величина потока платежей,
• величина отдельного платежа,
• норма доходности (процентная ставка),
• количество периодов проведения платежей./

Все эти характеристики, за исключением приведённой ценности, связывает зависимость, выражающаяся в простейшем случае, когда платежи и начисления процентов выполняются один раз в конце каждого периода, формулами (6.2) и (6.3):

Ранее в этой лекции на примерах было показано, как, зная значения величин R, n и i, вычислить наращенную сумму ренты S, или, зная значения величин S, n и i, вычислить величину платежа R. Не представляет труда записать приведенные решения примеров на рабочих листах Excel. Ещё раз отметим, что эти решения станут существенно компактней и наглядней, если создать на VBA две функции, которые мы назвали AnnCoeff и AnnPCoeff. Функция AnnCoeff реализует функцию , а AnnPCoeff - функцию :

Приведём тексты этих функций (листинг 6.1). Для читателей, хоть немного знакомых с программированием на VBA, не составит труда включить их в свой модуль и использовать при решении примеров и заданий из лекций. Всех остальных отсылаем к приложению Б в учебнике [5], в котором подробно объяснено, как это сделать.

Function AnnCoeff(n As Integer, i As Single) As Single
 AnnCoeff = ((1 + i) ^ n - 1) / i
End Function

Function AnnPCoeff(n As Integer, i As Single, p As Integer) _
 As Single
 AnnPCoeff = ((1 + i) ^ n - 1) / p / ((1 + i) ^ (1 / p) - 1)
End Function

Рис. 16. Пример на вычисление наращенной суммы ренты

На рис. 16 приведен фрагмент рабочего листа с решением примера 4. В решении дважды используется функция AnnCoeff (ячейки C11 и C12).

Если известны значения величин R, S и n, а требуется вычислить, какая ставка процента i применялась, то прямую формулу для вычисления значения i написать невозможно, так как i является корнем уравнения степени n. Простейший выход в этом случае - использовать встроенную функция СТАВКА.

В том случае, если известны значения величин R, S и i, а требуется вычислить, сколько периодов n проводились платежи, можно вывести необходимую формулу. В простейшем случае, когда платежи и начисления процентов выполняются один раз в конце каждого периода, из формул (6.2) и (6.3) получим формулу:

Однако в этой ситуации имеется другая возможность для вычисления количества периодов n - воспользоваться имеющейся для этого в Excel встроенной финансовой функцией КПЕР. Но прежде чем привести пример с использованием этой функции, сообщим общую информацию о встроенных в Excel финансовых функциях, предназначенный для анализа потока платежей.

В Excel имеется девять встроенных функций для вычисления характеристик потока платежей. Эти функции, как и большинство финансовых функций в Excel, содержатся в Пакете анализа, поэтому перед выполнением примеров и упражнений из этой лекции проверьте, установлен ли этот пакет установлен на вашем компьютере.^{1Если Excel установлен в полном объеме, то Пакет анализа всегда доступен.}

В пакете Excel в связи с реализацией набора финансовых функций выделено небольшое количество базовых понятий, которым присвоены имена, по возможности отражающие их содержание. В принципе в локализованной версии MS Office (а мы предполагаем, что читатель работает в русской версии) должны работать как английские, так и локальные наименования. Однако по нашему опыту это не всегда выполнено строго, поэтому рекомендуем использовать русские имена, а также учесть, что, в отличие от весьма стабильных наименований в английской версии, имена в русификации постоянно меняются от одного поколения продукта к другому (напомним, что мы в этих лекциях ориентируемся на MS Office 2010). Например, функция, вычисляющая процентную ставку ренты, в MS Office 2000 называлась НОРМА, тогда как, начиная с MS Office 2003, она превратилась в функцию СТАВКА, оставаясь, по-прежнему, RATE в базовом англоязычном пакете. Начнём с перечисления названий аргументов финансовых функций в той терминологии, которая использована в справочной системе MS Office 2010. Для удобства читателя соберем эти термины в следующей таблице:

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

Рис. 17. Вычисления с использованием функции БС

На рис.17 приведен фрагмент рабочего листа с решением примера 54 с использованием встроенной функции БС и функции AnnCoeff. Как видно на рисунке, результаты вычислений совпадают с ранее вычисленными.

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

Пример 61 Г-н Сидоров получил ссуду в размере 100 000 руб. под 8% годовых и согласен выплачивать ежемесячно по 2 000 руб. в счёт её погашения. Сколько месяцев потребуется для выплаты всей суммы ссуды?

Решение. В приведённой выше таблице находим функцию КПЕР, которая определяет необходимое для погашения ссуды количество выплат. Введем в любую ячейку формулу:

=КПЕР(8\%/12;-2000;100000)

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

Рис. 18. Пример на определение количества платежей

На рис.18 приведен фрагмент рабочего листа с решением примера 61.

Сделаем некоторые замечания, касающиеся применения функции КПЕР. Аргумент платеж может оказаться слишком мал, чтобы можно было вернуть ссуду. В этом случае в ячейке с формулой появится сообщение об ошибке: # ЧИСЛО!. Для возврата ссуды необходимо, чтобы ежемесячные выплаты были больше соответствующей процентной ставки, умноженной на полную величину ссуды. В рассмотренном примере величина ежемесячных выплат должна быть больше 666 руб.

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

Если некоторый аргумент функции является платежом (расходом), то он должен иметь отрицательное значение. Например, в формуле в ячейке B14 (рис.17) знак минус поставлен перед аргументом из ячейки B6 (величина платежа). Знак минус можно указывать либо в ячейках с данными, либо в формуле перед соответствующими аргументами.

Если значением формулы является величина платежа, то это значение также выдаётся со знаком минус. На экране монитора в этом случае и число, и знак минус перед ним выделяются красным цветом.

Список ключевых терминов

Аннуитет - синоним финансовой ренты при страховании жизни.

Поток денежных платежей - несколько денежных платежей следующих друг за другом.

Период ренты - временной интервал между последовательными выплатами финансовой ренты.

Перпетуитет - бессрочная рента, выплаты которой не ограничены никаким сроком.

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

Финансовая рента - последовательность платежей, равных по величине и производящихся через равные промежутки времени.

Краткие итоги

В лекции рассмотрены финансовые ренты. Обсуждаются основные виды финансовых рент и их использование в повседневной практике денежных расчётов потребительский кредит, ипотека, купонные платежи по облигациям и т.п.). В рассмотренных в тексте примерах используется информация актуальная в июле-августе 2016 г. Продолжен обзор возможностей программы Excel для решения рассмотренных в курсе примеров.