Billy Russel

Billy Russel - FP&A Strategist

January 2023 - min to read

IRR vs. XIRR: What's the difference? When do I use them?

IRR vs. XIRR: What's the difference? When do I use them?

vaIRR (Internal Rate of Return) and XIRR (Extended Internal Rate of Return) are both financial metrics used to measure the profitability of an investment.

But what's the difference between them?

How do you know when to use each?

And what exactly does "the profitability of an investment" mean, anyway?

Keep reading to find out.

Contents

  1. What is IRR?
  2. What is XIRR?
  3. What's the difference between IRR vs. XIRR?
  4. Conclusion

What is IRR?

IRR, or internal rate of return, measures the average return on an investment.

It's simple to understand: the higher the IRR, the better the return (and the better the investment).

The IRR is the discount rate that makes an investment's net present value (NPV) equal to zero in a discounted cash flow analysis.

It's used to measure the profitability of a single investment and is used to compare different investments.

The best use case for IRR is comparing the profitability of investments with regular cash flows. It's especially useful for comparing investments with different holding periods or cash flow patterns.

What is net present value?

Net present value (NPV) is the difference between the present values of cash inflows and outflows over a period of time.

To oversimplify, if you expect cash inflows of $3 and cash outflows of $2 over a given period, your net present value for that period is $3 - $2 = $1.

FP&A teams use net present value (NPV) to calculate the current value of future payments from a company, project, or investment.

You need to estimate the timing and amount of future cash flows and pick a discount rate equal to the minimum acceptable rate of return.

Here's the formula:

NPV = Cash flow ÷ [(1 + required return or discount rate)^(number of time periods)] - initial investment

In plain English, NPV is "today’s value of the expected cash flows" minus "today’s value of invested cash."

How does it relate to IRR?

Because IRR is the discount rate in the denominator that makes NPV equal to zero in a discounted cash flow analysis.

...so let's answer what a discounted cash flow analysis is.

What is a discounted cash flow analysis?

Discounted cash flow (DCF) analysis is a valuation method to estimate the value of an investment using its expected future cash flows.

In other words, discounted cash flow uses future cash flows equal to some estimated value.

How does DCF relate to IRR?

Because IRR is part of the denominator of the estimated cash flows.

But enough of that. Let's talk about how to calculate IRR.

How to calculate IRR

The formula for IRR looks complex, but it's pretty simple.

You calculate IRR by using the NPV formula and setting it equal to 0.

Here's the IRR formula:

​0 = NPV = [Cash inflow ÷ (1 + IRR)] - Total investment costs.

You can sum the number in brackets over many periods to get the average IRR over multiple periods.

If you're using Excel, there's an easier way to calculate IRR.

How to calculate IRR (in Microsoft Excel)

Microsoft Excel has a built-in IRR function you can use, and it's super easy.

Here's the syntax: 

IRR(values, [guess])

Where values is an array or reference to the cells you want to use to calculate the internal rate of return, and where guess is an optional argument that tells Excel where to begin its search for the optimal IRR.

If the IRR formula returns a #NUM! error, it means you should update (or remove) your guess. 

And that's your IRR calculation in Excel.

Limitations of IRR

Unfortunately, IRR isn't perfect.

IRR assumes periodic cash flows occur at regular intervals, which can lead to inaccuracies.

Likewise, since estimates in IRR and NPV can (obviously) differ from the actual results, it's best to combine IRR analysis with scenario analysis to show different possible NPVs based on varying assumptions.

This is because scenario analysis helps reduce some of the uncertainty your estimations introduce.

Is IRR the same as ROI (return on investment?)

No, IRR and ROI are different things.

Although IRR is often referred to as the ROI of a project, it should tell you why ROI isn't the same as IRR: people use it differently.

Put another way, the mathematical definition of IRR is precise and that of ROI is not.

New call-to-action

What is XIRR?

XIRR, or Extended Internal Rate of Return, is similar to IRR but is used to measure the profitability of investments with irregular cash flows.

This means it's a more accurate measure of profitability when the cash flows are not evenly spaced over time.

So XIRR is best for investments with irregular cash flows or varying holding periods.

What are irregular future cash flows?

Irregular cash flows are unexpected.

This means they're not in any predictions---they're a surprise.

An example of an irregular cash flow might be an outlier-sized sale or deal.

The XIRR formula

Calculating XIRR is pretty simple.

...Well, if you're using Excel. More on that in a second.

The calculations by hand can get a little hairy. Just look at the formula to calculate XIRR:

XIRR-formula

Gross, right?

That's why it's better to use Excel for your XIRR calculation.

The Excel XIRR function

Fortunately, Excel makes this super easy.

Here's the syntax:

XIRR(values, dates, [guess])

where the values argument is a series of cash flows that correspond to a schedule of payments in dates (entered with the DATE function), and guess is an optional argument of a number you think will be close to the XIRR.

Limitations of XIRR

XIRR assumes that the cash flows are reinvested at the XIRR rate, which is unlikely to be the case in the real world. Real life is inherently more chaotic and messy.

Likewise, XIRR doesn't consider the magnitude of cash flows---so outliers in our outliers, so to speak, can throw off your XIRR model.

What's the difference between IRR vs. XIRR?

IRR (Internal Rate of Return) and XIRR (Extended Internal Rate of Return) are both financial metrics used to measure the profitability of an investment.

IRR measures the average annual rate of return for an investment, assuming cash flows occur at regular intervals (e.g. annually or monthly). The IRR is the discount rate that makes an investment's net present value (NPV) equal to zero.

IRR measures the profitability of a single investment and is used to compare different investments.

XIRR, on the other hand, is similar to IRR but is used to measure the profitability of investments with irregular cash flows. It's a more accurate measure of profitability when the cash flows are not evenly spaced over time. It allows you to enter different cash flow dates and interpolate the internal rate of return.

In summary, IRR assumes regular cash flows and compares investments with the same holding period.

In contrast, XIRR assumes irregular cash flows and measures the profitability of investments with varying cash flow patterns and holding periods.

Both are commonly used in financial analysis and investment decision-making to evaluate the profitability and performance of an investment.

When to use IRR vs. XIRR

Use IRR when your cash flows are regular. 

Use XIRR when your cash flows are irregular.

Since they're both so easy to calculate in Excel, we recommend running the numbers for each so you're better able to forecast. 

Conclusion

It's important to keep in mind that both IRR and XIRR are only one part of a comprehensive financial analysis and should be used in conjunction with other tools such as net present value (NPV) and return on investment (ROI) to get a more complete picture of an investment's profitability.

New call-to-action

Related Articles