How to calculate IRR in Excel?

The Excel is a very powerful tool which every entrepreneur must use for the evaluation and the feasibility of the projects.

In today’s post, I will discuss the Internal Rate of Return or in the short form, the IRR.
​

• The Internal Rate of Return is the rate of return which makes the Net Present Value of all the cash flows from a project or an investment to zero.

It is generally used to measure the attractiveness of the investment or the project.

Let me suppose you invest in any of the projects of your field or choice.

How do you know that it is a profitable project and will give you the higher return than the cost of capital?

The IRR is one such tool which lets you know the internal rate of return of your project. It deals with the periodic cash flows.
If

• When IRR > cost of the capital or the hurdle rate

(The project is feasible)

• When IRR < cost of the capital or the hurdle rate

(The project is not feasible)
​

The formula

IRR Formula

Where,

• CF₁ = Cash flows at the 0 year
• CF₂ = Cashflows in the 1st year
• CF₃ = Cash flows in the 3rd year
• CFn = cash flows in the nth year

In order to calculate the IRR, the NPV value is set to zero.

• The value of IRR cannot be calculated analytically rather we use hit and trial method to calculate this value.

​

Why use IRR?

Example:
A company ABC is planning to purchase a machinery worth $ 4,00,000 and is expecting to generate the positive cash flows of $ 1,20,000 per year for the next four years.

In the 5th year, the management is planning to dispose it off at $ 40,000.

• The machinery must generate 8% annual return to cross the company’s hurdle rate.

In this case:

• If the IRR > 8% the company should purchase the machinery otherwise it should look for other alternative investment option.

If I arrange the cash flows in the table, it must look as follows:
​

The cash flows for 5 years

And after putting the values in the formula given above, the value of IRR comes to be 10.6 %.

The value of IRR is determined through hit and trial method and when I use the above formula, it is quite tedious.

But if I calculate the same using excel then it is quite easy and fast. So, let us calculate the IRR in the excel sheet. 

The syntax for IRR in the excel is as follows:

IRR (values, [guess])

The values part:

• In the value part, you enter the cash flows for the specific period. The cash flows need not be equal as in the case of an annuity but these must occur at the regular interval of time like quarterly or yearly. 
• For calculating the IRR your values must contain at least one negative and the one positive values.
  
• The negative values are taken as the investments or the cash outflows while the positive values are taken as the cash inflows or the profits.

The guess part:

• It is the value which you guess is near to the IRR. It is optional and you can leave it blank.

​

Calculating IRR in Excel

Step: 1
Arrange all the cash flows for the specific period as follows:
​

Cash flows of a project

The negative and red colored value indicates the cash outflow.

Step: 2

Choose the IRR function from the formulas and select all the cash flows

Calculating IRR

Leave the guess value blank

Step3:

IRR vs Hurdle rate

Conclusion

You can see the above screenshot that the IRR value is greater than the Hurdle rate, hence the project is acceptable and feasible. 

You can compare the two projects with the same duration.

However, the IRR has one major disadvantage as it does not disclose the amount of initial investment.

What I mean to say that it does not tells whether it is: 

• 15% on $ 4,00,000 OR 15% on $ 10,00,000,

Therefore, taking decision merely on IRR is not always advisable.

Let me explain it further, investment in project offering 15% return on $ 4,00,000 of the investment is more suitable for a small company which has less cash to invest than a project offering 20% on $ 10,00,000 of the initial investment.
​
Therefore, you need to keep all these points in mind while evaluating the IRR of the project and taking the final decision as the entrepreneur.