В чем ключевая разница формул excel irr и xirr

Обновлено: 29.06.2024

The IRR function is categorized under Excel Financial functions Functions List of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst . IRR will return the Internal Rate of Return Internal Rate of Return (IRR) The Internal Rate of Return (IRR) is the discount rate that makes the net present value (NPV) of a project zero. In other words, it is the expected compound annual rate of return that will be earned on a project or investment. for a given cash flow, that is, the initial investment value and a series of net income values.

The function is very helpful in financial modeling What is Financial Modeling Financial modeling is performed in Excel to forecast a company's financial performance. Overview of what is financial modeling, how & why to build a model. , as it helps calculate the rate of return an investment would earn based on series of cash flows Valuation Free valuation guides to learn the most important concepts at your own pace. These articles will teach you business valuation best practices and how to value a company using comparable company analysis, discounted cash flow (DCF) modeling, and precedent transactions, as used in investment banking, equity research, . It is frequently used by businesses to compare and decide between capital projects. One example is when a company is presented with two opportunities: one is investing in a new factory and the second is expanding its existing factory. Using the IRR function, we can estimate the IRRs for both scenarios and check which one is higher than the weighted average cost of capital ( WACC WACC WACC is a firm’s Weighted Average Cost of Capital and represents its blended cost of capital including equity and debt. ) of the business (also known as a hurdle rate ). If both are greater than the cost of capital, we will choose the one that shows a higher IRR and/or Net Present Value ( NPV NPV Formula A guide to the NPV formula in Excel when performing financial analysis. It's important to understand exactly how the NPV formula works in Excel and the math behind it. NPV = F / [ (1 + r)^n ] where, PV = Present Value, F = Future payment (cash flow), r = Discount rate, n = the number of periods in the future ) than the other.

Note: for increased precision, CFI recommends you use the XIRR function XIRR vs IRR Why use XIRR vs IRR. XIRR assigns specific dates to each individual cash flow making it more accurate than IRR when building a financial model in Excel. .

IRR Formula

=IRR(values,[guess])

The IRR function uses the following arguments:

Values (required argument) – This is an array of values that represent the series of cash flows. Cash flows include investment and net income values. Values can be a reference to a range of cells containing values.
[Guess] (optional argument) – This is a number guessed by the user that is close to the expected internal rate of return (as there can be two solutions for the internal rate of return). If omitted, the function will take a default value of 0.1 (=10%).

Notes

The argument value should contain at least one positive and one negative value to calculate the internal rate of return.
The IRR function uses the order of the values to interpret cash flows. Hence, it is necessary to enter the payments and income values sequentially.
If the array or reference argument contains logical values, empty cells or text, those values are ignored.

To learn more, launch our free Excel crash course now!

How to use IRR Function in Excel?

As a worksheet function, IRR can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider a few examples:

Example 1

For an initial investment of 1,000,000, the cash flows are given below:

The initial investment here is a negative value as it is an outgoing payment. The cash inflows are represented by positive values.

The internal rate of return we get is 14%.

Example 2

Let’s calculate the CAGR using IRR. Suppose we are given the following information:

The IRR function is not exactly designed for calculating compound growth rate, so we need to reshape the original data in this way:

The beginning value of the investment should be entered as a negative number. The ending value of the investment is a positive number. All intermediate values are kept as zeros.

Now, we would simply apply the IRR function to calculate the CAGR, as shown below:

The CAGR we get is:

Let’s do reverse engineering to check if the resulting value is correct or not.

The formula generally used to calculate CAGR is =((End Value/Start Value)^(1/Periods) -1.

The formula to use will be:

The result we get is same as the one we got using IRR function:

Things to remember about the IRR Function

Free Excel Course

If you want to learn more about functions and become an expert on them, check out CFI’s Free Excel Crash Course! Go through our step by step instructions and demonstrations on how to become an Excel power user.

Additional resources

Thanks for reading the CFI guide to important Excel formulas! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling and analysis. To learn more, check out these additional CFI resources:

XIRR vs IRR XIRR vs IRR Why use XIRR vs IRR. XIRR assigns specific dates to each individual cash flow making it more accurate than IRR when building a financial model in Excel.
Modified Internal Rate of Return (MIRR) MIRR Guide The Modified Internal Rate of Return (MIRR) is a function in Excel that takes into account the financing cost (cost of capital) and a reinvestment rate for cash flows from a project or company over the investment’s time horizon.
Finacial Modeling Guide Free Financial Modeling Guide This financial modeling guide covers Excel tips and best practices on assumptions, drivers, forecasting, linking the three statements, DCF analysis, more
Financial Analyst Program Become a Certified Financial Modeling & Valuation Analyst (FMVA)® CFI's Financial Modeling and Valuation Analyst (FMVA)® certification will help you gain the confidence you need in your finance career. Enroll today!

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

XIRR или Extended Internal Rate of Return, Расширенная внутренняя норма доходности - это мера доходности, используемая при совершении нескольких инвестиций (в разные моменты времени) в финансовый инструмент. Давайте посмотрим, что представляет собой XIRR и чем он отличается от CAGR.

Что такое CAGR?

Для начала нужно ответить на этот намного более простой вопрос.

Допустим, я инвестировал 12 000 рублей и через 5 лет стоимость моей инвестиции составит 22 991 рублей. Какова средняя скорость, с которой мои инвестиция увеличиваются из года в год?

Чтобы ее найти, запишем

22 991 рублей = 12 000 рублей x (1+ CAGR)^5

22 991 рублей = 12 000 рублей x (1+ CAGR) x (1+ CAGR) x (1+ CAGR) x (1+ CAGR) x (1+ CAGR)

CAGR показывает рост моей инвестиции в годовом выражении по ставке сложных процентов или по-другому Совокупный среднегодовой темп роста

В данном случае CAGR = 13,9% годовых

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

CAGR против XIRR

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

Теперь посмотрим, что представляет собой XIRR и как он вычисляется.

Ниже в Таблице 1 план ежегодных инвестиций (SIP)*. Инвестиции осуществляются один раз в начале каждого года. План ежемесячных инвестиций (SIP) составляется по той же самой логике, но он сложнее для восприятия.

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

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

« ставка » — процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12 , при ежеквартальных выплатах аргумент « ставка » будет равен 6%/4 ;
« кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
« плт » — постоянная выплата за каждый период (выплаты — отрицательные значения, поступления — положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент « плт » будет равен -10 000 .
« пс » — приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент « плт »),
« тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода).

2. БЗРАСПИС

( англ. FVSCHEDULE ) — возвращает будущую стоимость инвестиций после начисления ряда сложных процентов (с переменной процентной ставкой, подойдет для вкладов с капитализацией процентов): =БЗРАСПИС(первичное;план), где:

« первичное » — стоимость инвестиции на текущий момент,
«план» — массив применяемых процентных ставок.

3. ПС

( англ. PV) — возвращает приведенную (текущую) стоимость инвестиции или займа (на основе постоянной процентной ставки): =ПС(ставка; кпер; плт; [бс]; [тип]) , где:

« ставка » — процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12 , при ежеквартальных выплатах аргумент « ставка » будет равен 6%/4 ;
« кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
« плт » — постоянная выплата за каждый период (выплаты — отрицательные значения, поступления — положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент « плт » будет равен -10 000 .
« бс » — будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент « плт »),
« тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода).

4. ЧПС

( англ. NPV) – возвращает чистую приведенную или дисконтированную стоимость инвестиции при условии серии периодических денежных потоков и с использованием ставки дисконтирования: =ЧПС(ставка; значение1; [значение2],… ), где:

«ставка» — ставка дисконтирования за один период;
« значение1, значение2,… » — предполагаемые выплаты и поступления (должны быть равномерно распределены во времени, при этом выплаты должны осуществляться в конце каждого периода).

5. ЧИСТНЗ

( англ. XNPV) — возвращает чистую приведенную стоимость для денежных потоков, не обязательно являющихся периодическими: =ЧИСТНЗ(ставка;значения;даты), где:

«ставка» — ставка дисконтирования за один период;
« значение1, значение2,… » — предполагаемые выплаты и поступления (денежные потоки, соответствующие графику платежей, приведенному в аргументе «даты». Если первое значение является затратами или выплатой, оно должно быть отрицательным. Все последующие выплаты дисконтируются на основе 365-дневного года. Ряд значений должен содержать по крайней мере одно положительное и одно отрицательное значение);
«даты» — график дат платежей, который соответствует платежам для денежных потоков.

6. ПЛТ

( англ. PMT) — возвращает сумму периодического платежа с постоянным процентом и постоянной суммой платежа (подходит для расчета платежей по аннуитету): =ПЛТ(ставка; кпер; пс; [бс]; [тип]), где:

« ставка » — процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12 , при ежеквартальных выплатах аргумент « ставка » будет равен 6%/4 ;
« кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
« пс » — приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
« бс » — будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
« тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода).

7. ПРПЛТ

( англ. IPMT) — возвращает сумму процентных платежей за указанный период только в том случае, если платежи в каждом периоде осуществляются равными частями: =ПРПЛТ(ставка;период;кпер;пс;[бс];[тип]), где :

« ставка » — процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12 , при ежеквартальных выплатах аргумент « ставка » будет равен 6%/4 ;
«период» — период, для которого требуется найти платежи по процентам (число в интервале от 1 до аргумента «кпер»);
« кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
« пс » — приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
« бс » — будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
« тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода).

8. СТАВКА

( англ . RATE) – возвращает ставку процентов по аннуитету за один период: =СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз]), где:

« кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
« плт » — постоянная выплата за каждый период (выплаты — отрицательные значения, поступления — положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент « плт » будет равен -10 000 ;
« пс » — приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0);
« бс » — будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0, а аргумент « пс » является обязательным);
« тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода);
«прогноз» — предполагаемая величина ставки (если аргумент «прогноз » опущен, предполагается значение 10%).

9. ЭФФЕКТ

(англ. EFFECT) — возвращает фактическую (или эффективную) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты: =ЭФФЕКТ(номинальная_ставка;кол_пер) , где:

«номинальная_ставка» — номинальная процентная ставка;
«кол_пер» — количество периодов в году, за которые начисляются сложные проценты.

10. ДОХОД

( англ. YIELD) — возвращает доходность ценных бумаг ( облигаций ), по которым производятся периодические выплаты процентов: =ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение, частота; [базис]), где:

« дата_согл » — дата расчета за ценные бумаги (дата продажи ценных бумаг покупателю, более поздняя, чем дата выпуска);
« дата_вступл_в_силу » — срок погашения ценных бумаг (момент, когда истекает срок действия ценных бумаг);
« ставка » — годовая процентная ставка для купонов по ценным бумагам;
« цена » — цена ценных бумаг на 100 рублей номинальной стоимости;
« погашение » — выкупная стоимость ценных бумаг на 100 рублей номинальной стоимости;
« частота » — кол-во выплат по купонам за год (для ежегодных — 1, для полугодовых — 2, для ежеквартальных — 4);
«базис» — используемый способ вычисления дня (если 0 или опущен, то используется американский (NASD) 30/360).

11. ВСД

( англ. IRR) – возвращает внутреннюю ставку доходности для потоков денежных средств (для платежей (отрицательные величины) и доходов (положительные величины), которые имеют место в следующие друг за другом и одинаковые по продолжительности периоды): =ВСД(значения; [предположения]), где:

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

12. МВСД

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

In financial modeling and valuation, it’s critical to understand why to use XIRR vs IRR. Using the simple =IRR function in Excel can be misleading, as it assumes all the time periods in a series of cash flows are equal. This is frequently not the case, especially if you have an initial investment up front, and which is almost never on December 31.

XIRR gives you the flexibility to assign specific dates to each individual cash flow, making it a much more accurate calculation.

What is Internal Rate of Return (IRR)?

The Internal Rate of Return is the discount rate that sets the Net Present Value (NPV) of all future cash flows of an investment to zero. If the NPV of an investment is zero, that doesn’t mean it’s a good or bad investment, it just means you will earn the IRR (discount rate) as your rate of return.

What is IRR in Excel?

If you use the =IRR() formula in Excel, then you are using equal time periods between each cell (cash flow). This makes it challenging when you expect to enter an investment in the middle of a year. For this reason, (as outlined below) always use XIRR instead.

What is XIRR in Excel?

If you use the =XIRR() formula in Excel, then you have complete flexibility over the time periods of the cash flows. In order to do this, enter two series in your formula:

The series of cash flows
The corresponding dates of each of the cash flows

Example of XIRR vs IRR calculation

Below is an example of regular IRR versus XIRR, with a series of six cash flows. With regular IRR, it assumes all cash flows occur on Dec 31, but with XIRR, we can tell Excel that the first cash flow is in the middle of the year. This has a substantial impact on the internal rate of return calculation.

As you can see in the result below, using XIRR vs IRR produces 16.25% as compared to 13.45%, which is a material difference!

Download the Free Template

Enter your name and email in the form below and download the free template now!

Читайте также: