Multiple IRRs (internal rate of return) is an issue which we encounter in financial modelling very regularly. The trouble with IRR calculation is the inherent assumption that interim cashflows are re-invested at the same rate (i.e. at the rate of IRR).

Due to this basic assumption, when we have project that has unconventional cash flows, meaning that there are more than one sign changes in the cash flow, we will get multiple IRRs.

In one of the earlier posts, I have explored the issue of multiple IRRs. But while working on a recent project, I was tormented to investigate this issue further. 

Multiple IRRs poses a significant challenge in financial analysis, and particularly when cash flows change sign multiple times.

Before we deep-dive into this, let’s talk about how Excel calculates IRR. Excel have 3 functions to calculate the IRR:

  1. =IRR(values() [, guess ] )
  2. =XIRR(values,dates, guess )
  3. =MIRR(values, finance_rate, reinvest_rate)

If you want to explore them further, read this post on Excel IRR function.

Let’s look at the first function, the IRR function in Excel is rather simple; it follows the following syntax:

Internal Rate of Return = IRR(values, [guess])

Where values are the cash flows. Values must contain at least one positive number and one negative number. Also, note that it uses the order of values to interpret the order of cash flows (time).

Guess is an optional argument. If the guess is omitted, it is assumed to be 10%.

Important to note that the IRR function is related to the NPV function in Excel. The rate of return calculated by the IRR function is the discount rate corresponding to a 0 (zero) net present value.

So,

NPV(IRR(values), values) = 1.79E-09; then it will solve this equation for IRR.

It is very interesting that it is equated to 1.79E-09 and not 0, Excel says that within the accuracy of the IRR calculation, the value is effectively 0. It is 0.00000000179 but not 0.

The question is why does Excel equate NPV to 0.00000000179 to calculate IRR and not to 0? Want to explore more, read  here.

Now, with this background information, let’s get back to the issue of multiple IRRs.

Understanding the Issue of multiple IRRs:

The occurrence of multiple IRRs is common when the cash flows transition from negative to positive and vice versa multiple times. Let’s look at the below cash flows:

If we calculate the IRR by using the Excel IRR function (without any guess value), it will give a result of 6.6%.

If we put guess 1%, we will get 6.6% IRR but if we put guess at 100%, we will get 36.55% IRR.

Multiple IRR

Calculating NPV at both IRRs will give zero values. So, which IRR is the correct IRR?

Calculating and plotting NPVs at different discount rates, clearly highlight the existence of two IRRs.

Multiple IRR

Resolving the Issue of multiple IRRs by using alternative functions

Using XIRR Function

Now assume that this cash flow has dates:

Multiple IRR

Let’s calculate the IRR using XIRR function.

=XIRR(values,dates, guess )

You will notice that we get a #NUM! error!

The #NUM! error occurs in Excel when a calculation can't be performed.

So, XIRR function fails to calculate the IRR as there are multiple IRRs.

Using MIRR Function

Now, let’s calculate the IRR using the MIRR function.

=MIRR(values, finance_rate, reinvest_rate)

MIRR calculation involves two rates - finance rate, which can be the weighted average cost of capital, and the reinvest rate.

The other rate, the reinvest rate, is confusing. Because we don't know at what rate we may be able to re-invest the future cash flows. Assuming refinance rate as bank deposit rate or risk-free rate can be an option. But it is never correct; we don't know what will be the reinvest rate for future cash flows. The IRR definition assumes that the re-investment rate is same as the IRR – but we don’t know the IRR yet.

So, in my mind the MIRR formula is not very reliable, read more here why we don’t use MIRR function.

Let’s calculate the IRR using MIRR function, anyway.

For this calculation, let’s assume that both the finance rate and reinvest rate are 8%.

The MIRR function at these assumptions would give 8.1% IRR, but unfortunately the NPV at this IRR is not zero! 

Multiple IRR

Since we do not know the reinvestment rate, let’s goal seek NPV to be zero by changing the reinvestment rate.

Multiple IRR

The result of goal seek would be the IRR at 6.6% (our first IRR) and the reinvestment rate at 4.8%, and of course the NPV would be zero.

So, is the 6.6% the correct IRR?

Unfortunately, we are not sure yet.

If we put both the finance rate and the reinvest rate at 36.5% (our second IRR), we will get MIRR at 36.5% and NPV will be zero!

Multiple IRR

The bad news is that the MIRR function still doesn’t help. Though amongst the two rates, finance_rate, reinvest_rate – we can calculate, or know, the finance rate before calculating the IRR (assume 9% for our calculations).

When finance rate is fixed, we would hope that goal seek will give unique value of the re-investment rate by making NPV zero. But unfortunately, this is not the case.

Multiple IRR

Multiple IRR

So, how to solve the issue of multiple IRRs?

I can think of two ways to solve this:

  1. Neutralizing cash flow sign changes
  2. Considering the Nearest IRR value by collaborating it with other KPIs

Neutralizing the Cash Flow Sign Changes

A possible solution involves neutralizing sign changes in cash flows by applying Present Value (PV) to negative cash flows and Future Value (FV) to positive cash flows.

In our example, we will take PV of all negative cash flows to Year 0 and FV of all positive cash flows to Year 4 assuming appropriate discount rate, assumed at 9%.

The new cash flows will look like this.

Multiple IRR

Now let’s do the calculations.

Multiple IRR

We get some interesting results:

  1. NPV is not zero at the IRR.
  2. XIRR is calculated as there is only one IRR.
  3. Guess at 1% and guess at 100% both give 9.22% IRR.

So, this is also not correct IRR as NPV is not zero.

Let’s now goal seek the discount rate to make NPV zero.

We get the discount rate as 6.6% (our very first IRR)!

Now, I’m fully convinced that 6.6% is the true IRR.

Why?

  1. Because all Excel functions give same IRR (6.6%).
  2. And there are no multiple IRRs.

Yes, there are no multiple IRRs!

Multiple IRR

Considering the Nearest IRR by collaborating it with other KPIs

Now, as I’m fully convinced that 6.6% is the correct IRR, let’s collaborate this with other KPIs.

Incorporating other key performance indicators (KPIs) that do not factor the time value of money into the calculation, can offer additional insights.

By comparing the results of various KPIs such as ROI, Payback period etc. alongside the IRRs, a clearer understanding emerges regarding which IRR aligns more closely with overall project performance.

In our case, we have two IRRs, 6.6% and 36.55%. Though I’m fully convinced that 6.6% is the correct IRR, let’s calculate other KPIs.

The total profit of our cash flows is -100 and the margin on cost is -10%, which is only possible if the IRR is 6.6% and not 36.55%.

We should be thanking ourselves for solving the issue of multiple IRR in Excel for once and all.

Before we conclude!

Let’s look at the statement that multiple sign changes in cash flows results in multiple IRRs.

Look at the below cash flows:

Multiple IRR

Now let’s calculate the IRR by using various Excel function.

Multiple IRR

All Excel functions give same result.

XIRR gives a bit different result as the cash flow is assumed at the start of the period. So, this statement that if there are more than one sign changes in the cash flow, we will get multiple IRRs is not entirely true.

So, the solution is:

Step 1: Neutralize the cash flow sign changes by using an appropriate finance rate.

Step 2: Use MIRR function to calculate the IRR, link re-invest rate to finance rate.

Step 3: Goal seek finance rate for NPV to be zero.

This will give an IRR which should be further collaborated by other KPIs which doesn’t involve time value of money calculation, like margin etc.

Hope you enjoyed this post on multiple IRRs, let me know what you think in the comment section below.

 

Download Excel File

Download the Excle file for the multiple IRRs calculation.

feasibility expert

Naiyer Jawaid

ABOUT THE AUTHOR

I am a results-oriented real estate professional. My goal is to create projects that are not only profitable but also make a positive impact.

With a diverse background in engineering and finance, I have successfully managed projects across the globe, from the dynamic landscapes of UAE, Qatar, Oman, and the UK to the vibrant Far East and Eastern Europe. My portfolio includes 3 Mega and 1 Giga projects, encompassing nearly every real estate asset class.

I've analyzed and worked on $20,000,000,000 worth of real estate development projects with a total GFA of 17,900,000 sqm.

My core strength lies in providing strategic insights for large-scale mixed-use developments, and my proficiency in business planning and strategy implementation sets me apart in the industry.

As a consistent top performer, I have an established track record of turning projects into resounding financial successes.

Here I blog about project finance, development analysis and feasibility studies.

Are you someone involved with
real estate feasibility?

We are excited to launch the next generation of real estate feasibility software to help you manage your development projects with ease.

Register now for a free trail license!