“不务正业的”NPV函数

标签：Excel公式

NPV是一个财务函数，基于一系列将来的收（正值）支（负值）现金流和一贴现率，返回一项投资的净现值。其语法为：

NPV(rate,value1,value2,…)

本来是计算净现值的函数，然而，却可另作它用。

如下图1所示，列A中有一系列带有单位的数值，现在想要求其中的数字之和。

图1

怎么办呢？一种思路是，如图1所示先将列A中单元格内的数字提取到列B中，然后求和。

可以使用NPV函数来实现。在单元格B1中输入数组公式：

=NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A1,,,LEN(A1))),1)%,""))

拖动复制至数据单元格末尾即可。注意，这是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。

NPV的神奇之处在于NPV计算公式，其中每项乘以(1+rate)^n的倒数，其中n是序列中的第n项，例如，(1+rate)^1、(1+rate)^2等。通过使用不同的rate值，可以得到不同的结果。在本例中，使用-0.9，提供给1+rate=1+(-0.9)=0.1，所以得到类似于{0.1;0.01;0.001;0.0001;0.00001}的值，取倒数得到{10;100;1000;10000;100000}，等等，再加上NPV忽略文本值，因此可以得到了期望的结果。

下面，我们来具体分析一下。例如上图1中，单元格B3中的公式为：

=NPV(-0.9,,IFERROR(MID(A3,1+LEN(A3)-ROW(OFFSET(A1,,,LEN(A3))),1)%,""))

其中，

IFERROR(MID(A3,1+LEN(A3)-ROW(OFFSET(A1,,,LEN(A3))),1)%,"")

生成数组：

{"";"";0.04;0.03;0.02;0.01}

这样，公式就转化为：

=NPV(-0.9,, {"";"";0.04;0.03;0.02;0.01})

由于NPV函数忽略参数为空单元格、逻辑值、数字的文本表示式、错误值或不能转化为数值的文本，因此，上式相当于：

{10;100;1000;10000;100000}*｛；0.04;0.03;0.02;0.01}

即：

10*0+100*0.04+1000*0.03+10000*0.02+100000*0.01
=1234

小结

本文给我的最大冲击不是公式本身，而是思路。一个函数有它本身的用途，然而，如果思路打开，也许这个函数会得到更广泛且有想象力的应用。当然，前提是我们首先要熟悉函数的基本运作原理，能够熟练地运用它们，打造扎实的基本功。