Calculating Net Present Value Using Excel

Calculating Net Present Value and IRR Using Excel

Page 1 of 2

Calculating Net Present Value Suppose you want to calculate the net present value for the following problem: You are considering an investment in a machine that costs $100,000 and the additional cash inflows (net income + depreciation) from the machine will be $40,000, $ 50,000, and $60,000 over the next three years. The firm's cost of capital is 16%. Suppose you enter the data in an Excel spreadsheet as follows: -100,000 in cell C1 40,000 in cell C2 50,000 in cell C3 60,000 in cell C4 To calculate the net present value, go to the Function Wizard. The function you are going to use is in the function category of FINANCIAL and is NPV. You will have to provide the following information: RATE: .16 VALUE 1: C2:C4 For rate, enter the cost of capital, .16 For VALUE 1 enter the values that get discounted, C2:C4 Do not enter the initial cost (C1) since it does not get discounted. The value you will get from Excel for the above problem is 110,080.36. To get the net present value, you have to subtract the 100,000 (remember that adding a negative number is the same as subtracting) from the 110,080.36 and your answer is: 10,080.36. To get the Profitability Index (PI) , divide the present value of the inflows, 110,080.36 by the initial cost (100,000). The

http://academic.brooklyn.cuny.edu/economic/friedman/npvirr.htm

10/5/2010

Calculating Net Present Value and IRR Using Excel

Page 2 of 2

PI is1.10. This means that every invested present value dollar yields $1.10 in present value inflows. If the net present value is positive, the PI is greater than 1.

Calculating Internal Rate of Return (IRR)

Go to the function wizard and get the function IRR. You will have to provide the following information: VALUES: C1:C4 GUESS: .10 For Values, enter all the flows, including the cost. The cost should be a negative number. For Guess, enter any value. If you do not want to guess, use .10. The reason you are...