关于如何在excel中使用XIRR函数计算现金流的内部收益率,下面这视频做了比较详细的介绍:

视频中提到的现值就是指,将未来的钱贴现,而贴现就是指未来的钱换算成现在是多少钱。

当本金和年利率一定的情况下,我们可以计算出未来的某一年我们可以获得的现金。比如现在投资x元,年利率为r,n年后我们的钱y=x*(1+r)^n。

相反,在条件不变的情况下,如果我们知道n年后我们获得了x元,那么可以计算出现在投入的钱y=x/(1+r)^n

XIRR就是以第一期投入的时间当成现在,将每期的现金流按照负现金流(投入)和正现金流(回报)贴现到现在(第一期现金流的时间),这样我们可以得到一个总负现值和一个总正现值。当我们把所有现金流(包含负,正)贴现到现在,我们就可以换一个角度理解如何计算内部收益率:我们将现在的钱(总负现值)投资了一个资产,而这个资产获得了这笔钱(总正现值),然后这个资产在未来不定期的时间返回给我们收益,而这个收益都是我们投资给这个资产的钱(总负现值)产生的。这个资产将获得的这笔钱(总正现值)分成了若干份,分别投资了一个产品(一份钱,对应于一个产品,对应于未来某次回报),而该份钱的多少就等于所对应产品的回报的贴现。

根据上面的推论,我们很容易得出一个结论,总负现值=总正现值。于是计算内部收益率即为令负现值=正现值,也就是所有期现金流贴现为0,然后解出这个方程即可。

比如每月投资1000,连续投资12个月,最后一个月返还13200,我们假定一年360天,每月30天,那么:

13200/(1+r)^(360/360)=1000/(1+r)^0+1000/(1+r)^(30/360)+1000/(1+r)^(60/360)+ ... + 1000/(1+r)^(330/360)

这与官方公式一致:

62-1..gif

其中:

  • di = 第 i 个或最后一个支付日期。

  • d1 = 第 0 个支付日期。

  • Pi = 第 i 个或最后一个支付金额。

方程式无法直接计算,一般都是通过尝试求值,也就是假设一个收益率,发现不为0,继续调整这个收益率,直到最终算得的结果r精确到一定范围,excel中这个值是0.000001%,其默认的估计值为10%

通过excel计算(按365天每年贴现,每月1号投入),我们知道,这个值为18.8%,远比直觉中的10%大。

最后,如果不考虑复利(XIRR考虑复利)的情况下,我们将每1块钱占用一天,获得的收益算出来为:

r/360,在上述情景中,总共投入12000,每份1000分别占用360,330,300,...,30,于是总收益为: 1000r(360+330+...+30)/360=1200,我们算得:r=18.4615%,与XIRR方式算得基本一致

XIRR的官方文档:https://support.office.com/zh-cn/article/XIRR-%E5%87%BD%E6%95%B0-DE1242EC-6477-445B-B11B-A303AD9ADC9D