How to Calculate IRR with Excel

IRR is a frequently visited topic when talking about investments and returns.

Trying to calculate the IRR (which stands for the Internal Rate of Return), given a series of cash flows, can be quite complex and time-consuming, as it requires making a number of guesses until you get a result.

However, Excel makes this process extremely easy and quick. There is an in-built IR function in Excel, and it’s super easy to use.

In this tutorial, we will explore different IRR functions that Excel offers, as well as how and when to use them.

What is IRR and When is it Used?

IRR is short for Internal Rate of Return

Simply put, it is the rate of return you receive for the investments made.

So if you have a series of cash flows relating to a particular investment venture and want to decide whether the investment is worth it, or whether it gives better returns as compared to some other opportunity, the IRR can be a good indicator to help you decide.

To give you an example, if you invest $100K in a project that pays $2000 every year for the next 10 years, you can use the IRR formula to find out what is the rate of return. In this example, the IRR is 15%.

So you can decide whether to invest in this project or not based on the IRR. If you can get a better IRR somewhere else, you can invest there.

In Excel you can calculate the IRR by using the IRR function, which falls under the category of Excel’s financial functions.

Let us look at an example to understand how the IRR function can be used in Excel.

How to Calculate IRR with Excel?

Suppose you have the following dataset and you want to calculate the IRR for this project.

Data to calculate IRR in Excel

The negative values in the above figure represent payments made (i.e., an outflow), while the positive values represent income generated (i.e., inflow).

Note that the dates corresponding to each value are periodically spaced out. So it could be a gap in months or quarters or years, but it has to be the same gap.

If you had to mathematically calculate the IRR for the above cash flow series, you would need to make some guesses for the IRR, calculate the NPV for each guess until the NPV converges to 0.

With Excel’s IRR function, however, this takes much less time.

Syntax for the IRR Function

The syntax for Excel IRR function is as follows:

=IRR (values, [guess])

Here,

  • values is an array or reference to a range of cells that contains cash flow values
  • guess is an estimate for expected IRR. It is a number that you guess is close to the result of the IRR. This parameter is optional.

Note: The default value for the guess parameter is 0.1 or 10%.

So the IRR function takes in a set of cash flow values (both negative and positive) and returns the Internal Rate of Return in the form of a percentage value.

The function uses an iterative method to find the optimum IRR value, starting with a guess. It then keeps on cycling and adjusting the IRR value till it gets to a point where the result is accurate within the range of 0.00001%.

It cycles up to a maximum of 20 tries. If the results do not converge or if it is not able to find a result that works even at the end of 20 tries, the IRR function returns a #NUM! error.

So if you get a #NUM! error like this, you can try entering different guess values in the formula.

As expected, if the income generated is more than the investments made, the IRR function will return a positive value. Similarly, if the income generated is less than investments made, then IRR will return a negative value.

In the following example the Internal Return Rate can be calculated using the following formula:

=IRR(B2:B6)
IRR formula in Excel

It gives us an IRR of 39%, which means that we will make 39% return on a monthly basis on this project (since the time period gap is in months).

If the gap has been in years, this would mean that we would have made a return of 39% every year with project

Points to Remember when Using IRR Formula

There are a few points that you need to keep in mind when using the IRR function:

  • For the IRR function to work correctly, your cash flow values have to be spaced out in regular intervals, for example weekly, monthly, annually, bi-annually, etc.
  • The IRR function uses the order of the values to interpret the order of the cash flow. So make sure that you specify the cash flow values in sequential order.
  • Since it is assumed that the cash flow values are sequential and placed at regular intervals, there is no need to specify dates corresponding to the values.
  • Your cash flow series should have at least one positive and one negative value. Otherwise, the IRR function will return a #NUM! Error.
  • If the inputs of the IRR function contain a text or logical value, those values are ignored.

I am sure you agree when I say that using IRR in Excel is extremely easy (as easy as using the SUM function).

However, there are some obvious limitations with this formula, one being that the data needs to be spaced out.

But what if the cash inflows don’t happen at regular interval? What if it happens on specific dates or irregular time intervals?

To deal with it, Excel has another IRR formula.

Let’s look at some variations of the IRR formula in Excel.

Also read: How to Calculate Growth Rate in Excel

Other ways to Calculate IRR in Excel

Excel offers two other functions for calculating the Internal Rate of Return:

  • XIRR
  • MIRR

Let us take a look at the syntax of these two functions and how to use them:

Using the XIRR Function to Calculate IRR with Dates

The XIRR function is used to calculate the IRR when the series of cash flows occurs at irregular intervals.

The syntax for the function is as follows:

=XIRR (values, dates, [guess])

Here,

  • values is an array or reference to a range of cells that contain cash flow values
  • dates is an array or reference to a range of cells that contain the dates corresponding to the cash flow values.
  • guess is an estimate for expected IRR. It is a number that you guess is close to the result of the IRR. This parameter is optional.

So the XIRR function takes in a set of cash flow values (both negative and positive), along with their corresponding dates and returns the Internal Rate of Return in the form of a percentage value.

In the following example the Internal Return Rate can be calculated using the following formula:

=XIRR(B2:B6,A2:A6)
XIRR formula in Excel

A few points to note here:

The XIRR function can also work with a cash flow series in regular intervals, but it requires the date to be specified for each value.

Moreover the values and dates need to be in chronological order and the dates have to be valid Excel dates.

Also note that the XIRR function assumes that interest rates are the same for both incoming and outgoing cash.

So if there are separate interest rates for borrowing and investing money, then the XIRR will not return the correct result.

Using the MIRR Function to Calculate IRR with Different Interest Rates

MIRR stands for Modified Internal Rate of Return.

The MIRR function is used to calculate the IRR when the series of cash flows occur at different interest rates.

The function takes into account both the discount rates and the reinvestment rates for the cash flow.

The syntax for the function is as follows:

=MIRR (values, finance_rate, reinvest_rate)

Here,

  • values is an array or reference to a range of cells that contains cash flow values
  • finance_rate is the discount rate or required rate of return, specified as a percentage.
  • reinvest_rate is reinvestment rate or interest rate received on reinvestment. This is also specified as a percentage.

So the MIRR function takes in a set of cash flow values (both negative and positive), along with the finance and reinvestment rates and then returns the modified Internal Rate of Return in the form of a percentage value.

In the following example, you can use the below formula to calculate the Modified Internal Return Rate:

=MIRR(B2:B6,B8,B9)
MIRR formula in Excel

Note that MIRR assumes cash flows are spaced at regular intervals and are specified in chronological order.

To conclude, we saw three different IRR functions in Excel. Each of these are meant for different situations.

  • IRR is used when cash flow values are spaced at regular intervals.
  • XIRR is used when you have differently sized timed periods between each cash flow value.
  • MIRR is used when there are separate interest rates for borrowing and investing cash.

Another similar metric used when assessing projects based on expected return is the NPV method (where NPV stands for Net Present Value). While IRR gives you the rate of return at which NPV would be zero, the NPV method tells you how much money you would make given a specific rate of interest. While both the methods are used when assessing investments/projects, NPV is considered a better metric.

So, depending on your requirement, you should now be equipped to use the appropriate function to calculate the IRR and deduce the investment pathway best suited for you.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment