NPV

The NPV (Net Present Value) of $1 = 1/(1+i)N where i is the interest or discount rate, and the period number is N.

NPV values calculated by Excel, when used for investment analysis, are incorrect.

This is because the NPV function was incorrectly defined by someone in the past, and has never been corrected!

The NPV function hidden away within your spreadsheet program instructions is most probably written as:

NPV = Sum of this series for all values from time period =1 > n = At/(1+r)t > n

But with Net Present Value calculations, the amount in the first period (the investment) should not be discounted as this formula rule says, and the starting period number should be zero.

The correct equation for Net Present Values is:

NPV = Sum of this series for all values from time period =0 > n = At/(1+r)t > n

Example:
When you enter the figures in the table below into Excel, using NPV(0.08, cell range) the solution given is 5,355.99. The correct result is 5,784.47, a difference of 8% in this example.

Year Cash Flow Discount factor Present Value
0 -10,000 (1+0.08)0 = -10,000 - 10,000.00
1 +3,500 (1+0.08)1 = +3,500 x 0.92593 + 3,240.74
2 +4,000 (1+0.08)2 = +4,000 x 0.85734 + 3,429.35
3 +5,000 (1+0.08)3 = + 5,000 x 0.79383 + 3,969.16
4 +7,000 (1+0.08)4 = + 7,000 x 0.73503 + 5,145.21

Sums: + 9,500 + 5,784.47

Software Links

Investment-Calc More Info

Download Files

Net Present Value

Reference Pages

Discounted Cash Flow

NPV Rule

IRR

Scenario Manager