动态回收期excel计算公式 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：COUNTIF 法求整数周期如果累计经营性现金流回正后的剩余经营期间都不会变为负数，那么首次回正时间就是投资回收期。
如下图中，累计经营性现金流在第 4 期回正后，剩余的第 5-6 期都是正数，没有转为负数，此时，首次回正时间是第 4 期，投资回收期间即为 4 。

文章插图
文章插图
这种情况下，计算回收期问题就等同于在表示累计经营性现金流的一组数值中统计负数的个数 n ，如果这组数值包括代表投资首期期初的第 0 期，那么 n 即为投资回收期，否则 n+1 为投资回收期。
因此，使用 COUNTIF 函数来统计负数的个数进而计算投资回收期，就顺理成章了。
公式如下：
=COUNTIB3:H3,0)

文章插图
文章插图
公式说明：
COUNTIF 函数用于统计满足条件的单元格个数，它的首个参数（条件区域）B3:H3 为包含第 0 期的累计经营性现金流数值组，第二个参数设置为 "<0"，即可统计累计经营性现金流数值组中小于 0 的个数，其结果就是投资回收期。如果条件区域不包含第 0 期，则公式如下：
=COUNTIFC3:H3,"0"+1▋例 3：FREQUENCY 法求整数周期在一组数值中统计负数的个数 n，FREQUENCY 函数也是一把好手，而且似乎公式更为简洁。
公式如下:
=FREQUENCY(B3:H3,0)

文章插图
文章插图
公式说明：
FREQUENCY 函数用于计算数据范围内的单元格数值在指定范围中的分布频率，怎么理解？
Frequency 函数的基础语法：
1、=FREQUENCY(Data_array,Bins_array)2、=FREQUENCY(统计的区域, 分段点)相当于将第一个参数（数据范围）上的所有数值依次在数轴上描点，再按第二个参数（指定范围）的 n 个数值将数轴分为 n+1 段，统计每一数轴上的数据点个数。

文章插图
文章插图
本例中的第二个参数为 0，FREQUENCY 函数以 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 个，FREQUENCY 计算得到 {4;3} ，公式返回 4 。
▋例 4：MATCH 法取整数周期有些时候，累计经营性现金流在短暂回正后，会重新转为负数，然后在一段时间后再次实现回正。
此时，使用上述两种方法计算投资回收期就会出错。
例如下图中，累计经营性现金流在第 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 。

文章插图
文章插图
此时，我们可以使用 LOOKUP 来计算精确回收期，公式简单，但理解起来可能有点难度。
B6 单元格公式如下：
=LOOKUP(-0.1^9,B3:H3,COLUMN(A:G)-1-B3:H3/C2:I2)

文章插图
文章插图
公式说明:
查询区域 COLUMN (A:G)-1-B3:H3 / C2:I2 的设置是本公式的核心。
其中 COLUMN (A:G)-1 返回 0-6 组成的数组，表示当前期间以前经历的期数，-B3:H3 / C2:I2 为上期累计经营性现金流回正缺口占当期经营性现金流的比值，只有在现金流回正的前一期，查询区域对应位置的值才等于投资回收期，其余数值均为无效结果。

文章插图
文章插图
而 LOOKUP 的原理与 MATCH 模糊查找类似，刚好能够准确定位累计现金流回正前一期的位置，它根据条件区域 B3:H3 中不大于第 1 个参数-0.1^9 的最后一个值所处的位置 F3，返回查询区域中对应位置的值 COLUMN(E:E)-1-F3/G2，即 4.04，从而完成投资回收期的精确计算。
以上，就是小花分享的 5 种计算回收期的方法，包括：
? 使用 IF+MAX 构建辅助行再进行求和；
? 使用 COUNTIF 统计小于 0 的数值个数；
? 使用 FREQUENCY 统计数据范围小于等于 0 的频率；
? 使用 MATCH 模糊匹配最后一个负数的位置序数；
? 使用 LOOKUP 构建内含数组计算精确回收周期。
这些方法，特别是 MATCH 和 LOOKUP 两种方法，是否解决了你在计算投资回收期方面的困恼呢？
【动态回收期excel计算公式 5 组 Excel 公式，助你计算回收期】本文来自微信公众号：秋叶 Excel （ID：excel100），作者：小花编辑：小音、竺兰