Suppose you want to calculate the net present value for the following problem: You are considering an investment in a machine that costs $200,000 and the additional cash inflows (net income + depreciation) from the machine will be $80,000, $100,000, and $120,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:
-200,000 in cell C1
80,000 in cell C2
100,000 in cell C3
120,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:
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 220,160.73. To get the net present value, you have to subtract the 200,000 (remember that adding a negative number is the same as subtracting) from the 220,160.73 and your answer is: 20,160.73.
To get the Profitability Index (PI), divide the present value of the inflows, 220,160.73 by the initial cost (200,000). The PI is 1.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 asked to guess is because there may be several IRR's.
Make sure that you get the answer to two decimal places. You can get two decimal places by formatting the cells or by going to the menu bar and clicking on the "increase decimal" icon. The answer is 21.65%. If the IRR is greater than the cost of capital, you go ahead with the investment. Since 21.65% is greater than 16%, you invest in the machine.