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.
REFERENCES
"Microsoft Excel Functions and Macros," version 2.x, pages 92-94
"Microsoft Excel Function Reference," version 3.0, pages 162-163
KBCategory: kbusage Last reviewed: July 24, 1996 |