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

Пример 1.11

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

=БЗ(0,1; 4; -10000; -50000) (Результат: 119615,00).

Соответственно  изменится и формат функции для  определения величины ежегодного платежа:

=ППЛАТ(0,1; 4; -50000; 119615) (Результат: -10000,00).

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

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

=БС(0,1; 4; -10000; -50000; 1) (Результат: 124256,00).

=ППЛАТ(0,1; 4; -50000; 124256; 1) (Результат: -10000,00).

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

При начислении процентов m-раз в году, величины r и n корректируются также, как и в предыдущих примерах.

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

На рис. 1.7 приведен один из простейших вариантов подобного  шаблона, который может быть взят за основу. Формулы шаблона приведены в табл. 1.3.

Таблица 1.3

Формула шаблона (аннуитеты)

Ячейка	Формула
В15	=БС(B5/B6;B7*B6;B10;B8;B11)
В16	=НОРМА(B7*B6;B10;B8;B9;B11)
В17	=B16*B6
B18	=КПЕР(B5/B6;B10;B8;B9;B11)
В19	=ПС(B5/B6;B7*B6;B10;B9;B11)
В20	=ППЛАТ(B5/B6;B7*B6;B8;B9;B11)

Рис. 1.7. Шаблон для анализа аннуитетов

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

Пример 1.12

Корпорация  планирует ежегодно в течении 10 лет делать отчисления по 5000 для создания фонда выкупа своих облигаций. Средства помещаются в банк под 12% годовых. Какая сумма будет накоплена к концу срока операции?

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

Рис. 1.8. Решение  пример а 1.12

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

2.3 Денежные потоки в виде серии платежей произвольной величины

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

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

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

. (1.19)

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

. (1.20)

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

, (1.21)

где CF – периодический платеж по аннуитету, эквивалентному произвольному денежному потоку по величине современной стоимости.

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

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

Таблица 1.4

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

Наименование  функции		Формат  функции
Оригинальная версия	Локализованная версия
NPV	НПЗ	НПЗ(ставка; платежи)
IRR	ВНДОХ	ВНДОХ(платежи; [прогноз])
MIRR	МВСД	МВСД(платежи;ставка;ставка_реин)
XNPV	ЧИСТНЗ	ЧИСТНЗ(ставка; платежи; даты)
XIRR	ЧИСТВНДОХ	ЧИСТВНДОХ(платежи;даты;[прогноз])

Обязательные  для задания аргументы функций  имеют следующие значения:

ставка – процентная ставка (норма прибыли или цена капитала);

платежи – поток из n - платежей произвольной величины;

ставка_реин – ставка реинвестирования полученных средств;

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

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

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

Функция НПЗ() вычисляет современную величину потока платежей PV. Две другие функции – ВНДОХ() и МВСД() позволяют определить внутреннюю норму рентабельности инвестиций (internal rate of return – IRR) и модифицированную внутреннюю норму рентабельности инвестиций (modified internal rate of return – MIRR) соответственно.

Функции ЧИСТНЗ( ) и ЧИСТВНДОХ( ) являются самыми мощными в рассматриваемой группе. Они позволяют определить показатели чистой современной стоимости (net present value – NPV) и внутренней нормы рентабельности IRR для потоков платежей произвольной величины осуществляемых за любые промежутки времени. Эти функции удобно использовать для ретроспективного анализа эффективности операций с ценными бумагами, периодический доход по которым выплачивается по плавающей ставке (например – ОГСЗ, ОФЗ и т.д.), при анализе любых разновеликих потоков и оттоков платежей.

 

3 Выбор оптимального вида финансирования для ООО ПТФ «Энергетик» с помощью пакета Microsoft Excel

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

ООО "Альфа" выполняет заказы любого масштаба – от монтажа или замены отдельных элементов низковольтной аппаратуры до комплексных работ по проектированию и монтажу систем электроснабжения жилых зданий, котеджных поселков, зданий и сооружений производственного и бытового назначения.

Компания  предлагает своим заказчикам следующие виды работ:

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

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

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

Так как кредитные  и лизинговые платежи представляю собой аннуитеты, то мы используем шаблон Excel для анализа аннуитетов. Заносим туда всю заданную информацию и оцениваем результат. В качестве исходных данных будут выступать: Величина процентной ставки 25%, сумма договора 1млн. руб., срок финансирования 3года, с ежемесячными равными платежами, аванс для лизинга составляет 20% (см. приложения).

Предлагаются  настоящие условия

1. планируемый кредитный график платежей в соответствии с проектом кредитного договора.

Мес.	Долг	Погашение основного  долга	Погашение процентов	Платеж
1	1000 000,00	18 926,49	20 833,33	39 759,83
2	981 073,51	19 320,79	20 439,03	39 759,83
3	961 752,71	19 723,31	20 036,51	39 759,83
4	942 029,40	20 134,21	19 625,61	39 759,83
5	921 895,19	20 553,68	19 206,15	39 759,83
6	901 341,51	20 981,88	18 777,95	39 759,83
7	880 359,63	21 419,00	18 340,83	39 759,83
8	858 940,63	21 865,23	17 894,60	39 759,83
9	837 075,41	22 320,75	17 439,07	39 759,83
10	814 754,65	22 785,77	16 974,06	39 759,83
11	791 968,88	23 260,47	16 499,35	39 759,83
12	768 708,41	23 745,07	16 014,76	39 759,83
13	744 963,34	24 239,76	15 520,07	39 759,83
14	720 723,58	24 744,75	15 015,07	39 759,83
15	695 978,83	25 260,27	14 499,56	39 759,83
16	670 718,56	25 786,52	13 973,30	39 759,83
17	644 932,04	26 323,74	13 436,08	39 759,83
18	618 608,30	26 872,15	12 887,67	39 759,83
19	591 736,15	27 431,99	12 327,84	39 759,83
20	564 304,16	28 003,49	11 756,34	39 759,83
21	536 300,67	28 586,90	11 172,93	39 759,83
22	507 713,77	29 182,46	10 577,37	39 759,83
23	478 531,32	29 790,42	9969,40	39 759,83
24	448 740,89	30 411,06	9348,77	39 759,83
25	418 329,84	31 044,62	8715,20	39 759,83
26	387 285,21	31 691,38	8068,44	39 759,83
27	355 593,83	32 351,62	7408,20	39 759,83
28	323 242,21	33 025,61	6734,21	39 759,83
29	290 216,60	33 713,65	6046,18	39 759,83
30	256 502,95	34 416,01	5343,81	39 759,83
31	222 086,93	35 133,01	4626,81	39 759,83
32	186 953,92	35 864,95	3894,87	39 759,83
33	151 088,97	36 612,14	3147,69	39 759,83
34	114 476,83	37 374,89	2384,93	39 759,83
35	77 101,94	38 153,54	1606,29	39 759,83
36	38 948,40	38 948,40	811,43	39 759,83

 

 

 

 

 

2. Планируемый лизинговый график платежей в соответствии с проектом лизингового договора

Месяц	Сумма платежа	В т.ч. НДС
Аванс	200 000	30 508
1	31 797	4 850
2	31 797	4 850
3	31 797	4 850
4	31 797	4 850
5	31 797	4 850
6	31 797	4 850
7	31 797	4 850
8	31 797	4 850
9	31 797	4 850
10	31 797	4 850
11	31 797	4 850
12	31 797	4 850
13	31 797	4 850
14	31 797	4 850
15	31 797	4 850
16	31 797	4 850
17	31 797	4 850
18	31 797	4 850
19	31 797	4 850
20	31 797	4 850
21	31 797	4 850
22	31 797	4 850
23	31 797	4 850
24	31 797	4 850
25	31 797	4 850
26	31 797	4 850
27	31 797	4 850
28	31 797	4 850
29	31 797	4 850
30	31 797	4 850
31	31 797	4 850
32	31 797	4 850
33	31 797	4 850
34	31 797	4 850
35	31 797	4 850
36	31 797	4 850
ИТОГО	1 344 700	205 124