Функция всд в excel ошибка число

Функция ВСД в Excel и пример как посчитать IRR

Для расчета внутренней ставки доходности (внутренней нормы доходности, IRR) в Excel используется функция ВСД. Ее особенности, синтаксис, примеры рассмотрим в статье.

Особенности и синтаксис функции ВСД

Один из методов оценки инвестиционных проектов – внутренняя норма доходности. Расчет в автоматическом режиме можно произвести с помощью функции ВСД в Excel. Она находит внутреннюю ставку доходности для ряда потоков денежных средств. Финансовые показатели должны быть представлены числовыми значениями.

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

Внутренняя ставка доходности (IRR, внутренняя норма доходности) – процентная ставка инвестиционного проекта, при которой приведенная стоимость денежных потоков равняется нулю. При данной ставке инвестор вернет вложенные первоначально средства. Инвестиции состоят из платежей (суммы со знаком «–») и доходов (со знаком «+»), которые происходят в одинаковые по продолжительности временные промежутки.

Аргументы функции ВСД в Excel:

1. Значения. Диапазон ячеек, в которых содержатся числовые выражения денежных средств. Для данных сумм нужно посчитать внутреннюю норму доходности.
2. Предположение. Цифра, которая предположительно близка к результату. Аргумент необязательный.

Секреты работы функции ВСД (IRR):

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

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

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

Примеры функции ВСД в Excel

Расчет внутренней нормы рентабельности рассмотрим на элементарном примере. Имеются следующие входные данные:

Сумма первоначальной инвестиции – 7000. В течение анализируемого периода было еще две инвестиции – 5040 и 10.

Заходим на вкладку «Формулы». В категории «Финансовые» находим функцию ВСД. Заполняем аргументы.

Значения – диапазон с суммами денежных потоков, по которым необходимо рассчитать внутреннюю норму рентабельности. Предположение – опустим.

Искомая IRR (внутренняя норма доходности) анализируемого проекта – значение 0,209040417. Если перевести десятичное выражение величины в проценты, то получим ставку 20,90%.

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

Еще один показатель эффективности инвестиционного проекта – NPV (чистый дисконтированный доход). NPV и IRR связаны: IRR определяет ставку дисконтирования, при которой NPV = 0 (то есть затраты на проект равны доходам).

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

На основании полученных данных построим график изменения NPV.

Пересечение графика с осью Х (когда чистый дисконтированный доход проекта равняется нулю) дает показатель IRR для данного проекта. Графический метод показал результат ВСД, аналогичный найденному в Excel.

Как пользоваться показателем ВСД:

Если значение IRR проекта выше стоимости капитала для предприятия, то данный инвестиционный проект нужно принять.

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

Вернемся к нашему примеру. Допустим, для запуска проекта брался кредит в банке под 15% годовых. Расчет показал, что внутренняя норма доходности составила 20,9%. На таком проекте можно заработать.

ВСД (функция ВСД)

В этой статье описаны синтаксис формулы и использование функции ВСД в Microsoft Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

IRR: как рассчитать в Excel

Статьи по теме

Компания столкнется с внутренней нормой доходности (IRR), когда будет составлять бизнес-план для себя или банка. Показатель отражает возможность инвестиционного проекта приносить прибыль. Узнайте, как сделать расчет IRR в Excel и скачайте бесплатно пример вычисления.

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

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

Чтобы сделать вычисления вручную, нужно знать следующую информацию:

• за какой временной промежуток фирма выполнит расчет (t);
• какой денежный поток был у компании за этот период (CFt);
• какими были вложения в проект на этапе запуска (IC).

Тогда можно будет применить формулу:

Расчёт IRR в Excel

Очевидно, что ручные вычисления займут много времени. Гораздо проще найти IRR c помощью специальной формулы в Excel. Точнее, с помощью формул, ведь есть несколько путей, как вычислить доходность.

Сделать расчет IRR в Excel можно несколькими способами. Приведем три из них.

Способ 1. Функция «ВСД»

Во-первых, показатель можно вычислить с помощью функции ВСД (внутренняя ставка дисконта). Она возвращает ставку для ряда потоков денежных средств. В первоначальном виде формула для вычисления IRR выглядит так:

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

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

Еще один способ как рассчитать IRR в Excel – применить функцию «ЧИСТВНДОХ». Ее принцип аналогичен принципу опции «ВСД», но эта функция подходит для несистематических платежей. Общая формула:

• значения – денежный поток за конкретную дату;
• даты – движение денежных потоков в течение месяца (года и т.п.);
• предположение – предположительно близкая к результату «ЧИСТВНДОХ» величина, заполнять необязательно.

Способ 3. Функция «МВСД»

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

Подготовлено по материалам
«Системы Финансовый директор»

Расчет IRR в Excel: пример

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

Пример 1. Расчет по формуле «ВСД»

Составим таблицу, которая состоит из двух столбцов: «Период» и «Денежный поток». Предположим, что расчет нужно сделать за год. Зададим период от 0 до 12. Известно, что первоначальные траты составили 100 тыс. руб., поэтому их мы запишем в таблицу в нулевом периоде и со знаком «минус». Вычислим IRR. Для нашего примера формула выглядит следующим образом:

В2-В14 – показатели по денежному потоку за все периоды, в том числе и за нулевой.

Пример 2. Расчет по формуле «ЧИСТВНДОХ»

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

Здесь поля B2-B14 отражают информацию по денежным потокам, а A2-F14 – сведения по датам.

Пример 3. Расчет по формуле «МВСД»

Образец таблицы можно взять из предыдущих примеров. Рассмотрим, в чем будет кардинальное отличие от предыдущих ситуаций. Для функции МВСД необходимо знать ставку финансирования и реинвестирования. Допустим, норма дисконта составляет 12 процентов. А реинвестировать доход можно по ставке 10 процентов годовых. Получаем формулу для Excel:

Все примеры расчета IRR в Excel можно бесплатно скачать в этом материале.

Гость, уже успели прочесть в свежем номере?

Как оценить результат

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

1. Если показатель доходности окажется выше капитала, проект стоит рассмотреть.

2. Когда ВНД меньше капитала, лучше искать другие возможности.

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

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

Cрок окупаемости проекта: формула расчета

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

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

290000 / 67000 = 4 (результат округленный).

Функция ВСД для расчета внутренней ставки доходности в Excel

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

Примеры использования функции ВСД в Excel

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

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

Сравнительный анализ кредитов по условиям кредитования в Excel

Пример 1. Строительной компании требуется автокран стоимостью 6,5 млн рублей. Стоимость аренды автокрана у другой компании составляет 560000 рублей в год, а срок полезного использования составляет 10 лет, по истечению которых остаточная стоимость автокрана составит всего 1200000 рублей, а он возвращается в собственность арендодателю. Альтернативным вариантом является привлечение стороннего капитала со ставкой 19% годовых. Какой вариант более выгодный?

Вид таблицы данных:

Для расчета внутренней ставки доходности на основе имеющихся данных используем формулу:

Ячейки из диапазона D2:D12 содержат числовые значения финансовых потоков на протяжении 10 лет аренды автокрана. В результате расчетов получим:

Такой результат свидетельствует о том, что аренда автокрана выгоднее, чем привлечение внешних инвестиций на его приобретение (например, кредит в банке), так как 12%<19%

Как узнать годовую процентную ставку по депозиту в Excel?

Пример 2. Клиент открыл депозитный счет в банке на 5 лет и перевел средства на сумму 200000 рублей. Ежегодно он снимал по 25000 рублей процентов, а в последний год вывел со счета вложенную сумму и последние начисленные проценты. Определить годовую процентную ставку по депозиту.

Вид таблицы данных:

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

Депозитный счет был открыт под 13% годовых.

Сравнительный анализ инвестиций по условиям вкладов в Excel

Пример 3. Инвестор имеет 10 млн. рублей, которые он может вложить банк и получать 1100000 рублей ежегодно на протяжении 3 лет либо приобрести квартиру за 10 млн. рублей и сдавать ее в аренду по цене 40000 рублей в месяц в первый год и 45000 рублей – во второй и 50000 — третий, а по истечению 3-летнего периода аренды продать за 12 млн рублей. Определить более выгодный вариант для капиталовложения.

Вид таблицы данных:

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

Как видно, покупка квартиры и последующая сдача ее в аренду является более прибыльной идеей, однако это предприятие сопряжено с различными рисками и основывается на предположении, что квартиру удастся продать за 12 млн. рублей.

Особенности использования функции ВСД в Excel

Функция имеет следующую форму синтаксической записи:

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

• значения – обязательный аргумент, принимающий константу массива или ссылку на диапазон ячеек, которые содержат числовые значения расходов (отрицательные значения) и доходов (положительные), на основе которых будет произведен расчет внутренней ставки доходности;
• [предположения] – необязательный для заполнения аргумент, принимающий числовое значение, характеризующее предположительную величину ставки. Если этот аргумент явно не указан, по умолчанию принимается значение 10%.

1. Константа массива или диапазон ячеек, передаваемые в качестве аргумента значения, должны содержать не менее одного отрицательного и положительного чисел соответственно. Иначе функция ВСД вернет код ошибки #ЧИСЛО!.
2. Функция ВСД игнорирует любые нечисловые значения или данные, которые не могут быть преобразованы к числам (например, имена и текстовые строки, которые не могут быть интерпретированы как числа) при выполнении расчетов, в том числе логические ИСТИНА и ЛОЖЬ.
3. Функция ВСД выполняет итеративные вычисления, то есть поиск итогового результата методом последовательных приближений. «Опорной точкой» в таких расчетах является значение, указанное в качестве аргумента [предположения] или установленное по умолчанию значение 10% (если второй аргумент явно не указан). При расчетах выполняется 20 итераций (пересчетов итогового значения). Если точность полученного результата ниже 0,00001%, функция ВСД вернет код ошибки #ЧИСЛО!.
4. Если возвращаемый функцией ВСД результат далек от предполагаемого или функция закончила работу с кодом ошибки #ЧИСЛО!, рекомендуется указать другое значение в качестве второго аргумента и повторить расчет.
5. Чистая приведенная стоимость инвестиций со ставкой, равной внутренней ставке доходности, приблизительно равна нулю. Для расчета первой в Excel предусмотрена функция ЧПС. Так, формула =ЧПС(ВСД(A1:A5);A1:A5) вернет близкое к нулю значение, но не 0, поскольку функция ВСД выполняет приближенные расчеты.

Чистая приведенная стоимость NPV (ЧПС) и внутренняя ставка доходности IRR (ВСД) в MS EXCEL

Рассчитаем Чистую приведенную стоимость и Внутреннюю норму доходности с помощью формул MS EXCEL.

Начнем с определения, точнее с определений.

Чистой приведённой стоимостью (Net present value, NPV) называют сумму дисконтированных значений потока платежей, приведённых к сегодняшнему дню (взято из Википедии).
Или так: Чистая приведенная стоимость – это Текущая стоимость будущих денежных потоков инвестиционного проекта, рассчитанная с учетом дисконтирования, за вычетом инвестиций (сайт cfin.ru)
Или так: Текущая стоимость ценной бумаги или инвестиционного проекта, определенная путем учета всех текущих и будущих поступлений и расходов при соответствующей ставке процента. (Экономика. Толковыйсловарь. — М.: «ИНФРА—М«, Издательство «ВесьМир«. Дж. Блэк.)

Примечание1. Чистую приведённую стоимость также часто называют Чистой текущей стоимостью, Чистым дисконтированным доходом (ЧДД). Но, т.к. соответствующая функция MS EXCEL называется ЧПС() , то и мы будем придерживаться этой терминологии. Кроме того, термин Чистая Приведённая Стоимость (ЧПС) явно указывает на связь с Приведенной стоимостью.

Для наших целей (расчет в MS EXCEL) определим NPV так:
Чистая приведённая стоимость — это сумма Приведенных стоимостей денежных потоков, представленных в виде платежей произвольной величины, осуществляемых через равные промежутки времени.

Совет: при первом знакомстве с понятием Чистой приведённой стоимости имеет смысл познакомиться с материалами статьи Приведенная стоимость.

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

Как было сказано, в MS EXCEL для вычисления Чистой приведённой стоимости используется функция ЧПС() (английский вариант — NPV()). В ее основе используется формула:

CFn – это денежный поток (денежная сумма) в период n. Всего количество периодов – N. Чтобы показать, является ли денежный поток доходом или расходом (инвестицией), он записывается с определенным знаком (+ для доходов, минус – для расходов). Величина денежного потока в определенные периоды может быть =0, что эквивалентно отсутствию денежного потока в определенный период (см. примечание2 ниже). i – это ставка дисконтирования за период (если задана годовая процентная ставка (пусть 10%), а период равен месяцу, то i = 10%/12).

Примечание2. Т.к. денежный поток может присутствовать не в каждый период, то определение NPV можно уточнить: Чистая приведённая стоимость — это Приведенная стоимость денежных потоков, представленных в виде платежей произвольной величины, осуществляемых через промежутки времени, кратные определенному периоду (месяц, квартал или год). Например, начальные инвестиции были сделаны в 1-м и 2-м квартале (указываются со знаком минус), в 3-м, 4-м и 7-м квартале денежных потоков не было, а в 5-6 и 9-м квартале поступила выручка по проекту (указываются со знаком плюс). Для этого случая NPV считается точно также, как и для регулярных платежей (суммы в 3-м, 4-м и 7-м квартале нужно указать =0).

Если сумма приведенных денежных потоков представляющих собой доходы (те, что со знаком +) больше, чем сумма приведенных денежных потоков представляющих собой инвестиции (расходы, со знаком минус), то NPV >0 (проект/ инвестиция окупается). В противном случае NPV Похожие задачи