Уокенбах Формулы в Excel

,, ; Д

A/c. 72.9. Вычисление БС с использованием функции ЧПС

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

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

ПРИМЕР9

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

Это типичная задача, в которой усредненный по времени один платеж заменяет ряд изменяемых платежей. Пример — соглашение, в котором план различных платежей арендной платы заменяется постоянными платежами. В примере, показанном на рис. 12.10, следующая формула возвращает (в ячейке С27) $10 923,24, что является значением платежа, которое неизменно и эффективно заменяет разные платежи в столбце В:

=ПЛТ(С7;С6;-В25;0;С8)

Пример этого раздела предоставляет гибкость пользователю в выборе ставки и частоты выплат. Проверка данных используется, чтобы позволить выбор: использовать эффективную или нормальную ставку в ячейке СЗ. Этот тип вычислений часто используется для получения альтернативных фиксированных и переменных арендных выплат.

Использование функцииВСД

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

ВСД(значения;предположения)

Рис. 12.10. Вычисление эквивалентных платежей с помощью ЧПС

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

представление данных плохо анализируется Excel, если текст содержит символы пробела, дефиса или (хуже всего) нуля.

В большинстве случаев, ВСД может быть подсчитав только итерацией. Аргумент предположения, если таковой используется, служит "основой" для итерационного вычисления. Доказано, что прогноз -0,9 всегда приводит к конечному ответу. Другие прогнозы, например 0, обычно (но не всегда) приводят к ответу.

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

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

ПРИМЕР 10

В этом примере задается основная матрица вычислений ВСД (рис. 12.11). В нем демонстрируется постоянная проблема частоты поступления платежей и определения ВСД для текущей частоты. Так, если платежи производятся ежемесячно, функция возвратит ежемесячную ВСД. В примере проводится проверка данных, что позволяет пользователю указать тип выплат (1, 2, 4, 12, 13, 26, 52, 365, 366). Сделанный выбор определяет способ преобразования процентной ставки, а также влияет на текст в ячейках строки 5, которые содержат формулы, использующие текст в ячейкеD3.

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

Следующая формула вячейке D2 2 проверяет достоверность ПОЛУЧЕННЫХ данных: =4nC(D20;D6:D18)*(1+D20)

ВСД — это учетная ставка, при которой денежные выплаты приводят к ЧПС, равному 0. Формула в ячейке D22 использует ВСД в функции ЧПС применимо ктем жевыплатам. Подстановка ВСД в ЧПС (квартально) приводит к результату $0,00 — так проверяется результат основных вычислений.

ПРИМЕР 11

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

Решение состоит в использовании функции ВСД для вычисления средней геометрической нормы прибыли. Это простое вычисление, которое заключается в определении отдельного процента запериод, который аналогичен нескольким другим.

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

=(В5-В4)/В4

Востальных столбцах показан средний геометрический темп прироста за различные периоды. Формулы в строке 10 применяют функцию ВСД для вычисления внутренней нормы прибыли. Например, формула вячейке F10 возвращает 5,241%:

=BCfl(F4:F8;-0,9)

Другими словами, темпы прироста 5,21%, 4,86% и 5,66% длятрех отдельных периодов эквивалентны среднему геометрическому темпу прироста 5,241% заодин общий период.

В вычислении ВСД учитываются направление платежей.

ПРИМЕР12

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

Рис. 12.13. Проверка ВСД и ЧПС в результате суммирования текущих значений

Чистая приведенная стоимость вычисляется вячейке В16:

= Ч П С ( D 3 ; В б : В 1 4 ) * ( 1 + D 3 )

Внутренняя ставка доходности вычисляется вячейке В17:

=ВСД(Вб:В14;-0/9)

В столбце С с помощью формул вычисляется текущее значение. Онииспользуют результат определения ВСД(вячейке В17) в качестве учетного процента, а количество периодов (в столбце А) в качестве показателя степени. Например, формула вячейке Сб следующая:

=В6* ( l + $B$17)/ v -A6

Сумма значений в столбце Сравна 0.

Формулы встолбце Dиспользуют учетную ставку (в ячейке D3)для вычисления текущего значения. Например, формула вячейке D6 следующая:

=B6*(1+$D$3)A-A6

Сумма значений в столбце Dравна чистой приведенной стоимости.

Для серьезных задач функций ЧПС и ВСД — это взаимозаменяемые средства перекрестного контроля.

Несколько ставок вфункциях ВСДиМВСД

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

ПРИМЕР 13

На рис. 12.14 показан пример с двумя ставками ВСД, каждая из которых использует различное "начальное значение" в качестве аргумента предположения. Как вы видите, формула возвращает различные результаты.

И

Рис. 12.14. Рабочийлист, демонстрирующий многократные ВСД

Формула ВСД в ячейке В21 (которая возвращает результат 13,88%) следующая: =ВСД(В7:В16;ВЗ)

Формула ВСД в ячейке В22 (которая возвращает результат 7,04%) следующая: =ВСД(В7:В16;В4)

Таким образом, какая ставка правильная? К сожалению, обе. На рис. 12.14 показан учетная ставка и балансовые вычисления для обоих значений ВСД. Оба результата показывают, что инвестор может выплачивать и получать любую процентную ставку и может обеспечить конечный баланс $0. Интересно, что суммарный доход ($1 875) одинаковый.

Но в подобных вычислениях существует один недостаток. Этот пример иллюстрирует "сценарий наихудшего случая" практичной ошибки многих вычислений ВСД. Анализ ЧПСи ВСД принимает два предположения:

•Можно действительно получить прогнозируемый (для ЧПС) или вычисленный (для ВСД) процент на невыплаченном балансе.

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

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

ПРИМЕР 14

Функция МВСД пытается решить последнюю задачу нескольких ставок. В примере этого раздела демонстрируется эффективность использования функции МВСД.

На рис. 12.15 показан рабочий лист, в котором используются те же данные, что и в примере 13.Отдельные ставки предоставляются длязайма (ячейка ВЗ)и длявклада (ячейка В4). Они используются в качестве аргументов функции МВСД (ячейка В19), а результат равен 6,1279 %,который отличается дляобоих вычислений ВСД:

=МВСД(В7:В1б;ВЗ;В4)

Рис. 12.15.Модифицированная внутренняяставка доходности

Функция МВСД разделяет отрицательные и положительные денежные потоки, вычисляя их по разным ставкам: ставка займа (дляотрицательных потоков) и депозитная ставка (для положительных потоков).

Мы можем проанализировать алгоритм вычислений МВСД, видоизменив денежный поток, в котором сравнивается двазначения ЧПС (см. рис. 12.15; столбцы С:Е). Отрицательное значение ЧПС размещается в точке 0, а положительное значение выражается, как эквивалентное будущее значение (в результате накопления на депозите) в конце срока инвестиции. ВСД измененного денежного потока та же, что иМВСД исходного денежного потока.

Этот пример показывает, чтоприменяемая методика вычислений может быть разной.При разделении отрицательных и положительных потоков в МВСД используется подход, в котором процент рассчитывается на основе денежных потоков. Банки, конечно, рассчитывают процент, исходя избалансовой стоимости. Попытка решить подобную задачу продемонстрирована в следующем примере.

ПРИМЕР 15

Функция МВСД использует две ставки: одну для отрицательных, а другую для положительных платежей. Фактически, процентные ставки основываются набалансе, а не наденеж-

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

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

В нашем примере ставка займа устанавливается пользователем. Процент, получаемый на положительных балансах, изначально "подбирается" пользователем. Процент на отрицательных балансах— это и есть ставка займа. Процент на положительных балансах определяется по "подобранной" ставке. Если "подобранная" ставка точно равняется доходу, то конечный баланс равен 0. Команда Сервис=>Поиск решения Excel используется для определения точной ставки в результате подбора процентной ставки на положительных балансах для получения конечного баланса равного 0. Описанный метод демонстрируется в примере на рис. 12.16.

xampie13-1b(M!RR|.xts

2;

Д^ Метод подсчета баланса

Рис. 12.16. Накопление балансадля нескольких ВСД

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

Но что все это значит? Это означает, что, если оплачивать 9% на отрицательных балансах, то в этом проекте предоставляется ставка 8,579% на положительных балансах. Название проекта переводится как "Эквивалент ставки риска ВСД" и свидетельствует о том, что он определяет, насколько эффективно возвращаются деньги, инвестированные в банк.

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

Использование функции БЗРАСПИС

Функция БЗРАСПИС вычисляет будущее значение начальной суммы после применения со временем ряда разных ставок. Ее синтаксис следующий:

БЗРАСПИС(первичное;план)

Функция БЗРАСПИС доступна только приустановке надстройки "Пакета анализа".

ПРИМЕР16

В этом примере, показанном на рис. 12.17, используется функция БЗРАСПИС для вычисления накопленной суммы вместе с другими формулами, которые используют предоставленные данные для вычислениякоэффициента и среднего геометрического темпа прироста.

Этот рабочий лист содержит коэффициент цен на акции между 1997 и 2001 годами; для 1997 года задан коэффициент 100. Этот пример может ответить на следующий запрос: Если бы мы купили акции в 1997 на сумму $1 000, какую стоимостьони имели бы в 2001, и какой должен быть среднийтемп прироста ?

Стоимость акций, показанная в ячейке В13, будет составлять $1 296,81. Это эквивалент использования ставки 6,714% для начальной инвестиции$1 000.

Рис. 12.17. Использование функции БЗРАСПИС

Накопленная сумма (ячейка В13) вычисляется по следующей формуле:

=БЗРАСПИС(ВЗ;В7:В10)

Обратите внимание, что функция БЗРАСПИС не зависит от знака. Она возвращает будущее значение с темжезнаком, что и текущее значение. Также обратите внимание, что темпы прироста задаются в виде эффективных ставок за соответствующий период времени. В данном примере период времени вычисляетсяв годах, такчто темпы прироста также выражены в ежегодных сроках.

Формула в ячейке В14 вычисляет средний геометрический темп прироста:

=СТАВКА(4;0;-ВЗ;В17;0)

Обратите внимание, что в формуле используется знак "минус" в третьем аргументе (текущее значение). Можно также вычислить среднюю геометрическую ставку доходности, используя еще одну формулу (ячейка В15):

=СТАВКА(4;0;-ВЗ;БЗРАСПИС(ВЗ;В7:В10);0)

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

Вычисления обесценивания

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

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

Таблица 12.1. Функции обесценивания Excel

^Аргументы в скобках необязательны.

Аргументы для функций обесценивания описываются следующим образом:

•нач_стоимость. Первоначальная стоимость актива.

•ост_стоимость. Стоимость актива после полного обесценивания.

•время_эксплуатации. Ряд периодов, в течение которых фонд обесценится.

•период. Период времени, для которого производится вычисление.

•месяцы. Число месяцев в первом году; если опустить, Excel по умолчанию использует 12.

•коэффициент. Ставка снижения баланса; если опустить, предполагается равной 2 (то есть, двойное понижение).

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

• без переключения. ИСТИНА или ЛОЖЬ. Уточняет, переключаться ли на линейное начисление амортизации, если обесценивание больше величины, рассчитанной методом снижающегося остатка.

На рис. 12.18 показано вычисление обесценивания, с использованием описанных выше функции. Первоначальная стоимость актива $10000устанавливается такой, чтобы иметь срок 10 лет с остаточным значением $1 000.Диапазон Declaration Amount показывает ежегодное обесценивание актива. Диапазон Value of Asset показывает обесцененное значение актива напротяжении срока.

Puc. 12.18. Сравнение четырехфункций обесценивания

На рис. 12.19 показана диаграмма изменения актива. Как вывидите, функция АПЛ имеет вид прямой линии; другие функции изогнуты из-за того, чтообесценивание больше наранних стадиях срока службы актива.

О "7

10

И 4 * n\Depreciation/VBD/

Рис. 12.19.На этой диаграммепоказанозначениеактива, изменяемое во времени, рассчитанноепо четыремфункциям обесценивания