Финансовые функции MS Excel для вкладных и кредитных операций

Финансовые  функции MS Excel для вкладных и кредитных операций

1. Необходимые сведения

Движение денежных средств  можно представить численными рядами из последовательности платежей, распределенных во времени. Такой ряд называется потоком платежей (cash flow, CF). В финансовой практике часто встречаются операции, характеризующиеся возникновением таких потоков.

Потоки платежей могут  быть элементарными, аннуитетными (равной величины) и произвольной величины.

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

 

Аргументы функций  для кредитных и вкладных операций

Наименование	Значение	Примечание
Кпер	Общее число периодов платежей	Если начисление процентов  осуществляется раз в году, аргумент необходимо откорректировать соответствующим  образом.
Плт	Выплата, производимая каждый период.
Пс	Приведенная к текущему моменту  стоимость или общая сумма, которая  на текущий момент равноценна ряду будущих платежей.	Представляется отрицательным  числом в случае выплаты денежных средств и положительным в  случае их получения. Если аргумент опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента  плт.
Бс	Требуемое значение будущей  стоимости или остатка средств  после последней выплаты.	Если  аргумент опущен, он полагается равным 0. Будущая стоимость  займа равна 0. Если предполагается накопить некую сумму в течение какого-то времени, то эта сумма и есть будущая  стоимость.
Тип	Указание, когда должна производиться  выплата.	0 в случае постнумерандо. 1 в случае пренумерандо. Если этот аргумент опущен, то он полагается равным 0.
Предположение	Предполагаемая величина ставки.	Если значение предположения  опущено, то оно полагается равным 10 %.
Первичное	Современная стоимость.
План	Массив применяемых процентных ставок.	Значения могу быть числами  или пустыми ячейками, любые другие значения дают в результате значение ошибки #ЗНАЧ!. Пустые ячейки трактуются как нули (нет дохода).
Ставка	Процентная ставка за период.	Обычно задается в виде десятичной дроби. Если начисление процентов  осуществляется раз в году, аргумент необходимо откорректировать соответствующим образом.
Значения	Массив или ссылка на ячейки, содержащие числа, для которых требуется  подсчитать внутреннюю норму доходности.	Необходимо наличие по крайней мере одного положительного и одного отрицательного значения. Если аргумент, который  является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, такие значения игнорируются.
Предположение	Величина, предположительно близкая к результату.	В большинстве случаев  нет необходимости задавать аргумент. Если он опущен, предполагается значение 0,1 (10%).
Ставка_финанс	Ставка процента, выплачиваемого за заемные средства.	Указывается в случае использования  заемных средств.
Ставка-реинвест	Ставка процента, получаемого  на денежные потоки при их реинвестировании.
Эффект_ставка	Фактическая процентная ставка.	Если значение аргумента < 0, то функция НОМИНАЛ возвращает значение ошибки #ЧИСЛО!
Кол_пер	Количество периодов в  году, за которые начисляются сложные  проценты.	Должен  быть целым числом. Если значение аргумента < 1, то функция НОМИНАЛ возвращает значение ошибки #ЧИСЛО!
НЗ	Исходная сумма.	Аргумент должен быть положительным  числом, иначе функция возвратит  ошибку #ЧИСЛО!
Нач_период	Номер первого периода, включенного  в вычисления.	Нумерация периодов выплат начинается с 1.
Кон_период	Номер последнего периода, включенного  в вычисления.
Период	Номер периода выплат.	Значение  должно находиться в интервале от 1 до кпер.
Даты	График платежей.	Если хотя бы одно из чисел  в аргументе «даты» не является допустимой датой или предшествует начальной  дате, то функция возвращает значение ошибки #ЧИСЛО!
Номинальная_ ставка	Номинальная годовая процентная ставка.	Если значение аргумента < 0, то функция ЭФФЕКТ возвращает значение ошибки #ЧИСЛО!

 

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

Функция БС. Вычисляет будущую стоимость денежного потока (future value, FV) за n периодов при условии постоянной процентной ставки. Синтаксис: БС (ставка; кпер; плт; пс; [тип]). Функция используется для определения общей суммы выплаты по истечении определенного периода времени.

Функция ВСД. Вычисляет внутреннюю норму доходности (internal rate of return, IRR) для регулярных потоков денежных средств произвольной величины. Синтаксис: ВСД (значения; предположение). Функция применяется, когда необходимо оценить целесообразность вложения средств, определить максимально допустимые затраты на инвестиционный проект.

Функция КПЕР. Вычисляет длительность (количество периодов) операции при условии периодических постоянных выплат и постоянной процентной ставки. Синтаксис: КПЕР (ставка; плт; пс; [бс]; [тип]).

Функция МВСД. Вычисляет модифицированную (скорректированную с учетом норм реинвестиции) внутреннюю норму доходности (modified internal rate of return, MIRR) для регулярных потоков денежных средств произвольной величины. Синтаксис: МВСД (значение; [ставка_финанс]; ставка_реинвест). Функция может применяться для оценки проектов, предполагающих очень высокую или очень низкую норму прибыли. Использование показателя MIRR вместо IRR смягчает эффект от инвестиций.

Функция НОМИНАЛ. Вычисляет номинальную годовую ставку по известной эффективной (фактической) ставке.Синтаксис: НОМИНАЛ (эффект_ставка; кол_пер).

Функция ОБЩДОХОД. Вычисляет накопленную между двумя любыми периодами сумму, поступившую в счет погашения основного долга по займу. Синтаксис: ОБЩДОХОД (ставка; кол_пер; нз; нач_период; кон_период; тип).

Функция ОБЩПЛАТ. Вычисляет накопительную сумму процентов за период между двумя любыми выплатами. Синтаксис: ОБЩПЛАТ (ставка; кол_пер; нз; нач_период; кон_период; тип).

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

Функция ПЛТ. Вычисляет сумму периодического платежа для аннуитета при условии равных сумм платежа и постоянной процентной ставки. Синтаксис: ПЛТ (ставка; кпер; пс; бс; [тип]).

Функция ПРОЦПЛАТ. Вычисляет проценты, выплачиваемые за определенный инвестиционный период. Синтаксис: ПРОЦПЛАТ (ставка; [период]; кпер; пс).

Функция ПРПЛТ. Вычисляет часть периодического платежа, которая направлена на погашение процентов при условии постоянной процентной ставки. Синтаксис: ПРПЛТ (ставка; период; кпер; пс; [бс]; [тип]).

Функция ПС. Вычисляет современную стоимость потока за n периодов (present value, PV). Современная (приведенная) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат. Синтаксис: ПС (ставка; кпер; [плт]; бс; [тип]).

Функция СТАВКА. Вычисляет периодическую процентную ставку за один период Синтаксис: СТАВКА (кпер; плт; пс; бс; тип; предположение).

Функция ЧИСТВНДОХ. Вычисляет внутреннюю норму рентабельности IRR (см.функцию ВСД) для нерегулярных потоков платежей произвольной величины. Синтаксис: ЧИСТВНДОХ: (значения; даты; [предп]).

Функция ЧИСТНЗ. Вычисляет чистую современную стоимость инвестиции (netpresent value, NPV) нерегулярных потоков платежей произвольной величины Синтаксис: ЧИСТНЗ (ставка; значения; даты).

Функция ЧПС. Вычисляет чистую приведенную стоимость инвестиции (netpresent value, NPV), регулярных потоков платежей произвольной величины Синтаксис: ЧПС (ставка; значение1; значение2; …).

Функция ЭФФЕКТ. Вычисляет эффективную (фактическую) годовую процентную ставку (effective percentage rate, EPR). Синтаксис: ЭФФЕКТ (номинальная_ставка; кол_пер).

2. Примеры решения задач

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

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

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

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

=БС (11,5%/2;3*2;;-37 000) = 51 746,86 руб., с точки зрения вкладчика это  доход;

=БС (11,5%/2;3*2;;37 000) = - 51 746,86 руб., с точки зрения банка это  расход, т.е. возврат денег банком  вкладчику 

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

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

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

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

= БС(17%;5;-20000;1) = 164 136,96 руб.

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

= БС(17%;5;-20000) = 140 288 руб.

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

Задача 3. Клиент получил ссуду 50 т.р. по сложной годовой ставке 12%. Проценты начисляются раз в год и ежегодно снижаются на 1,5%. Ссуда погашается одноразовым платежом через 4 года. Найти величину этого платежа.

Алгоритм решения  задачи. Составим таблицу массива ставок в ячейках В2:В6

Год	Ставка %
1	12,0%
2	10,5%
3	9,0%
4	7,5%

Тогда сумма возврата составит  =БЗРАСПИС(50000;B3:B6)=72 507,89 руб.

3. Задачи

Задача 1. По облигации номиналом 50 000 руб., выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в первый год – 10%, в следующие два года – 20%, в оставшиеся три года – 25%. Определить будущую стоимость облигации с учетом переменной процентной ставки.

Ответ: 154 687,50 р.

Задача 2. Ссуда размером 1 млн. руб. выдана под 13% годовых сроком на 3 года; проценты начисляются ежеквартально. Определить величину общих выплат по займу за второй год.

Ответ: 331 522,23 р.

Задача 3. По облигации, выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в первый год – 10%, в следующие два года – 20%, в оставшиеся три года – 25%.  Рассчитать номинал облигации, если известно, что ее будущая стоимость составила 154 687,50 руб.

Ответ: 50 000 р.

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

Ответ: 355 890,12 р.

Задача 5. Клиент заключает с банком договор о выплате ему в течение 5 лет ежегодной ренты в размере 5 тыс. руб. в конце каждого года. Какую сумму необходимо внести клиенту в начале первого года, чтобы обеспечить эту ренту, исходя из годовой процентной ставки 20%?

Ответ: 14 953,06 р.

Задача 6. Пусть инвестиции в проект к концу первого года его реализации составят 20 000 руб. В последующие четыре года ожидаются годовые доходы по проекту: 6 000 руб., 8 200 руб., 12 600 руб., 18 800 руб. Рассчитать чистую текущую стоимость проекта к началу первого года, если процентная ставка составляет 10% годовых.

Ответ: 13 216,93 р.

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

• по 1-му проекту – начальные инвестиции составляют  550 тыс. руб., ожидаемые доходы за 5 лет соответственно 100, 190, 270, 300 и 350 тыс. руб.;
• по 2-му проекту – начальные инвестиции составляют  650 тыс. руб., ожидаемые доходы за 5 лет соответственно 150, 230, 470, 180 и 320 тыс. руб.

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

Ответ: 203691,03р., 225392,59р.

Задача 8. Сравнить инвестиционную привлекательность двух проектов. Цена капитала составляет 10%. Предварительные инвестиции в первый проект составляют 100 млн. руб., во второй – 105 млн. руб. Продолжительность первого проекта – 2 года; доходы по годам – 50 и 70 млн. руб. соответственно. Продолжительность второго проекта – 3 года; доходы по годам – 34, 40 и 60 млн. руб. соответственно.

Ответ: При однократном  выполнении проектов предпочтительным выходит второй проект

Задача 9. Определить чистую текущую стоимость по проекту на 5.04.2005 г. при ставке дисконтирования 8%, если затраты по нему на 5.08.2005 г. составят 90 млн. руб., а ожидаемые доходы в течение следующих месяцев будут: 10 млн. руб. на 10.01.2006 г., 20 млн. руб. на 1.03.2006 г., 30 млн. руб. на 15.04.2006 г., 40 млн. руб. на 25.07.2006 г.

Ответ: 4,26755931 млн. р.

Задача 10. Рассчитать, через сколько лет вклад размером 100 000 руб. достигнет 1000000 руб., если годовая процентная ставка по вкладу 13,5% годовых и начисление процентов производится ежеквартально.

Ответ: 18

Задача 11. Для покрытия будущих расходов фирма создает фонд. Средства в фонд поступают в виде постоянной годовой ренты постнумерандо. Сумма разового платежа 16 000 руб. На поступившие взносы начисляются 11,2% годовых. Необходимо определить, когда величина фонда будет равна 100 000 руб.

Ответ: через 5 лет.

Задача12. Предположим, что для получения через 2 года суммы в 1 млн. руб. предприятие готово вложить 250 тыс. руб. сразу и затем каждый месяц по 25 тыс. руб. Определить годовую процентную ставку.

Ответ: 12,63%.

Задача 13. Определить эффективную процентную ставку, если номинальная ставка составляет 9%, а проценты начисляются: а) раз в полгода; б) поквартально; в) ежемесячно.

Ответ: а) 9,2% б) 9,31%  в) 9,38%.

Задача 14. Известно, что эффективная ставка составляет 16%, начисления производятся ежемесячно. Определить номинальную ставку.