Internal Rate of Return

The Internal Rate of Return (IRR) can be defined as the rate of discount which makes the Net Present Value (NPV) equal to zero.  

If you do not understand the concept of Future Value and Present Value of money it might be an idea to read the tutorials on these first.  Let's take the time to understand what Rate of Return means.  The Rate of Return is the speed at which money comes back to you expresses as a percentage per annum.  For example;

Example 1

If you invest $1000 now.

You then receive a $100 return every year into perpetuity.  

In this situation; 

Rate of Return=$100 / $1000
Rate of Return=10% p/a

Lets try another example;

Example 2

If you invest $1000 now.

At the end of Year 1 you receive $100
At the end of Year 2 you receive $100
At the end of Year 3 you receive $100

Year 4 the money dries up and you do not receive any more money, ever.

What is the Internal rate of return in the above example?

NPV  = − Initial Investment X (1 + r)-0   +  Return X (1 + r)-1 + Return X (1 + r)-2  + Return X (1 + r)-3 =  0

Translating this formula to our problem above.

NPV= (1000) X * (1 + r)-0 + 100 X (1 + r)-1 + 100* (1 + r)-2 + 100* (1 + r)-3

You will notice that the rate r above is not solved.  This is the part which is not known or is 'Internal' (unknown).  So we need to devise a cunning way to determine the rate.  This sound crazy but we are going to guess at the rate and see how close we come to 0.   Our initial guess will be 10% so we replace r with 10% above.

(1000) X (1 - 0.10)^0 + 100 X (1 + 0.10)^ -1 + 100 X (1 + 0.10)^ -2 + 100 X (1 + 0.10)^-3=0

In Excel this translates to;

NPV  =-1000*(1.1)^0+100*(1.1)^-1+100*(1.1)^-2+100*(1.1)^-3

The answer is -751

Now since -751 is not equal to zero then we know the answer is not 10%. So we keep keying numbers till our equation produces a number which is very close to zero.  So you are not changing the variables multiple times do the following.  

The formula to produce the Answer is as follows.

=B2*(1+B4)^0+B3*(1+B4)^-1+B3*(1+B4)^-2+B3*(1+B4)^-3

Cell B4 is all I need to change now to determine the IRR.  So keep changing our guess percentage till it gets very close to zero.  The answer is

-42.44175%

Now that I have explained the theory which is important if you want to know what is happening when you apply this to Excel's built in IRR formula.  Below is my example;

The following is the same with our initial investment ($1000) and the cash inflows $100, for each year.  Then we use the IRR formula to calculate the Internal Rate of Return %.  This is as follows;

=IRR(B2:B5)

You can see this formula is a lot simpler than our long hand formula above but I think it is very important to understand what the IRR formula in Excel is calculating.  While this is the correct answer, this is a terrible return for a potential investment and based on this information you would not invest.  However, it is important because when you know the rate of return, you can compare it to the rates of return you could earn by investing your money in other projects or investments.  In finance it is one of several methods used to evaluate projects.