Home » Business, Tech, Tutorial

How to calculate NPV and IRR using Excel Sheet

31 July 2012 Written by: No Comment

Another ‘how to’ tutorial for calculating NPV and IRR using MS Excel. NPV stands for Net Present Value. NPV is defined as the sum of the present values of the individual cash flows of the same entity. It is basically used in analysing the feasibility of a project.

IRR stands for Internal Rate of Return. IRR is defined as the discount rate at which Net present value is zero. It is sometimes also referred as Economic Rate of Return(ERR).  It is basically used to analyse the desirability to undertake a project. More is the Internal Rate of Return, more is the returns from the project.

Ques: How to calculate npv using excel?

Ans:  Here are the steps to calculate NPV

Methods to calculate NPV :

NPV Method 1:

  1. You could easily calculate it by writing NPV function in function bar.
  2. Function for the same is: =NPV(r,A2:A10)-A1

r = Cost of Capital (in %age)

A2 : A10 is the value of Cash Flow from t=1 to t=9.

Finally you need to subtract value of cash flow at A1 since you do not want to discount.

calculate NPV using formula in excel

NPV Method 2:

  1. You can also calculate NPV using Financial tab in Formulas Menu bar.
apply NPV formula using Excel

2. Select NPV from the drop down list.

3. Next in the pop up window :

  • a. Write Cost of capital (in %age) in rate textbox.
  • b. In box Value1 write,  A2:A10 i.e. the cash flows at t=1 to t=9.
  • c. Leave blank Value2.

4. Finally subtract value A1 i.e. Cash flow at t=0, to arrive at final NPV value.

NPV calcutated in ms excel

Ques. : How to calculate IRR in excel?

Ans.: Here are the steps to calculate irr:

Methods to calculate IRR:

IRR Method 1:

  1. You could easily calculate it by writing IRR function in function bar.
  2. Function for the same is: =IRR(A1:A10)

r = Discount Rate (in %age)

A1 : A10 is the value of Cash Flow from t=0 to t=9.

final IRR in excel using formula

Method 2:

  1. You can also calculate IRR using Financial tab in Formulas Menu bar.

Select IRR using Financial tab in Formulas Menu bar

2. Select IRR from the drop down list.

3. Next in the pop up window :

  • a. In the values section write, A1:A10 i.e. the cash flows at t=0 to t=9.
  • b. Write any guess value in the Guess row as IRR often gives two value. So the value close to guess value will be selected. You may use .1 for instance.

Select IRR from the drop down list.

Hence, we see that calculating NPV and IRR using Microsoft Excel is very easy and simple and avoids the use of calculator for lengthy calculations thus saving time increasing accuracy.

Further discussions about calculating NPV(Net Present Value) and IRR(Internal Rate of Return) are welcome in the comments below.

Leave your response!

Please add your comment below. Here is the trackback Link and subscribe to these comments Comment RSS Link.

Be nice. Keep this blog clean. Please dont spam.

We support Gravatar. To get your own gravatar, please register at Gravatar.