XIRR Function

Categorized as a financial function that calculates the internal rate of return for non-periodic cash flows.

Author: Christopher Haynes
Christopher Haynes
Christopher Haynes
Asset Management | Investment Banking

Chris currently works as an investment associate with Ascension Ventures, a strategic healthcare venture fund that invests on behalf of thirteen of the nation's leading health systems with $88 billion in combined operating revenue. Previously, Chris served as an investment analyst with New Holland Capital, a hedge fund-of-funds asset management firm with $20 billion under management, and as an investment banking analyst in SunTrust Robinson Humphrey's Financial Sponsor Group.

Chris graduated Magna Cum Laude from the University of Florida with a Bachelor of Arts in Economics and earned a Master of Finance (MSF) from the Olin School of Business at Washington University in St. Louis.

Reviewed By: Kevin Henderson
Kevin Henderson
Kevin Henderson
Private Equity | Corporate Finance

Kevin is currently the Head of Execution and a Vice President at Ion Pacific, a merchant bank and asset manager based Hong Kong that invests in the technology sector globally. Prior to joining Ion Pacific, Kevin was a Vice President at Accordion Partners, a consulting firm that works with management teams at portfolio companies of leading private equity firms.

Previously, he was an Associate in the Power, Energy, and Infrastructure Investment Banking group at Lazard in New York where he completed numerous M&A transactions and advised corporate clients on a range of financial and strategic issues. Kevin began his career in corporate finance roles at Enbridge Inc. in Canada. During his time at Enbridge Kevin worked across the finance function gaining experience in treasury, corporate planning, and investor relations.

Kevin holds an MBA from Harvard Business School, a Bachelor of Commerce Degree from Queen's University and is a CFA Charterholder.

Last Updated:September 23, 2023

What Is The XIRR Function?

XIRR is categorized as a financial function that calculates the internal rate of return for non-periodic cash flows.

XIRR Function

This function is similar to the IRR function in Excel that calculates the internal rate of return; the only difference between the two is that the latter is used for cash flows scheduled at regular intervals, while this formula is used for cash flows at discrete intervals.

For example, a regularly scheduled cash flow is something that you would receive on the 1st of every month, whereas a cash flow at discrete intervals could be the 1st of this month, the 20th of the succeeding month, the 15th of the month after, and so on.

What is the Internal Rate of Return (IRR)? You might have heard "Internal Rate of Return" a lot.

But what even is it, and why are we concerned about calculating it? To go one step further, what made Microsoft create its separate function, and why is it so important to the finance community?

IRR is a financial metric used to calculate the profitability of different potential investments and projects based on the cash flows they generate. IRR is, in turn, related to calculating the NPV of the investment. 

The NPV, which is short for Present Net Value, calculates the difference between the present cash inflows and present cash outflows. 

A positive NPV indicates that the investment is profitable, while a negative NPV indicates a loss-making investment.

Key Takeaways

  • XIRR is an Excel financial function that calculates the internal rate of return for non-periodic cash flows, different from IRR for regular intervals.
  • Internal Rate of Return (IRR) assesses investment profitability based on generated cash flows and relates to calculating Net Present Value (NPV) for positive investment indication.
  • When comparing investments with different IRR values, prioritize higher NPV for more long-term cash generation amid changing market conditions and discount rates.
  • XIRR in Excel predicts return rates for non-periodic cash flows, which is useful for real-life scenarios with irregular intervals.
  • Users should be cautious of potential issues with the XIRR function, such as errors related to mismatched date and value ranges, missing cash flows, or invalid date formats, resulting in #NUM! or #VALUE! errors.

Example for IRR and NPV

So how is IRR related to NPV? First, the rate of return on a project or investment will equal the net present value to zero. 

Let's see an example to understand it better: Assume that Wall Street Oasis wants to buy XYZ portfolio management company for $100,000. 

Our chief monkey calls the investment team and asks them to estimate the future cash flows that the portfolio management company will generate for the next five years. 

Recall that when it comes to the time value of money, "a dollar today is worth more than a dollar tomorrow," so the investment team discounts the future cash flows into the present using a discount rate (note that this rate is not the IRR).

The future and present cash flows are illustrated below:

Excel

The $100,000 at year 0 represents the cash outflow to acquire the company, while from year 1, WSO can expect all the cash inflows. Note that the parentheses indicate a negative value.

Since NPV is the difference between cash inflows and outflows, we will simply use the SUM function (as the outflow is already negative) in cell D12 to give the result of $21,567.53. 

Based on the NPV rule, a positive NPV indicates a good investment opportunity. However, making investment choices just based on NPV is not appropriate. 

An IRR is the lowest return you can expect from the investment to ensure that your decision is worthwhile. 

If the IRR is higher than the discount rate, the investment adds value to the portfolio, while if it is less than the discount rate, it destroys the investment value. Remember that when calculating the IRR, the value of NPV is equal to zero. 

We find that the IRR for the cash flows equals 6.7%, which is far less than our discount rate. 

What Decision Would You Make?

So which company would you buy - XYZ portfolio management company that generates an NPV of $21,567.53 with an IRR of 6.7% or ABC portfolio management company with an NPV of $20,000 and IRR of 9%? 

So even though ABC has a higher IRR of 9%, it is always advisable to select the investments that generate higher NPV in the long run. This is because market conditions can change drastically during any year, resulting in different discount rates for different years. 

If you have two mutually exclusive investment choices, i.e., either invest in A or B, look for the one that has a higher NPV. In our case, this is XYZ portfolio management company with an NPV of $21,567.53.

The reason to go with NPV is that IRR is as good as your underlying assumptions. A higher IRR does not necessarily mean a better choice of investment. On the other hand, a higher NPV means more cash generated from the project.

A short-term project might show a higher IRR, while a long-term project may estimate a lower IRR, making it look like a worse investment.

The WSO Investment Team's Choice: Now, the investment team has two options - make the investment choice solely based on IRR or the combination of IRR and NPV. 

If the decision is based on a higher IRR rule, i.e., the investment should have a higher IRR than its discount rate, then ABC portfolio management company is a good investment choice.

However, if both IRR and NPV are considered, then the obvious investment choice will be the one with a higher NPV, which is XYZ portfolio management company. Not relying on a single analysis alone is the key to profitable ventures. 

Most companies also study the required rate of return (IRR) and the weighted average cost of capital (WACC) to determine what to invest in.

Excel

Back to the XIRR function. In real-life scenarios, most investments or projects do not generate regular cash flows. 

For one year, you might get the cash flows on 1st January 2022, while for the next, the cash flows might generate on 6th March 2023, and so on. 

Using the Excel function, you can predict the rate of return earned on an investment and use it in financial modeling

The syntax for the XIRR function

Before diving deep into its application, it's essential to comprehend its syntax.

The syntax for the XIRR function is:

= XIRR(values, dates, [guess])

where,

  • values = (required) the cells' range representing cash inflows and outflows. 
  • Dates =(required) the cell range showing dates corresponding to cash inflows and outflows. 
  • Guess = This is an optional parameter that asks you to take an IRR guess. If ignored, the default value assumed is 10% IRR.

XIRR Function example in Excel

Suppose that you can put $5,000 in an investment. Then, the two investment alternatives generate cash flows inflows and outflows for five years, as illustrated below:

Notice that for both investments, the cash flows are non-periodic. The cash flows for Investment 1 are in the range C6:C11 and are discounted to the present value using the discount rate of 8%. 

The range D6:D11 represents the discounted cash flows, while E6:E11 represents the date cash flows were made. 

The formula that you will be used to calculate the internal rate of return is 

=XIRR(D6:D11,D6:D11) in cell C13 and =XIRR(I6:I11,J6:J11) in H13 respectively. You will get the result:

Even though both the investments hardly have a difference of $100 in total cash inflows (sum of cash flow from year 1 to year 5), the evident difference in the IRR is due to the date cash flows are realized. 

In most instances for Investment 2, the cash inflows are earlier than the cash inflows for Investment 1. 

Another reason Investment 2 will be a better investment opportunity is that cash flows try to return the initial investment at the earliest time. This is called the payback period for the investment. 

It is an entirely different concept. However, if you calculate the payback period for both investments, you will find that investment 2 returns the $5,000 earlier, i.e., in 3.838 years.

Investment 2 takes 4.39 years to return the principal amount based on the present value of our cash inflows.

Also, you can check out our article on the payback period to better understand the concept.

You might wonder where the guess argument comes into all this logic. Usually, there is one point where IRR is such that NPV equals zero.

But if the cash flows go from negative to positive multiple or vice versa multiple times, there are multiple such points. So Excel asks for the range of the desired IRR, so it knows which one to give you through the optional guess argument.

XIRR vs. IRR

XIRR and IRR both calculate the internal rate of return from the investment's cash flows; the only difference is that the latter can only calculate the IRR for periodic cash flows. 

For example, a bond that makes annual payments exactly on the 1st of January each year uses periodic payments. However, on the other hand, a new project that a company is undertaking can have non-periodic cash flows.

The syntax for the IRR function is:

=IRR(values,[guess])

where,

  • values = (required) the cells' range representing cash inflows and outflows.
  • guess = This is an optional parameter that asks you to take an IRR guess. If ignored, the default value assumed is 10% IRR.

Let's assume that after undertaking project A, Company XYZ makes a series of cash flows. Column D represents the present value of the cash flows with a discount rate of 8%.

Excel

We will try to find the internal rate of return for periodic and non-periodic cash flows. 

The formula to find periodic cash flow is =IRR(D6:D11), while for non-periodic cash flows is =XIRR(D6:D11,E6:E11), which gives you the result illustrated below:

Excel

There is a difference between both the results even though the discounted cash inflows and outflows are exactly equal. 

So it is safe to say that the evident difference is due to the difference in the date on which the cash flows are realized.

Practical Example of XIRR Function

A great example of where you can use the function is for calculating the returns from investments in mutual funds

Prepare an Excel sheet that shows all the cash flows for the systematic investment plan (SIP) and the date on which transactions were made.

A SIP is an investment scheme where a certain amount is invested in a mutual fund each month. 

Suppose you start a SIP worth $400 each month that is made on the 3rd of every month. After two years, the SIP will pay $10,373.28 on redemption at 8% interest. 

You keep investing for two years such that your table will look as illustrated below: 

Excel

Finally, after two years, on 3rd March 2024, you redeem the entire accumulated principal along with interest, which totals $10,373.28.

The principal amount is $9,600, and the interest amount is $773.28.

If you need to find the internal rate of return for SIP, the formula that you will use is =XIRR(C3:C27, B3:B27) which gives the result of 7.6%.

Our total returns from the investment for two years were 8.06%((10373.28-9600)/9600*100), while the XIRR or the annualized returns were 7.6%.

Excel

The total return is just the % difference between the cash flows into the SIP, equal to $9600, and the amount redeemed, equal to $10373.28 at the end of two years.

On the other hand, the annualized returns are the average amount of money earned by an investor each year for the given period, which in this case is equal to two years.

Finding the Monthly XIRR

We calculated the XIRR or the annualized returns for the SIP. But what if we need to find the monthly XIRR from cash flows? The process is quite simple.

Excel

We calculated our XIRR as 7.6%. Remember that this is an annualized rate, i.e., for the cash flows in an entire year.

If you need to calculate the monthly XIRR, use the formula =((1+annualized XIRR)^(1/12))-1 in cell F8 such that the result you get is 0.006145. Then, use the keyboard shortcut Alt + H + P key to change the formatting to percentage. 

The final result will be as illustrated below:

Excel

Common issues of the XIRR Function

You can face numerous issues while using the function, such as:

  • #NUM! error usually occurs when the date and value argument range differ.
  • The range consisting of a value argument will give a #NUM! Error if it does not consist of both cash inflows as well as cash outflows.
  • If any of the subsequent dates are earlier than the first cash inflow/outflow date, you will receive a #NUM! Error.
  • Excel will give #VALUE! Error if it is unable to identify a valid format for the date.

XIRR Function FAQs

Researched and Authored by Akash Bagul | Linkedin

Reviewed and Edited by Sara De Meyer | LinkedIn

Free Resources:

To continue learning and advancing your career, check out these additional helpful WSO resources: