Финансовый анализ

Финансовые функции для вычисления будущего значения

Как мы только что убедились, финансовые вычисления можно проводить с использованием стандартных возможностей электронных таблиц. Однако в категории Финансовые мастера функций имеется около 50 специальных финансовых функций. В частности, для решения задачи, поставленной в предыдущем примере, можно использовать функцию БС (будущая сумма). Ее синтаксис:

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

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

Кпер — это общее число периодов платежей по аннуитету.

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

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

Тип — это число 0 или 1, обозначающее, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. Если этот аргумент опущен, то он полагается равным 0.

Особенностью всех финансовых функций Excel является результат со знаком минус, если сумму придется отдавать, и со знаком плюс, если это сумма, которую получают.

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

В результате в ячейке В12 вычисляется значение -2 238 904,11р. Знак минус показывает, что эта сумма подлежит возврату.

Функция БC позволяет использовать в качестве аргументов не только адреса ячеек, но и параметры. Сама функция может находиться в любой ячейке ЭТ.

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

=БС(20%*("31.08.2009"-"25.01.2009")/365;1;;2000000).

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

Сложные проценты

В этом случае в договорах указывается годовая ставка r и количество начислений процентов. Начисление производится каждый раз на наращенную сумму. В этом случае сумма долга к концу первого периода будет равна

S1 = P+P×r = P(1+r)

К концу второго периода

S2 = S1+S1×r = S(1+r) = P(1+r)2

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

S=P(1+r)n

Задание 3

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

Решение

Р = 100 000 руб, r = 20 %, n = 3 года.

S = Р(1+r)n = 100 000(1+0,2)3 = 172 800 (руб).

Задание 4

Ссуда в 10 000 долларов дана в долг на 2 года под 12 % годовых с ежеквартальным начислением. Какова будет сумма накопленного долга?

Решение

Р = $10 000, r = 12 %, n = 2 года.

Так как процентная ставка дана годовая, а период начисления процентов — квартал, сначала рассчитаем процентную ставку за период, она равна:

rn = r/n = 12%/4 = 3%

Тогда число периодов (кварталов) равно 2×4=8. Накопленный долг равен

S = Р(1+rn)n = 10 000(1+0,03)8 = 12 666,7 (долларов).

Для вычисления по схеме сложных процентов в Excel используется функция БC.

Задание 5

Ссуда в размере 30000 долларов дана на три года под ставку 32 % годовых с ежеквартальным начислением. Определить сумму конечного платежа.

Решение

Здесь базовый период - квартал. В году четыре квартала, значит срок ссуды 3*4=12 периодов. За один период выплачивается 32 %/4=8 %. Тогда формула для решения задачи примет вид

=БC(32%/4;3*4;;30000).

Она возвращает результат -75 545,10. Знак «минус» означает, эта сумма подлежит возврату.

Задание 6

Банк принимает вклады на срок 3 месяца с объявлением годовой ставки 100 % или на шесть месяцев под 110 %. Как выгоднее вкладывать деньги на полгода: дважды на три месяца или один раз на шесть месяцев?

Решение

Считаем, что вклад равен 1 000 руб. Вычислим наращение суммы для обеих предлагаемых схем вклада. Так как деньги вкладчик отдает банку, начальное значение должно иметь знак «минус»!

Для первой схемы получаем формулу

=БС(100%*3/12;2;;-1000).

Она возвращает результат 1 562,50 руб.

Для второй схемы формула

=БС(110%*6/12;1;;-1000) возвращает результат 1550 руб.

Значит, вклад по первой схеме выгоднее.

Постоянные ренты

Рента - это финансовая схема с многократными взносами или выплатами R1 = R2 = ... = Rn, разделенными равными промежутками времени. Для вычисления ренты также можно использовать функцию БС.

Задание 7

На счет в банке вносится сумма 10 000 долларов в течение 10 лет равными долями в конце каждого года. Годовая ставка 4 %. Какая сумма будет на счету через 10 лет?

Решение

Каждый год нужно вносить 10 000/10=1 000 (долларов). Это будет значение аргумента «Плт». Аргумент «Пс» отсутствует, его можно опустить. Аргумент «Тип» равен нулю, так как выплаты проводятся в конце периода. Функция имеет вид

=БС(4%;10;-1000;;0).

Она возвращает результат $12 006,11. Поскольку аргумент «Тип» равен 0, его можно опустить. Тогда выражение примет вид

=БС(4%;10;-1000).

Задание 8

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

Решение

В этом случае аргумент «Тип» равен единице. Формула имеет вид

=БС(4%;10;-1000;;1) и возвращает результат $12 486,35.

Задание 9

Рассматриваются две схемы вложения денег на три года: в начале каждого года под 24 % годовых или в конце каждого года под 36 %. Ежегодно вносится 4 000 руб. Какая схема выгодней?

Для первой схемы имеем формулу

=БС(24%;3;-4000;;1) с результатом 18 736,9 руб.

Для второй схемы функция имеет вид =БС(36%;3;-4000) и возвращает результат 16 838,4 руб.

Таким образом, первая схема выгоднее.

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

Теперь рассмотрим, как по будущему значению определить текущее (современное) значение. В этом случае используется финансовая функция ПС (приведенная сумма).

Задание 10

Вкладчик собирается положить деньги в банк на четыре года под 25 % и накопить 15 000 руб. Какую сумму он должен вложить?

Решение

S = 15 000 руб, n=4, r=25 %.

Найдем

Р = S/(1+r)n =15 000(1+0,25)4 = - 6 144 (руб).

(1 + г)п     (1 + 0,25)4

Теперь воспользуемся функцией ПC. Она примет вид =ПC(25%;4;;15000). Эта функция вернет значение – 6 144 руб (знак «минус» означает, что данная сумма отдается вкладчиком в банк).

Задание 11

Вексель на 5 млн долларов с процентной ставкой 10 % и начислением процентов дважды в год выдан на два года. Какую сумму можно получить под этот вексель?

Процентная ставка дана годовая, а проценты начисляются дважды в год. Значит, ставка за период равна 10 %/2=5 %. Число периодов 2*2=4. Рассчитаем, какую сумму можно получить сегодня, взяв на себя обязательство вернуть через два года 5 млн долларов.

S              5000000       лл„е„,

Р = S/(1+r)n =              5 000 000(1+0,05)4 = 4 113 512 (долларов).

Теперь проведем эти расчеты в Excel, введя в любую ячейку функцию ПЗ. Функция имеет вид =ПC(10%/2;2*2;;-5000000). Эта функция возвращает значение 4 113 512,37 руб.

Вычисление продолжительности ссуды

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

Для решения этой задачи удобно воспользоваться функцией КПЕР (количество периодов). Она имеет синтаксис

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

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

Плт — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.

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

Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0.

Тип — число 0 или 1, обозначающее, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. Если этот аргумент опущен, то он полагается равным 0.

Задание 12

За какой срок сумма, равная 30 000 рублей, достигнет 100 000 рублей при начислении процентов по сложной ставке 18 %?

Запишем функцию

=КПЕР(18%;;-30000;100000). Она возвращает значение 7,27 лет.

Вычисление процентной ставки

Если известны современное и будущее значения сумм, а также число периодов выплат, можно вычислить процентную ставку с использованием функции СТАВКА. Ее синтаксис:

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

Кпер — общее число периодов платежей по аннуитету.

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

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

Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0.

Тип — число 0 или 1, обозначающее, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. Если этот аргумент опущен, то он полагается равным 0.

Предположение — предполагаемая величина ставки. Если значение предположения опущено, то оно полагается равным 10 процентам.

Задание 13

В долг на полтора года дана сумма 5 000 долларов с условием возврата 8 000 долларов. Вычислить годовую процентную ставку.

Функция имеет вид =СТАВКА(1,5;;5000;-8000) и возвращает значение 37  %.

Расчет размера платежей ренты

Для расчета определенного члена ренты R в Excel используется функция ПЛТ. Ее синтаксис

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

Ставка — процентная ставка по ссуде.

Кпер — общее число выплат по ссуде.

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

Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение Бс равно 0.

Тип — число 0 или 1, обозначающее, когда должна производиться выплата.

Задание 14

Семья планирует взять в банке 6-месячную ссуду в размере $20 000. Какова будет величина месячных выплат при процентной ставке, равной 6 %?

Решение

1. Если в условии не указан тип процентной ставки, подразумевается, что это сложная процентная ставка. Итак, n=6 месяцев, P=$20 000, S=0, r=6 % годовых.

2. Ставка дана годовая, значит ее величина за период составит

6 % / 2 = 3 % = 0,03.

А за каждый период выплат (месяц) 3 % = 0,5 % = 0,05.

3. Если в условии задачи не оговаривается, в начале или в конце периода производятся выплаты, подразумевается, что они осуществляются в конце периода (т.е. аргумент тип равен 0 или опускается). Число периодов 6. В любую ячейку Excel вводим формулу

=ПЛТ(6%/12;6;20000;0). В результате получаем -3 391,91.

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

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

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

Период — это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до «Кпер».

Кпер — общее число периодов выплат годовой ренты.

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