Левин Л.А. Финансовая математика в EXCEL

Подождите немного. Документ загружается.

Методику определения будущей стоимости аннуитета покажем на следующем

примере.

Пример 3-1

На счет в банке в течении пяти лет в конце каждого года будут вноситься суммы в

размере 500 руб., на которые будут начисляться проценты по ставке 30%. Определить

сумму, которую банк выплатит владельцу счета.

Решение

=CF*(1+r)

n-1

+ CF*(1+r)

n-2

+…+ CF =

1 1

( r )

CF *

+ −

+ −+ −

+ −

( 3-1)

1 30 1

500

( %)

+ −

+ −+ −

+ −

=4521,55

Таким образом, сумма всех взносов с начисленными процентами будет равна

4521.545 руб



При использовании финансовых функций Excel

=БС(30%;5;-500;;0)=4521.55

Рис. 3-4 Решение примера с

использованием функции БС()

Как мы уже отмечали

ранее, платежи могут

осуществляться

-раз в году

(ежемесячно, ежеквартально и

т.д.). Наиболее распространен случай, когда число платежей в году совпадает с числом

начислений процентов, В этом случае общее число платежей за n-лет будет равно m*n,

процентная ставка – r/m, а величина платежа – CF/m. Тогда, выполнив преобразования над

(5-1), получим:

(

((

(

)

))

)

(

((

(

)

))

)

1 1 1 1

m*n m*n

r r

m m

FV * CF *

m r

+ − + −

+ − + −+ − + −

+ − + −

= =

= == =

= =

( 3-2)

Пример 3-2

Предположим, что каждый год ежемесячно в банк помещается сумма в 1000. Ставка

равна 12% годовых, начисляемых в конце каждого месяца. Какова будет величина вклада к

концу 4-го года ?

Общее количество платежей за 4 года равно: 4* 12 = 48. Ежемесячная процентная

ставка составит: 12% / 12 = 1%.

Решение:

(

((

(

)

))

)

4 12

1 1

1000

FV *

+ −

+ −+ −

+ −

=61222,61



При использовании финансовых функций Excel

=БС(12%/12;4*12;-1000)= 61222,61

Рис. 3-5 Решение

примера 3-2

3.3.2. Современная (текущая)

величина аннуитета. Функция ПС()

Современная (текущая) величина потока платежей

(капитализированная или

приведенная величина) – это сумма платежей, дисконтированных на момент начала ренты

по ставке начисляемых сложных процентов.

Рис. 3-6 Логика

финансовой операции

определения современной

величины потока платежей

Это важнейшая характеристика финансового анализа, т.к. является основой для измерения

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

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

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

необходимую наращенную сумму.

CF CF CF

(время)

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

следующий вид:

(

((

( )

))

)

(

((

( )

))

)

(

((

( )

))

)

1 1 1 1

n n

r r

PV CF * CF *

r* r

−

−−

−

 

  

 

  

 

+ − − +

+ − − ++ − − +

+ − − +

 

  

 

  

 

= =

= == =

= =

 

  

 

  

 

 

  

 

  

 

( 3-3)

Для случая, когда выплаты сумм аннуитета (j) и начисления процентов (m)

совпадают во времени, т.е. j

удобно использовать соотношение вида:

(

((

(

)

))

)

1 1

m*n

n, j

PV CF *

+ +

+ ++ +

+ +

( 3-4)



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

знакомая нам финансовая функция ПС(),

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

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

Пример 3-3

Предположим, что мы хотим получать доход, равный $1000 в год, на протяжении 4-х

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

равна 10% годовых?

Решение.

PV = 1000*[((1-(1+10%)-4)/10%=

3169,87

При использовании финансовой

функции Excel

=ПС(10%;4;-1000)=3169.87

Рис. 3-7 Решение примера 3-3

Таким образом, для получения в

течение четырех лет ежегодного дохода в $1000 необходимо сегодня положить в банк

$3169.87.

Пример 3-4

Рассматриваются два варианта приобретения дома стоимостью 100 мл. руб.:

единовременный платеж.

ежемесячно в течение 15 лет вносить в банк по 1 мл.. руб.

Легко видеть, что выражения в квадратных скобках в (4-3) представляет собой множитель, равный

современной стоимости аннуитета в 1 денежную единицу. Разделив современную стоимость

денежного

потока любого вида на этот множитель, можно получить величину периодического платежа

эквивалентного ему аннуитета. Эта математическая зависимость часто используется в финансовом анализе

для приведения потоков с неравномерными поступлениями к виду обыкновенного аннуитета

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

процента – 8% годовых, а проценты начисляются ежемесячно?

Решение.

Для ответа на поставленный вопрос нам необходимо сравнить, что выгоднее:

заплатить сегодня всю суммы полностью или растянуть платежи на 15 лет.

Для сравнения необходимо привести эти денежные потоки к одному периоду

времени, т.е. рассчитать текущую

стоимость будущих фиксированных периодических

выплат.

Таким образом, текущая стоимость будущих периодических платежей больше

запрашиваемой стоимости дома (104,64 мл. руб. > 100 мл. руб.), следовательно выгоднее

покупать дом сразу.

Рис. 3-8

Решение примера 3-4

3.3.3. Нерегулярные потоки платежей, Функция БЗРАСПИС()

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

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

Для получения обобщающих характеристик, таких как

PV,

потребуется

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

последующему их суммированию:

(

((

( )

))

)

n k

FV CF * r

−

−−

−

 

  

 

= +

= += +

= +

 

  

 

∑

∑∑

∑

( 3-5)

Пример 3-5

Ежегодно в конце года в течение 5 лет вкладчиком вносились в банк суммы: 100,

200, 200,300, 300 руб. Ставка банка – 12%. Проценты на вклад начисляются в конце года.

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

срока.

Решение.

В соответствии с формулой (4-5) наращенная сумма составит

FV = 100*(1+12%)

+ 200**(1+12%)

+200**(1+12%)

+300**(1+12%)

+300*(1+12%)

= 1325.22.



Для решения задачи в Excel:

1-й вариант:

построим таблицу исходных данных, подобную приведенной в левой части рис.

5-12

(ячейки

А1:С7

);

в ячейке

С2

разместим формулу расчета наращенной суммы;

Обратите внимание, что в строке «Кпер» диалогового окна функции

БС()

записывается

«5

– А2».

Где число

«5» –

общий период

наращения (5 лет), а «

А2

» номер периода внесения

суммы платежа. Таким образом, разность «

5-А2

» характеризует период наращения

вложенной суммы

;

скопируем формулу в ячейки

С3:С6;

В ячейке

С7

разместим формулу

=Сумм(С2:С6) –

расчета наращенной суммы за

весь период накопления.

Таким образом, по окончании 5 –летнего срока вкладчик получит

1325. 22

руб.

Рис. 3-9 Фрагмент

листа Excel с решением

задачи

2-й вариант:

Построим таблицу исходных данных, подобную таблице, приведенной на рис.3-10

Рис. 3-10 Таблица исходных

данных примера 3-5



Установите курсор в

ячейку

«В7»;



вызовите функцию

СУММ()

(категория функций

«Математические»)

либо



щелкните на пиктограмме

вызовите функцию

БС() –

(

категория функций

Финансовые»

Рис. 3-11 Диалоговое окно функции

БС()

Напомним, что по условиям задачи платежи и начисления процентов производятся в конце года.

Таким образом, наращение первой вложенной суммы будет проходить в течение 5-1 =4 лет; наращение второй

вложенной суммы – 5-2= 3 года и т.д



В диалоговом окне функции:



в строке «Ставка» запишите величину годовой процентной ставки;



в строке «Кпер» сделайте ссылку на массив ячеек, содержащих значения

периодов начисления на платежи

(«А2:А6

»);



в строке «

ПС

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

(В2:В6);



Нажмите клавиши

Ctrl+Shift+Enter

В результате выполненных действий в ячейке «

В7»

будет записана формула

={СУММ(БС(12%;5-A2:A6;;B2:B6))}

=1325.22 руб.

3.3.4. Определение величины периодического платежа. Функция ПЛТ()

Пример 3-6

Решим предыдущую задачу, используя функцию

ПЛТ()

Разместим исходные данные задачи в таблице подобной приведенной на рисунке.

Рис. 3-12 Таблица Excel с исходными

данными задачи

Для решения задачи:



Установить курсор в ячейку

В4

(Плт).



Из категории функций

«Финансовые» вызвать

функцию

ПЛТ().



В диалоговом окне функции

сделать необходимые ссылки

на ячейки таблицы исходных

данных



Щелкнуть на кнопке «

ОК

Рис. 3-13 Диалоговое окно

функции ПЛТ()

Напомним, одновременное нажатие клавиш Ctrl+Shift+Enter позволяет ввести формулу как

формулу для массива данных.

Обратите внимание, что формула заключена в фигурные скобки, что характеризует ее как формулу

массива.

В результате выполненных действий в ячейке «

В4

» будет размещена найденная

сумма ежемесячного платежа.

Рис. 3-14 Таблица исходных данных с

найденной величиной ежемесячного платежа

(ячейка В4)

Пример 3-7

Как только Вам исполнилось 20 лет Вы решили ежемесячно вносить в банк по 25

долларов США.

В каком возрасте Вы сможете стать миллионером, если ставка банка – 15%,

начисляемых ежемесячно по схеме сложных процентов.

Решение.

Для решения задачи может быть использована финансовая функция

КПЕР().

Рис. 3-15 Диалоговое окно

функции КПЕР()

В строку

Ставка

диалогового окна функции

КПЕР()

заносится величина годовой

процентной ставки, деленной на количество платежей в течении года ( в нашей задаче –12

платежей).

В строку «Плт» заносится величина ежемесячных платежей

В строку «Бс» заносится величина будущей наращенной суммы ( в нашей задача

1000000 руб.

После щелчка на кнопке «

ОК

» в ячейку, где была размещена формула, будет

занесено количество платежей, необходимых для накопления желаемой суммы =

500.43.

Т.е. через 500.43месяцев или 500.43/12 = 41,7 года Вам потребуется для накопления

суммы 1000000 долларов США. Таким образом, Когда Вам исполнится 62 года Вы станете

миллионером!

3.3.5. Расчет платежей по процентам. Функция ПРПЛТ()

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

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

ставки.

Пример 3-8

Необходимо определить величину платежей по процентам за первый месяц

трехгодичного займа в 800тыс.руб. Ставка банка 10%.

Решение.

=ПРПЛТ(10%/12;1;3*

12;-800)

Рис. 3-16 Диалоговое

окно функции ПРПЛТ() с

решением примера 3-8



В поле «Ставка»

диалогового окна заносится

величина месячной процентной ставки;



в поле «Период» заносится номер периода для которого мы хотим определить

величину платежей по процентам;



в поле «Кпер»заносится количество периодов начисления процентов ( в нашем

примере 3*12);



в поле «Пс» заносится величина займа.

После нажатия кнопки «ОК» мы получим, что платежи по процентам за первый

месяц составили -6.66 тыс. руб

Пример 3-9

За счет ежегодных отчислений в течении 6 лет был создан фонд в 5 млн. руб.

Необходимо определить какой доход принесли вложения за последний год, если

ставка банка составляла 12%

Решение

=ПРПЛТ(12%;6;6;;5) =0,469млн. руб

Рис. 3-17 Решение

примера 3-9

3.3.6. Расчет суммы платежей по процентам по займу. Функция ОБЩПЛАТ()

Функция позволяет вычислить накопленный

доход (сумму платежей по процентам)

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

периода, между двумя периодами выплат.

Синтаксис функции: ОБЩПЛАТ (ставка; Кол_пер; Нз

; нач_период;

кон_период;тип)

Пример 3-10

Для приобретения недвижимости была взята ссуда 12 000тыс. руб, Условия ссуды:

Процентная ставка -9%;

Срок - 25 лет

Проценты начисляются ежемесячно

Необходимо найти сумму выплат за 2-й год и за 1 -й месяц займа

Решение

В диалоговом окне функции ОБЩПЛАТ() :

В строке «

Ставка

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

(9%/12);

в строке «

Кол_пер

» записывается количество периодов начисления платежей

(25*12);

в строке «

Нз

» записывается величина займа;

в строках «

Нач_период

» и «

Кон_период

» записываются начальный и конечный

периоды, для которых вычисляется сумма выплат по процентам (13 и 24), соответственно;

После щелчка на кнопке «

ОК

» будет рассчитана сумма платежей по процентам за

второй год:

=ОБЩПЛАТ(9%/12;25*12;12000;13;24;0) =-1062тыс. руб.

Рис. 3-18 Диалоговое окно функции

ОБЩПЛАТ() с введенными условиями

расчета суммы выплат по процентам за

второй год.

Аналогичным образом может быть вычислена сумма выплат по процентам за

первый месяц займа:

=ОБЩПЛАТ(9%/12;25*1;12000;1;1;0)=-90тыс. руб.

Аргумент Нз функции эквивалентен, аргументу Пс, использовавшемуся в других финансовых

функциях.

Рис. 3-19 Диалоговое окно

функции ОБЩПЛАТ() с

введенными условиями расчета

суммы выплат по процентам за

первый месяц

3.3.7. Расчет величины основных платежей по займу. Функция ОСПЛТ()

Функция позволяет вычислить величину основного платежа (выплаты) по займу,

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

расчетный период.

Пример 3-11

Была взята ссуда в размере 70000тыс. руб. сроком на 3 года под 17% годовых.

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

Решение

Напомним, что сумма основного платежа по займу получается как разность между

фиксированной периодической выплатой и процентами по непогашенной части долга.

Размер основных выплат по займу, определяемый с помощью функции

ОСПЛТ()

может быть определен как:

Период Формула

Основной

платеж

1-й год

=ОСПЛТ(17%;1;3;70000)

-19 780.16р.

2-й год

=ОСПЛТ(17%;2;3;70000)

-23 142.78р.

3-й год

ОСПЛТ(17%;3;3;70000) -27 077.06р.

ИТОГО

-70 000.00р.

Рис. 3-20 Диалоговое окно

функции ОСПЛТ() с данными

расчета основного платежа за 1-й

период займа