Финансы в Excel

для тех, кто не пользуется калькулятором

  • Увеличить размер шрифта
  • Размер шрифта по умолчанию
  • Уменьшить размер шрифта
Главная Статьи Практика Финансовые функции и таблицы подстановки

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

E-mail Печать
Содержание
Оценка инвестиций
Аннуитетные платежи по кредиту
Таблицы подстановки
Вложения:
Скачать файл (finfunc2.xls)finfunc2.xls[Финансовые функции]37 Kb

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

Целью данной статьи не является полный обзор функций, относящихся к финансовому разделу. Такое описания представлено в справочной системе Excel и других интернет-ресурсах. Следует также заметить, что некоторые финансовые функции имеют достаточно специфическую локальную направленность, другие сохраняются в целях обратной совместимости со старыми версиями Excel (и Lotus 1-2-3). Некоторые функций не включены в ядро Excel, а подключается только при активизации надстройки «Пакет анализа» (Analysis ToolPak). Большие проблемы также вызывает не совсем корректная русификация финансовых функций. Дело в том, что в России на момент русификации Excel просто не было многих общепринятых в настоящее время терминов (это относится даже к NPP и IRR). В дальнейшем прижились именно английские варианты сокращений, что почему-то никак не отражается на политике Microsoft в новых версиях Excel. По нашему мнению имена большинства финансовых функций аналогично тригонометрическим не требуют перевода на русский язык.

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

Оценка инвестиций

Чистая современная стоимость (чистая приведенная стоимость, чистая текущая стоимость, чистый дисконтированный доход, англ. Net present value, принятое в международной практике анализа инвестиционных проектов сокращение — NPV) — это сумма дисконтированных значений потока платежей, приведённых к сегодняшнему дню.

NPV

В примере рассчитана величина NPV на основе одного периода инвестиций и четырех периодов получения доходов (строка 3 «Денежный поток»).

Формула в ячейке Пример1!B6 вычисляет NPV с помощью финансовой функции:

=ЧПС($B$4;$C$3:$E$3)+B3

Формулы для расчета дисконтированного денежного потока в каждом периоде находятся в примере в строке 5.

Пример1!C5:

=C3/((1+$B$4)^C2)

Формула в ячейке Пример1!C6 получает тот же результат через формулу работы с массивами:

{=СУММ(B3:E3/((1+$B$4)^B2:E2))}

Внутренняя норма доходности (англ. internal rate of return, общепринятое сокращение — IRR) — это процентная ставка, при которой чистый дисконтированный доход (NPV) равен 0. NPV рассчитывается на основании потока платежей, дисконтированного к сегодняшнему дню.

IRR

В отличие от NPV коэффициент IRR не может быть рассчитан простым алгоритмом. Excel в собственных вычислениях действует методом подбора. Так работает функция в ячейке Пример1!B7. Для точности подбора во время расчета можно использовать второй параметр функции IRR (в примере, использовано значение по умолчанию 0.1%).

=ВСД(B3:E3)

В связи с этим можно сделать вывод, что реализация алгоритма расчета IRR простыми формулами вызывает серьезные затруднения. К счастью, в Excel встроен интерфейсный метод поиска решения однопараметрических задач – «Подбор параметра». Диалоговое окно вызывается в Excel2007 через Данные \ Работа с данными \ Анализ «что-если» \ Подбор параметра, в Excel 97-2003 через меню Сервис \ Подбор параметра.

 

Из определения IRR следует, что необходимо задать величину NPV = 0 и вычислить доходность обратным счетом. Укажите эти параметры поиска через адреса ячеек и подтвердите результат поиска. Итог вычислений в примере представлен в ячейке C7.

Аннуитетные платежи по кредиту

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

Аннуитет

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

=ПЛТ($B$24/12;12;$C$28)

   

Для контроля расчетов сформирована таблица выплат по кредиту за каждый период с расчетом процентов. Методом подбора параметра получим значение в ячейку B15 с условием, что конечный остаток (E26) будет равен нулю.
При правильно выполненных действиях результат не должен отличаться от значения, полученного в результате применения функции ПЛТ.

Таблицы подстановки

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

   

В файле с примером рассмотрены два вида таблиц подстановки. Для получения набора результатов функции с одним параметром необходимо сформировать 2 столбца (либо строки): ячейки параметров ячейки результатов. Ссылка на формулу для получения базового результата анализа должна располагаться в верхнем левом углу диапазона. Затем следует выделить область таблицы, включая ячейку с формулой (в примере B29:C33), и вызвать диалог формирования таблицы подстановки. В Excel2007 - через Данные \ Работа с данными \ Анализ «что-если» \ Таблица данных, в Excel 97-2003 через меню Данные \ Таблица.

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

   

Аналогичные действия необходимо произвести в случае двухмерной таблицы подстановки (матрицы). В диалоговом окне, кроме ссылки на параметр в строках требуется указать ссылку на параметр в столбцах (в примере $B$3). Формула должна содержать ссылки на оба параметра и располагаться в верхнем левом углу таблицы.

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

Текст статьи и файл примера исправлены 17.04.12, благодаря замечаниям Алексея Макарова.

 

Смотри также

» Условное объединение строк

Функция efSumText возвращает объединенный текст с указанным разделителем.

Комментарии  

 
0 #6 Марат 17.04.2012 16:49
Поправил текст статьи и пример.

Алексей, буду рад если "докопаетесь" еще до чего-нибудь, размещенного на сайте. А то практически нет конструктивных замечаний. Хотя я вполне отдаю себе отчет, что многие утверждения субъективны и спорны.

Удачи тоже, с уважением
Марат Шарифуллин
Цитировать
 
 
0 #5 Alexey Makarov 17.04.2012 16:34
Учимся каждый день. Я уже достаточно давно работаю в консалтинге с экселем, пишу на VBA и все равно практически каждый день узнаю что-то новое :)

Рад, что мой комментарий оказался полезным.

Удачи!
Алексей.
Цитировать
 
 
0 #4 Марат 17.04.2012 15:51
Алексей, спасибо за комментарий. Как бы не хотелось, но вынужден признать Вашу правоту ) Я уже даже нашел файлы для анализа чувствительност и с таблицами подстановки. Пожалуй, надо менять пример. Да и текст статьи тоже. Некорректно все-таки сложную формулу в таблицу подстановки запихивать. Слабым оправданием могу только сказать, что всегда использовали макросы для подстановки различных значений в фин.моделях (иногда плохо быть программистом). Еще раз спасибо.
Цитировать
 
 
+1 #3 Alexey Makarov 17.04.2012 15:16
Марат, здравствуйте. Я позволю не согласиться с вами по поводу таблиц подстановки.

Приведу пример, когда без нее не обойтись.

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

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

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

С такой логикой модель ваша изменится и в ячейках С29 и B36 будет прописана не сама формула а всего лишь ссылка на результат, вас интересующий (=B6).

С уважением,
Алексей.
Цитировать
 
 
0 #2 Марат 14.04.2012 13:40
ну давайте попробуем поспорить )
Описание функциональност и таблицы подстановки в качестве "удобного средства для подготовки анализа чувствительност и" обычно приводится в справочных системах и в простых примерах "для чайников". Но если задуматься, то какая бы сложная не была ваша финансовая модель, в конечном итоге для таблицы подстановки должна остаться одна результирующая функция с одним или двумя изменяемыми параметрами. Пусть даже предварительные расчеты к этой функции основаны на пяти листах - не важно. По-моему проще скопировать эту формулу на всю матрицу, чем организовывать таблицу подстановки. Если у вас предполагается частое изменение алгоритма вычислений результата, то сделайте функцию в виде формулы обработки массива на всю матрицу - тогда тоже можно будет избавиться от копирования при редактировании текста. Мне кажется, таким образом вычисления выглядят прозрачнее и правильнее.
Единственный тезис, который я не могу ни подтвердить, ни опровергнуть, это то, что таблица подстановки в сложных моделях работает быстрее. Тестирование производительно сти электронных таблиц - это отдельная сложная тема.
Вообще, мне кажется, что эти таблицы подстановки действительно создавались когда-то для ускорения расчетов типа анализа чувствительност и. При этом предполагалось, что основная работа ведется в режиме вычислений "Автоматически, кроме таблиц". Что давало, видимо, существенное преимущество в производительно сти. Но с тех незапамятных времен мощности вычислений выросли в сотни (если не в тысячи) раз, поэтому вы вряд ли почувствуете какой-то эффект на стандартных файлах.
Обеспечение обратной совместимости версий заставляет тянуть поддержку таблиц подстановки от версии к версии до сих пор. Но это моё частное, возможно неверное, мнение. Я совершенно серьезно буду рад ошибиться и буду признателен, если опровергнете мое мнение на конкретном файле (пришлите на ). Мне это нужно, хотя бы для демонстрации полезных примеров на тренингах.
Цитировать
 
 
+1 #1 Alex84 13.04.2012 13:16
тезис о «ненужности» Таблицы подстановки очень спорный. В простых случаях можно обойтись простыми формулами. А если строится сложная финансовая модель, которую невозможно описать в одной функции. Инструмент Таблицы подстановки в этом случае удобное средство для быстрой подготовки анализа чувствительност и результата модели от 1-2х параметров. Очень помогает при анализе чувствительност и модели расчета стоимости бизнеса от ставки дисконтирования и темпа роста в териминальном периоде.
Цитировать
 

Язык формул

Вход