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

ПРИМЕР 34

Яхочу одолжить деньги на основе отсроченной выплаты. Период отсрочки— один год.

Сэтого времени заем выплачивается на протяжении 10 лет с ежемесячнымиплатежами. Эффективная процентнаяставка 8% в год. Заем обеспечивается недвижимостью, которой

явладею, и банк готов предоставитьаренду с выплатами, не превышающим 75%оцененной прибыли $9 500 за месяц. Сколько мне молено занять ?

Следующая формула использует обычную функциюAnnEf f jEf f x и возвращает $550 422,02: =nC(AnnEff_Effx(8%;12;10*12;-9500*75%;0;0)*(1+AnnEff_Effx(8%;12))Л -12

Оценка серии регулярных платежей

Мы можем расширить основной принцип дисконтирования последовательных, но различных платежей в результате формированияцепочки функций ПС.Например,если ПС1, ПС2 и ПСЗ представляют различные текущие значения ряда платежей на протяжении периодов времени КПЕР1, КПЕР2 и КПЕРЗ, приведенная стоимость всего ряда выплат может быть найдена с помощьюфункции:

ПС1+ПС2(1+1)А -КПЕР1+ПС2(1 + 1)Л -(КПЕР1+КПЕР2)

ПРИМЕР 35

Какая текущая стоимость имущества, приносящего прибыль $5 000 в месяц на протяжении четырехлет, $6 500 в месяц на протяжении последующихтрех лет и $8 500 в месяц на протяжении последних трех лет? Через 10 лет имущество будет оцениваться в $1 300 000. Ежегоднаяставка 10%, а все выплаты производятсяавансом.

Следующая формула возвращает -$978 224,54:

=nC(AnnEff_Effx(10%;12);48;5000;0,1)+ПС(AnnEff_Effx(10%;12); 36;6500;0; 1)*(1+AnnEff_Effx(10%;12))Л -

48+nC(AnnEff_Effx(10%;12);36;8500;1300000;1)*(1+AnnEff_Effx(10%;12))Л- (48+36)

Обратите внимание на то, что будущее значение $1 300 000 включено в последнюю функцию ПС.Тот же ответ можно получить в результате "встраивания" следующего текущего значения в предыдущую функцию, как будущего значения. Но, помня о том, что ПС при этом представляет будущую прибыль, знак необходимо изменить на противоположный. Следующая формула возвращает $978 224,54:

=nC(AnnEff_Effx(10%;12);48;5000;-ПС(AnnEff_Effx(10%;12);36,6500,- nC(AnnEff_Effx(10%;12);36;8500;1300000;1);1);1)

Из этих двух вариантов первая формула (использование суммы формулдисконтирования) выглядит проще последнего метода; она выглядит более приемлемой, хотя и громоздкой для построения. Для упрощения она разбивается на три части (три ячейки), которые впоследствии объединяются вместе.

Следующая формула возвращает $200 344,00: =nC(AnnEff_Effx(10%;12);48;5000;0;1)

Следующая формула возвращает $139 559,07:

=nC(AnnEff_Effx(10%;12);36;6500;0;1)*(1+AnnEff_Effx(10%;12))Л -48 Следующая формула возвращает $638 331,47:

=nC(AnnEff_Effx(10%;12);36;8500;1300000;1)*(1+AnnEff_Effx(10%;12))"-48+36) Сумма всех трех элементов равняется $978 224,54.

Резюме

Вэтой главе описаны финансовые функции и основные понятия временных денежных потоков

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

Вследующей главе представлены примеры, которые обеспечивают в Excel расчет обесценивания, и описаны методы определения чистых приведенных прибылей и внутренней нормы прибыли.

Глава12

Финансовые функции дисконтирования и обесценивания

Вэтой главе...

•Использование функции ЧПС

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

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

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

•Резюме

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

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

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

ЧПС(ставка;значение1;значение2;...)

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

Если отрицательные потоки превышают положительные, то функция возвратит отрицательную сумму. Точно так же, если положительные потоки превышают отрицательные потоки, функцияЧПС возвратит положительную сумму.

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

Используемая учетная ставка — это единая эффективная ставка на протяжении периода учета денежных потоков. Таким образом, если денежные потоки регистрируются ежемесячно, то надо использовать ежемесячную эффективную ставку.

Определение ЧПС

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

Если использовать функцию ЧПС без дополнительной корректировки, то результат не будет соответствовать этому определению.

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

•Расценивайте число периодов скорее за количество точек, чем диапазонов времени между точками.

•Всегда включайте начальный денежный взнос в расчет, даже если выплаты не проводятся до конца 1 периода (точка 1).

•Используйте формулу, подобную показанной ниже, для включения начального денежного потока:

=ЧПС(ставка;диапазон)*(1+ставка)

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

Примеры функции ЧПС

В этом разделе содержится ряд примеров, которые демонстрируют функциюЧПС.

ПРИМЕР 1

На рис. 12.1 показан рабочий лист с вычислением чистой приведенной стоимости для ряда денежных потоков в диапазоне В б : В13.

Вычисление ЧПС в ячейке В15 требует использования следующей формулы. Эта формула возвращает -$33 629,14:

=ЧПС(ВЗ;Вб:В13)*(1+ВЗ)

Рабочий лист на рис. 12.1 также демонстрирует метод перекрестного контроля результатов вычислений ЧПС. Столбец Е содержит копию исходных денежных потоков за одним исключением. В точке 0 денежный поток теперь равен исходному потоку в точке 0 (В6) минус подсчитанное ЧПС. В этом примере значение равно -$166 370,86. Формула перекрестной проверки в ячейке Е15, показанной здесь, возвращает $0,00:

=ЧПС(ВЗ;Еб:Е13)*(1+ВЗ)

Как работает перекрестная проверка? Учетная ставка 10% используется для вычисления излишка или недостатка, который возникает от желаемого 0% уровня. В данном случае излишек равен $33 629,14. Другой излишек выражается в сроках текущей стоимости (точка 0). Если излишек вычитается из потока точки 0, то в результате повторного расчета не должно возникать излишка. Другими словами, если текущее значение ЧПС вычитается из потока в точке 0, то ЧПС при той же ставке должно равняться 0. Если оно равно 0, это означает, что был достигнут необходимый процент.

19

и "ЗГf Й^иатр1<иХ>*й^

Рмс. 72.7. Этот рабочий лист использует функцию ЧПС

ПРИМЕР2

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

к

ш&ГС/^шРХ Шк *,.;.;,,L..,, -;«...:,..;..,..

Рис. 12.2. На этом рабочем листе вычисляется ЧПС для денежных потоков, которые начинаютсяв конце первого периода

Вычисление ЧПС в ячейке В16 требует использования следующей формулы: =ЧПС(ВЗ;В7:В14)*(1+ВЗ)

Вычисления указывают на то, что мы можем позволить себе кредит в $166 370,86 при выплате учетной ставки 10%.

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

=(1+$В$3)л-А7

Текущие значения вычисляются в столбце D в результате умножения каждого платежа на коэффициент соответствующего текущего значения. Формула в ячейке D7 следующая:

=С7*В7

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

ПРИМЕР3

Вэтом примере (рис. 12.3) вычисляется чистая приведенная стоимость денежных потоков

сначальным (точка 0)положительным значением.

Рис. 12.3. На этом рабочем листе вычисляется чистая приведенная стоимость дляначальногопотока наличных

Вычисление чистой приведенной стоимости проводится в ячейке В15, которая содержит следующую формулу:

=ЧПС(ВЗ;Вб:В13)*(1+ВЗ)

Вычисление указывает на то, что мы можем инвестировать $165 939,65 и получить денежные потоки сучетной ставкой 10%. В этом случае, однако, мы инвестируем $165 939,65 и имеем право наполучение в начальный момент времени поток размером $40 000.

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

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

ПРИМЕР4

В этом примере (рис. 12.4)вычисляется чистая приведенная стоимость с окончательным значением иавансовыми денежными потоками.

Этот пример описывает типичную арендную плату за недвижимое имущество с выплатами, предоставляемыми ежегодно авансом, с допустимой продажей после семи лет за $450 000. Обратите внимание на начальную и конечную выплаты. В этом случае, инвестор собирается получить первую арендную плату размером $30 000 немедленно, а также $40 000 в конце. Что может несоответствовать действительности, и,если последняя оплата невзимается, то надо сделать ееравной $0.

jjjjeampte 01 ОЭINPV) «1*

зГ

Рис. 72.4. Этя рабочая книга демонстрируетвыплаты с конечным значением

Вычисление ЧПС в ячейке D15 следующее: =4nC(B3;D6:Dl3)*(1+ВЗ)

ПРИМЕР5

ЭТОТ пример, показанный на рис. 12.5, похож на предыдущий, но в нем продемонстрирован случай (в ячейке В14), когда конечное значение суммируется с последним платежом.

Формула в ячейке В16 следующая:

=ЧПС(ВЗ;В7:В14)*(1+ВЗ)

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

ПРИМЕР6

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

Следующая формула указывает, что, при запрашиваемой цене $280 000, превышение предполагаемой прибыли равняется $148 026,29:

=4riC(B3;D8:D15) * (1+ВЗ)

С другой стороны, инвестор мог выплачивать всего $428 026,29 и все-таки достичь учетной ставки 10%.

ПРИМЕР7

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

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

На рис. 12.7 видно, что арендная плата $12 000 выплачивается ежеквартально авансом. Также видно, что начальная цена составляет $700 000 ипродажа (через три года) осуществляется за $900 000. Обратите внимание на то, что рента выплачивалась авансом, потому покупатель выплачивает исправленную начальную цену. Однако, по истечении трех лет (12 кварталов), применяется то же правило, и арендная плата, выплачиваемая вследующем квартале, будет получена новым владельцем. Если годовая эффективнаяставка равна 7%, то ЧПС возвращает $166 099,72.

Рис. 12.7. Вычисление ЧПС с использованием ежеквартальных денежных потоков

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

щиками, которые использовали таблицы предварительных расчетов и специальные константы перерасчетов. На рис. 12.8 показаны те же данные, но на этот раз мы предположили, что рента $48 000 в год выплачивается ежегодно с задержкой. Если годовая эффективная ставка равна 7%, то ЧПСвозвращает $160 635,26.

Рис. 12.8. Вычисление ЧПСв результатепреобразования ежеквартальных потоковв эжегодные

Использование функции ЧПС для вычисления накопленных сумм

В этом разделе представлено два примера использования функции ЧПС для вычисления будущего значения или накопленного капитала. Эти примеры основаны на том факте, что!

БС=ПС*(1+ставка)

ПРИМЕР 8

Данные для этого примера показаны на рис. 12.9. Вычисление чистой приведенной стоимости выполняется по следующей формуле в ячейке В15:

=ЧПС(ВЗ;Вб:В13)*(1+ВЗ)

Будущее значение вычисляется по такой формуле (в ячейке В17): =ЧПС(ВЗ;В6:В13)*(1+ВЗ)*(1+ВЗ)"7