如何在 Power BI 中计算投资回收期

自《业财一体化演示平台》案例发布以来，大家对于其中如何实现的细节产生了很多好奇，特别是如何在 Power BI 计算投资回收期，本文将针对投资回收期做具体实现分享。

场景描述

投资回收期是投资决策分析中一个重要的参考指标，它是指投资资金刚好收回所需的投资年限。投资回收期计算一般有两种：一是静态投资回收期：指在不考虑资金时间价值的条件下，投资项目的净收益回收其全部投资所需要的时间；二是动态投资回收期：即在考虑资金时间价值的条件下，投资项目的净收益回收其全部投资所需要的时间。下面以静态投资回收期为例，来说明在 Power BI 中如何计算投资回收期。

具体实现分享

一般而言，在计算投资回收期前，我们需要有一个未来投资期间现金流量的预测表，如下：

投资期限（月）	当期现金流出	当期现金流入	当期现金净流量	累计现金净流量
0	2000	0	-2,000	-2,000
1	400	200	-200	-2,200
2	400	220	-180	-2,380
3	400	242	-158	-2,538
4	400	266	-134	-2,672
5	400	293	-107	-2,779
6	400	322	-78	-2,857
7	400	354	-46	-2,903
8	400	390	-10	-2,913
9	400	429	29	-2,884
10	400	472	72	-2,813
11	400	519	119	-2,694
12	400	571	171	-2,523
13	400	628	228	-2,295
14	400	690	290	-2,005
15	400	759	359	-1,646
16	400	835	435	-1,210
17	400	919	519	-691
18	400	1,011	611	-80
19	400	1,112	712	632
20	400	1,223	823	1,455
21	400	1,345	945	2,400
22	400	1,480	1,080	3,481
.........	.........	.........	.........	.........

从该表就可以看出投资回收期为 18+80/712=18.11 (月)。因此，如果用 DAX 表达式计算投资回收期也需要：

1）构建自定义投资日历表（投资时间轴）

2）计算当期现金净流量

3）计算累计现金净流量

完成以上三步后，接下来最关键的是使用 DAX 表达式计算出投资回收期。

下面分别说明：

1）构建自定义投资日历表（投资时间轴）

本步比较简单，也可以直接在 Excel 中完成，结构如下：

2）计算当期现金净流量

当期现金净流量可以使用当期现金流入 - 当期现金流出

这里先假设当期现金流出为恒定值，当期现金流入保持一个恒定的增长率。则有：

代码语言：javascript

复制

当期现金流出 = 
var InitialInvestment=2000 //初始投资额
var perPayment=400 //每期固定支出
return
 IF( VALUES('自定义投资日历表'[月序列])=0,InitialInvestment,perPayment)

代码语言：javascript

复制

当期现金流入 = 
var InitialIncome = 200 // 初始现金收入（第1期）
var GrowthRate= 0.1 // 后期增长率10%
return
 IF( 
     VALUES('自定义投资日历表'[月序列])=0,0,
     InitialIncome*POWER(1+GrowthRate,VALUES('自定义投资日历表'[月序列])-1)
 )

代码语言：javascript

复制

当期现金净流量 = 
SUMX(VALUES('自定义投资日历表'[月序列]),
    [当期现金流入]-[当期现金流出]
)

3）计算累计现金净流量

代码语言：javascript

复制

累计现金净流量 = 
CALCULATE(
    [当期现金净流量],
    FILTER(ALL('自定义投资日历表'),'自定义投资日历表'[月序列]<=MAX('自定义投资日历表'[月序列]))
)

最后基于以上三个度量值计算投资回收期：

代码语言：javascript

复制

投资回收期 = 
VAR cashTable= //先构建一个项目投资的现金流量表
ADDCOLUMNS(
    VALUES('自定义投资日历表'[月序列]),
    "Date",
    EDATE(DATE(1900,1,31),'自定义投资日历表'[月序列]-1),
    "Cashflow",
    [当期现金净流量],//各期现金净流量
    "ACCcashflow",//各期累计现金净流量
    CALCULATE(
        [累计现金净流量],
        FILTER(
            ALL('自定义投资日历表'),
            '自定义投资日历表'[月序列]<=EARLIER('自定义投资日历表'[月序列])
        )
    )
)
VAR FILTERtable=//筛选出累计现金净流量大于或等于0的部分
TOPN(
    1,
    FILTER(cashTable,[ACCcashflow]>=0),
    [ACCcashflow],
    ASC
)
RETURN
IF(
    COUNTROWS(FILTERtable)=0,"未回收",//没有满足条件的筛选结果，即没有收回投资
    SUMX(
        FILTERtable,
        '自定义投资日历表'[月序列]-1+DIVIDE([cashflow]-[ACCcashflow],[cashflow])
    ) //按插值算法计算回收期
)

用卡片图显示出来，可以看出结果与手工计算结果一致：

当然，实际投资分析中，每期的现金流量可能比较复杂，可以将增长率、初始投资额可以作为输入参数供用户自由调整；还有新投资的项目与公司历史的项目类似，是否可以基于历史项目的现金流量预测新项目的现金流量？等等，这些因素，在作品《业财一体化演示平台》中均有考虑。

该作品案例可直接参考：

excel120.com/go/case/zzfinance