5组Excel公式，助你计算回收期

原标题《工作再忙也要看看这五组Excel公式，保证按时下班！》

在财务投资的计算中，回收期是一个非常重要的参考指标，是指从投资到收回本金的时间。

与NPV和IRR不同，Excel中没有特殊的回收期函数。

所以，为了计算回收期，金融朋友们可谓八仙过海，各显神通！

小华见过的最卑微的方法之一，就是用IF函数建立辅助线，然后通过求和得到回收期。

▋例1:if+辅助线求准确回收期

C4的公式如下:

=如果，1)

公式描述:

使用IF函数进行条件判断如果当期累计经营性现金流小于等于0，则返回1

如果当前累计经营现金流量大于0，则返回前次累计经营现金流量与当前经营现金流量之比的负值与0之间的较大值M。

此时，只有当累计经营现金流在当期第一次返回正值时，m为正，否则MAX函数返回0辅助线相加的结果就是准确的回收期

不难发现，当累计经营现金流转正后，现值为负或累计值再次转负时，公式无法正确计算。

辅助线+逻辑复杂，这个公式还是漏洞百出回收期的计算真的那么难吗

其实不是！

学习小花分享的这些公式，让你轻松应对。

01.寻找整数投资回收期的方法

很多情况下，我们在计算投资回收期时，不需要像例1中的小数点那么精确，只需要整数位即可。

在这种情况下，有许多公式可用下面，小花只分享三个经典方法

▋例2:COUNTIF方法求整数周期

如果累积的经营现金流在剩余的经营期内不会变为负值，那么第一个回报时间就是投资回收期。

如下图所示，第4期累计经营性现金流转正后，其余5—6期均为正，未转正此时第一次回报时间为第4期，投资回收期为4

在这种情况下，计算回收期相当于计算一组表示累计经营现金流的值中负数N的个数如果这组值包括代表第一个投资周期开始的第0个周期，那么N就是回收期，否则n+1就是回收期

所以用COUNTIF函数统计负数的个数，计算投资回收期是符合逻辑的。

公式如下:

=COUNTIB3:H3，0)

公式描述:

COUNTIF函数用于计算符合条件的单元格的数量它的第一个参数B3:H3是0期累计经营现金流量的值组，它的第二个参数设置为lt，0 "，可以统计累计经营现金流量值组中小于0的数，结果就是投资回收期

=COUNTIFC3:H3，"0"+1▋例3:用3求整数周期:频率法

统计一组值中负数的个数n，频数函数也是一手好牌，似乎公式更简洁。

公式如下:

=频率

公式描述:

FREQUENCY函数用于计算指定范围内数据区域中单元格值的分布频率怎么理解呢

频率函数的基本语法:

1，=频率2，=频率(统计区域，分割点)

相当于把第一个参数的所有值依次标绘在数轴上，然后根据第二个参数的n个值(指定范围)把数轴分成n+1段，统计每个数轴上的数据点个数。

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

需要注意的是，如果累计经营现金流可能严格等于0，就会出现一些问题，如下图所示:

如果数据点包含0且分段点为0，则将包含0。

更严谨的公式，应该用类似—0.1 9这样接近0的负数作为分界点。公式如下:

=频率

公式描述:

B3:H3有4个值小于等于—0.1 9，3个值大于0，频率计算结果在4，3，公式返回4。

▋示例4:匹配方法采用整数周期

有时，累积的经营性现金流会在短暂的转正后再次转负，一段时间后又再次转正。

此时，用上述两种方法计算回收期将是错误的。

比如下图，累计经营性现金流在第二期第一次转正后，在第三—四期转正，然后在第五期完全实现在这个例子中，投资回收期应该是5，但上面两个公式的计算结果都是4，这显然是错误的

这是因为，在这种情况下，计算回收期不再等于求负数的个数，而是求最后一个负数出现的位置序数我们需要使用匹配的模糊搜索来实现这一点

公式如下:

=匹配

公式描述:

=匹配

MATCH的最后一个参数是1，表示模糊搜索该公式返回条件区域B3:H3中最后一个值的位置，该位置不大于第一个参数的—0.1 9满足此条件的H3 B3的值是—6，这是H3 B3的第五个值因此，该公式返回5

02.寻找准确投资回收期的方法。

如果我们需要计算一个准确的投资回收期，上述三种方法将不再适用。

这是因为，在累计现金流转正的当期，对应的回收期不再是1，而是取前期累计经营现金流的回报缺口与当期经营现金流的比值。

例4中，第五期收回了累计经营现金流量，但第四期累计经营现金流量为—6，经营净流入只需做到+6，第五期经营现金流量为+140，相当于6/140=0.04期，所以确切的回收期应该是4.04而不是5。

此时，我们可以使用LOOKUP来计算确切的回收期公式很简单，但是可能有点难理解

细胞B6的公式如下:

=查找—1—B3:H3/C2:I2)

公式描述:

查询区域列—1—B3:H3/C2:I2的设置是该公式的核心。

COLUMN —1返回一个0—6的数组，指示当前周期中以前的周期数—B3:H3/C2:I2是前期累计经营现金流量的回收缺口与当期经营现金流量的比率只有在现金流回收的前一期，查询区对应位置的值等于投资回收期，其他值无效

LOOKUP的原理和MATCH fuzzy search类似，刚好可以准确定位累计现金流回到前期的位置根据条件区域B3:H3中最后一个值的位置F3，不大于第一个参数—0.1 9，返回查询区域中—1—F3/G2列的值，即4.04，从而完成回收期的精确计算

以上，小华分享的计算回收期的方法有五种，包括:

用IF+MAX建立辅助线，然后求和，

使用COUNTIF计算小于0的值的个数，

使用统计数据范围小于等于0的频率，

使用MATCH fuzzy匹配最后一个负数的位置序号，

使用LOOKUP构建一个包含数组来计算准确的收集周期。

这些方法，尤其是匹配法和查找法，解决了你计算回收期的麻烦了吗。

郑重声明：此文内容为本网站转载企业宣传资讯，目的在于传播更多信息，与本站立场无关。仅供读者参考，并请自行核实相关内容。