Кривелевич М.Е. Долгосрочная финансовая политика
Подождите немного. Документ загружается.
9. ОСПЛТ - возвращает величину платежа в погашение основной суммы по инвестиции за
данный период на основе постоянства периодических платежей и постоянства процентной
ставки.
9.1. Синтаксис: ОСПЛТ (ставка; период; кпер; пс; бс; тип)
9.2. Пример расчета:
Данные Описание
10% Годовая процентная ставка
2 Срок займа в годах
2000 Сумма займа
Формула Результат
=ОСПЛТ(A2/12; 1; A3*12; A4) Величина платежа в погашение основной суммы за
первый месяц указанного займа (-75,62)
Очевидно, что сумма процентных платежей и платежей в погашение основной суммы
долга составляет всю периодическую выплату.
ПРПЛТ + ОСПЛТ = ПЛТ
10. ОБЩПЛАТ - возвращает кумулятивную (нарастающим итогом) величину процентов,
выплачиваемых по займу в промежутке между двумя периодами выплат.
10.1.Синтаксис: ОБЩПЛАТ(ставка; кол_пер; нз; нач_период; кон_период; тип)
10.2. Примечания. Аргументы Кол_пер, нач_период, кон_период и тип усекаются до
целых. Если ставка ≤ 0, кол_пер ≤ 0 или нз ≤ 0, то функция ОБЩПЛАТ возвращает значение
ошибки #ЧИСЛО!. Если нач_период < 1, кон_период < 1 или нач_период > кон_период, то
функция ОБЩПЛАТ возвращает значение ошибки #ЧИСЛО!. Если тип является любым
числом, кроме 0 и 1, то функция ОБЩПЛАТ возвращает значение ошибки #ЧИСЛО!.
10.3 Пример расчета:
Данные Описание
9% Годовая процентная ставка
30 Срок займа в годах
125 000 Стоимость на текущий момент
Формула Результат
=ОБЩПЛАТ(A2/12;A3*12;A4;13;24;0) Кумулятивная выплата за второй год, с 13 периода
до 24 (-11135,23)
=ОБЩПЛАТ(A2/12;A3*12;A4;1;1;0) Кумулятивная за первый месяц (-937,50)
11. ОБЩДОХОД - Возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую
в погашение основной суммы займа в промежутке между двумя периодами. Функция
полностью аналогична ОБЩПЛАТ и в дополнительных комментариях не нуждается.
Пример шаблона для разработки планов погашения кредитов приведен на рисунке 7.
Рис. 7. Шаблон для разработки планов погашения кредитов
Первая часть этого шаблона предназначена для ввода условий, на основании которых
получен (выдан) кредит, т.е. для задания величин PV, r, n. Кроме того, как и в предыдущих
случаях, необходимо предусмотреть вариант выплат процентов m раз в году, а также
различные типы начисления процентов - в начале или в конце каждого периода. По
умолчанию определим: m = 1, тип начисления - 0 (конец периода).
Для записи исходных данных удобно использовать табличную форму с более
компактным и наглядным их представлением. С учетом оформления, заголовков и таблицы
для ввода исходных данных эта часть шаблона будет занимать первые шесть строк таблицы.
Перед тем как приступить к проектированию второй части шаблона, целесообразно
выполнить еще одну полезную операцию - определить собственные имена для ячеек, в
которые будут вводиться исходные данные. Предлагаемые имена для ячеек приведены в
таблице 5.
Таблица 5. Имена ячеек шаблона
Ячейка Имя
А6 Сумма
В6 Срок
С6 Выплат
D6 Ставка
Е6 Тип
Напомним, что в EXCEL ячейкам можно присваивать символические имена,
определяемые пользователем. Эти имена могут использоваться в качестве адресных ссылок
на ячейки, блоки, отдельные значения или формулы. Определение имен - своего рода
правило хорошего тона и дает целый ряд преимуществ. Например, формула
=Количество*Цена несет в себе гораздо больше информации, чем формула =А1*В1. В свою
очередь формулу в ячейке можно также задать именем, например, =Выручка,
предварительно определив ее как =Количество*Цена или =А1*В1. В общем случае
символические имена (именные ссылки) могут быть использованы везде, где можно
применить обычные адресные ссылки EXCEL.
При определении имен следует руководствоваться правилами:
• имя должно начинаться с буквы или символа _;
• использование пробелов в именах недопустимо, в качестве разделителей слов
следует применять знак (например, Число_выплат);
• длина имени не должна превышать 255 символов.
Существует несколько способов определения имен. Наиболее простой -
использование окна имен, которое расположено в левой части строки ввода EXCEL.
По умолчанию, если имена в рабочей книге не определены, окно имени всегда
показывает адрес активной ячейки (например, в новой таблице его содержимым будет
ссылка на первую ячейку - А1). Для того чтобы определить имя для ячейки, необходимо
выполнить следующие действия:
1) сделать ячейку активной (т.е. установить в нее указатель);
2) щелкнуть мышью по окну имен. При этом ссылка на ячейку будет выделена, а
указатель примет вид вертикальной черты.
3) ввести с клавиатуры требуемое имя и нажать клавишу [ENTER].
После выполнения указанных действий при активизации данной ячейки в окне всегда
будет показано определенное для нее имя. Задание имен можно также осуществить в режиме
диалога, воспользовавшись пунктом Имя подменю Вставка главного меню EXCEL.
Руководствуясь любым способом, определите имена, приведенные в табл. 5, для
соответствующих ячеек шаблона. Продолжим его формирование.
Вторая часть шаблона должна содержать результаты вычислений по периодам. Ее
можно представить в виде таблицы, состоящей из шести граф: номер периода, баланс на
конец периода, сумма основного долга, сумма процентов, сумма накопленного долга, сумма
накопленных процентов. Формулы, используемые в шаблоне, приведены в таблице 6.
Таблица 6. Формулы шаблона
Ячейка Формула
С9 = - ПЛТ (Ставка/Выплат ; Срок* Выплат; Сумма; ; Тип)
F9 =Срок*Выплат
В12 =Сумма - Е12
С12 = - ОСПЛТ (Ставка/Выплат ; А12 ; Срок* Выплат ; Сумма; ; Тип)
D12 = - ПРПЛТ (Ставка/Выплат ; А12 ; Срок*Выплат ; Сумма; ; Тип)
Е12 = - ОБЩДОХОД( Ставка/Выплат; Срок*Выплат ; Сумма; 1; А12; Тип)
F12 = - ОБЩПЛАТ (Ставка/Выплат; Срок* Выплат ; Сумма; 1; А12; Тип)
Обратите внимание на то, что все функции заданы с отрицательным знаком. Это
обеспечивает возможность ввода исходных данных и получения результатов вычислений в
виде положительных величин, избавляя нас от проблем интерпретации знаков. Кроме того,
требование ввода исходных данных в виде положительных величин обусловлено
спецификой форматов функций ОБЩПЛАТ и ОБЩДОХОД. Наличие ошибок в блоке
формул В12. F12 связано с отсутствием исходных данных.
Сформированный шаблон требует дополнительных пояснений. Выполняя операции
по формированию шаблона, вы уже обратили внимание на способ указания имен ячеек при
задании формул. При разработке универсального шаблона для автоматизации расчетов по
составлению планов погашения долгосрочных кредитов мы заранее не можем знать, какие
сроки проведения операции будут предусмотрены тем или иным контрактом. Известно
лишь, что сроки проведения подобных операций составляют не менее одного года (периода).
Поэтому при разработке шаблона необходимо предусмотреть возможность выполнения
необходимых расчетов по крайней мере для минимально возможного срока проведения
операции n = 1. Именно такая " базовая" таблица - шаблон и была сформирована в результате
выполнения описанных выше действий. Имея базовый шаблон, можно легко получить
таблицу для любого числа периодов, скопировав необходимое количество раз формулы
блока В12 . F12.
Однако в случае использования обычной (относительной) адресации ячеек при
выполнении команды копирования произойдет автоматическая перенастройка адресов ячеек
в формулах относительно начала блока - получателя, что приведет к искажению общего
смысла и ошибкам в вычислениях.
Напомним, что параметры PV, r, n, m, тип, принимающие участие в расчетах,
являются постоянными на протяжении всего срока проведения операции, тогда как номер
периода t должен изменяться от 1 до m × n. Поэтому после выполнения команды
копирования при относительном способе адресации только номер периода (изменяемый
параметр) в функциях будет указан правильно. Чтобы избежать подобных коллизий в
формулах, содержащих постоянные параметры (РV, r, n, m, тип), необходимо использовать
метод абсолютной адресации ячеек. Этот вид адресации и обеспечивают в данном случае
пользовательские имена, присвоенные ячейкам А6, В6, С6, D6, Е6 (таблицу 5 ). Кроме того,
применение пользовательских имен повышает наглядность формул, делая их более
понятными.
Ячейка С9 содержит формулу расчета периодического платежа, a F9 - общего числа
периодов проведения операции. Значение последней показывает нам также предел
копирования формул блока В12. F12.
Проверим работоспособность шаблона на следующем примере.
Банком выдан кредит в $150 000 на 3 года под 18,7% годовых, который должен быть
погашен равными долями, выплачиваемыми раз в конце каждого полугодия. Разработать
план погашения кредита.
Рассмотрим решение данного примера по этапам.
1. Введите исходные данные в блок ячеек А6.Е6. После ввода данных в ячейке С9 появится
результат расчета периодического платежа, а в F9 - общего числа периодов проведения
операции.
2. Сделайте активной ячейку А12. Выберите в главном меню тему Правка пункт Заполнить
подпункт Прогрессия. На экране появится диалоговая форма подпункта Прогрессия.
Сделайте активным переключатель по столбцам и щелкните левой клавишей мыши в
поле Предельное значение. Введите число периодов (ячейка F9) в поле Предельное
значение. Нажмите кнопку [ОК] или клавишу [ENTER] . Результатом выполнения этих
действий будет заполнение ячеек колонки А последовательным рядом чисел, начиная с
ячейки А12.
3. Скопируйте формулы из блока B12.F12 необходимое число раз.
Полученная в результате таблица будет иметь вид, показанный на рисунке 8.
Рисунок 8. Погашение банковского кредита (решение)
Указанные в п. 2 операции можно было выполнить и без использования главного
меню, произведя следующие действия:
1) сделать активной ячейку А12 и установить указатель мыши на ее нижний правый
угол. При этом указатель примет вид маркера заполнения - +;
2) нажать клавишу [CTRL] и не отпуская ее протащить мышью маркер заполнения
необходимое количество раз вниз (по колонке А). При этом в левом углу строки ввода будет
выводиться значение счетчика ряда.
Доработаем получившийся шаблон с использованием программных модулей,
реализованных на языке VBA (Visual Basic for Application), таким образом, чтобы добиться
маштабируемости шаблона. Это важный шаг в сторону облегчения работы пользователя. Что
бы такого доработать? Сразу бросается в глаза, то, что количество периодов в решении (то
есть строчек отведенных под решение) не зависит от условий задачи. Давайте это исправим
добавив в шаблон две кнопки «Решить» и «Очистить» и код VBA, приведенный ниже.
Пример кода для автоматизации шаблона погашения кредита:
Создать две кнопки со следующими свойствами:
Имя (Name) Надпись (Caption) Автозагрузка (Autoload)
CmdSolve Решить True
CmdClear Очистить True
Задать событию нажатие_на_кнопку «Решить» следующий исходный текст:
Private Sub CmdSolve_Click()
Const Start = 12 'Фиксация первой строки, с которой начинать заполнение
Dim Finish ' Создаем новую переменную, пока Finish=0
If [Ставка] * [Срок] * [Сумма] * [Выплат] > 0 Then
Finish = [F9].Value + Start - 1
Cells(Start, "A").Select
Selection.AutoFill Destination:=Range(Cells(Start, "A"), Cells(Finish, "A")),
Type:=xlFillSeries
Range(Cells(Start, "B"), Cells(Start, "F")).Select
Selection.Copy
Range(Cells(Start + 1, "B"), Cells(Finish, "B")).Select
ActiveSheet.Paste
Application.CutCopyMode = xlCopy
Else
MsgBox ("Заданы не все параметры!")
End If
End Sub
Задать событию нажатие_на_кнопку «Очистить» следующий исходный текст:
Private Sub CmdClear_Click()
Const Start = 12 'Фиксация первой строки, с которой начинать заполнение
Dim Finish ' Создаем новую переменную, пока Finish=0
Finish = [F9].Value + Start - 1
[Ставка] = ""
[Срок] = ""
[Сумма] = ""
[выплат] = 1
[Тип] = 0
If Start < Finish Then Range(Cells(Start + 1, "A"), Cells(Finish, "F")).ClearContents
End Sub
Теперь шаблон автоматически масштабируется под число периодов выплаты кредита
и все, что требуется от пользователя – это заполнить исходные данные и нажать «Решить», а
потом очистить шаблон, нажав «Очистить»
Глава 4. Расчеты, связанные с выбором инвестиционных
альтернатив
Рассмотренная в предыдущей главе концепция временной ценности денег, а также
связанные с ней методы исчисления характеристик денежных потоков служат тем
фундаментом, на котором базируются современные методики анализа эффективности
долгосрочных инвестиционных проектов.
Предмет изучения в настоящей главе - динамические методы, позволяющие учесть
фактор времени, так как они отражают наиболее современные подходы к оценке
эффективности инвестиций и преобладают в практике крупных и средних предприятий
развитых стран. В хозяйственной практике России применение этих методов обусловлено
также и высоким уровнем инфляции.
Динамические методы часто называют дисконтными, поскольку они базируются на
определении современной величины (т.е. на дисконтировании) денежных потоков,
связанных с реализацией инвестиционного проекта.
При этом делаются следующие допущения:
• потоки денежных средств на конец (начало) каждого периода реализации проекта
известны;
• определена оценка, выраженная в виде процентной ставки (нормы дисконта), в
соответствии с которой средства могут быть вложены в данный проект. В качестве
такой оценки обычно используются: средняя или предельная стоимость капитала
для предприятия; процентные ставки по долгосрочным кредитам; требуемая норма
доходности на вложенные средства и др.
Существенными факторами, оказывающими влияние на величину оценки, являются
инфляция и риск. В дальнейшем, говоря об оценке, мы будем абстрагироваться от ее
конкретного экономического содержания, используя термин норма дисконта.
Первым из рассмотренных в данной главе методов принятия решений будет расчет
чистой современной стоимости (NPV). Формула по которой рассчитывается NPV должна
быть известны читателям из курса «Инвестиции», тем не менее напомним:
∑
=
−
+
=
n
t
t
t
I
r
CF
NPV
1
0
1 )(
, (9)
где r - норма дисконта;
n - число периодов реализации проекта;
CF
t
- чистый поток платежей в периоде t;
I
0
- инвестиции нулевого периода
Общее правило NPV: если NPV > 0, то проект принимается, иначе его следует
отклонить. Кроме NPV мы также будем рассчитывать индекс рентабельности (PI) и
внутреннюю норму доходности проекта (IRR).
Расчет вручную показателей, базирующихся на дисконтных методах, достаточно
трудоемок. Поэтому при выполнении расчетов будем пользоваться специальной группой
финансовых функций EXCEL, предназначенных для автоматизации анализа эффективности
инвестиционных проектов (таблице 8).
Таблица 8. Функции расчета NPV, PI, IRR
Англоязычная версия Русская версия
(старое название)
Русская версия
(новое название)
NPV НПЗ ЧПС
IRR ВНДОХ ВСД
MIRR МВСД МВСД
XNPV ЧИСТНЗ ЧИСТНЗ
XIRR ЧИСТВНДОХ ЧИСТВНДОХ
12. ЧПС - возвращает величину чистой приведенной стоимости инвестиции, используя
ставку дисконтирования, а также стоимости будущих выплат (отрицательные значения) и
поступлений (положительные значения).
12.1. Синтаксис: ЧПС(ставка; значение1; значение2; ...),
где
- Ставка — ставка дисконтирования за один период.
- Значение1, значение2, ... — от 1 до 29 аргументов, представляющих расходы и
доходы.
• Значение1, значение2, ... должны быть равномерно распределены во времени,
выплаты должны осуществляться в конце каждого периода.
• ЧПС использует порядок аргументов значение1, значение2, ... для определения
порядка поступлений и платежей. Убедитесь в том, что ваши платежи и
поступления введены в правильном порядке.
• Аргументы, которые являются числами, пустыми ячейками, логическими
значениями или текстовыми представлениями чисел, учитываются; аргументы,
которые являются значениями ошибки или текстами, которые не могут быть
преобразованы в числа, игнорируются.
• Если аргумент является массивом или ссылкой, то учитываются только числа.
Пустые ячейки, логические значения, текст или значения ошибок в массиве или
ссылке игнорируются.
12.2 Примечания. Считается, что инвестиция, значение которой вычисляет
функция ЧПС, начинается за один период до даты денежного взноса значение 1 и
заканчивается с последним денежным взносом в списке. Вычисления функции ЧПС
базируются на будущих денежных взносах. Если первый денежный взнос приходится на
начало первого периода, то первое значение следует добавить к результату функции ЧПС, но
не включать в список аргументов.
Если n — это количество денежных потоков в списке значений, то формула для
функции ЧПС имеет вид:
(10)
ЧПС аналогична функции ПС (текущее значение). Основное различие между
функциями ПС и ЧПС заключается в том, что ПС допускает, чтобы денежные взносы
происходили либо в конце, либо в начале периода.
12.3. Пример расчета:
Данные Описание
8% Годовая ставка дисконтирования. Она может представлять собой темп
инфляции или процентную ставку по конкурирующим инвестициям.
-40 000 Начальные затраты на инвестиции
8 000 Доход за первый год
9 200 Доход за второй год
10 000 Доход за третий год
12 000 Доход за четвертый год
14 500 Доход за пятый год
Формула Результат
=ЧПС(A2;
A4:A8)+A3
Чистая приведенная стоимость этой инвестиции (1 922,06)
13. ВСД - Возвращает внутреннюю ставку доходности для ряда потоков денежных средств,
представленных их численными значениями. Эти денежные потоки не обязательно должны
быть равными по величине, как в случае аннуитета. Однако они должны иметь место через
равные промежутки времени, например ежемесячно или ежегодно. Внутренняя ставка
доходности — это процентная ставка, принимаемая для инвестиции, состоящей из платежей
(отрицательные величины) и доходов (положительные величины), которые осуществляются
в последовательные и одинаковые по продолжительности периоды.
13.1. Синтаксис: ВСД(значения; предположение)
Значения — это массив или ссылка на ячейки, содержащие числа, для которых
требуется подсчитать внутреннюю ставку доходности.
• Значения должны содержать, по крайней мере, одно положительное и одно
отрицательное значение.
• ВСД использует порядок значений для интерпретации порядка денежных выплат
или поступлений. Убедитесь, что значения выплат и поступлений введены в
правильном порядке.
• Если аргумент, который является массивом или ссылкой, содержит текст,
логические значения или пустые ячейки, то такие значения игнорируются.
Предположение — это величина, о которой предполагается, что она близка к
результату ВСД.
• Microsoft Excel использует метод итераций для вычисления ВСД. Начиная со
значения предположение, функция ВСД выполняет циклические вычисления, пока
не получит результат с точностью 0,00001 процента. Если функция ВСД не может
получить результат после 20 попыток, то выдается значение ошибки #ЧИСЛО!.
• В большинстве случаев нет необходимости задавать предположение для
вычислений с помощью функции ВСД. Если предположение опущено, то оно
полагается равным 0,1 (10 процентов).
• Если ВСД возвращает значение ошибки #ЧИСЛО! или если результат далек от
ожидаемого, можно попытаться выполнить вычисления еще раз с другим
значением аргумента предположение.
13.2. Примечания. ВСД тесно связана с функцией ЧПС. Ставка доходности,
вычисляемая ВСД, связана с нулевой чистой текущей стоимостью. Взаимосвязь функций
ЧПС и ВСД отражена в следующей формуле:
ЧПС(ВСД(B1:B6);B1:B6) = 3.60E-08 [Учитывая точность расчета для функции
ВСД, значение 3,60E-08 можно считать 0 (нулевым).]
13.3. Пример расчета:
Данные Описание
-70 000 Начальная стоимость бизнеса
12 000 Чистый доход за первый год
15 000 Чистый доход за второй год
18 000 Чистый доход за третий год
21 000 Чистый доход за четвертый год
26 000 Чистый доход за пятый год
Формула Результат
=ВСД(A2:A7) Внутренняя ставка доходности проекта (9%)
Развитием функции ВСД является функция МВСД, которая устраняет очень важный
недостаток предшественницы – допущение согласно которому реинвестирование средств
осуществляется на тех же условиях, что и основной проект. Предположим, вложив 1 млн. вы
открыли ресторан и обеспечили себе доходность 35% годовых. Через год вы подсчитали
прибыль и увидели, что на вырученные средства новый ресторан открыть нельзя, а вложить
их можно только на банковский вклад - максимум под 14% годовых.
14. МВСД - возвращает модифицированную внутреннюю ставку доходности для ряда
периодических денежных потоков. МВСД учитывает как затраты на привлечение
инвестиции, так и процент, получаемый от реинвестирования денежных средств.
14.1. Синтаксис: МВСД (значения; ставка_финанс; ставка_реинвест)
Значения — массив или ссылка на ячейки, содержащие числовые величины. Эти
числа представляют ряд денежных выплат (отрицательные значения) и поступлений
(положительные значения), происходящих в регулярные периоды времени.
Ставка_финанс — ставка процента, выплачиваемого за деньги, используемые в
денежных потоках.
Ставка_реинвест — ставка процента, получаемого на денежные потоки при их
реинвестировании.
14.2. Примечания. МВСД использует порядок расположения чисел в аргументе
значения для определения порядка выплат и поступлений. Убедитесь, что значения выплат и
поступлений введены в нужной последовательности и с правильными знаками
(положительные значения для получаемых денег и отрицательные значения для
выплачиваемых).
Если n — это количество чисел в аргументе значения, f — это ставка_финанс, а r —
это ставка_реинвест, то формула для вычисления функции МВСД будет иметь вид:
(11)
14.3. Пример расчета:
Данные Описание
-120 000 руб. Начальная стоимость
39 000 Доход за первый год
30 000 Доход за второй год
21 000 Доход за третий год
37 000 Доход за четвертый год
46 000 Доход за пятый год
10,00% Годовая процентная ставка по кредиту размером 120 000
12,00% Годовая процентная ставка по реинвестированным прибылям
Формула Описание (результат)
=МВСД(A2:A7; A8;
A9)
Модифицированная ставка доходности по инвестициям после
четырех лет (13%)
15. ЧИСТНЗ (ставка; платежи; даты)
Функция ЧИСТНЗ - самая мощная в своей группе. Она позволяет определить
показатель NPV для потоков с платежами произвольной величины, осуществляемых за
любые промежутки времени. Техника ее использования практически аналогична только что