Excel IRR functions

Free Preview

There are 3 ways to calculate an IRR in Excel.

Goalseek the NPV to zero

One way to determine the IRR is to GoalSeek it. IRR can be defined as the discount rate that makes the NPV equal to zero.

To follow along go to 17.11

So if you have set up an IRR calculation as below, you can run a goal seek where you tell Excel to set the NPV (cell B47) to zero by changing the discount rate (B44).

IRR

The resultant answer of 50.57% is the IRR. What this means is that the cash flows represent a 50.57% compounded return.

IRR function in Excel

The IRR function in Excel will automatically do what the Goalseek did above.

To follow along go to 17.11

https://youtu.be/tmKXKr8f2QY

As shown below, in this case (unlike NPV) you MUST include the first period which is an outflow (opposite sign to the rest of the cash flows). As part of the function you also have an option to put a guess. This is just to help Excel start its calculations.

IRR

IRR has similar constraints as the normal NPV function in that it must be equally spaced in time i.e. all cash flows must be EITHER monthly, quarterly etc and they cannot change from say monthly to quarterly.

XIRR function in Excel

Similar to NPV, there is an XIRR function which, in our opinion, is a better function.

To follow along go to 17.11

https://youtu.be/89TRZNz2pLw

For XIRR you need to identify the cash flows (including the initial cash flow) and the dates of the cash flows as shown below. You can also specify a guess.

IRR

The XIRR does not need equally spaced cash flows as long as the dates reflect when the cash flows. You will note that the answer of 50.62% is slightly different from the IRR function (50.57%) as a result of it using the exact dates provided.

Understand what the IRR means

Lets just step back and understand what the IRR means and why we compare it to an investment from a bank.

To follow along go to 17.11

https://youtu.be/scn4uXXoNd0

In our example we generate an answer of an IRR of 50.57%. Below we have set up a bucket where we put the money into a bank and ask them to pay us out the same amounts that the project would generate at the same time. We ‘grow’ this investment at 50.57% as if the bank is paying us 50.57% interest per year.

As seen below we end up on the final payment of 18 677 with a closing balance of zero. So by growing our initial 20 000 at 50.57% and getting repayments as per the project cash flow, at the end of the project the bank owes us nothing.

IRR

Problems with IRR to watch out for

IRR is a very popular method of assessing a series of cash flows. It does however have some problems being:

  • If cash flows fluctuate from negative to positive often, there can be multiple answers
  • As it is an iteration calculation it may not find the answer unless you give it a similar guess number. So if you get an error message, try changing the guess number.
  • For IRR’s that are large, it makes the assumption that you can take the cash you received and invest it at the same rate. This could be difficult to replicate (look at MIRR below).
  • If you have very short periods of time, the IRR can be misleading. If I go to a casino, bet on red and win, IRR assumes that I can do the same thing every day for the year. So my 1 on day 1 becomes 2 on day 2, 4 on day 3, 8 on day 4 etc. The IRR is enormous and unrealistic.

MIRR function in Excel

Although we don’t see the MIRR (modified internal rate of return) function much in the business world it seems to conceptually make the most sense. It addresses the issue of being able to achieve the same return with the money you have received in other projects.

To follow along go to 17.11

https://youtu.be/3J1RKIjnshc

As shown below it is similar to IRR except you have to provide a finance rate and a re-investment rate. These rates are used to determine how much the outflows cost you and how your return on the inflows will be invested.

So in our example where the traditional IRR’s gave an answer of over 50%, if we assume that the money we invest is free (0%) but any money we receive back can only earn 11% per year, the MIRR now becomes 32.3%. This is a vast difference but more likely because how many 50% projects are out there?

IRR




Back to: Financial Calculations and compound interest in Excel > Time Value of Money- Debt, NPV, IRR