Нил знает, как рассчитать простую внутреннюю норму доходности с помощью функции IRR. Однако он задается вопросом, как рассчитать IRR для более сложной сделки, такой как ежемесячные выплаты в течение 36-месячного срока, когда первые 12 месяцев являются беспроцентными, а следующие 24 месяца — под 5%.

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

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

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

Тогда давайте посмотрим, как это будет работать в случае с Нилом. Допустим, он дает кому-то ссуду в размере 50 000 долларов под 5% годовых. Эти проценты применимы только за последние 24 месяца ссуды, поскольку Нил указывает, что первые 12 месяцев беспроцентные. Таким образом, выплаты за первые 12 месяцев составят 1 388,89 долларов в месяц, что рассчитывается путем простого деления 50 000 долларов на 36.

По истечении этого первого года выплаты составят 1 462,38 доллара США, потому что человек фактически получает ссуду в размере 33 333,32 доллара США (оставшийся баланс, который составляет 2/3 первоначальной суммы ссуды) под 5% годовых на 24 месяца.

При расчете IRR у вас есть 36 рассчитанных выплат — 12 из них по цене 1 388,89 доллара, а затем 24 из них по 1 462,38 доллара. Поместите исходное значение в ячейку A1 (как отрицательное значение), а платежи (как положительные значения) в ячейки A2: A37. Затем вы можете применить функцию IRR к диапазону из 37 значений. (См. Рис. 1.)

image

Рисунок 1. Расчет IRR для кредита в размере 50 000 долларов США.

Обратите внимание, что IRR составляет 0,19%. Поскольку денежные потоки представляют собой ежемесячные платежи, это IRR в месяц. Чтобы скорректировать его так, чтобы он отображался как годовая ставка, просто умножьте его на 12, и вы получите годовой IRR в 2,24%. Это то, что Нил фактически зарабатывает на ссуде в 50 000 долларов. Это средний процент, полученный в течение срока ссуды. Оказывается, она немного ниже 5% ставки, потому что треть срока кредита действительно беспроцентная.

Следует отметить, что все эти расчеты основаны на том, что векселя действительно является беспроцентной в течение первых 12 месяцев. Если это не так — если проценты просто отсрочены для выплаты в течение последних 24 месяцев — тогда результат будет совершенно другим, но расчеты такие же. Человек, выплачивающий ссуду, по-прежнему будет платить только 1388,89 долларов в месяц в течение первых 12 месяцев. Однако, поскольку проценты по-прежнему начисляются, по истечении первого года непогашенный остаток составляет 35 504,12 доллара.

Этот непогашенный остаток затем используется для определения регулярного платежа за оставшиеся 24 месяца, который составит ежемесячный платеж в размере 1557,62 долларов США. Включение этих цифр в тот же график погашения позволяет рассчитать новую внутреннюю норму дохода (IRR) в размере 0,42%, что в годовом исчислении составляет ровно 5%. (См. Рис. 2.)

image

Рисунок 2. Расчет IRR на основе кредита в размере 50 000 долларов США с отсроченными процентами.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (4359) применим к Microsoft Excel 2007, 2010, 2013 и 2016.