Решение финансово-экономических задач средствами excel

14. Если Вы занимаете 30 000 рублей на два года под 8% годовых, то сколько всего денег Вы должны возвратить?

15. Если начальный баланс  на счете 6 000 рублей и ежемесячный взнос 500 рублей (в конце каждого месяца), то сколько можно накопить за три года при ставке 0,75% в месяц?

16. Имеется возможность  приобретения недвижимости, выплатив  строго фиксированную сумму 1 500 000 руб. равномерными авансовыми месячными платежами по 15 000 руб. в течение некоторого периода. В дальнейшем, через 5 лет, недвижимость предполагается продать. Какой на этот момент должна быть ее цена, если планируется за весь срок получить доход, равный 1% в месяц?

17. Финансовая компания  создает фонд для погашения  обязательств путем помещения  в банк суммы в 60 000 руб., с последующим ежегодным пополнением суммами по 10 000 руб. Ставка по депозиту равна 12% годовых. Какова будет величина фонда к концу 6-го года?

 

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

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

Текущая стоимость получается как результат приведения будущих доходов и расходов к начальному периоду времени. Функции Excel, относящиеся к данной теме – ПС (ставка; кпер; плт; бс; тип), ЧПС (ставка; значения), ЧИСТНЗ (ставка; значения; даты).

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

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

Функция ЧИСТНЗ применяется, если денежные потоки представлены в виде платежей произвольной величины, осуществляемых за любые промежутки времени.

Задача 1.

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

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

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

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

При непосредственном вводе данных получается то же значение вклада:

= ПС (12%; 3; ; 500000) = - 355 890,12 руб.

Рис. 4.6. Фрагмент окна Excel с панелью функции ПС

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

          (4.6)

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

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

Задача 2.

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

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

 

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

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

= ПС( 20%; 5; 5000; 0; 0) = -14 953,06 руб.

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

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

   (4.7),

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

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

Кпер – общее число периодов выплат (поступлений);

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

Вычисления по формуле (4.7) дают то же значение (без учета знака):

Задача 3.

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

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

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

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

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

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

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

Чистая текущая стоимость проекта к началу первого года составляет:

= ЧПС (10%; -20000; 6000; 8200; 12600; 18800) = 13 216,93 руб.

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

 

Рис. 4.7. Фрагмент окна Excel с панелью функции ЧПС

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

       (4.8),

где: ЧПС – чистая текущая стоимость периодических выплат и поступлений;

Значениеi – суммарный размер i-го денежного потока на конец периода (поступления – со знаком «плюс», выплаты – со знаком «минус»);

Ставка – норма дисконтирования за один период;

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

i – номер периода денежного потока.

Аналитический расчет задачи дает аналогичный результат:

Задача 4.

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

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

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

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

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

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

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

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

 

 

 

 

 

Непосредственное задание параметров в формулах расчета, как и вычисления с использованием формулы (4.8),  дают те же результаты.

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

= ЧПС (15%; 100000; 190000; 270000; 300000; 350000) – 550000 = 203 691,03р.

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

= ЧПС (15%; 150000; 230000; 470000; 180000; 320000) – 650000 = 225 392,59р.

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

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

1) в функции ПС периодические выплаты предполагаются одинаковыми, а в функции ЧПС они могут быть различными;

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

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

Примечание.

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

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

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

   (4.9),

где:  ЧПС(n) – чистая приведенная эффективность исходного проекта, найденная с учетом предварительных инвестиций;

n – длительность исходного проекта;

i – число повторов исходного проекта;

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

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

  (4.10)

Задача 5.

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

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

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

Для решения задачи предварительно рассчитаем чистую приведенную стоимость проектов при их однократном выполнении, воспользовавшись функцией ЧПС и вычтя предварительные инвестиции. Затем, принимая во внимание разную продолжительность проектов, рассчитаем значения эффективности проектов по формулам (4.9) и (4.10).

При однократном выполнении проектов предпочтительным выходит второй проект (ЧПС1 = 3,306; ЧПС2 = 4,046). Но такой вывод преждевременный (рис. 4.9).

Расчет эффективности проектов за 6 лет, а также при их бесконечном повторении дает результат полностью противоположный – более привлекательным является первый проект:

ЧПС1(2,3) = 8,296    ЧПС2(3,2) = 7,086

ЧПС1(2,∞) = 19,048     ЧПС2(3, ∞) = 16,269  

 

Задача 6.

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

Определить чистую текущую стоимость по проекту на 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.9. Иллюстрация оценки эффективности инвестиционных проектов разной продолжительности

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

Поскольку в данном случае имеем дело с нерегулярными переменными расходами и доходами, для расчета чистой текущей стоимости по проекту на 5.04.2005 г. необходимо применить функцию ЧИСТНЗ.

Расчет чистой текущей стоимости нерегулярных переменных расходов и доходов с помощью функции ЧИСТНЗ осуществляется по формуле:

     (4.11),

где: Чистнз – чистая текущая стоимость нерегулярных переменных выплат и поступлений;

Ставка – норма дисконтирования;

d1 –  дата 0-й операции (начальная дата);

di – дата i-й операции;

Значениеi – суммарное значение i–й операции;

n – количество выплат и поступлений.

Для нахождения решения задачи предварительно построим таблицу с исходными данными. Рассчитаем рядом в столбце число дней, прошедших от начальной даты до соответствующей выплаты. Затем найдем требуемый результат – с помощью функции ЧИСТНЗ и по формуле (4.11). Получим значение – 4 267 559 руб. 31 коп. Иллюстрация решения приведена на рис. 4.10.

Непосредственный ввод параметров в ЧИСТНЗ дает тот же результат:

=ЧИСТНЗ (8%;{0;-90;10;20;30;40}; B4:B8) =  4,26755931 млн. руб.

Вычисление решения задачи по формуле (4.11):

Примечания.

1. При явной форме записи  функции ЧИСТНЗ нельзя непосредственно указывать в каком бы то ни было допустимом формате массив дат в качестве ее параметров. Обязательно следует ссылаться на ячейки, где эти даты приведены.

2. Аналитические вычисления  по формулам следует выполнять  на листе Excel (а не на калькуляторе).

 

 

 

 

 

Рис. 4.10. Иллюстрация примера использования функции ЧИСТНЗ  

Задания для самостоятельной работы

Показатели	Проект 1	Проект 2
Инвестиции	740000	800000
Доходы:
1 год	280000	320000
2 год	340000	340000
3 год	350000	380000