工作再忙，这5组Excel公式也要看，保你准点下班！

原文标题《工作再忙，这5组Excel公式也要看，保你准点上班！》 #

在财务投资推算中，回收期是很重要的参考指标，它是指从投资到收回本息的时间。

#

与净折现NPV和内部回报率IRR不同，Excel中并不存在专门的回收期函数。 #

然后，为了估算回收期，财务同学们，堪称八仙过海，各显神通！ #

小花见过的最低贱的一种方式，居然是用IF函数建立辅助行，再通过求和得出回收期。

▋例1：IF+辅助行求准确回收期

#

C4公式如下：

#

=IF(C0,MAX(-B3/C2,0),1) 
#
 

公式说明： #

使用IF函数进行条件判定，当期累计经营性现金流大于或等于0的，返回1。 #

假如当期累计经营性现金流小于0，则返回下期累计经营性现金流与当期经营性现金流之比的正数和0之间的较大值m（使用MAX来完成大小判定）。

#

此刻，只有累计经营性现金流在当期首次实现回正，m才为负数，否则MAX函数返回0。将辅助行求和的结果即为准确回收期。

不难发觉，在累计经营性现金流回正后，当期值出现负值或累计值重新转负时，该公式均未能正确估算。 #

辅助行+逻辑复杂，那样的公式仍漏洞百出，回收期估算问题真的那么难解吗？

虽然不然！

#

学会小花分享的某些公式，让你轻松拿捏它。 #

01、求整数回收期的方式 #

这些时侯，我们估算投资回收期时，并不须要像例1这样准确到小数，只需求整数位即可（相当于例1结果向下取整）。 #

这些状况下，可用的公式十分多，以下，小花仅分享其中比较精典的三种办法。

▋例2：法求整数周期

#

假如累计经营性现金流回正后的剩余经营其间都不会变为正数，这么首次回正时间就是投资回收期。

如右图中，累计经营性现金流在第4期回正后，剩余的第5-6期都是负数，没有转为正数，此刻，首次回正时间是第4期，投资回收其间即为4。

这些状况下，估算回收期问题就等同于在表示累计经营性现金流的一组数值中统计正数的个数n，倘若这组数值包括代表投资首期期初的第0期，这么n即为投资回收期，否则n+1为投资回收期。 #

然而，使用函数来统计正数的个数因而估算投资回收期，就顺理成章了。

#

公式如下：

=COUNTIB3:H3,0) 
#
 

公式说明：

函数适于统计满足条件的单元格个数，它的首个参数（条件区域）B3:H3为包含第0期的累计经营性现金流数值组，第二个参数设置为" #

=COUNTIFC3:H3,"0"+1 # 

▋例3：法求整数周期

#

在一组数值中统计正数的个数n，函数只是一把好手，但是其实公式更为简约。

#

公式如下:

=FREQUENCY(B3:H3,0) 
#
 

公式说明： #

函数适于估算数据范围内的单元格数值在指定范围中的分布速率，如何理解？

函数的基础时态：

1、=FREQUENCY(Data_array,Bins_array)
2、=FREQUENCY(统计的区域,?分段点) 
#
 

#

相当于将第一个参数（数据范围）上的所有数值依次在数轴上描点，再按第二个参数（指定范围）的n个数值将数轴分为n+1段，统计每一数轴上的数据点个数。

#

本例中的第二个参数为0，函数以0为分界点，返回B3:H3中大于等于0的数据点个数4，即投资回收期。

#

还要留意的是，假如累计经营性现金流或许出现严苛等于0的状况，都会有点问题，如右图： #

假如数据点包含0,分段点为0的状况下，0会被包含出来。 #

愈发缜密的公式应当使用-0.1^9那样接近于0的正数来作为分界点，公式如下： #

=FREQUENCY(B3:H3,-0.1^9) 
#
 

#

公式说明： #

B3:H3中大于等于-0.1^9的值有4个（包含第0期），小于0的值有3个，估算得到{4;3}，公式返回4。 #

▋例4：MATCH法取整数周期 #

有些时侯，累计经营性现金流在短暂回正后，会再次转为正数，于是在一段时间后重新实现回正。

此刻，使用上述两种方式估算投资回收期才会出错。 #

比如右图中2023动态回收期计算公式2023动态回收期计算公式，累计经营性现金流在第2期首次回正后，在3-4期左转为正数，第5期才完全实现回正，该例中的投资回收期应当为5，但上述两个公式的估算结果都为4，虽然错误。 #

这是由于，这些状况下估算回收期不再等同于求正数的个数，而是求最后一个正数出现的位置序数，我们还要使用MATCH的模糊查找来实现。

公式如下： #

=MATCH(-0.1^9,B3:H3,1) 
#
 

#

公式说明： #

=MATCH（查找目标，查找范围，查找模式)

MATCH的最后一个参数为1，表示模糊查找，公式返回条件区域B3:H3中不小于第1个参数-0.1^9（无限接近于0）的最后一个值所处的位置，B3:H3中满足这个条件的值为-6，它是B3:H3中的第5个值，所以，公式返回5。

#

02、求准确回收期的方式 #

假如我们还要估算准确的投资回收周期，则上述三种方式都将不再适用。 #

这是由于，累计现金流回正的当期，所对应的回收期不再为1，而是取下期累计经营性现金流回正缺口占当期经营性现金流的比值。 #

例4中，累计经营性现金流在第5期实现回正，但第4期累计经营性现金流为-6，经营性净流入只需再实现+6，即可实现回正，而第5期经营性现金流为+140，相当于实现+6仅占用了6/140=0.04期时间，因此准确回收期应当为4.04，而不是5。

#

此刻，我们可以使用来估算准确回收期，公式简略，但理解上去或许有点难度。

B6单元格公式如下：

=LOOKUP(-0.1^9,B3:H3,COLUMN(A:G)-1-B3:H3/C2:I2) # 

公式说明:

查询区域(A:G)-1-B3:H3/C2:I2的设置是本公式的核心。

其中(A:G)-1返回0-6组成的递归，表示当前其间曾经经历的期数，-B3:H3/C2:I2为下期累计经营性现金流回正缺口占当期经营性现金流的比值，只有在现金流回正的前一期，查询区域对应位置的值才等于投资回收期，其除数值均为无效结果。

而的原理与MATCH模糊查找类似，正好才能精确定位累计现金流回正前一期的位置，它依据条件区域B3:H3中不小于第1个参数-0.1^9的最后一个值所处的位置F3，返回查询区域中对应位置的值(E:E)-1-F3/G2，即4.04，以便完成投资回收期的准确估算。 #

以上，就是小花分享的5种估算回收期的方式，包括：

?使用IF+MAX建立辅助行再进行求和；

#

?使用统计大于0的数值个数；

?使用统计数据范围大于等于0的速率； #

?使用MATCH模糊匹配最后一个正数的位置序数；

#

?使用建立内含递归估算准确回收周期。 #

这种方式，非常是MATCH和两种方式，是否解决了你在估算投资回收期方面的困恼呢？