Пример расчета эффективности неравномерных капиталовложений с помощью функций ЧПС, ВСД и ПОДБОР ПАРАМЕТРА

Все упражнения в данной лабораторной работе выполняйте в книге ФИНАНСОВЫЙ АНАЛИЗ на Листе2. Лист2 переименуйте в ЧПС, ВСД.

Рассмотрим следующую задачу. Вас просят в долг 10000 руб. и обещают вернуть через год 2000 руб., через два года - 4000 руб., через три года - 7000 руб. При какой годовой процентной ставке эта сделка выгодна?

В приводимом на рис.4.2.1 расчете в ячейку В11 введена формула =ЧПС(В10;В5:В7) (см. рис. 4.2.2)

Рис. 4.2.1 Расчет годовой процентной ставки

Введите представленные на рис. 4.2.2. данные на лист “ЧПС, ВСД” и сравните полученный результат с данными на рис. 4.2.1.

Рис. 4.2.2 Формулы для расчета годовой процентной ставки

Кроме того, для автоматизации составления таблицы в ячейку С6 введена формула:

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

Первоначально в ячейку В10 вводится произвольный процент, например 3%. После этого выбираем команду Сервис / Подбор параметра (Tools / Goal Seek) заполняем открывшееся диалоговое окно Подбор параметра (Goal Seek), как показано на рис.4.2.3.

Рис. 4.2.3 Диалоговое окно Подбор параметра при расчете годовой процентной ставки

В поле Установить в ячейке (Set Cell) даем ссылку на ячейку В8, в которой счисляется чистый текущий объем вклада по формуле:

=ЧПС(B10;B5:B7)

В поле Значение (То Value) указываем 10000 — размер ссуды. В поле Изменения значения ячейки (By Changing Cell) даем ссылку на ячейку В10, в которой вычисляется годовая процентная ставка. После нажатия кнопки ОК средство подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 руб. Результат вычисления выводится в ячейку В10. В нашем случае годовая учетная ставка равна 11,79%. Вывод: если банки предлагают большую годовую процентную ставку, то предлагаемая сделка не выгодна.

Функция ЧПС (npv) возвращает чистый текущий объем вклада, вычисляемый на основе ряда последовательных поступлений наличных и нормы амортизации. Чистый текущий объем вклада — это сегодняшний объем будущих платежей (отрицательные значения) и поступлений (положительные значения). Например, вам предлагают следующую сделку. У вас берут в долг некоторую сумму денег и предлагают через k₁ лет вернуть сумму, равную Р_k1, через k₂ лет — Р_k2 и т. д. и, наконец, через k_n лет — Р_kn. Кроме данной сделки, у вас есть альтернативный способ использования ваших денег, например, положить их в банк под i процентов годовых. Тогда чистым текущим объемом вклада является та сумма денег, которой вам нужно располагать начальный год, чтобы, положив их в банк под i процентов годовых, получили предлагаемую прибыль. В нашем случае чистый текущий объем вклада равен (2).

(2)

Синтаксис: ЧПС(ставка; 1-е значение; 2-е значение;…..)

Аргументы:

ставка — ставка дисконтирования за один период.

Значение1, значение2, — от 1 до 29 аргументов, представляющих расходы и доходы.

· значение1, значение2, … должны быть равномерно распределены во времени, выплаты должны осуществляться в конце каждого периода.

· ЧПС использует порядок аргументов значение1, значение2, … для определения порядка поступлений и платежей. Убедитесь в том, что ваши платежи и поступления введены в правильном порядке.

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

Если п — это количество денежных взносов в списке значений, Р — j-e значение и i — ставка, то функция ЧПС вычисляется по формуле (3):

(3)

Функция ЧПС связана с функцией ВСД (внутренняя скорость оборота). ВСД — это скорость оборота, для которой ЧПС равняется нулю:

ЧПС(ВСД(…);…)=0.

Функция ВСД (irr) возвращает внутреннюю скорость оборота для ряда последовательных операций с наличными, представленными числовыми значениями. Объемы операций не обязаны быть регулярными, как в случае ренты. Внутренняя скорость оборота — это процентная ставка дохода, полученного от инвестиций, состоящих из выплат (отрицательные значения) и поступлений (положительные значения), которые происходят в регулярные периоды времени.

Синтаксис: ВСД (значения; предположение).

Аргументы:

значения – массив или ссылка на ячейки, содержащие числовые величины, для которых вычисляется внутренняя ставка доходности. Значения должны включать, по крайней мере, одно положительное значение и одно отрицательное значение, для того чтобы можно было вычислить внутреннюю скорость оборота. Функция ВСД использует порядок значений для интерпретации порядка денежных выплат или поступлений, поэтому нужно следить, чтобы значения выплат и поступлений вводились в правильном порядке.

Предположение – величина, о которой предполагается, что она близка к результату ВСД.

Если п + 1 — количество значений в списке, Pj — j-e значение, то ВСД является корнем относительно I (ставки) уравнения (4):

(4)

Для вычисления ВСД Excel использует метод итераций. Начиная со значения прогноз, функция ВСД выполняет циклические вычисления, пока не получит результат с точностью 0,00001. Если функция ВСД не может получить результат после 20 попыток, возвращается значение ошибки #ЧИСЛО!

В большинстве случаев нет необходимости задавать прогноз для вычислений помощью функции ВСД. Если прогноз опущен, то он полагается равным 0,1 (10%).

Если ВСД выдает значение ошибки #ЧИСЛО! Или результат далек от ожидаемого, можно попытаться выполнить вычисления еще раз, но уже с другим значением аргумента прогноз.

Таким образом, задачу, сформулированную в самом начале данного раздела, можно решить также с помощью функции ВСД. Для этого в ячейку D3 надо ввести -10 000 р., а в ячейку D10— функцию ВСД(D3:D7), которая и найдет минимальную годовую учетную ставку.