How to Use the XIRR Function in Excel (Explained Easy)

The name of this function might be a little spooky, I get it – but the applicability of the XIRR function is very practical and widespread.

The XIRR function in Excel is a powerful financial function of Excel that returns the internal rate of return for a series of cash flows, even if they occur at irregular intervals.⏳

The IRR of an investment helps you make informed financial decisions by evaluating the profitability of your investments and projects. This guide will answer all your questions about the XIRR function.

So, download your free practice workbook for this tutorial here and read till the end to learn all about the XIRR function of Microsoft Excel.

Table of Contents

What is the XIRR function

The XIRR function is an advanced version of the IRR function.

It is designed to calculate the Internal Rate of Return (IRR) for a series of cashflows occurring at irregular intervals.

You give it a series of cashflows, and it calculates the implied internal rate of return for them.💵

Pro Tip!

What is IRR?

IRR stands for Internal Rate of Return. The rate that makes the present value of a series of cashflows equal to zero.

This is the annual rate of return on your investment. To exemplify, if you invest $100 in a project today that yields you $50 each year for the coming three years, what is the rate of return for your investment?

It’s 23% – this is the rate at which your investment of $100 equates to the returning cash flows of $50 for the coming three years upon valuing them all.🤯

In other words, if you discount $50 each year for three years at 23%, it will equate to $100.

This 23% is what we call the internal rate of return for this investment. This tells how much you’ll ultimately earn on this investment and if it is even feasible to invest in it.

Syntax

The syntax of the XIRR function is all about three arguments:

Values: The series of cashflows. To be supplied as a range of cell references containing the cashflows.

Dates: The dates when these cashflows occurred.

Guess (Optional argument): A guess of what you think the IRR for the given cashflows would be. If omitted, Excel assumes it to be 10%.

Pro Tip!

The XIRR function works on a trial-and-error method. It would try different rates that make the closest IRR for the given series of cashflows.

However, if it fails to converge to a rate even after 100 iterations, it results in the #NUM! error.

The guess argument is optional and can be omitted most of the time. However, sometimes doing so might result in the #NUM! error if Excel fails to converge to a rate after 100 iterations.

Giving in a guess rate helps Excel start iterations closer to this rate converging to the correct answer within 100 iterations. Use a guess rate other than 10% (it is assumed by Excel by default).

Using the XIRR function of Excel

Time we see a practical example of running the XIRR function in Excel.

Suppose I invest in a business on 01 January 2024. Per my observations of the relevant industry and economy, I forecast the business to generate cashflows at multiple intervals over the coming 7 years.📆

Here is what the forecasted cashflows look like.

What is the rate of return earned by this investment?

Let’s calculate.

Step 1) Write the XIRR function as below.

Step 2) Refer to the cells containing the cashflows as the value argument.

Pro Tip!

The first cash flow in the above series is a negative value, whereas all the others are positive.

This is because the first cash flow represents a cash outflow (investment that I made). And the remaining cash flows represent a cash inflow (investment starts yielding returns).🧐

It is important to supply at least one negative and one positive cash flow as values to the XIRR function. If all the values are positive or negative, the XIRR function will return a #NUM! error.

It works to find the rate that equates your cash outflow to the cash inflows that occur in the future. Hence, it is important to supply it with at least one cash outflow (negative number) and one cash inflow (positive number).

Step 3) Refer to the range of cells containing the dates when the cash flows occur.

Step 4) Leave the guess argument omitted.

Step 5) Press the Enter button to have the IRR computed.

If the answer comes out as a decimal number like above, don’t fret.

Step 6) Go to the Home tab> Number group > Click the percentage Icon.

This converts the IRR to a percentage.

So, we have the IRR computed as 12.53%.

Pro Tip!

This was quick and easy, but what does this 12.53% mean?

Your investment of $1000 yields an annual return of 12.53%. If you discount all your cash inflows at 23% based on the intervals when they occur they would equal $1000.😎

And if you discount all your cashflows (inflows and outflows both) at 12.53% based on the intervals when they occur, the answer would be zero.

Let’s quickly see if the IRR we calculated works as it should.

Step 7) Calculate the number of years between the first date of your cashflows (01 January 2024) and the date of each cash flow by using the following formula.

Step 8) Drag it down to the whole list.

Step 9) Calculate the present value factor for each cash flow by using the following formula.

Step 10) Use an absolute reference for the IRR and drag the formula down the whole list.

Step 11) Divide each cash flow by its PV factor.

We have all present values calculated.

Step 12) Sum them up.

See that? The IRR for an investment is meant to calculate a rate where the present value of its cash inflows equates to the present value of its outflows.

When netted, the answer must be zero.

The above math tells the XIRR returned precisely accurate results.

XIRR vs. IRR

The only difference between the XIRR and IRR functions is the additional “dates” argument.

The XIRR function allows you to manually supply the dates for when the cashflows occurred hence, even if they occur at irregular intervals, it is catered to📅

However, the IRR function allows no such argument and assumes that all cash flows occurred at regular, periodic intervals.

To understand this through an example, I have redone the dates in our above example to bring them to periodic intervals.

Pay attention to note that all the dates are now periodically arranged at 1-year intervals.

Step 1) Calculate the IRR for these cashflows and dates using the XIRR function.

Comes out as 20.979%.

Step 2) Now calculate the IRR for these cashflows using the IRR function.

Step 3) Since the IRR function doesn’t have any date argument, this is it. Press Enter.

Comes out as 20.975%.

Both the IRRs are the same (ignore the very minor 0.004% difference).

This tells us that if the cashflows occur periodically, the IRR and the XIRR function work out the same answer.⚖

However, the difference only comes when your cashflows occur at irregular intervals.

For example, if I calculate IRR using the IRR function for our example with cashflows occurring at irregular intervals, the results would have been:

While the XIRR function incorporates the irregularity in cashflow intervals and returns the IRR as 12.53%, the IRR function fails to do so and still returns the IRR as 20.98%.

Conclusion

XIRR is a very smart and practical version of the IRR function that caters to the reality of irregular cashflows. It makes an important financial function that’s widely used by Excel users.

The IRR of any investment is a very important financial modeling metric, and it makes a prime part of investment-related decision-making.

Investors usually compare the IRR (Internal Rate of Return) of their investments with their Weighted Average Cost of Capital (WACC) to see if it is feasible to take up the investment or not. Other finance concepts commonly applied by investors include the concept of Net Present Value (NPV) and Compound Annual Growth Rate (CAGR).