Irr функция в excel: Функция ВСД (IRR)

Содержание

Внутренняя норма доходности на excel

Внутреннюю норму доходности можно довольно легко рассчитать при помощи встроенной финансовой функции ВСД (IRR) в MS Exel.

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

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

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

ВСД(Значения;Предположение)

где
Значения — это массив или ссылка на ячейки, содержащие числа, для которых требуется подсчитать внутреннюю ставку доходности, учитывая требования указанные выше;
Предположение — это величина, о которой предполагается, что она близка к результату ВСД:

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

Пример расчёта внутренней ставки доходности (на основе данных о денежных потоках по трём проектам, которые рассматривались выше):

Внутренняя норма доходности проекта. — Do Excellent — LiveJournal

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

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

Взаимосвязь NPV, ставки дисконтирования и IRR можно проследить на графике ниже.

Внутреннюю норму доходности можно интерпретировать следующим образом: если IRR проекта равна 15%, мы получаем годовую норму доходности или прибыли 15% для инвестированного нами потока денежных средств.

Внутреннюю норму доходности вычисляет функция ВСД (внутренняя ставка доходности).
ВСД (ряд_потоков_денежных_средств; [предположение])
Важно:

при расчете ВСД необходимо осуществлять работу по недисконтированному потоку денежных средств.

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

Проект является прибыльным, если значение ВСД больше цены капитала.

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

Во многих ситуациях ставка, под которую компания одалживает денежные средства, отличается от ставки, с которой компания реинвестирует денежные средства. В вычислениях ВСД предполагается, что ставки финансирования и реинвестирования денежных средств равны. Если известны реальные ставки, по которым мы заимствуем деньги и реинвестируем вложения, тогда функция вычисления модифицированной внутренней ставки доходности МВСД ( ) MIRR ( ) подсчитывает ставку дисконтирования, которая делает ЧПС всех наших денежных потоков денежных средств (включая возврат нашего кредита и наши доходы от реинвестирования с заданными ставками) равной 0. У функции МВСД ( ) следующая запись:
МВСД (значения_потоков_денежных_средств; ставка_финансирования; ставка_реинвестирования).

Задачи:
1. Вычислите ВСД для следующего ряда потоков денежных средств.

Ссылка на файл.

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

Ссылка на файл.

3. Вычислите ВСД для следующих потоков денежных средств из задачи 1 при условии, что мы можем взять кредит под 12% годовых и инвестировать доходы с годовой ставкой реинвестирования 15%. Вычислите МВСД проекта.

Ссылка на файл.

Numbers – Совместимость на Mac – Apple (RU)

Свойства листа	Поддерживается	Частично поддерживается	Не поддерживается
Упорядоченные объекты
Фон
Закреплённые области		Поддерживаются при экспорте
Сгруппированные объекты
Граница изображения		Некоторые границы могут выглядеть иначе или отсутствовать
Эффекты на изображениях		Некоторые эффекты могут выглядеть иначе или отсутствовать
Вставленный рисунок
Вставленное изображение
Вставленные фигуры
Заблокированная ячейка
Поля
Разрывы страниц
Ориентация страницы
Защищённый лист
Стили фигур		Некоторые стили могут выглядеть иначе или отсутствовать
Графические элементы SmartArt		Импортируются как фигуры
Разбиения
Листы
Свойства таблицы	Поддерживается	Частично поддерживается	Не поддерживается
Чередующиеся столбцы		Форматирование сохраняется
Чередующиеся строки		Форматирование сохраняется
Ширина столбца
Первый столбец
Строка заголовка		Форматирование сохраняется
Вставленные формулы
Вставленные символы
Последний столбец
Объединённые ячейки
Высота строки
Стили таблиц		Форматирование сохраняется
Строка итогов		Значения ячеек сохраняются, но сами формулы удаляются
Тип ячейки	Поддерживается	Частично поддерживается	Не поддерживается
Финансовый формат
Условное форматирование
Денежный формат
Пользовательское форматирование
Формат даты и времени
Дробный формат
Общий формат
Числовой формат
Процентный формат
Экспоненциальный формат
Особое форматирование
Текстовый формат
Работа с данными	Поддерживается	Частично поддерживается	Не поддерживается
Проверка данных
Внешние данные
Фильтры		Сохраняется форматирование и результаты
Сводная таблица
Срезы
Таблица сортировки
Форматирование	Поддерживается	Частично поддерживается	Не поддерживается
Форматирование границы
Заливка ячейки
Поля вставленного в ячейку объекта
Перенос текста в ячейке
Гиперссылки в электронной почте
Типы шрифтов		Импортируются шрифты, доступные для скачивания. Шрифты, которые невозможно импортировать, заменяются.
Скрытие и отображение столбцов
Скрытие и отображение строк
Горизонтальное выравнивание в ячейке
Форматированный текст в ячейках
Стили штриха
Вертикальное выравнивание в ячейке
Гиперссылки на веб-страницы
Свойства и форматы диаграмм	Поддерживается	Частично поддерживается	Не поддерживается
Название оси категорий
Подписи категорий/метки рядов
Источник данных диаграммы
Макеты диаграмм
Стили диаграмм
Название диаграммы
Заливка
Название оси ряда
Источник данных ряда
Порядок рядов
Тень
Показ таблицы данных
Показывать ключи легенд в таблице данных
Отображение основных линий сетки ряда
Отображение вспомогательных линий сетки ряда
Спарклайны
Стили штриха
Заголовок оси значений
Диаграммы	Поддерживается	Частично поддерживается	Не поддерживается
Двумерная гистограмма
Двумерная гистограмма с накоплением
Двумерная нормированная гистограмма с накоплением
Двумерная линейчатая диаграмма
Двумерная линейчатая диаграмма с накоплением
Двумерная нормированная линейчатая диаграмма с накоплением
Двумерный график
Двумерный график с накоплением		Импортируется как линейная диаграмма
Двумерный график с маркерами
Двумерный график с маркерами и накоплением
Двумерный нормированный график с маркерами и накоплением
Двумерный нормированный график с накоплением		Импортируется как линейная диаграмма
Двумерная диаграмма с областями
Двумерная диаграмма с областями и накоплением
Двумерная нормированная диаграмма с областями и накоплением
Двумерная точечная диаграмма с гладкими кривыми и маркерами
Двумерная точечная диаграмма с гладкими кривыми
Двумерная точечная диаграмма с прямыми отрезками и маркерами
Двумерная точечная диаграмма с прямыми отрезками
Двумерная круговая диаграмма
Двумерная разрезанная круговая диаграмма
Двумерная вторичная круговая диаграмма		Импортируется как круговая диаграмма
Двумерная вторичная линейчатая диаграмма		Импортируется как круговая диаграмма
Двумерная точечная диаграмма с маркерами
Трёхмерная гистограмма
Трёхмерная гистограмма с накоплением
Трёхмерная нормированная гистограмма с накоплением
Трёхмерная линейчатая диаграмма
Трёхмерная линейчатая диаграмма с накоплением
Трёхмерная нормированная линейчатая диаграмма с накоплением
Трёхмерный график
Трёхмерная диаграмма с областями
Трёхмерная круговая диаграмма
Трёхмерная разрезанная круговая диаграмма
Трёхмерная диаграмма с областями и накоплением
Трёхмерная нормированная диаграмма с областями и накоплением
Пузырьковые диаграммы
Кольцевая диаграмма		Импортируется как одноуровневая кольцевая диаграмма
Разрезанная кольцевая диаграмма		Импортируется как одноуровневая кольцевая диаграмма
Поверхностные диаграммы
Лепестковые диаграммы
Биржевая диаграмма (самый высокий курс, самый низкий курс, курс закрытия)		Импортируется как линейная диаграмма
Биржевая диаграмма (курс открытия, самый высокий курс, самый низкий курс, курс закрытия)		Импортируется как линейная диаграмма
Биржевая диаграмма (объём, самый высокий курс, самый низкий курс, курс закрытия)		Импортируется как полосчатая диаграмма
Биржевая диаграмма (объём, курс открытия, самый высокий курс, самый низкий курс, курс закрытия)		Импортируется как полосчатая диаграмма
Цилиндрическая гистограмма
Цилиндрическая гистограмма с накоплением
Нормированная цилиндрическая линейчатая диаграмма с накоплением
Трёхмерная цилиндрическая гистограмма
Коническая гистограмма		Импортируется как трёхмерная цилиндрическая гистограмма
Коническая гистограмма с накоплением		Импортируется как трёхмерная цилиндрическая гистограмма
Нормированная коническая гистограмма с накоплением		Импортируется как трёхмерная цилиндрическая гистограмма
Трёхмерная коническая гистограмма		Импортируется как трёхмерная цилиндрическая гистограмма
Пирамидальная гистограмма		Импортируется как трёхмерная прямоугольная гистограмма
Пирамидальная гистограмма с накоплением		Импортируется как трёхмерная прямоугольная гистограмма
Нормированная пирамидальная гистограмма с накоплением		Импортируется как трёхмерная прямоугольная гистограмма
Трёхмерная пирамидальная гистограмма		Импортируется как трёхмерная прямоугольная гистограмма
Цилиндрическая линейчатая диаграмма
Цилиндрическая линейчатая диаграмма с накоплением
Нормированная цилиндрическая линейчатая диаграмма с накоплением
Коническая линейчатая диаграмма		Импортируется как цилиндрическая линейчатая диаграмма
Коническая линейчатая диаграмма с накоплением		Импортируется как цилиндрическая линейчатая диаграмма
Нормированная коническая линейчатая диаграмма с накоплением		Импортируется как цилиндрическая линейчатая диаграмма
Пирамидальная линейчатая диаграмма		Импортируется как трёхмерная прямоугольная линейчатая диаграмма
Пирамидальная линейчатая диаграмма с накоплением		Импортируется как трёхмерная прямоугольная линейчатая диаграмма
Нормированная пирамидальная линейчатая диаграмма с накоплением		Импортируется как трёхмерная прямоугольная линейчатая диаграмма
Другое	Поддерживается	Частично поддерживается	Не поддерживается
Комментарии
Рукописные примечания		Импортируются как рисунки
Макросы
Объекты OLE
Защищённая книга
Отслеживаемые изменения
Функции куба	Поддерживается	Частично поддерживается	Не поддерживается
КУБЭЛЕМЕНТКИП
КУБЭЛЕМЕНТ
КУБСВОЙСТВОЭЛЕМЕНТА
КУБПОРЭЛЕМЕНТ
КУБМНОЖ
КУБЧИСЛОЭЛМНОЖ
КУБЗНАЧЕНИЕ
Функции базы данных	Поддерживается	Частично поддерживается	Не поддерживается
ДСРЗНАЧ
БСЧЁТ
БСЧЁТА
БИЗВЛЕЧЬ
ДМАКС
ДМИН
БДПРОИЗВЕД
ДСТАНДОТКЛ
ДСТАНДОТКЛП
БДСУММ
БДДИСП
БДДИСПП
ПОЛУЧИТЬ. ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Функции даты и времени	Поддерживается	Частично поддерживается	Не поддерживается
ДАТА
РАЗНДАТ
ДАТАЗНАЧ
ДЕНЬ
ДНЕЙ360
ДАТАМЕС
КОНМЕСЯЦА
ЧАС
МИНУТА
МЕСЯЦ
ЧИСТРАБДНИ
ЧИСТРАБДНИ.МЕЖД
ТДАТА
СЕКУНДА
ВРЕМЯ
ВРЕМЗНАЧ
СЕГОДНЯ
ДЕНЬНЕД
НОМНЕДЕЛИ
РАБДЕНЬ
РАБДЕНЬ. МЕЖД
ГОД
ДОЛЯГОДА
Технические функции	Поддерживается	Частично поддерживается	Не поддерживается
БЕССЕЛЬ.I
БЕССЕЛЬ.J
БЕССЕЛЬ.K
БЕССЕЛЬ.Y
ДВ.В.ДЕС
ДВ.В.ШЕСТН
ДВ.В.ВОСЬМ
КОМПЛЕКСН
ПРЕОБРАЗОВАТЬ
ДЕС.В.ДВ
ДЕС. В.ШЕСТН
ДЕС.В.ВОСЬМ
ДЕЛЬТА
ФОШ
ФОШ.ТОЧН
ДФОШ
ДФОШ.ТОЧН
ПОРОГ
ШЕСТН.В.ДВ
ШЕСТН.В.ДЕС
ШЕСТН.В.ВОСЬМ
МНИМ.ABS
МНИМ.ЧАСТЬ
МНИМ.АРГУМЕНТ
МНИМ.СОПРЯЖ
МНИМ. COS
МНИМ.ДЕЛ
МНИМ.EXP
МНИМ.LN
МНИМ.LOG10
МНИМ.LOG2
МНИМ.СТЕПЕНЬ
МНИМ.ПРОИЗВЕД
МНИМ.ВЕЩ
МНИМ.SIN
МНИМ.КОРЕНЬ
МНИМ.РАЗН
МНИМ.СУММ
ВОСЬМ.В.ДВ
ВОСЬМ.В.ДЕС
ВОСЬМ. В.ШЕСТН
Финансовые функции	Поддерживается	Частично поддерживается	Не поддерживается
НАКОПДОХОД
НАКОПДОХОДПОГАШ
АМОРУМ
АМОРУВ
ДНЕЙКУПОНДО
ДНЕЙКУПОН
ДНЕЙКУПОНПОСЛЕ
ДАТАКУПОНПОСЛЕ
ЧИСЛКУПОН
ДАТАКУПОНДО
ОБЩПЛАТ
ОБЩДОХОД
ФУО
ДДОБ
СКИДКА
РУБЛЬ. ДЕС
РУБЛЬ.ДРОБЬ
ДЛИТ
ЭФФЕКТ
БС
БЗРАСПИС
ИНОРМА
ПРПЛТ
ВСД
ПРОЦПЛАТ
МДЛИТ
МВСД
НОМИНАЛ
КПЕР
ЧПС
ЦЕНАПЕРВНЕРЕГ
ДОХОДПЕРВНЕРЕГ
ЦЕНАПОСЛНЕРЕГ
ДОХОДПОСЛНЕРЕГ
ПЛТ
ОСПЛТ
ЦЕНА
ЦЕНАСКИДКА
ЦЕНАПОГАШ
ПС
СТАВКА
ПОЛУЧЕНО
АПЛ
АСЧ
РАВНОКЧЕК
ЦЕНАКЧЕК
ДОХОДКЧЕК
ПУО
ЧИСТВНДОХ
ЧИСТНЗ
ДОХОД
ДОХОДСКИДКА
ДОХОДПОГАШ
Информационные функции	Поддерживается	Частично поддерживается	Не поддерживается
ЯЧЕЙКА
ТИП. ОШИБКИ
ИНФОРМ
ЕПУСТО
ЕОШ
ЕОШИБКА
ЕЧЁТН
ЕЛОГИЧ
ЕНД
ЕНЕТЕКСТ
ЕЧИСЛО
ЕНЕЧЁТ
ЕССЫЛКА
ЕТЕКСТ
Ч
НД
ТИП
Логические функции	Поддерживается	Частично поддерживается	Не поддерживается
И
ЛОЖЬ
ЕСЛИ
ЕСЛИОШИБКА
НЕ
ИЛИ
ИСТИНА
Функции поиска и работы со ссылками	Поддерживается	Частично поддерживается	Не поддерживается
АДРЕС		Сохраняется последнее рассчитанное значение, а сама функция может быть сохранена или удалена в зависимости от использованных значений
ОБЛАСТИ
ВЫБОР
АГРЕГАТ
СТОЛБЕЦ
ЧИСЛСТОЛБ
ГПР
ГИПЕРССЫЛКА
ИНДЕКС (массив)
ИНДЕКС (ссылка)
ДВССЫЛ
ПРОСМОТР (массив)
ПРОСМОТР (вектор)
ПОИСКПОЗ
СМЕЩ
СТРОКА
ЧСТРОК
ТРАНСП
ВПР
Математические и тригонометрические функции	Поддерживается	Частично поддерживается	Не поддерживается
ABS
ACOS
ACOSH
АГРЕГАТ
ASIN
ASINH
ATAN
ATAN2
ATANH
ОКРВВЕРХ
ОКРВВЕРХ. ТОЧН
ЧИСЛКОМБ
COS
COSH
ГРАДУСЫ
ЧЁТН
EXP
ФАКТР
ДВФАКТР
ОКРВНИЗ
ОКРВНИЗ.ТОЧН
НОД
ЦЕЛОЕ
ISO.ОКРВВЕРХ
НОК
LN
LOG
LOG10
МОПРЕД
МОБР
МУМНОЖ
ОСТАТ
ОКРУГЛТ
МУЛЬТИНОМ
НЕЧЁТ
ПИ
СТЕПЕНЬ
ПРОИЗВЕД
ЧАСТНОЕ
РАДИАНЫ
СЛЧИС
СЛУЧМЕЖДУ
РИМСКОЕ
ОКРУГЛ
ОКРУГЛВНИЗ
ОКРУГЛВВЕРХ
РЯД. СУММ
ЗНАК
SIN
SINH
КОРЕНЬ
КОРЕНЬПИ
ПРОМЕЖУТОЧНЫЕ.ИТОГИ
СУММА
СУММЕСЛИ
СУММЕСЛИМН
СУММПРОИЗВ
СУММКВ
СУММРАЗНКВ
СУММСУММКВ
СУММКВРАЗН
TAN
TANH
ОТБР
Статистические функции	Поддерживается	Частично поддерживается	Не поддерживается
СРОТКЛ
СРЗНАЧ
СРЗНАЧА
СРЗНАЧЕСЛИ
СРЗНАЧЕСЛИМН
БЕТА. РАСП
БЕТА.ОБР
БЕТАРАСП
БЕТАОБР
БИНОМ.РАСП
БИНОМ.ОБР
БИНОМРАСП
ХИ2РАСП
ХИ2ОБР
ХИ2.РАСП
ХИ2.РАСП.ПХ
ХИ2.ОБР
ХИ2.ОБР.ПХ
ХИ2.ТЕСТ
ХИ2ТЕСТ
ДОВЕРИТ
ДОВЕРИТ. НОРМ
ДОВЕРИТ.СТЬЮДЕНТ
КОРРЕЛ
СЧЁТ
СЧЁТЗ
СЧИТАТЬПУСТОТЫ
СЧЁТЕСЛИ
СЧЁТЕСЛИМН
КОВАР
КОВАРИАЦИЯ.Г
КОВАРИАЦИЯ.В
КРИТБИНОМ
КВАДРОТКЛ
ЭКСП.РАСП
ЭКСПРАСП
F. РАСП
F.РАСП.ПХ
F.ОБР
F.ОБР.ПХ
F.ТЕСТ
FРАСП
FРАСПОБР
ФИШЕР
ФИШЕРОБР
ПРЕДСКАЗ
ЧАСТОТА
ФТЕСТ
ГАММА.РАСП
ГАММА.ОБР
ГАММАРАСП
ГАММАОБР
ГАММАНЛОГ
ГАММАНЛОГ. ТОЧН
СРГЕОМ
РОСТ
СРГАРМ
ГИПЕРГЕОМЕТ
ОТРЕЗОК
ЭКСЦЕСС
НАИБОЛЬШИЙ
ЛИНЕЙН
ЛГРФПРИБЛ
ЛОГНОРМОБР
ЛОГНОРМ.РАСП
ЛОГНОРМ.ОБР
ЛОГНОРМРАСП
МАКС
МАКСА
МЕДИАНА
МИН
МИНА
МОДА
МОДА. НСК
МОДА.ОДН
ОТРБИНОМ.РАСП
ОТРБИНОМРАСП
НОРМ.РАСП
НОРМ.ОБР
НОРМ.СТ.РАСП
НОРМ.СТ.ОБР
НОРМРАСП
НОРМОБР
НОРМСТРАСП
НОРМСТОБР
ПИРСОН
ПЕРСЕНТИЛЬ
ПРОЦЕНТИЛЬ.ИСКЛ
ПРОЦЕНТИЛЬ. ВКЛ
ПРОЦЕНТРАНГ
ПРОЦЕНТРАНГ.ИСКЛ
ПРОЦЕНТРАНГ.ВКЛ
ПЕРЕСТ
ПУАССОН
ПУАССОН.РАСП
ВЕРОЯТНОСТЬ
КВАРТИЛЬ
КВАРТИЛЬ.ИСКЛ
КВАРТИЛЬ.ВКЛ
РАНГ
РАНГ.СР
РАНГ.РВ
КВПИРСОН
СКОС
НАКЛОН
НАИМЕНЬШИЙ
НОРМАЛИЗАЦИЯ
СТАНДОТКЛОН
СТАНДОТКЛОН. Г
СТАНДОТКЛОН.В
СТАНДОТКЛОНА
СТАНДОТКЛОНП
СТАНДОТКЛОНПА
СТОШYX
СТЬЮДЕНТ.РАСП
СТЬЮДЕНТ.РАСП.2Х
СТЬЮДЕНТ.РАСП.ПХ
СТЬЮДЕНТ.ОБР
СТЬЮДЕНТ.ОБР.2Х
СТЬЮДРАСП
СТЬЮДРАСП
ТЕНДЕНЦИЯ
УРЕЗСРЕДНЕЕ
ТТЕСТ
ДИСП
ДИСП. Г
ДИСП.В
ДИСПА
ДИСПР
ДИСПРА
ВЕЙБУЛЛ
ВЕЙБУЛЛ.РАСП
Z.ТЕСТ
ZТЕСТ
Текстовые функции	Поддерживается	Частично поддерживается	Не поддерживается
ASC
СИМВОЛ		Сохраняется последнее рассчитанное значение, а сама функция может быть сохранена или удалена в зависимости от используемых значений
ПЕЧСИМВ
КОДСИМВ		Сохраняется последнее рассчитанное значение, а сама функция может быть сохранена или удалена в зависимости от используемых значений
СЦЕПИТЬ
РУБЛЬ
СОВПАД
НАЙТИ
НАЙТИБ
ФИКСИРОВАННЫЙ
JIS
ЛЕВСИМВ
ЛЕВБ
ДЛСТР
ДЛИНБ
СТРОЧН
ПСТР
ПСТРБ
PHONETIC
ПРОПНАЧ
ЗАМЕНИТЬ
ЗАМЕНИТЬБ
ПОВТОР
ПРАВСИМВ
ПРАВБ
ПОИСК
ПОИСКБ
ПОДСТАВИТЬ
T
ТЕКСТ
СЖПРОБЕЛЫ
ПРОПИСН
ЗНАЧЕН
ЙЕНА

Excel Data Финансовый анализ — CoderLessons.

com

Вы можете легко выполнить финансовый анализ в Excel. Excel предоставляет вам несколько финансовых функций, таких как PMT, PV, NPV, XNPV, IRR, MIRR, XIRR и т. Д., Которые позволяют быстро получить результаты финансового анализа.

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

Что такое аннуитет?

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

Положительное число представляет полученные денежные средства.
Отрицательное число представляет выплаченные деньги.

Приведенная стоимость серии будущих платежей

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

PV — рассчитывает текущую стоимость инвестиций с использованием процентной ставки и серии будущих платежей (отрицательные значения) и дохода (положительные значения). По крайней мере, один из денежных потоков должен быть положительным, и, по крайней мере, один должен быть отрицательным.
NPV — Расчет чистой приведенной стоимости инвестиций с использованием ставки дисконтирования и серии периодических будущих платежей (отрицательные значения) и дохода (положительные значения).
XNPV — вычисляет чистую приведенную стоимость для графика движения денежных средств, который не обязательно является периодическим.

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

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

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

Обратите внимание, что —

Денежные потоки PV должны быть постоянными, тогда как денежные потоки NPV могут быть переменными.
Денежные потоки PV могут быть в начале или в конце периода, тогда как денежные потоки NPV должны быть в конце периода.
Денежные потоки NPV должны быть периодическими, тогда как денежные потоки XNPV не должны быть периодическими.

Денежные потоки PV должны быть постоянными, тогда как денежные потоки NPV могут быть переменными.

Денежные потоки PV могут быть в начале или в конце периода, тогда как денежные потоки NPV должны быть в конце периода.

Денежные потоки NPV должны быть периодическими, тогда как денежные потоки XNPV не должны быть периодическими.

В этом разделе вы поймете, как работать с PV. Вы узнаете о NPV в следующем разделе.

пример

Предположим, вы покупаете холодильник. Продавец говорит, что цена холодильника составляет 32000, но у вас есть возможность выплатить сумму через 8 лет с процентной ставкой 13% годовых и ежегодными выплатами 6000. У вас также есть возможность совершать платежи. либо в начале или в конце каждого года.

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

Вы можете использовать функцию Excel PV —

PV (rate, nper, pmt, [fv ], [type])

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

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

Вы получите следующие результаты —

Следовательно,

Если вы делаете платеж сейчас, вам нужно заплатить 32 000 от текущей стоимости.
Если вы выбираете ежегодные платежи с оплатой в конце года, вам нужно заплатить 28 793 от приведенной стоимости.
Если вы выберете ежегодные платежи с оплатой в конце года, вам нужно будет заплатить 32 536 от текущей стоимости.

Вы можете ясно видеть, что вариант 2 выгоден для вас.

Что такое EMI?

Приравниваемый ежемесячный платеж (EMI) определяется Investopedia как «Фиксированная сумма платежа, сделанная заемщиком кредитору на определенную дату каждого календарного месяца. Приравниваемые ежемесячные платежи используются для выплаты процентов и основной суммы каждый месяц, так что в течение По истечении указанного количества лет кредит погашается полностью ».

EMI на займе

В Excel вы можете рассчитать EMI по ссуде с помощью функции PMT.

Предположим, вы хотите взять ипотечный кредит на 5000000 с годовой процентной ставкой 11,5% и сроком кредита на 25 лет. Вы можете найти свой EMI следующим образом —

Рассчитать процентную ставку в месяц (Процентная ставка в год / 12)
Рассчитать количество ежемесячных платежей (количество лет * 12)
Используйте функцию PMT для расчета EMI

Как вы видите,

Приведенная стоимость (PV) — это сумма кредита.
Будущая стоимость (FV) равна 0, так как в конце срока сумма кредита должна быть 0.
Введите 1, поскольку EMI оплачиваются в начале каждого месяца.

Вы получите следующие результаты —

Ежемесячная выплата основной суммы и процентов по кредиту

EMI включает в себя как процентную, так и частичную оплату основного долга. По мере увеличения времени эти два компонента EMI будут меняться, уменьшая баланс.

Получить

Процентная часть ваших ежемесячных платежей, вы можете использовать функцию Excel IPMT.
Оплата основной части ваших ежемесячных платежей, вы можете использовать функцию Excel PPMT.

Процентная часть ваших ежемесячных платежей, вы можете использовать функцию Excel IPMT.

Оплата основной части ваших ежемесячных платежей, вы можете использовать функцию Excel PPMT.

Например, если вы взяли кредит в размере 1 000 000 на срок 8 месяцев по ставке 16% годовых. Вы можете получить значения для EMI, уменьшающиеся суммы процентов, увеличивающуюся выплату основных сумм и уменьшающийся остаток займа в течение 8 месяцев. По истечении 8 месяцев остаток займа будет равен 0.

Следуйте процедуре, приведенной ниже.

Шаг 1 — Рассчитайте EMI следующим образом.

Это приводит к EMI Rs. 13261,59.

Шаг 2 — Затем рассчитайте проценты и основные части EMI за 8 месяцев, как показано ниже.

Вы получите следующие результаты.

Проценты и основная сумма выплачиваются между двумя периодами

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

Вычислите совокупные проценты, выплаченные между 2 и 3 месяцами, используя функцию CUMIPMT.
Проверьте результат, суммируя значения процентов за 2- ^й и 3- ^й месяцы.
Вычислите совокупную основную сумму, выплаченную между 2- ^м и 3- ^м месяцами, используя функцию CUMPRINC.
Проверьте результат, суммируя основные значения за 2- ^й и 3- ^й месяцы.

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

Проверьте результат, суммируя значения процентов за 2- ^й и 3- ^й месяцы.

Вычислите совокупную основную сумму, выплаченную между 2- ^м и 3- ^м месяцами, используя функцию CUMPRINC.

Проверьте результат, суммируя основные значения за 2- ^й и 3- ^й месяцы.

Вы получите следующие результаты.

Вы видите, что ваши расчеты совпадают с вашими результатами проверки.

Расчет процентной ставки

Предположим, вы берете кредит в размере 100 000 и хотите погасить кредит в течение 15 месяцев с максимальным ежемесячным платежом 12 000. Возможно, вы захотите узнать процентную ставку, по которой вы должны платить.

Найти процентную ставку с помощью функции Excel RATE —

Вы получите результат как 8%.

Расчет срока кредита

Предположим, вы берете кредит в размере 100 000 с процентной ставкой 10%. Вы хотите максимальный ежемесячный платеж в размере 15 000. Возможно, вы захотите узнать, сколько времени вам потребуется, чтобы очистить кредит.

Найти количество платежей с помощью функции Excel NPER

Вы получите результат в течение 12 месяцев.

Решения об инвестициях

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

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

Чистая приведенная стоимость последовательности денежных потоков, полученных в разные моменты времени в течение n лет (n может быть дробью), составляет 1 / (1 + r) ⁿ , где r — годовая процентная ставка.

Рассмотрим следующие две инвестиции в течение 3 лет.

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

Денежные потоки могут возникнуть

В конце каждого года.
В начале каждого года.
В середине каждого года.

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

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

Вы получите следующие результаты —

Как вы видите, NPV для Инвестиции 2 выше, чем для Инвестиции 1. Следовательно, Инвестиция 2 — лучший выбор. Вы получили этот результат, так как потоки вывода средств для Инвестиции 2 относятся к более поздним периодам по сравнению с Инвестициями 1.

Денежные потоки в начале года

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

Вы получите следующие результаты —

Денежные потоки в середине года

Предположим, что денежные потоки происходят в середине каждого года. В таком случае вам нужно умножить NPV, полученную из денежных потоков, на $ \ sqrt {1 + r} $, чтобы получить чистую приведенную стоимость.

Вы получите следующие результаты —

Денежные потоки на нерегулярных интервалах

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

Однако в Excel вы можете легко сделать такой расчет с помощью функции XNPV.

Упорядочите свои данные с датами и денежными потоками.

Примечание . Первая дата в ваших данных должна быть самой ранней из всех дат. Другие даты могут встречаться в любом порядке.

Используйте функцию XNPV для расчета чистой приведенной стоимости.

Вы получите следующие результаты —

Предположим, что сегодняшняя дата — 15 марта 2015 года. Как вы заметили, все даты движения денежных средств относятся к более поздним датам. Если вы хотите найти чистую приведенную стоимость на сегодняшний день, включите ее в данные вверху и укажите 0 для денежного потока.

Вы получите следующие результаты —

Внутренняя норма доходности (IRR)

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

Рассмотрим следующие денежные потоки, различные процентные ставки и соответствующие значения NPV.

Как вы можете наблюдать между значениями процентной ставки 10% и 11%, знак NPV меняется. Когда вы настраиваете процентную ставку до 10,53%, NPV составляет почти 0. Следовательно, IRR составляет 10,53%.

Определение IRR денежных потоков для проекта

Вы можете рассчитать IRR денежных потоков с помощью функции Excel IRR.

IRR составляет 10,53%, как вы видели в предыдущем разделе.

Для данных денежных потоков IRR может:

существуют и уникальны
существует и несколько
не существует

Уникальный IRR

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

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

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

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

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

Как вы заметили, IRR имеет уникальное значение для различных значений догадки.

Несколько IRR

В некоторых случаях у вас может быть несколько IRR. Рассмотрим следующие денежные потоки. Рассчитать IRR с различными значениями догадки.

Вы получите следующие результаты —

Вы можете наблюдать, что есть два IRR — -9,59% и 216,09%. Вы можете проверить эти два IRR, вычисляя NPV.

Как для -9,59%, так и для 216,09%, NPV равно 0.

Нет IRR

В некоторых случаях у вас может не быть IRR. Рассмотрим следующие денежные потоки. Рассчитать IRR с различными значениями догадки.

Вы получите результат как #NUM для всех значений догадки.

Результат #NUM означает, что IRR для рассматриваемых денежных потоков отсутствует.

Модели денежных потоков и IRR

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

Если в денежных потоках изменилось более одного знака, IRR может не существовать. Даже если он существует, он не может быть уникальным.

Решения, основанные на IRR

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

Вы уже видели, что NPV позволит вам принимать финансовые решения. Однако IRR и NPV не всегда приводят к одному и тому же решению, когда проекты являются взаимоисключающими.

Взаимоисключающие проекты — это те, для которых выбор одного проекта исключает принятие другого. Когда сравниваемые проекты являются взаимоисключающими, может возникнуть конфликт ранжирования между NPV и IRR. Если вам нужно выбрать между проектом A и проектом B, NPV может предложить принятие проекта A, тогда как IRR может предложить проект B.

Этот тип конфликта между NPV и IRR может возникнуть по одной из следующих причин:

Проекты очень разных размеров, или
Сроки движения денежных средств разные.

Проекты значительной разницы в размерах

Если вы хотите принять решение с помощью IRR, проект A дает доход 100, а проект B — 50. Следовательно, инвестиции в проект A выглядят выгодно. Однако это неправильное решение из-за разницы в масштабах проектов.

Рассмотрим —

У вас есть 1000, чтобы инвестировать.
Если вы инвестируете все 1000 в проект А, вы получите возврат 100.
Если вы инвестируете 100 в проект B, у вас все еще будет 900, которые вы можете инвестировать в другой проект, скажем, в проект C. Предположим, вы получаете доход в размере 20% по проекту C, а затем общий доход по проекту B и проекту C 230, что значительно опережает рентабельность.

У вас есть 1000, чтобы инвестировать.

Если вы инвестируете все 1000 в проект А, вы получите возврат 100.

Если вы инвестируете 100 в проект B, у вас все еще будет 900, которые вы можете инвестировать в другой проект, скажем, в проект C. Предположим, вы получаете доход в размере 20% по проекту C, а затем общий доход по проекту B и проекту C 230, что значительно опережает рентабельность.

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

Проекты с разными сроками движения денежных средств

Опять же, если вы считаете, что IRR решит, проект B будет выбором. Тем не менее, проект А имеет более высокую NPV и является идеальным выбором.

IRR нерегулярно расположенных денежных потоков (XIRR)

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

Внутренняя норма прибыли, которая в результате составляет 26,42%.

Модифицированный IRR (MIRR)

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

Например, рассмотрим денежные потоки, приведенные ниже —

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

Вы получите результат 7%, как показано ниже —

Примечание. В отличие от IRR, MIRR всегда будет уникальным.

Как в Excel рассчитать внутрифирменный коэффициент окупаемости инвестиций — Трюки и приемы в Microsoft Excel

Внутрифирменный коэффициент окупаемости инвестиций (Internal Rate of Return — IRR) — это учетная ставка, когда приведенная стоимость денежных притоков равняется первоначальной инвестиции. Если не пользоваться калькулятором или Excel, тогда соответствующие вычисления представляют собой последовательность предположений, выполняемых наудачу.

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

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

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

Формула для IRR окажется неработоспособной, если в ней не будет хотя бы одного положительного и одного отрицательного числа. Но, по здравому размышлению, ничего странного в этом нет: если все ваши денежные поступления являются отрицательными величинами, о выполнении соответствующего проекта не может быть и речи. Добейтесь, чтобы ваши поступления и расходы были выстроены в той последовательности, в какой они случались в действительности, поскольку эта последовательность учитывается при вычислении IRR. В проекте А, например, Excel выполняет вычисления в ячейках В4-В7. Вообще говоря, когда приходится выбирать между альтернативными проектами, предпочтение следует отдавать проекту с самым высоким значением IRR. В нашем примере более выгодным вариантом является проект В.

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

панацея от неопределённости или проклятие финансиста?

Мы продолжаем наше путешествие по азам принятия инвестиционных решений. В прошлый раз, когда мы приземлились на благодатную почву финансового анализа мы прониклись методом расчёта чистой приведённой стоимости и даже более того, успели немного поковыряться с соответствующей функцией в Excel. Однако, как оказалось, при всех достоинствах NPV как инструмента анализа проекта, без недостатков не обошлось (в частности, не учитывается масштаб проекта, а также устойчивость проекта к повышению рисков и следовательно, ставки дисконтирования). Поэтому из нашей кладовой с инструментами мы сегодня презентуем метод оценки под названием Internal Rate of Return (он же IRR, он же Внутренняя норма доходности). Поехали!

1. Формула расчёта

Идея, которая лежит в основе, напоминает, в целом, то, что мы наблюдали в концепте чистой приведённой стоимости. Только изначально вопрос стоит по иному: какова должна быть ставка дисконтирования, чтобы приведённая стоимость проектов была равна нулю (при NPV вопрос стоит так: какова приведённая стоимость денежных потоков при заданной ставке дисконтирования/риске)? По сути, берётся прежняя формула, но изменятся наборы переменных и констант. Стоимость конечных потоков приравниваем к нулю, а ищем ставку процента.

Формула расчёта внутренней нормы доходности

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

2. Правило принятия решения

Что мы получаем на выходе? Верно, ту ставку дисконтирования, при которой проект больше не принесёт дополнительной единицы приведённого дохода фирме. И? Что с этим делать дальше. Допустим, у одного проекта ставка 10%, а у второго 20% — выбирать только второй? Или только оба? Или ни одного? А принцип при прочих равных условиях действует следующий:

Правило: Принимайте все проекты, чей IRR выше затрат на капитал вашей фирмы. И наоборот, отклоняйте те, чья норма доходности ниже расходов на капитал компании.

Это академическая точка зрения. Если Вас ни коим образом не волнует то, что Ваш сосед вложился в Tesla и срубает по 50% в год альтернативная возможность инвестирования вашего капитала, можно упростить его до формы: берите проект с наивысшим IRR. Но после этого не удивляйтесь почему сосед ездит на Porsche, а вы на Kia Rio (хотя ещё не факт что лучше, в наших условиях) вероятному возмущению акционеров.

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

3. Как считать?

Итак, теорию усвоили. Что дальше? Берём ручку, бумагу, калькулятор и кладём их в сторону, потому что высчитывать корень пятнадцатой степени из суммы частных займёт недели две. Открываем Excel, забиваем денежные потоки в одну строчку, периоды в другую. Отдельное значение отдаём NPV, ставочку процента оставляем чуть ниже. Получается нечто вроде такой картинки.

Норма доходности проекта Ugrick равна 20 процентам

IRR в данном случае находился через функцию Solver (поиск решения). Удобнее пользоваться встроенной функцией Excel, но об этом будет отдельный разговор. Как мы видим, ничего сложного с применением современных вычислительных пакетов не происходит. Однако существует парочка «но», которые могут испортить подобную идиллию. О них мы и поговорим поподробнее.

4. В дебрях степенных функций

Прежде чем начать «разбор полётов» предлагаю бросить взгляд на следующую картинку, имеющую прямое отношение к вышеуказанному проекту с кодовым названием «Ugrick».

Кривая NPV «Ugrick»

Линейный график показывает, каким образом изменяется ценность денежных потоков при росте ставки дисконтирования. Как мы видим, стоимость падает по мере роста ставки, а в точке Rate = 20% равняется нулю. Всё, как по заказу. Увы, так бывает далеко не всегда.

4.1: Множество положительных корней

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

Последовательная и непоследовательная смена знаков денежных потоков приводит к коллизиям подобной той, что отражена на графике

Случай с Mu-La аналогичен предыдущему, за исключением того, что положительные и отрицательные потоки сменяют друг друга. Почему так? Потому что NPV — степенная функция. NPV = f (Rate) и посему дополнительная смена знаков приводит к появлению ещё одного корня, при котором NPV = 0. И здесь правило, данное выше не работает, потому что в соответствии с ним, если ставка дисконтирования лежит выше 5% (что является одним из решений), мы должны отклонить проект. В действительности, как видно из графика, именно на промежутке с 5 до 20% NPV лежит выше нуля. Виной всему большой положительный денежный поток в первом периоде, который затем сменяется отрицательным. Поскольку скорость роста дисконтирования у второго периода выше, чем у первого (за счёт формулы сложных процентов), можно подобрать соответствующую ставку процента, при которой поток от второго периода будет покрывать как затраты в начальном периоде, так и (за счёт действия эффекта сложных процентов) нивелировать эффект от негативного денежного потока в следующем периоде. Причём не стоит забывать и о зеркальных ситуация вроде этой:

В подобном случае, правило IRR рабочее, хотя упускается из виду и второе решение (20%)

Всё остаётся прежним, за исключением знаков, которые были изменены на противоположные. Мало того, здесь уже и указания, описанные ранее, также подходят, хотя из него можно не увидеть, что проект стоит принимать и если ставка дисконтирования выше 20%. Но ведь пока не построишь график и не рассчитаешь NPV никогда не узнаешь, верно? А поскольку функция степенная, корней можно теоретически найти ровно столько же, сколь часто менялся знак денежного потока проекта. А допустим, мы считаем потоки многолетнего проекта по кварталам? Перспектива вырисовывается не самая радужная.

4.2: Положительный наклон функции

Встречаем нового героя: объект «Ha-Shi». В нём всё прекрасно: положительные потоки приносит сразу да ещё и два периода подряд. Одна беда: в самом конце на бедного собственника вываливается целая гора отрицательного денежного потока. В двойном объёме. Из-за чего функция принимает следующий вид:

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

Посмотрите не это творение теоретической финансовой мысли! Проект выходит на «окупаемость» при достижении ставки дисконтирования в 20%, а с дальнейшим увеличением только и продолжает расти! Разве это не прекрасно? В Зимбабве или Сомали такой проект принимался бы с закрытыми глазами и с полным консенсусом. В чём причина? Чем выше ставка дисконтирования, тем сильнее обесцениваются потоки последующих лет. Поскольку первые денежные потоки положительные, и лишь во втором периоде меняют знак, рост ставки процента нивелирует значение отрицательных денежных потоков и общая сумма в итоге растёт.

4.3: Отсутствие решения

Представляем вниманию братьев близнецов: «Fumbai» и «Lumbai». Они похожи друг на друга как две капли воды за исключением одного фактора: один из проектов всегда приносит положительный денежный поток, а второй — строго отрицательный. Зато главное, что их объединяет — отсутствие решения IRR как такового!

Проекты, где IRR не имеет решения также имеют место быть

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

4.4: Возможность манипулирования показателем, изменяя метод финансирования

Напоследок мы приготовили самое вкусное из рубрики «Как одурачить своего начальника». Допустим у нас есть возможность инвестировать в три точки по продаже ананасов. На инвестиции претендуют четыре господина: Pumba, Dumba, Gumba и Jumba. Ну-ка, скажите, кто фаворит в этой гонке, взглянув на таблицу ниже?

Если руководствоваться только IRR — наиболее выгодный проект у Gumba

Gumba по предварительным результатам выигрывает с отрывом от ближайшего преследователя на целых 15 процентных пунктов. Немаленький результат, правда? Но обратите внимание, у Pumba и Dumba — IRR одинаковый, хотя даже на глаз очевидно, что проекты имеют кардинально различный масштаб. А представьте, если на счёт директору не отправляют выкладку по потокам, а только конечный IRR.

А теперь фокус: покажем значение чистой приведённой стоимости и сравним результаты. Считаем, что ставка дисконтирования по всем проектам характерна для фирмы в целом и составляет 20%.

Оценка проектов с применением IRR и NPV

Что же получается? Оказывается, Dumba и Gumba не так уж и различаются друг от друга не смотря на разницу IRR. Объём денежных потоков у них одинаков с точностью до двух запятых. Значительную ясность в определении выгодности проекта внесено в отношении Pumba и Dumba, где виден стократная разница в денежных потоках. Проект Jumba также растерял свои позиции, показывая денежный поток ниже, чем у Dumba, хотя IRR у него выше. Здесь мы подходим к самому интересному. С одной стороны, можно считать, что эффективность вложения денежных средств в Dumba ниже, чем у Jumba. То есть на каждый вложенный рубль денежных средств мы получаем на 0,12 рубля меньше во втором проекте по сравнению с четвёртым. Но вдруг внутри погодовых денежных потоков «хранятся» разные скелеты:

Детализация денежных потоков проектов

Посмотрим внимательно. Общий объём финансирования Dumba равен четырём тысячам. Причём во втором году компания отрицательного денежного потока больше не несёт. С Jumba история другая. Объём финансирования его проекта составляет четыре тысячи и восемьсот единиц. Но за счёт того, что руководитель растянул отрицательные денежные потоки на периоды, где имеется большой положительный поток, он смог нарастить IRR. По сути, искусственно. Без анализа NPV выявить это невозможно, а в таком случае, это уже нечестная игра.

Вердикт

Читатель, наверняка, заметит: «Да, IRR неидеален, но ведь добрая половина перечисленных недостатков характерны для нетрадиционных денежных потоков. Где, скажите на милость, вы встретите проекты, где сначала идут положительные, а потом отрицательные денежные потоки? К тому же, если известно, что при проекте денежные потоки меняют знак, можно уже заранее быть готовым к последствиям. К тому же IRR отражает устойчивость проекта к рискам как средняя ставка процента».

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

С другой стороны, так или иначе, это не снимает вопрос о последней проблеме, где мы нарочно использовали «стандартную» схему поступлений и оттоков денежных средств. И на мой взгляд, именно это составляет основной контраргумент в отношении решения пользоваться ли чистым IRR или нет. Иными словами, Вас могут попросту надуть.

Что касается «готовности к нестандартным потокам», то это вопрос спорный, потому что у большого начальника нет времени вникать в каждый из десятки проектов, разглядывая денежные потоки каждой новой булочной/автомата/торговой точки (подчеркнуть нужное). В отчёте ему на стол положат несколько цифр без длинной таблички из Excel. Использовать только IRR чревато повышением риска принятия неверного инвестиционного решения.

А что касается аргумента о невозможности явлений, то лучше меня здесь ответит В. Гафт:

Поэтому вывод простой: NPV и IRR имеют как преимущества, так и недостатки, поэтому рационально применять их вместе, а не порознь, чтобы получить больше информации о проекте. Тем более, что применяя Excel, не составит труда выделить ячейки под оба показателя, автоматизировав процесс (как поступает большинство рациональных лиц). В противном случае, стоит обратить внимание на MIRR (Modified Internal Rate of Return), но с данной метрикой разговор отдельный.

На этом всё! Читайте финансовую теорию и принимайте только правильные решения.

python — Функция Python IRR дает другой результат, чем Excel XIRR

Я использую следующие функции для вычисления IRR с помощью Python:

from scipy.optimize import newton

def xnpv(rate, values, dates):
    
    if rate <= -1.0:
        return float('inf')
    min_date = min(dates)
    return sum([
        value / (1 + rate)**((date - min_date).days / 365)
        for value, date
        in zip(values, dates)
     ])


def xirr(values, dates):
    return newton(lambda r: xnpv(r, values, dates), 0)

Источник функций: https://2018.pycon.co/talks/ personal-pynance / personal-pynance.pdf

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

import pandas as pd
import datetime
import numpy as np
from decimal import *

# Input Data
dates = [datetime.date(2020, 8, 31), datetime.date(2020, 5, 5), datetime.date(2020, 2, 28), datetime.date(2020, 8, 31),datetime.date(2018, 6, 30)]
values = [50289.0, -75000.0, 0.0, 0.0, 0.0]

# Create Dataframe from Input Data
test = pd.DataFrame({"dates" : dates, "values" : values})

# Filter all rows with 0 cashflows
test = test[test['values'] != 0]

# Sort dataframe by date
test = test.sort_values('dates', ascending=True)
test['values'] = test['values'].astype('float')

# Create separate lists for values and dates
test_values = list(test['values'])
test_dates = list(test['dates'])

# Calculate IRR
xirr(test_values, test_dates)

В Python я получаю 0,0001 , тогда как в Excel я получаю -0,71 , и я понятия не имею, что мне здесь не хватает. Может у кого-то есть идея?! ??!

Daniel 8 Сен 2020 в 19:15

1 ответ

Лучший ответ

Функции оптимизации Scipy подвержены ошибкам до локальных минимумов. Измените метод оптимизации на другой, например anderson, и получите то, что ожидаете.

Proof

from scipy.optimize import anderson

def xnpv(rate, values, dates):
    
    if rate <= -1.0:
        return float('inf')
    min_date = min(dates)
    return sum([
        value / (1 + rate)**((date - min_date).days / 365)
        for value, date
        in zip(values, dates)
     ])


def xirr(values, dates):
    return anderson(lambda r: xnpv(r, values, dates), 0)

import datetime
from decimal import *

# Input Data
dates = [datetime.date(2020, 8, 31), datetime.date(2020, 5, 5), datetime.date(2020, 2, 28), datetime.date(2020, 8, 31),datetime.date(2018, 6, 30)]
values = [50289.0, -75000.0, 0.0, 0.0, 0.0]

# Create Dataframe from Input Data
test = pd.DataFrame({"dates" : dates, "values" : values})

# Filter all rows with 0 cashflows
test = test[test['values'] != 0]

# Sort dataframe by date
test = test.sort_values('dates', ascending=True)
test['values'] = test['values'].astype('float')

# Create separate lists for values and dates
test_values = list(test['values'])
test_dates = list(test['dates'])

# Calculate IRR
xirr(test_values, test_dates)
array(-0.70956212)

Sergey Bushmanov 8 Сен 2020 в 16:36

Как использовать функцию IRR (WS, VBA)

В этом руководстве Excel объясняется, как использовать функцию Excel IRR с синтаксисом и примерами.

Описание

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

Функция IRR — это встроенная функция в Excel, относящаяся к категории Финансовая функция .Его можно использовать как функцию рабочего листа (WS) и функцию VBA (VBA) в Excel. В качестве функции рабочего листа функцию IRR можно ввести как часть формулы в ячейку рабочего листа. В качестве функции VBA вы можете использовать эту функцию в коде макроса, который вводится через редактор Microsoft Visual Basic Editor.

Синтаксис

Синтаксис функции IRR в Microsoft Excel:

 IRR (диапазон, [оценочная_irr])

Параметры или аргументы

диапазон: Диапазон ячеек, представляющих ряд денежных потоков.
Estimated_irr: Необязательно. Это ваше предположение о внутренней норме доходности. Если этот параметр опущен, предполагается, что оцениваемый_irr равен 0,1 или 10%.

Примечание

Excel пытается пересчитать IRR, пока результат не станет точным в пределах 0,00001 процента. Если после 20 попыток Excel не рассчитал точное значение, он вернет # ЧИСЛО! ошибка.

Возвращает

Функция ВСД возвращает числовое значение.

Относится к

Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

Функция рабочего листа (WS)
Функция VBA (VBA)

Пример (как функция рабочего листа)

Давайте рассмотрим несколько примеров функции IRR в Excel и рассмотрим, как использовать функцию IRR в качестве функции рабочего листа в Microsoft Excel:

На основе таблицы Excel выше:

Этот первый пример возвращает внутреннюю норму доходности 28%.Предполагается, что вы начинаете бизнес по цене 7500 долларов. Вы получаете следующий доход за первые четыре года: 3000, 5000, 1200 и 4000 долларов.

 = IRR (A1: A5)
  Результат:  28%

В следующем примере возвращается внутренняя ставка доходности 5%. Предполагается, что вы начинаете бизнес стоимостью в 10 000 долларов. Вы получите следующий доход за первые три года: 3400 долларов, 6500 долларов и 1000 долларов.

 = IRR (B1: B4)
  Результат:  5%

Пример (как функция VBA)

Функцию IRR можно также использовать в коде VBA в Microsoft Excel.

Давайте посмотрим на некоторые примеры функции IRR в Excel и узнаем, как использовать функцию IRR в Excel, код VBA:

 Dim LNumber as Double
Статические значения (5) как двойные

Значения (0) = -7500
Значения (1) = 3000
Значения (2) = 5000
Значения (3) = 1200
Значения (4) = 4000

LNumber = Irr (Значения ())

В этом примере переменная с именем LNumber теперь будет содержать значение 0,276668413.

Как рассчитать IRR в Excel и финансовом калькуляторе

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

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

Хотя это непростой расчет, формула IRR в Excel упрощает вычисление IRR для принятия мгновенных инвестиционных решений.

Формула Excel для IRR

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

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

Введите это число в ячейку непосредственно под столбцом с вашими номерами денежных потоков.

Калькулятор IRR в Excel Пример

Вот простой пример того, как найти IRR в Excel, используя следующие цифры:

Первоначальные инвестиции: 50 000 долларов США
Последующие денежные потоки: 20 000 долларов США каждый год в течение 5 лет; 40 000 долларов в год 6.

Как найти IRR на финансовом калькуляторе

Если вы не используете Excel, вы все равно можете рассчитать IRR с помощью финансового калькулятора (например, Texas Instrument BA II Plus).Вам понадобятся те же цифры, которые вы использовали для определения IRR в Excel: первоначальные инвестиции и последующие денежные потоки.

Используя финансовый калькулятор, введите следующее:

Нажмите кнопку CF (денежный поток), чтобы запустить регистр денежных потоков.
Введите начальные инвестиции (отрицательное число).
Нажмите Enter.
Нажмите стрелку вниз, чтобы перейти к CF1 или денежному потоку за первый год.
Введите сумму за год 1.
Дважды нажмите стрелку вниз, чтобы ввести денежный поток за 2-й год.
Повторяйте процесс до тех пор, пока не введете прогнозируемый денежный поток за каждый год.
Нажмите кнопку IRR.
Нажмите кнопку CPT для вашего IRR.

Расчет IRR с помощью финансового калькулятора Пример

Вот пример того, как найти IRR с помощью финансового калькулятора, используя следующие цифры:

Первоначальные инвестиции: 150 000 долларов США
Последующие денежные потоки: 50 000 долларов США в год для 5 лет.

Шаг 1 : Нажмите кнопку движения денежных средств (CF)

При этом запускается регистр движения денежных средств, когда вы вводите свои первоначальные инвестиции. Поскольку выплачиваются наличные, это отрицательное число. В этом примере введите — 150 000 долларов и нажмите Enter.

Шаг 2 : Нажмите стрелку вниз один раз

Калькулятор должен показать CF1. Здесь вы вводите денежный поток за первый год. В этом примере введите 50 000 долларов.

Шаг 3 : Дважды нажмите стрелку вниз

Калькулятор должен показать CF2.Здесь вы войдете в поток денежных средств за второй год. В этом примере введите 50 000 долларов.

Шаг 4 : Повторите

Нажмите стрелку вниз еще два раза и повторяйте Шаг 3, пока не введете все пять лет денежного потока в размере 50 000 долларов США.

Шаг 5 : Нажмите кнопку IRR

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

Как читать ваши результаты IRR

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

Функция Excel XIRR для расчета IRR для непериодических денежных потоков

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

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

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

Функция XIRR в Excel

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

Эта функция была представлена в Excel 2007 и доступна во всех более поздних версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019 и Excel для Office 365.

Синтаксис функции XIRR следующий:

XIRR (значения, даты, [предположение])

Где:

Значения (обязательно) — массив или диапазон ячеек, представляющих серию притоков и оттоков.
Даты (обязательно) — даты, соответствующие денежным потокам. Даты могут указываться в любом порядке, но дата первоначального вложения должна быть первой в массиве.
Guess (необязательно) — ожидаемая внутренняя норма доходности, выраженная в процентах или десятичном числе. Если этот параметр не указан, Excel использует коэффициент по умолчанию 0,1 (10%).

Например, чтобы рассчитать IRR для ряда денежных потоков в A2: A5 и дат в B2: B5, вы должны использовать эту формулу:

= XIRR (A2: A5, B2: B5)

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

6 фактов о функции XIRR, которые следует знать

Следующие примечания помогут вам лучше понять внутреннюю механику функции XIRR и наиболее эффективно использовать ее в своих таблицах.

XIRR в Excel предназначен для расчета внутренней нормы доходности для денежных потоков с неравными сроками. Для периодических денежных потоков с неизвестными точными датами платежей можно использовать функцию IRR.
Диапазон значений должен содержать как минимум одно положительное (доход) и одно отрицательное (исходящий платеж) значение.
Если первое значение — это затраты (начальные вложения), оно должно быть представлено отрицательным числом. Первоначальные вложения не дисконтируются; последующие платежи возвращаются к дате первого денежного потока и дисконтируются на основе 365-дневного года.
Все даты усекаются до целых чисел, что означает, что дробная часть даты, представляющая время, удаляется.
Даты должны быть действительными датами Excel, введенными как ссылки на ячейки, содержащие даты или результаты формул, таких как функция ДАТА. Если даты вводятся в текстовом формате, могут возникнуть проблемы.
XIRR в Excel всегда возвращает годовой IRR даже при расчете ежемесячных или еженедельных денежных потоков.

Расчет XIRR в Excel

Функция XIRR в Excel использует метод проб и ошибок, чтобы найти скорость, которая удовлетворяет этому уравнению:

Где:

П — денежный поток (платеж)
d — дата
i — номер периода
n — всего периодов

Начиная с предположения, если он предоставлен, или с 10% по умолчанию, если нет, Excel выполняет итерации, чтобы получить результат с 0.((B2: B5- $ 2 млрд) / 365)))

Где:

A2: A5 — денежные потоки
B2: B5 — даты
E1 — ставка, возвращаемая XIRR

Как показано на скриншоте ниже, результат очень близок к нулю. Q.E.D. 🙂

Как рассчитать XIRR в Excel — примеры формул

Ниже приведены несколько примеров, демонстрирующих общее использование функции XIRR в Excel.

Базовая формула XIRR в Excel

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

= XIRR (A2: A8, B2: B8)

Где A2: A8 — денежные потоки, а B2: B8 — даты, соответствующие денежным потокам:

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

Сравнивая несколько вариантов инвестирования, помните, что прогнозируемая доходность — это лишь один из факторов, которые вы должны оценить, прежде чем принимать решение. Для получения дополнительной информации см. Какова внутренняя норма доходности (IRR)?

Полная форма функции Excel XIRR

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

Для ввода данных, показанного ниже, формула XIRR без предположения возвращает ошибку:

= XIRR (A2: A7, B2: B7)

Ожидаемая ставка возврата (-20%), указанная в аргументе предположение , помогает Excel прийти к результату:

= XIRR (A2: A7, B2: B7, -20%)

Как рассчитать XIRR для ежемесячных денежных потоков

Для начала запомните: какие бы денежные потоки вы ни рассчитывали, функция Excel XIRR дает годовой нормы прибыли . (1/12) -1

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

= IRR (A2: A8)

В результате этих расчетов мы получаем ежемесячный XIRR в размере 7,77%, что очень близко к 7,68%, полученным по формуле IRR:

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

Шаблон Excel XIRR

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

Введите денежные потоки и даты в два отдельных столбца (A и B в этом примере).
Создайте два динамически определенных диапазона с именами Cash_flows и Даты . Технически это будут называться формулы:
Денежные_потоки:
= СМЕЩЕНИЕ (Лист1! $ A $ 2,0,0, СЧЁТ (Лист1! $ A: $ A), 1)
Даты:
= СМЕЩЕНИЕ (Лист1! $ B $ 2,0,0, СЧЁТ (Лист1! $ B: $ B), 1)
Где Sheet1 — это имя вашего рабочего листа, A2 — это первый денежный поток, а B2 — первая дата.
Подробные пошаговые инструкции см. В разделе «Как создать динамический именованный диапазон в Excel».
Добавьте динамически определенные имена, которые вы создали, в формулу XIRR:

= XIRR (денежные_потоки, даты)

Готово! Теперь вы можете добавлять или удалять столько денежных потоков, сколько хотите, и ваша динамическая формула XIRR будет соответственно пересчитана:

Загрузить шаблон Excel XIRR

XIRR против IRR в Excel

Основное различие между функциями Excel XIRR и IRR заключается в следующем:

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

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

В качестве примера сравним результаты IRR и XIRR для одних и тех же денежных потоков:

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

Если сроки денежных потоков неравные , разница между результатами весьма значительна:

XIRR и XNPV в Excel

XIRR тесно связан с функцией XNPV, потому что результатом XIRR является ставка дисконтирования, которая приводит к нулевой чистой приведенной стоимости.Другими словами, XIRR — это XNPV = 0. Следующий пример демонстрирует взаимосвязь между XIRR и XNPV в Excel.

Допустим, вы рассматриваете некоторую инвестиционную возможность и хотите изучить как чистую приведенную стоимость, так и внутреннюю норму доходности этих инвестиций.

С денежными потоками в A2: A5, датами в B2: B5 и ставкой дисконтирования в E1 следующая формула XNPV даст вам чистую приведенную стоимость будущих денежных потоков:

= XNPV (E1, A2: A5, B2: B5)

Положительное значение NPV указывает на прибыльность проекта:

Теперь давайте посмотрим, какая ставка дисконтирования сделает чистую приведенную стоимость равной нулю.Для этого используем функцию XIRR:

= XIRR (A2: A5, B2: B5)

Чтобы проверить, действительно ли ставка, полученная с помощью XIRR, приводит к нулевому NPV, поместите ее в аргумент rate вашей формулы XNPV:

= XNPV (E4, A2: A5, B2: B5)

Или встроить всю функцию XIRR:

= XNPV (XIRR (A2: A5, B2: B5), A2: A5, B2: B5)

Да, XNPV, округленное до двух знаков после запятой, действительно равно нулю:

Чтобы отобразить точное значение NPV, выберите отображение большего количества десятичных знаков или примените научный формат к ячейке XNPV.-5 = 0,0000111

Функция Excel XIRR не работает

Если у вас возникла проблема с функцией XNPV в Excel, ниже приведены основные моменты, которые необходимо проверить.

# ЧИСЛО! ошибка

Ошибка #NUM может возникнуть по следующим причинам:

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

# ЗНАЧЕНИЕ! ошибка

Ошибка #VALUE может быть вызвана следующими причинами:

Любое из предоставленных значений не является числовым.
Некоторые из предоставленных дат не могут быть идентифицированы как действительные даты Excel.

Вот как вы рассчитываете XIRR в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец книги для функции Excel XIRR.Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

Функция

IRR в Excel и пример подсчета IRR

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

Взаимодействие с другими людьми

Особенности и синтаксис функций IRR

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

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

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

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

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

Есть секреты работы функции IRR:

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

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

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

Взаимодействие с другими людьми

Примеры функции IRR в Excel

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

Размер первоначальной инвестиции — 7000. За анализируемый период было еще две инвестиции — 5040 и 10.

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

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

Требуемая IRR (внутренняя норма доходности) анализируемого проекта — 0,20

16569432. Если передать десятичное выражение значения в процентах, то получим ставку 21%.

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

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

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

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

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

Как использовать индикатор IRR:

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

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

Скачать пример подсчета функций IRR и NPV в Excel.

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

Excel IRR Function и другие способы расчета IRR в Excel

Скачать книгу Excel

Скачать рабочую тетрадь

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

В этом руководстве обсуждаются методы расчета IRR с использованием метода проб и ошибок или с использованием одномерной таблицы данных. Также объясняется, как рассчитать IRR с использованием NPV или XIRR в Excel.

Основные выводы

Как лучше всего рассчитать IRR набора денежных потоков?
Рассчитать IRR методом проб и ошибок
Отключение одномерной таблицы данных для определения корня
Использование IRR () в Excel
Использование XIRR () в Excel
Общие приложения IRR

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

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

IRR — это эффективная комбинированная ставка доходности в годовом исчислении, обозначаемая буквой «r». Математически это можно сформулировать как:

Как лучше всего рассчитать IRR для набора денежных потоков?

В этом руководстве мы обсудим возможные методы расчета IRR:

Использование метода проб и ошибок (для облегчения понимания)
Использование одномерной таблицы данных
Расчет IRR с использованием функции Excel NPV ()
Расчет IRR с использованием XIRR

Загружаемая рабочая книга Excel выше была подготовлена для продемонстрировать расчет IRR.Для удобства мы рекомендуем вам загрузить книгу во время чтения этого руководства.

Метод проб и ошибок для расчета IRR в Excel

IRR — это ставка дисконтирования, для которой NPV равна нулю и может быть рассчитана методом проб и ошибок.

NPV (IRR (…),…) = 0
XNPV (XIRR (…),…) = 0

Процесс проб и ошибок выглядит следующим образом:

Начните с предположения о ставке дисконтирования «r»
Рассчитайте NPV, используя «r» — обратитесь к нашим руководствам о том, как рассчитать NPV с формулами Excel или без них
Если NPV близко к нулю, то « r ‘- это IRR
Если ЧПС положительное, увеличьте’ r ‘
Если ЧПС отрицательное, уменьшите’ r ‘
Продолжайте процесс до тех пор, пока ЧПС не достигнет нуля

Используя пример из рабочей книги:

См. Снимок экрана 1: NPV, рассчитанная с учетом ставки дисконтирования 10%, составляет 19 долларов США.31 миллион, следовательно, мы знаем, что IRR должна быть больше 10%.

. См. Снимок экрана 2. Давайте теперь попробуем «r», равное 18%. NPV составляет — 0,87 миллиона долларов. Он отрицательный, но на этот раз NPV ближе к нулю.
Таким образом, можно было предположить, что IRR должен быть чуть ниже 18%.

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

Использование одномерной таблицы данных для определения корня

Мы рекомендовали представлять NPV с различными ставками дисконтирования с использованием быстрой одномерной таблицы данных со ставками дисконтирования в качестве вертикального параметра, как показано на снимке экрана 3.

Изобразим приведенную выше таблицу данных в виде графика — см. Снимок экрана 4. Становится ясно, что IRR находится между 17,50% и 18,00%. Если быть точным, IRR составляет 17,53%, что мы могли бы получить с помощью функции Excel.

Использование IRR () в Excel

IRR () синтаксис:

IRR (CF1, CF2,…)

Мы можем рассчитать IRR с помощью функции Excel IRR (), но, как и NPV (), она имеет некоторые ограничения:

Денежные потоки (CFi) должны быть равномерно распределены во времени и возникать в конце каждого периода
CFi должны вводиться в правильной последовательности

Использование функции XIRR () в Excel

Из-за ограничений функции IRR (без X) лучше избегать.Более надежной функцией будет XIRR (). Он возвращает внутреннюю норму прибыли для графика денежных потоков, который не обязательно является периодическим.

XIRR () — это добавляемая функция в Excel, имеющая синтаксис: XIRR (CFi, даты)

Как показано на скриншоте 5, расчетная IRR составляет 17,53%. Мы могли бы дважды проверить расчет, вернув IRR в расчет NPV в качестве ставки дисконтирования, для которой NPV равняется нулю.

Общие приложения IRR

Составление бюджета капиталовложений: инструмент принятия инвестиционных решений

IRR — это показатель, позволяющий решить, стоит ли инвестировать в отдельный проект.Теоретически можно установить простой ориентир для принятия решения: принять проект, если IRR превышает стоимость капитала, и отклонить, если эта IRR меньше стоимости капитала.

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

Оптимизация тарифов

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

Corality Academy: кампус Corality по финансовому моделированию

В кампусе Corality Financial Modeling Campus есть множество других руководств и бесплатных ресурсов, связанных с финансовым моделированием.

Некоторые из наиболее популярных курсов по этой теме:

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

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

Инструкции в этой статье применимы к Excel 2019, 2016, 2013, 2010, 2007, Excel для Mac, Excel для Microsoft 365 и Excel Online.

Общие сведения о функции IRR

Синтаксис функции IRR следующий: = IRR (значения, [предположение]) , где «значения» — это список значений, представляющих серию денежных потоков с равными приращениями, например, на определенную дату каждый месяц или месяц.Значения также могут быть ссылками на ячейки или диапазонами ссылок. Например, A2: A15 будет значениями в диапазоне ячеек от A2 до A15.

«Предположение» — это необязательный аргумент, который, по вашему мнению, близок к вашему результату IRR. Если вы не используете этот аргумент, Excel по умолчанию использует значение 0,1 (10%). При использовании значения Guess вы получаете ошибку #NUM или конечный результат не соответствует вашим ожиданиям. Но вы всегда можете изменить это значение.

Использование формулы IRR в Excel

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

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

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

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

Выделите ячейки, которые хотите проверить, или измените формат.
Щелкните правой кнопкой мыши и выберите Формат ячеек .
На вкладке Number выберите Number или Accounting .
Вы можете использовать формат Accounting , если хотите использовать круглые скобки вокруг отрицательных значений.
Внесите любые корректировки форматирования слева от поля Категория , затем выберите OK .
Например, если используется учетная запись, и вы хотите установить для символа на долларов, используйте раскрывающееся меню, чтобы выбрать долларов. Вы также можете выбрать количество десятичных знаков в ваших значениях.
Выберите ячейку, в которую вы хотите поместить значение IRR, и введите следующее:
```
= IRR (значения; [предположить]) 
 
```
В этом примере формула будет выглядеть так:
```
= ВСД (B4: B16) 
 
```
Здесь диапазон ячеек используется вместо фактических значений.Это особенно полезно при работе с финансовыми таблицами, где значения могут изменяться, а расположение ячейки — нет. Также обратите внимание, что в используемой формуле используется значение по умолчанию для Excel 0,1. Если вы хотите использовать другое значение, например 20%, введите формулу как: = IRR (B4: B16,20%) .
После того, как вы ввели и отформатировали формулу в соответствии с вашими потребностями, нажмите . Введите , чтобы увидеть значение.
Если вы хотите использовать значение ячейки, используйте ссылку на ячейку вместо числа.В этом примере список значений предположения был введен в столбец E. Чтобы рассчитать 20% -ное предположение, используйте эту формулу: = IRR (B4: B16, E5) . Это упрощает вычисление различных значений, вводя в ячейку ссылки, где вы можете продолжать изменять число, а не формулу.

Спасибо, что сообщили нам!

Расскажите, почему!

Другой Недостаточно подробностей Сложно понять

Рассчитать IRR в Excel | Как использовать функцию IRR? (с примерами)

y app

Расчет IRR

в Excel

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

IRR в Excel означает I nternal R ate из R eturn. IRR в Excel — одна из встроенных функций, доступных в Microsoft Excel. IRR в Excel подпадает под категорию финансовых функций.

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

Функция

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

Формула, используемая для расчета IRR в Excel

Формула IRR Excel выглядит следующим образом:

Где,

диапазон = Это обязательный параметр.Диапазон ячеек, обозначающий ряд денежных потоков, для которых будет рассчитана внутренняя норма прибыли.
guess = Это необязательный параметр. Он обозначает число, которое, по вашему мнению, близко к результату внутренней нормы прибыли. Если значение не указано, используется значение по умолчанию 0,1 или 10%
«[]» обозначает необязательные параметры.

Функция возвращает числовое значение. Это могло быть положительно или отрицательно.

Как рассчитать IRR в Excel? (с примерами)

Расчет IRR в Excel можно выполнить двумя способами: i.е., функция рабочего листа (WS) или функция VBA. Как функцию WS, ее можно ввести как часть формулы в ячейку рабочего листа. В качестве функции VBA его можно использовать в коде макросов, который вводится через редактор Microsoft Visual Basic, интегрированный в MS Excel. Обратитесь к расчету IRR в примере Excel, приведенном ниже, чтобы лучше понять.

Пример №1 — Расчет IRR в листе Excel

В этом примере расчета IRR -3000 обозначает сумму инвестированного капитала.Последующие числа 2000 и 5000 обозначают доход. Функция IRR в Excel рассчитывается для значений -3000, 2000 и 5000. Результат составляет 67%.

Пример № 2

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

Пример № 3

В этом примере расчета IRR все значения отрицательны.Это указывает на отсутствие дохода. Следовательно, сгенерированный результат — # ЧИСЛО!

Пример # 4

В этом примере расчета IRR всего пять значений. -10000 означает произведенную оплату. Следующие значения относятся к годам, т.е. доход, полученный в год 1 — 2000, год 2 — 5000, год 3 — 4000, год 5 — 6000.

Чтобы рассчитать доход, полученный в конце трех лет, примените формулу IRR к ячейкам от B2: B4.Результат составляет -19%, так как сумма выплаты в первый год больше, чем доход, полученный в последующие годы.

Чтобы рассчитать доход, полученный в конце пяти лет, примените формулу IRR к ячейкам от B2: B6. Результат 22%. Результат положительный, так как доход, полученный в последующие годы, превышает сумму выплат, вложенных в первый год.

Пример № 2 — Расчет IRR в Excel VBA

Функцию IRR можно использовать в коде VBA в Microsoft Excel следующим образом:

Формула IRR в Excel VBA :

Формула Irr в VBA выглядит следующим образом.

Где,

Значения = Денежный поток

IRRVal = Внутренняя норма прибыли

IRR в Excel, пример VBA

Давайте рассмотрим пример для Irr в VBA.

В этом примере расчета IRR IRRVal — это переменная типа double. Он содержит результат, возвращаемый функцией IRR. Значения представлены в виде массива типа данных double, в котором начальное значение равно -10000, что указывает на оплату, произведенную в первый год.Последующие значения — 2000, 5000, 4000 и 6000, что указывает на годовой доход в течение следующих четырех лет.

Что следует помнить

Массив значений ( диапазон ) должен содержать минимум одно положительное и одно отрицательное число.
Значения должны быть в последовательном порядке. IRR использует порядок значений для интерпретации порядка денежных потоков. Позаботьтесь о вводе значений выплат и доходов в той последовательности, в которой они произошли или вы хотите, чтобы это произошло.
Если массив ссылочного аргумента содержит текст, логические значения или пустые ячейки, эти значения игнорируются.
MS Excel использует итеративный метод расчета IRR с помощью Excel. Начиная с предположения, IRR выполняет циклическое вычисление до тех пор, пока результат не станет точным в пределах 0,00001 процента; если IRR не может найти результат, который работает после 20 попыток, #NUM! Возвращается значение ошибки.
Если функция ВСД в excel возвращает # ЧИСЛО! или если результат не такой, как вы ожидали, попробуйте другое значение для
В большинстве случаев вам не нужно указывать предполагаемое значение для расчета IRR в Excel.Если параметр предположения пропущен, программа принимает его равным 0,1, то есть 10%.
IRR тесно связана с NPV, то есть функцией чистой приведенной стоимости. Норма прибыли, рассчитываемая IRR с помощью excel, представляет собой процентную ставку, соответствующую нулевой чистой приведенной стоимости.