birmaga.ru
добавить свой файл

1



Приложение Б

Финансовые функции для анализа инвестиций



Среди полного перечня финансовых функций Microsoft Excel, непосредственно предназначенных для финансовых расчетов, выделяется группа функций, используемых для анализа инвестиций и расчета операций по кредитам, ссудам и займам (табл. 1).

Таблица 1 - Назначение и формат финансовых функций для анализа инвестиций

Формат

Назначение




1

2

БЗРАСПИС(первичное;план)

Возвращает будущую стоимость первоначальной основной суммы после применения ряда (плана) ставок сложных процентов. Функция БЗРАСПИС используется для вычисления будущей стоимости инвестиции с переменной процентной ставкой

БС(ставка ;кпер;плт;пс;тип)

Возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки

ВСД(значения;предположение)

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


КПЕР(ставка ;плт;пс;бс;тип)

Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки

МВСД(значения;ставка_финанс;ставка_реинвест)

Возвращает модифицированную внутреннюю ставку доходности для ряда периодических денежных потоков. МВСД учитывает как затраты на привлечение инвестиции, так и процент, получаемый от реинвестирования денежных средств

НОМИНАЛ(эффект_ставка; кол_пер)

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

ОБЩДОХОД(ставка;кол_пер;нз;нач_период;кон_период;тип)

Возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами

ОБЩПЛАТ(ставка;кол_пер;нз; нач_период;кон_период;тип)

Возвращает кумулятивную (нарастающим итогом) величину процентов, выплачиваемых по займу в промежутке между двумя периодами выплат

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

Возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки

ПЛТ(ставка ;кпер;пс;бс;тип)


Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки

ПРОЦПЛАТ(ставка;период; кпер; пс)

Вычисляет проценты, выплачиваемые за определенный инвестиционный период

ПРПЛТ(ставка;период;кпер;пс; бс;тип)

Возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки

ПС(ставка ;кпер;плт;бс;тип)

Возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат

СТАВКА(кпер;плт;пс;бс;тип;предположение)

Возвращает процентную ставку по аннуитету за один период, вычисляется путем итерации

ЧИСТВНДОХ(значения;даты;предп)

Возвращает внутреннюю ставку доходности для графика нерегулярных денежных потоков переменной величины

ЧИСТНЗ(ставка ;значения;даты)

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

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

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


ЭФФЕКТ(номинальная_ставка;кол_пер)

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

* Жирным шрифтом набраны обязательные параметры функции, а обычным – необязательные.
Подробное описание аргументов финансовых функций приведено в табл. 2.

Таблица 2 - Аргументы финансовых функций Excel анализа инвестиций

Аргумент

Назначение аргумента




1

2

Даты (дата1, …датаN)

Расписание дат платежей, соответствующее ряду денежных потоков

Значения (сумма1, …суммаN)

Ряд денежных потоков – выплат и поступлений (соответственно – отрицательные значения и положительные значения), соответствующий графику платежей

Кол_пер

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

Кон_период

Номер последнего периода, включенного в вычисления

Кпер

Общее число периодов платежей по аннуитету (функция КПЕР)

Нач_период

Номер первого периода, включенного в вычисления

Номинальная_ставка


Номинальная годовая процентная ставка (функция НОМИНАЛ)

Первичное (нз, инвестиция)

Стоимость инвестиции на текущий момент

Первый_период

Дата окончания первого периода

Период

Период, для которого определяется прибыль (выплата); находится в интервале от 1 до Кпер

План

Массив применяемых процентных ставок

Плт

Фиксированная выплата, производимая в каждый период

Предложение

Прогнозная величина процентной ставки (по умолчанию – 0,1%)

Пс

Приведенная к настоящему моменту стоимость инвестиции, начальное значение вклада (функция ПС)

Ставка

Процентная ставка за период (функция Ставка)

Ставка_реинвест

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

Ставка_финанс

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

Тип

Коэффициент, определяющий время выплаты: 0 – в конце периода (по умолчанию), 1 – в начале периода

Эффективная_ставка

Фактическая годовая процентная ставка (функция ЭФФЕКТ)



Функции Excel для расчета операций по кредитам, ссудам и займам обеспечивают решение следующих задач:

  • Определение наращенной суммы (будущей стоимости)

  • Определение начального значения (текущей стоимости)

  • Определение срока платежа и процентной ставки;

  • Расчет периодических платежей, связанных с погашением займов.



Определение будущей стоимости на основе постоянной процентной ставки



Задача 1

Постановка задачи

На банковский счет под 11,5% годовых внесли 37 000 руб. Определить размер вклада по истечении трех лет, если проценты начисляются каждые полгода.

Алгоритм решения задачи

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

В связи с тем, что проценты начисляются каждые полгода, аргумент ставка равен 11,5%/2. общее число периодов начисления равно 3*2 (аргумент кпер). Если решать данную задачу с точки зрения вкладчика, то аргумент пс (начальная стоимость вклада), равный 37 000 руб., задается в виде отрицательной величины (-37 000), поскольку для вкладчика это отток его денежных средств (вложение средств).

Аргумент плт отсутствует, так как вклад не пополняется. Аргумент тип равен 0, так как в подобных операциях проценты начисляются в конце каждого периода (задается по умолчанию). Тогда к концу каждого года на банковском счете имеем:

=БС(11,5%/2; 3*2; 0; -37000)= 51746,86, с точки зрения вкладчика это доход.

Этот же расчет можно выполнить по формуле

(1)

Подставив в формулу числовые данные, получим:


Нахождение решения задачи по формуле (1) дает тот же результат.


Примечание.

При аналитических вычислениях в Excel с помощью функций, связанных с аннуитетом (финансовая рента), - БЗРАСПИС, БС, ОБЩДОХОД, ОБЩПЛАТ, ОСПЛТ, ПЛТ, ПРПЛТ, ПС, СТАВКА, ЧИСТВНДХ, ЧИСТНЗ – используется следующее основное уравнение:

(2)


Иллюстрация решения приведена на рисунке 1. Проверка решения аналитическим методом представлена на рисунке 2.



Рисунок1 - Фрагмент листа Excel с решением задачи о нахождении будущего размера вклада


Рисунок 2 - Фрагмент листа Excel с аналитическим решением задачи о нахождении будущего размера вклада
Задача 2

Постановка задачи

Определить, сколько денег окажется на банковском счете, если ежегодно в течении пяти лет под 17% годовых вносится 20 000 руб. Взносы осуществляются в начале каждого года.

Алгоритм решения задачи

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

=БС(17%;5;-2000;;1)=164136,96р.

Если бы взносы осуществлялись в конце каждого года, результат был бы

=БС(17%;5;-2000)=140288р.

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

Решение задачи может быть найдено с помощью формулы

(3)


где БС – будущая стоимость потока фиксированных периодических платежей;

Плт – фиксированная периодическая сумма платежа;

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

Ставка – постоянная процентная ставка;

I – номер текущего периода выплаты платежа.

Результат аналитического вычисления


Задача 3

Постановка задачи

Достаточно ли положить на счет 85 000 руб. для приобретения через пять лет легкового автомобиля стоимостью 160 000 руб.? Банк начисляет проценты ежеквартально, годовая ставка 12%. Произвести расчеты при разных вариантах процентной ставки.

Алгоритм решения задачи

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

Получим:

=БС(12%/4;5*4;;-85000;0)=153519,45р.

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

1-й вариант

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

Иллюстрация решения приведена на рисунке 3.


Рисунок 3 - Фрагмент листа Excel с заполненными полями подбора параметров
После подтверждения введенных данных в ячейки В7 установится значение 160 000 руб., а в ячейке В3 отобразится результат – 88 588,12р.

2-й вариант

В данном случае также можно применить средство Подбор параметра, изменяя ячейку, в которой находится процентная ставка.


Определение текущей стоимости



Задача 4

Постановка задачи

Фирме требуется 500 000 руб. через три года. Определить, какую сумму необходимо внести фирме сейчас, чтобы к концу третьего года вклад увеличился до 500 000 руб., если процентная ставка составляет 12% годовых.

Алгоритм решения задачи

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


Рисунок 4 - Фрагмент листа Excel с панелью функции ПС
При непосредственном вводе данных получается то же значение вклада:

=ПС(12%;3;;500000)=-355890,12р.

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

(4)

Формула (4) дает аналогичный результат решения задачи, но, базируясь на формуле (1), не учитывает знак «минус» для денежных потоков от клиента



Вычисления на основе уравнения (2) дают полностью правильный результат.
Задача 5

Постановка задачи

Пусть инвестиции в проект к концу первого года его реализации составят 20000 руб. В последующие четыре года ожидаются годовые доходы по проекту: 6000 руб., 8200 руб., 12600 руб., 18800 руб.

Рассчитать чистую текущую стоимость проекта к началу первого года, если процентная ставка составляет 10% годовых.


Алгоритм решения задачи

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

Так как по условию задачи инвестициям в сумме 20000 руб. вносится к концу первого периода, то это значение следует включить в список аргументов функции ЧПС со знаком «минус» (инвестиционный денежный поток движется «от нас»). Остальные денежные потоки представляют собой доходы, поэтому при вычислениях укажем их со знаком «плюс».

Иллюстрация решения задачи представлена на рисунке 5.



Рисунок 5 - Фрагмент листа Excel с панелью функции ЧПС
Чистая текущая стоимость проекта к началу первого года составляет:

=ЧПС(10%;-20000;6000;8200;12600;18800;12600;18800)=13216,93р.

Данный результат представляет собой чистую прибыль от вложения 20000 руб. в проект с учетом покрытия всех расходов.

При расчете чистой приведенной стоимости инвестиций с помощью функции ЧПС учитываются периодические платежи переменной величины как суммы ожидаемых расходов и доходов в каждый из периодов, дисконтированные нормой процентной ставки, с использованием следующей формулы:
Задача 6

Постановка задачи

Инвестор с целью инвестирования рассматривает два проекта, рассчитанных на пять лет. Проект характеризуется следующими данными:


  • по первому проекту – начальные инвестиции составляют 550000 руб., ожидаемые доходы за пять лет соответственно 100000, 190000, 270000, 300000 и 350000 руб.;

  • по второму проекту – начальные инвестиции составляют 650 000 руб., ожидаемые доходы за пять лет соответственно 150000, 230000, 470000, 180000 и 320000 руб.

Определить, какой проект является наиболее привлекательным для инвестора при ставке банковского проекта – 15% годовых.

Алгоритм решения задачи


Оценку привлекательности проектов выполним с помощью показателя чистой текущей стоимости (функции ЧПС).

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

Для облегчения анализа полученного решения исходные данные задачи представим в виде таблицы и в соответствующие ячейки введем значения формул с функциями ЧПС (рисунке 6)


Рисунок 6 - Иллюстрация решения задачи с предварительными инвестициями
Непосредственное вычисление параметров в формулах расчета, как и вычисления с использованием формулы (4), дают те же результаты.

Для первого проекта:



Для второго проекта:



Таким образом, второй проект является для инвестора более привлекательным.


Примечание.

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



Определение периодических платежей, связанных с погашением займов



Задача 7

Постановка задачи

Клиенту банка необходимо накопить 200 000 руб. за два года. Клиент обязуется вносить в начале каждого месяца постоянную сумму под 9% годовых.


Какой должна быть эта сумма?

Алгоритм решения задачи

Для определения ежемесячных выплат применяется функция ПЛТ с аргументами: Ставка = 9%/12 (ставка процента за месяц); Кпер = 2*12 = 24 (общее число месяцев начисления процентов); Бс = 200 (будущая стоимость вклада); Тип = 1, так как вклады вносятся в начале периода.

Тогда величина ежемесячных выплат равна:



Результат со знаком «минус», так как 7580 руб. клиент ежемесячно вносит в банк.

Иллюстрация решения приведена на рисунке 7.



Рисунок 7 - Иллюстрация применения функции ПЛТ
Выплаты, определяемы функцией ПЛТ, включают основные платежи и платежи по процентам. Расчет выполняется по формуле, определяемой из равенства (2):

(5)

Расчет задачи по формуле (5) дает тот же результат.
Задача 8

Постановка задачи

Клиент банка осуществляет заем в размере 5 000 руб. под 6% годовых на 6 месяцев. Определите ежемесячные платежи клиента. Платежи осуществляются в конце месяца.

Алгоритм решения задачи

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



Отметим, что для банка выданный кредит – это отрицательная величина, а рассчитанные ежемесячные поступления от клиента – положительная величина.
Задача 9

Постановка задачи

Определите платежи по процентам за первый месяц от трехгодичного займа в 100000 руб. из расчета 10% годовых.

Алгоритм решения задачи


Для определения платежа по процентам за первый месяц заданного периода применим функцию ПРПЛТ со следующими аргументами: Ставка = 10%/12 (процентная ставка за месяц); Период = 1 (месяц); Кпер = 3*12 = 36 (месяцев), Пс = 100 000 (величина займа). Тогда платежи по процентам за первый месяц составят:



Знак «минус» означает, что платеж по процентам необходимо внести.

Иллюстрация решения задачи приведена на рисунке 8.



Рисунок 8 - Иллюстрация применения функции ПРПЛТ
Задача 10

Постановка задачи

Клиент ежегодно в течение пяти лет вносил деньги на свой счет в банке и накопил 40 000 руб. Определите, какой доход получил клиент банка за последний год, если годовая ставка составила 13,5%.

Алгоритм решения задачи

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

Для расчета воспользуемся функцией ПРПЛТ



Заметим, что при решении данной задачи значения аргументов функции ПРПЛТ Бс и Тип не указываются (считаются равными 0).
Задача 11

Постановка задачи

Определить значение основного платежа для первого месяца двухгодичного займа в 60000 руб. под 12% годовых.

Алгоритм решения задачи

Сумма основного платежа по займу вычисляется с помощью функции ОСПЛТ



Знак «минус» означает, что сумму основного долга по займу необходимо внести.

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


Например, для ранее приведенной задачи 8 ежемесячная выплата клиента составляет:



Размер основного платежа:



Размер платежа по процентам:


Задача 12

Постановка задачи

Организация взяла ссуду в банке в размере 500 000 руб. на 10 лет под 10,5% годовых, проценты начисляются ежемесячно. Определите сумму выплат по процентам за первый месяц и за третий год периода.

Алгоритм решения задачи

Для вычисления суммы платежей по процентам за требуемый периоды воспользуемся функцией ОБЩПЛАТ (рисунок 9).



Рисунок 9 - Иллюстрация использования функции ОБЩПЛАТ
Аргументы функции: Кол_пер = 10*12 = 120 месяцев (общее число выплат); Ставка = 10,5%/12 (процентная ставка за месяц); Нз = 500 000 руб. (заем); Тип = 0; для выплаты процентов за первый месяц Нач_период = 1 и Кон_пер = 1,для выплаты процентов за третий год Нач_период = 25 и Кон_пер = 36.

Выплата за первый месяц составит:



Сумма выплат по процентам за третий год периода составит:




Примечание.

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