Excel公式技巧61:插值公式技术

excelperfect

一个项目从2013年开始投资,相关数据如下图1所示,求该项目的投资回收期?也就是说,累计现金流等于0的那个时间点。

图1

从工作表中可以看出,该项目的投资回收期在2015年至2016年之间,且更接近于2016年。

可以以时间为横坐标,现金流数据为纵坐标,绘制图表后,测量横坐标上的时间来近似求出;也可以使用三角形等比公式来精确求得。这里使用公式来计算。

在单元格D8中输入公式:

=(FORECAST(0,OFFSET(C4,,MATCH(0,C6:G6)-1,1,2),OFFSET(C6,,MATCH(0,C6:G6)-1,1,2))-C4)/365

得到该项目投资回收期。

公式的关键在FORECAST函数,包含有3个参数。其中:

1.参数x:0

2.参数known_y’s:OFFSET(C4,,MATCH(0,C6:G6)-1,1,2)

3.参数known_x’s:OFFSET(C6,,MATCH(0,C6:G6)-1,1,2)

FORECAST函数通过插值找到未知的x值。

在公式中:

MATCH(0,C6:G6)

在单元格区域C6:G6中查找值0,返回-9所在的位置3,这样:

OFFSET(C4,,MATCH(0,C6:G6)-1,1,2)

转换为:

OFFSET(C4,,2,1,2)

得到单元格区域:E4:F4

同理,公式中的:

OFFSET(C6,,MATCH(0,C6:G6)-1,1,2)

得到单元格区域E6:F6

因此,公式中的FORECAST函数转换为:

=FORECAST(0, E4:F4, E6:F6)

返回2015年10月13日,即收支平衡的日期。

再通过减去开始日期并除以一年的天数365天来计算年数,即公式:

=(FORECAST(0, E4:F4, E6:F6)-C4)/365

得到结果:

2.78

注:这是在Chandoo.org论坛上看到的一个贴子,特整理于此,供学习参考。

欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。

欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料。

(0)

相关推荐