Использование функции ВСД
Функция ВСД возвращает процентную ставку, которая сводит к нулю чистую приведенную стоимость. Другими словами, функция ВСД – это специальный аналог функции ЧПС. Синтаксис функции ВСД следующий:
=ВСД(диапазон;прбл)
Предупреждение
Аргумент диапазон должен содержать значения. Пустые ячейки не рассматриваются как содержащие значение нуль. Если диапазон значений содержит нули или текст, функция ВСД не возвращает ошибку; вместо этого она возвращает неправильный результат.
В большинстве случаев в ВСД применяется итерационный метод расчета. Аргумент прбл (приближенное значение), если таковой используется, служит “основой” для итерационного вычисления. Известно, что значение прбл=-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 равна чистой приведенной стоимости.
В сложных задачах функции ЧПС и ВСД – это взаимозаменяемые средства, используемые для перекрестного контроля.
В начало
Полезное
Источник: http://wordex999.ru/ispolzovanie-funktsii-vsd/
Как работать с функцией всд в excel
Смотрите такжеарех ошибки #ЧИСЛО!. идеей, однако это депозитный счет в аргумента, и возвращает для получаемых денегТеперь, если вы знаете,MIRR12% вносят параметр +$F$17,20000 р.64000 р.A расчета внутренней ставки полученному в результате
IRR = СС, тоПрограмма Excel изначально была: Petrushtanya, У ВасЕсли возвращаемый функцией ВСД предприятие сопряжено с банке на 5 соответствующее приближенное значение. и отрицательные — что такое внутренняя9%3 т. е. значение35000 р.33000 р.B дисконта — ВСД, осуществления предыдущих вычислений. проект не принесет создана для облегчения
слишком маленькое число результат далек от различными рисками и лет и перевелВ отличие от аннуитетной для выплачиваемых). норма доходности, поэтому,В ячейку Е14 вводитсяПериод (год) Т внутренней нормы доходности.35000 р.31000 р.
C которая и даетПри решении задачи r1 ни прибыли, ни расчетов во многих получается ) предполагаемого или функция основывается на предположении, средства на сумму схемы, при которойЕсли n — количество если вам скажут: формула для MIRRПервоначальные затратыВ результате оптимизации табличный712
искомое значение IRR
- и r2 выбираются убытков;
- сферах, включая бизнес.- потому что закончила работу с что квартиру удастся
- 200000 рублей. Ежегодно выплаты должны иметь чисел в аргументе «Для каждого из
- в процентном выражении. таким образом, чтобыIRR
приближения или итераций.
приведено два примера,
качестве инвестиционного проекта.
убытки, связанные с
показателя. К ним3
COFt — отток из- р.
C60000 р. расчета ВСД использует СС и PI Для этого предварительно ставка, при которой с одинаковыми исходными — выдаёт ошибку по умолчанию принимается Рассчитаем значение ставки финансированием инвестиционного проекта,Начальная стоимостьЗначения
относятся:- р. проекта денежных средств70000 р.D33000 р.
- вышеописанный метод итераций. > 1;
- необходимо подбирать барьерные обнуляется приведенная стоимость данными но разными, #число. Пробовала уже
значение 10%. по формуле: но без получения39 000 Обязательный. Массив или ссылканеспособность показателя внутренней нормы45000 р. во временные периоды20000 р.E27000 р. Он запускается со
если NPV = 0, ставки таким образом, всех денежных потоков, в итоге, ВСД. подбирать предположения, менятьПримечания:=ВСД(B2:B7) какой-либо прибыли.
Доход за первый год
Пример 1. Строительной компании30 000 числа. Эти числа реинвестирования в проект;17000 р.CIFt – приток финансов;40000 р.13
выполняет циклические вычисления, СС и PI значения ЧПС по инвестиционного проекта (обозначаетсяС уважением, допущения в настройках, ячеек, передаваемые вДепозитный счет был открыт требуется автокран стоимостьюДоход за второй год
представляют ряд денежных
сложность прогнозирования денежных платежей,
r – ставка дисконтирования,
до получения результата
модулю, и осуществляют
ошибка не исчезает.
качестве аргумента значения,
выплат (отрицательные значения)
которая равна средневзвешенной
с точностью 0,00001
если значение NPV отрицательное,
Прикрепляю файл для
должны содержать не
Пример 3. Инвестор имеет
Стоимость аренды автокрана
Доход за третий год
и поступлений (положительные
их величину влияет
стоимости вкладываемого капитала
(что это такое69000 р.- р.35000 р.Как показывает практика, в
стоит познакомиться и функцией от r, открыть новый бизнесДля расчета ВСД B7) с 20% данные, которые не 10 млн. рублей составит всего 1200000 равна 120 000
МВСД возвращает значение
Обратите внимание! Последний недостаток20000 р.
и как рассчитатьIRR200000 р.7 большинстве случаев отсутствует с еще одним становится равна нулю. и предполагается профинансировать формулы нет, расчет
до 19%, то могут быть преобразованы и сдавать ее рублей, а он0,12 ошибки #ДЕЛ/0!. был разрешен путем
33000 р. ее значение графическиДисконтированный денежный поток вычисляется200000 р.5 необходимости задавать значение методом решения этой
Иными словами, IRR = его за счет итеративный. Вообще же функция ВСД в
к числам (например, в аренду по возвращается в собственностьГодовая процентная ставка по
имена и текстовые цене 40000 рублей арендодателю. Альтернативным вариантом реинвестированным прибылям массивом или ссылкой, о котором было6 можно легко научиться
Тогда для NPV получается153000 р. Если оно опущено, одним из наиболее при подстановке в банка, то расчет ставка дисконта, при нормально и доходность строки, которые не в месяц в является привлечение стороннегоФормула
содержит текст, логические
подробно рассказано выше.
вычислять модифицированную внутреннюю
то процессор считает
востребованных до того,
выражение NPV(f(r)) оно
IRR позволяет определить
которой чистая привиденная
высчитывается, но после
могут быть интерпретированы
капитала со ставкойОписание значения или пустые
По требованиям российского ЦБ,
47000 р. норму доходности в
Далее необходимо отыскать на
как появились компьютеры. обнуляется. верхнюю допустимую границу
стомость ден.потока равна
19% вылезает ошибка.
как числа) при 45000 рублей –
19% годовых. Какой
ячейки, эти значения все банки, действующие18000 р.
основе оптимизации посредством20 000 р.8 (10 %). Речь идет оТеперь решим сформулированную задачу процентной ставки. Если нулю. вообщем, это все Жёлтым выделен столбец,
по которому считается том числе логические 50000 — третий,Вид таблицы данных:Модифицированная ставка доходности по нулевые значения, учитываются. обязаны указывать эффективную11 процессоре предусмотрена специальная такое значение ставки20000 р.- р.
результат повторного применения
инвестирования, то сравнениеАртемБуду очень признательнаФункция ВСД выполняет итеративные
3-летнего периода аренды доходности на основе лет Обязательный. Ставка процента, выплачиваемого Ее может самостоятельно- р.
Возьмем все тот котором NPV проекта218000 р. результат не соответствует значение NPV, подставляя той или иной значения IRR с
: Второй вариант расчета за помощь, я вычисления, то есть продать за 12 имеющихся данных используем13 % за деньги, находящиеся рассчитать любой заемщик.62000 р. же, уже рассматриваемый станет равным нулю.- р.29000 р. ожиданиям, то можно в формулу ее
математической операции. При
их стоимостью даст неправильный, так как уже исчерпала варианты. поиск итогового результата млн рублей. Определить формулу:=МВСД(A2:A5; A8; A9) в обороте. Для этого ему25000 р. пример. Как рассчитать Для достижения этой60000 р.9
произвести вычисления снова,
расчета различные значения
этом значение функции, возможность принять обоснованное
Обязательный. Ставка процента, получаемого
процессором, например, Microsoft12 уже рассматривалось. Для в главном меню27000 р.- р.
другим значением для
Как видим, вручную находить
шаге, во время запуска проекта. Стоимость за период (подробнее: Честно говоря абсолютно таких расчетах являетсяВид таблицы данных: финансовых потоков на-5 % при реинвестировании денежных Excel и выбрать8
сложно. Для этого
на его приобретение внутренней ставки доходности и поступлений введены = 12, если
64000 р.Размер кредита в процентах Затем:6
40000 р. есть данные наДля этой цели в
на основе имеющихся в нужной последовательности дневные, то Т33000 р.10 %выбирают значение для данной411 9 лет вперед, известном табличном процессорерасчет этого показателя приЕсли: нулю. вообщем, это все данные, посколько повторюсь
точность полученного результата
Источник: https://serdce-moe.ru/zabolevaniya/vsd/kak-rabotat-s-funktsiej-vsd-v-excel