How to Use Excel to Calculate NPV

By C. Taylor

Calculating the net present value or NPV of an investment allows you to assess the profitability of the venture it's associated with. The NPV is the cumulative sum of expected profits per time period, discounted by future inflation or competing investments. Microsoft Excel simplifies this calculation with its NPV function, and can calculate NPV for up to 254 time periods, including the initial investment period.

Step 1

Open Microsoft Excel and enter the discount percentage per period in cell A1. This percentage could be the annual rate of inflation, or a comparable investment. As an example, if investing in this venture prevents you from investing in a mutual fund with an expected annual return of 5 percent, you would enter "5%" in cell A1.

Step 2

Enter the cost of the investment, realized at the end of the first period, in cell A2. In the example, if you invested $10,000 and would receive no returns the first year, enter "-10000" in cell A2.

Step 3

Enter the expected profits at the end of each consecutive year, starting in cell A3 and working down. In the example, if you expected profits of $2000, $3000, and $6,000 in the next three years, you would enter those figures in cells A3, A4 and A5.

Step 4

Enter "=NPV(A1,A2:end_range)" in the next available cell and replace "end_range" with the cell reference of your last data point. Press "Enter" to see your NPV. In the example, you would enter "=NPV(A1,A2:A5)" to result in -$182.02. Your result is that although the total profit is $1,000 more than your initial investment, you would actually lose $182.02, compared to the mutual fund.