MEND MY LADDER
  • Home
  • Blog
  • About
  • Privacy
Find your domain and create your site at Weebly.com!

How to calculate IRR in Excel?

24/5/2019

0 Comments

 
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
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:
​

Cash Flows
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
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
Calculating IRR
Leave the guess value blank

Step3:
IRR vs hurdle rate
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.
0 Comments



Leave a Reply.


    Author

    Hi! I write posts on growing your business


    Categories

    All
    Grow Your Business


    Archives

    May 2019
    April 2019
    March 2019
    February 2019


    Create your online store and start selling. Try it today at Weebly.com!

    Categories

    All
    Grow Your Business


    Start Your Free Trial Today

    Start Your Free Trial Today


    RSS Feed

Copyright Mend My Ladder @ 2019
Proudly Powered by Weebly
  • Home
  • Blog
  • About
  • Privacy