IRR Calculations in Excel – Part 1

I have been delaying this post for quite sometime but I finally got hold of me. I have broken down this post into 3 parts and I discuss everything from from IRR to its nitty-gritties, its loopholes and the workarounds. I have written it in English so that even your kids can understand! Enjoy

Part 1 – Contents

1. What is IRR ? (A complex and simple definition)
2. A Case to explain the concept of IRR
3. How to Calculate IRR (Simple math equation and by using Excel’s IRR function)
4. Investing or Not Investing in the Business (Case Analysis)
5. Interpreting Positive and Negative IRR

 

What is IRR ?

If you googled ‘What is IRR’ you would get something like this –

“ Internal rate of return is the interest rate at which the net present value of all the cash flows (both positive and negative) from a project or investment equal zero. Internal rate of return is used to evaluate the attractiveness of a project or investment “

Well, if the above definition sounds a bit overwhelming, I’ll give you a simpler one –

IRR can be crudely understood as the maximum return achievable from the project

Sounds fair enough? But I don’t want to leave you halfway, rather I really want you to understand IRR in real sense!

 

Let’s take a look at this Case

Assume that you have an opportunity to invest in a business that is yielding the following cash flows

My simple question – Would you invest ?  No Hurry, take some time to do your own math! What ever be your answer (Yes or No). The excitement lies in finding out WHY?

Let’s do a bit of analysis

1. The business needs an upfront investment (-670) but the returns (1,134) come in 7 years from today. This brings me to a critical concept in finance
   • Money today is more valuable than Money tomorrow (I am sure you know this). It is called Time Value of Money
   • Because the cash returns are lying in the future you have to check that if the returns (1,134) are less or more than the your present day investment (-670) ?
2. So the question is how much is 1,134 worth today? You obviously understood that we need a discount rate, but again what should be the discount rate?

Instead of arguing about the discount rate, let’s figure out what could be a maximum discount rate.

Maximum means the discount rate should be such that the total cash flows earned over 7 years (1134) should be at least be equal to our present day investment (-670) So that we don’t make a loss on our investment

If you have understood everything till here then you know that I am trying to find out the maximum discount rate which makes my Net Present Value (Total Investment – Present Value of Cash Flows Earned)=0

Now when you read the definition from Google Search once again, it would make much more sense

“ Internal rate of return is the interest rate at which the net present value of all the cash flows (both positive and negative) from a project or investment equal zero. Internal rate of return is used to evaluate the attractiveness of a project or investment “

The last line talks about attractiveness of the project, we are still to evaluate that. Let’s find the discount rate (IRR) first

How to Calculate IRR ?

All you have got to do is to solve this equation

Or we could just ask Excel do it with the help of the IRR function

So if you discount the cash flows by ~14.5% they will result in a Present Value of 670 or Net Present Value of 0 (Investment – Present value of cash flows)

The IRR formula : Note the IRR formula has 2 parts

1. Values : These are the Cash Flows. It is mandatory to have positive and negative cash flows for excel to calculate the IRR
2. Guess : For excel to start the IRR calculation it assumes a discount rate (default) of 10%. It is not mandatory to provide input for this argument. We will see more specific use of guess in the next part

 

Should we invest in this Business?

We have not yet answered this question! But now we have done a bit of analysis to take a call.

Case 1 : Imagine that you have only invested in bank deposits which give you a return of 6% (assumption). So would you like to invest in a business which gives a maximum return (IRR) of 14.5% ? Ideally you should say YES!

Case 2 : Imagine that you have invested in equities before which gave you a return of 18% (assumption). Would you like to invest in a business which gives an IRR of 14.5% ? Definitely NOT! You would rather put them in your stocks.

Certainly this is not the most sophisticated piece of analysis but it does give you an idea about IRR

 

Interpreting IRR when positive or negative !

Case 1 : Negative IRR

Note that IRR is negative. Think about this logically, you are investing -670 today for receiving 650 (which is less than your investment) over 7 years. This is stupid. It is because your Investment is more than your returns you have a Negative IRR*

 

Case 2 : Positive IRR

We have discussed the positive IRR example before, but lets do it again. Clearly you are receiving more than what you are investing, so a positive IRR

* Note that there could be other reasons of getting a negative IRR, which I will discuss in Part 2  of this post

I am all ears to your Questions

Please put down your questions in the comments. I would love to know your feedback