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

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

Например, предположим, что вы инвестируете в бизнес своего друга и предоставляете деньги (50 000 долларов США) 1 ноября. Из-за сезонного характера бизнеса вашего друга выплаты запланированы на 1 марта, 1 апреля, 1 мая. 1 июня, 1 июля и 1 августа на каждый из трех лет. В течение первого года выплаты будут составлять 3500 долларов каждая, в течение второго года они будут составлять 4250 долларов США, а в течение третьего года они будут составлять 5000 долларов США каждая.

Поскольку 50 000 долларов — это деньги, которые вы выплачиваете, они вводятся в Excel как отрицательное значение. Остальные значения вводятся как положительные. Например, вы можете ввести –50000 в ячейку D4, 3500 в ячейки с D5 по D10, 4250 в ячейки с D11 по D16 и 5000 в ячейки с D17 по D22.

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

=XIRR(D4:D22,C4:C22)

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

Диапазоны, которые вы используете с функцией XIRR, должны включать как минимум один платеж и одну квитанцию. Если вы получили ошибку #NUM и включили платежи и квитанции в диапазон, Excel потребуется дополнительная информация для расчета IRR. В частности, вам необходимо предоставить «начальное предположение»

для работы с Excel. Например:

=XIRR(D4:D22,C4:C22,10%)

Это использование означает, что функция XIRR начинает вычисление с 10%, а затем рекурсивно пытается разрешить XIRR на основе значений в диапазонах.

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

Этот совет (2503) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Using_the_XIRR_Function [Использование функции XIRR].