Функция всд

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

Предупреждение

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

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

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

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

Вычисление нормы доходности

В следующем примере задается основная матрица вычислений ВСД. Важное соглашение при вычислении ВСД связано с частотой проплат. Если платежи осуществляются ежемесячно, то и процентная ставка будет месячной. Как правило, в задачах процентную ставку всегда преобразуют в годовую. В представленном примере проверяется содержимое ячейки В3, чтобы пользователь смог выбрать периодичность проплат (ежемесячно, ежеквартально или раз в год), отображаемую в ячейке С3. Выбор значения требует определенного преобразования процентной ставки; он также влияет на содержимое строки 5, где отображается текст формулы, использованной в ячейке СЗ.

Ячейка D19 содержит следующую формулу:

=ВСД(D6:D18;-0.9)

В ячейке D20 содержится следующая формула:

=БС(D19;BЗ;0;-1)-1

Представленная ниже формула в ячейке D21 проверяет достоверность полученных данных.

=ЧПС(D19;D7:D18)+D6

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

Вычисление средней геометрической нормы прибыли

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

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

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

=(В5-В4)/В4

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

=ВСД(F4:F8;-0.9)

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

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

Проверка результатов

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

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

=ЧПС(D3;В7:В14)+В6

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

=ВСД(В6:В14;-0.9)

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

=ПС($В$17;А6;0;-В6)

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

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

=ПС($D$3;А6;0;-В6)

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

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

В начало

Полезное

Источник: wordex999.ru

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

чистой настоящей величины дохода(net present value – NPV) и

внутренней нормы рентабельности(internal rate of return – IRR).

В табличном процессоре Excel реализованы три встроенные функции для проведения расчета данных критериев:

а) ЧПС() – функция для расчета чистой (приведенной) текущей стоимости капитала;

б) ВСД() – функция для расчета внутренней нормы рентабельности;

в) МВСД() – функция для расчета модифицированной внутренней нормы рентабельности;

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

— потоки платежей на конец (начало) каждого периода известны;

— для всего срока проведения операции определена оценка в виде процентной ставки, в соответствии с которой, полученные средства могут быть реинвестированы.

В качестве такой оценки наиболее часто используют: среднюю или предельную стоимость капитала, банковские ставки по долгосрочным кредитам (депозитам), требуемую норму рентабельности и пр.

а) Функция ЧПС( ).

Допустим, Ваша фирма собирается вложить денежные средства в новое оборудование, приобретение, доставка и установка которого оценивается в 100000 тыс. руб. Ожидается, что его эксплуатация обеспечит на протяжении 6 лет получение чистой прибыли в 10000, 25000, 30000, 35000, 40000, 45000 тыс. руб. соответственно. Приемлемая норма рентабельности составляет 10%. Необходимо определить целесообразность осуществления данного проекта, т.е. необходимо определить чистый приведенный доходв конце периода в зависимости от входных параметров.

Для решения задачи выполните следующие действия:

1) Введите исходные данные на рабочий лист. В данном случае диапазон ячеек А9:А15 содержит порядковые номера периодов эксплуатации актива. В ячейке B9 введено значение первоначальных инвестиций (причем в виде отрицательной величины), соответствующее нулевому периоду. В диапазоне B10:B15 содержатся значения ожидаемой чистой прибыли (денежных потоков).

2) Установите курсор в ячейку C9, выполните команду Формулы / Библиотека функций / Вставить функциюили нажмите кнопку
Строки формул. В категорииФинансовыеиз списка выберите функцию ЧПС( ). Нажмите ОК.

3) Введите необходимые аргументы в соответствии с синтаксисом данной функции:

=ЧПС (ставка; значение1; значение2;…; значениеN),

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

значение1, значение2,…, значениеN – денежные потоки из N платежей произвольной величины.

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

где: PV – текущая величина потока;

P_i – сумма платежей за i-й период;

r– процентная ставка (норма дисконтирования);

n – число периодов.

Следует обратить внимание на то, что данное соотношение не учитывает величину первоначальных инвестиций I₀, т.е. инвестиций, сделанных на момент времени i=0. Поэтому для определения показателя чистый приведенный доход (net present value)из полученного результата (PV) следует вычесть величину первоначальных вложений I₀.

Если разность PV – I₀ > 0, то проект возмещает первоначальные затраты, обеспечивает получение прибыли согласно заданному стандарту – процентной ставки r, а также некоторый финансовый резерв, равный: NPV = PV – I₀. Отрицательная разность показывает убыточность проекта. На практике при проведении расчетов удобно задавать I₀ как отрицательную величину, а NPV рассчитывать по формуле: NPV = I₀ + PV, где I₀ < 0.

Проиллюстрируем все вышеизложенное на нашем примере.

Функция будет выглядеть следующим образом:

=ЧПС(0,1;10000;25000;30000;35000;40000;45000),
(Возвращаемый результат: 126435,16),

где: 0,1 – процентная ставка;

10000, 25000, 30000, 35000, 40000, 45000 – денежные потоки поступлений в виде чистой прибыли за соответствующие периоды.

Чистый приведенный доход, таким образом, составит:

NPV = I₀ + PV = -100000+126435,16 = 26435,16.

Поскольку NPV = 26435,16 >0, то проект обеспечивает возмещение первоначальных затрат и заданную норму рентабельности, а также дополнительную (сверх установленной нормы) прибыль в размере 26435,16.

Определив показатель PV, не трудно рассчитать еще один важный критерий оценки эффективности инвестиций – индекс рентабельности(PI),используя формулу: PI = PV / I₀.Для нашего примера он может быть рассчитан следующим образом:

=ЧПС(0,1;10000;25000;30000;35000;40000;45000)/100000 (Результат: 1,26).

б) Другим широко используемым на практике критерием оценки эффективности долгосрочных инвестиций является показатель внутренней нормы рентабельности(internal rate of return – IRR). В экономическом смысле данная величина является процентной ставкой, при которой уровень капитализации регулярного дохода (т.е. будущая стоимость вложений) даст сумму, равную первоначальным инвестициям. Другими словами, это процентная ставка, при которой PV=I₀, а NPV=0. Таким образом, если величина IRR больше заданной процентной ставки r, то проект следует считать эффективным, т.е. приносящий доход, иначе его следует отклонить, как убыточный.

Критерий внутренней нормы рентабельности предполагает реинвестирование получаемых доходов по ставке IRR.

Для вычисления этого показателя в Excel реализована функция ВСД().

Синтаксис функции:

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

где: значения – массив данных или диапазон ячеек, содержащих числовые величины, для которых вычисляется внутренняя скорость оборота денежных средств;

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

Табличный процессор Excel использует метод итераций для вычисления внутренней нормы рентабельности начиная со значения предположение.Функция ВСД() выполняет циклические вычисления пока не возвратит результат с точностью 0,00001 процента. Если функция ВСД() не может получить результат после 20-ти попыток, то возвращается значение ошибки #ЧИСЛО!.

Рассмотрим ее применение в нашей задаче.

Введите в ячейку рабочего листа, например D15, формулу ВСД() в соответствии с ее синтаксисом и нажмите ОК:

=ВСД (начальное значение: конечное значение;
заданная норма рентабельности).

=ВСД(B9:B15;0,01) (Возвращаемый результат: 17%).

Поскольку полученная внутренняя норма рентабельности (17%) выше заданной (10%), то проект следует принять и посчитать его прибыльным.

Показатель IRR, рассчитываемый в процентах, является более удобным критерием в оценке инвестиционной привлекательности проекта и для принятия решений, чем показатель NPV, так как абсолютные величины труднее поддаются интерпретации.

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

Синтаксис функции:

= МВСД(значения; ставка финанс; ставка реинвест),

где: значения – это массив данных или ссылка на ячейки, содержащие числовые величины. Эти числа представляют ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящие в регулярные периоды времени. Аргумент значения должен содержать, по крайней мере, одно положительное и одно отрицательное значение для того, чтобы можно было вычислить модифицированную внутреннюю скорость оборота (норму рентабельности). В противном случае функция МВСД() возвращает значение ошибки #ДЕЛ/0!;

ставка_финанс– это норма прибыли вложений денежных средств, находящихся в наличном обороте;

ставка_реинвест– это норма прибыли вложений денежных средств, находящихся в наличном обороте при их реинвестировании.

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

Предположим, что в нашем примере предоставляется реальная возможность реинвестирования получаемых доходов по ставке 12%, т.е. выше заданной (10%).

Для проведения расчета выполните следующие действия:

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

2) Установите курсор в ячейку E10, выполните команду Формулы / Библиотека функций / Вставить функциюили нажмите кнопку Строки формул. В категорииФинансовыеиз списка выберите функцию МВСД( ). Нажмите ОК.

3) Введите необходимые аргументы: в поле аргумента Значения: массив исходных данных, выделив диапазон B9:B10 для расчета модифицированной нормы рентабельности за первый год реализации проекта; введите также значения процентных ставок в соответствующие поля аргументов функции.

Функция, в соответствии с синтаксисом, будет выглядеть следующим образом:

=МВСД(B9:B10;0,1;0,12).

4) Нажмите ОК.

В ячейке ввода E10 функция возвратит полученный результат – модифицированную внутреннюю норму рентабельности за первый год, равную минус 90%. Отрицательный знак означает, что получаемая прибыль не обеспечивает окупаемость первоначальных инвестиций к концу первого года.

В конце 4-го года проекта значение модифицированной нормы рентабельности становится положительным и равняется 3%. По завершении всего срока эксплуатации актива, т.е. в конце 6-го года, модифицированная внутренняя норма рентабельности составит 15%.

Полученный результат ниже предыдущего значения (IRR=17%), однако выше заданной (10%), поэтому даже при более пессимистических прогнозах реальных условий, которые могут сложиться на рынке, проект можно считать прибыльным.

Вопросы для самоконтроля

1. Назовите виды финансовых функций.

2. Что такое амортизация и функции, использующиеся для ее вычисления?

3. Чем определяется использование метода равномерного списания и метода быстрого износа активов?

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

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

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

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

8. Постройте график амортизации активов по годам.

9. Определите величину амортизационных отчислений при использовании метода двойного процента со снижающегося остатка за период с 3 по 18 месяцы и с 1 по 160 день.

10. Перечислите финансовые функции Excel для анализа обыкновенных аннуитетов.

11. Назовите базовые аргументы финансовых функции Excel для анализа обыкновенных аннуитетов.

12. Фирма решила создать специальный фонд для погашения своих долгосрочных обязательств (кредитов, займов), срок погашения которых наступит, через 6 лет, путем периодического (ежегодного) пополнения депозита в банке. Начальная сумма депозита составляет 15000 тыс. руб. Размер ежегодных платежей – 2000 тыс. руб. Процентная ставка по банковскому депозиту – 12%. Необходимо определить величину фонда к концу 6-го года.

13. Определите число выплат (поступлений) денежных средств, если процентная ставка 0,12, периодический платеж 2000 тыс. руб., начальная и будущая величины потоков платежей составляют соответственно 15000 и 39141 тыс. руб.

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

15. Определите величину периодического платежа по ссуде при следующих условиях: величина будущей стоимости вложений 45026 тыс. руб., срок 6 лет, процентная ставка 12%, настоящая стоимость вложений 15000 тыс. руб.

16. Рассчитайте, какую сумму необходимо вложить в банк на депозит, чтобы получить через 6 лет величину вклада 49785 тыс. руб. при ежегодном пополнении вклада на 2000 тыс. руб., если годовая банковская ставка составляет 12%.

17. Какую сумму необходимо вложить в банк при начислении процентов и пополнении вклада по полугодиям?

18. Перечислите финансовые функции Excel для анализа эффективности инвестиционных проектов.

19. Фирма собирается вложить денежные средства в новое оборудование, приобретение, доставка и установка которого оценивается в 130000 тыс. руб. Ожидается, что его эксплуатация обеспечит на протяжении 6 лет получение чистой прибыли в 10000, 25000, 35000, 40000, 45000, 50000 тыс. руб. соответственно. Приемлемая норма рентабельности составляет 10%. Определите приведенную к настоящему моменту времени, величину потока будущих платежей с учетом заданной оценки.

20. Определите для задания 17 чистый приведенный доходв конце периода.

21. Определите для задания 17 индексы рентабельности для каждого года, начиная с 1-го.

22. Рассчитайте для задания 17 внутреннюю норму рентабельности для всех периодов, начиная с 3-го.

23. Рассчитайте для задания 17 модифицированную внутреннюю норму рентабельности (MIRR) для всех периодов, начиная с 1-го.

24. Результаты расчетов в п. 17 – 21 должны выглядеть следующим образом:

25. Сделать общий вывод по инвестиционному проекту.

Источник: studopedia.ru

Описание

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

Синтаксис

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

Аргументы функции ВСД описаны ниже.

• Значения    — обязательный аргумент. Массив или ссылка на ячейки, содержащие числа, для которых требуется подсчитать внутреннюю ставку доходности.

  • Значения должны содержать по крайней мере одно положительное и одно отрицательное значение.

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

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

• Предположение    — необязательный аргумент. Величина, предположительно близкая к результату ВСД.

  • В Microsoft Excel используется итеративный метод расчета ВСД. Начиная с предположения, ВСД циклически перейдет к вычислению, пока результат не станет точным в 0,00001%. Если функция ВСД не может найти результат, который работает после 20 попыток, #NUM! возвращено значение ошибки.

  • В большинстве случаев для вычислений с помощью функции ВСД нет необходимости задавать аргумент «предположение». Если он опущен, предполагается значение 0,1 (10%).

  • Если функция ВСД возвращает значение ошибки #ЧИСЛО! или результат далек от ожидаемого, попробуйте повторить вычисление с другим значением аргумента «предположение».

Замечания

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

ЧПС(ВСД(A2:A7),A2:A7) равняется 1.79E-09 [Учитывая точность расчета для функции ВСД, значение можно считать нулем).]

Пример

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

Данные	Описание
-70 000 ₽	Начальная стоимость бизнеса
12 000 ₽	Чистый доход за первый год
15 000 ₽	Чистый доход за второй год
18 000 ₽	Чистый доход за третий год
21 000 ₽	Чистый доход за четвертый год
26 000 ₽	Чистый доход за пятый год
Формула	Описание	Результат
=ВСД(A2:A6)	Внутренняя ставка доходности по инвестициям после четырех лет	-2,1 %
=ВСД(A2:A7)	Внутренняя ставка доходности после пяти лет	8,7 %
=ВСД(A2:A4;-10%)	Для подсчета внутренней ставки доходности после двух лет следует включить предположение (в данном примере — -10 %)	-44,4 %

Источник: support.office.com

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

Синтаксис функции ВСД:

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

где

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

предположения — это приблизительная оценка будущей внутренней ставки доходности. Это необязательный аргумент. Если он опущен, аргумент предположения полагается равным 10%.

Числовые значения могут быть введены вручную или находиться в ячейке, на которую дается ссылка.

Чтобы применить функцию ВСД,

1. выделите ячейку, в которой требуется отобразить результат,
2. щелкните по значку Вставить функцию , расположенному на верхней панели инструментов,
   или щелкните правой кнопкой мыши по выделенной ячейке и выберите в меню команду Вставить функцию,
   или щелкните по значку перед строкой формул,
3. выберите из списка группу функций Финансовые,
4. щелкните по функции ВСД,
5. введите требуемые аргументы через точку с запятой,
6. нажмите клавишу Enter.

Результат будет отображен в выделенной ячейке.

Вернуться на предыдущую страницу

Источник: helpcenter.onlyoffice.com