如何在 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