Microsoft KB Archive/51585

= Microsoft Knowledge Base =

Excel NPV Function Result Versus Lotus 1-2-3 Result
Last reviewed: July 24, 1996

Article ID: Q51585

The information in this article applies to:


 * Microsoft Excel for Macintosh, versions 2.x, 3.0, 4.0, 5.0 5.0a
 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.x, 5.x, 7.0 7.0a
 * Microsoft Excel for OS/2 versions 2.2, 2.21, and 3.0

SUMMARY
In Microsoft Excel, the NPV (net present value) function is defined as follows

NPV(rate,value1,value2,...) where

n       Value i   NPV=(SUM) i=1     (1+rate)^i In Lotus 1-2-3, the i starts at 0 (zero). Therefore, if the NPV in Microsoft Excel's calculation includes the investment or the first entry, it does not give the same answer as Lotus or a calculator. To get the same answer, the investment should be excluded in the NPV values and added at the end of the NPV formula.

MORE INFORMATION
For example:

A1: Investment B1: $-40000    <--- negative value required A2: year1      B2: $  8000 A3: year2      B3: $  9200 A4: year3      B4: $ 10000 A5: year4      B5: $ 12000 A6: year5      B6: $ 14500 If the NPV is entered in Excel as =NPV(8%,B1:B6), it may give an unexpected result. It could be entered as =NPV(8%,B2:B6)+B1. This will return $1,922, which is what Lotus 1-2-3 and a calculator would return.