内部收益率(Internal Rate of Return,IRR)也叫 Economic Rate of Return,在资本预算中用来对投资利润进行评估和比较的,它也被称为贴现现金流的收益率(Discounted Cash Flow Rate Of Return,DCFROR)。在储蓄和贷款中,它也被称为有效利率(实际利率,Effective Rate of Return)。内部收益率里的“内部”一词表明这种收益率的计算式不考虑经济环境因素,如利率的通胀。

计算投资的内部收益率就是在计算该投资的年化复合有效收益率。假设一项投资的现金流时间序列是(n,C_n),其中n=0,1,...,N,那么就是要计算出这样的内部收益率r满如如下等式:

\mathrm{NPV} = \sum_{n=0}^{N}\frac{C_n}{(1+r)^n} = 0

其中的 NPV 代表净现值(Net Present Value)。

例子

假设一项投资的现金流序列如下:

(n) 现金流(C_n)
0 -123400
1 36200
2 54800
3 48100

那么 IRR 的值r由下式给出:

\mathrm{NPV} = -123400 + \frac{36200}{1+r} + \frac{54800}{(1+r)^2} + \frac{48100}{(1+r)^3} = 0

可以计算得到此处r=5.96\%.

计算

用 Excel 计算

在 Excel 中用 IRR() 函数可以直接计算,如下图所示
IRR-Excel计算
IRR(values, [guess]) 函数第二个参数一般不用给出,当原函数无法给出正确值时再给 IRR 函数配上第二个参数,即内部收益率的估计值。

在线网站计算

可以在如 DataDynamica 类型的在线网站直接进行计算。

datadynamica_IRR

这个网站还指出了用 Excel 计算 IRR 时可能会得到错误值,Are you using a false IRR generated from Excel or handheld financial calculator.

今天用Excel时碰到一个问题就是要求类似于SQL里的这么一个语句的功能:

 SELECT SUM(ISNULL(Column01,Column02)) FROM Table01;

Excel中的数据如下图,目的就是当B列值为空时用A列对应行上的值替换,这样求得前5行数的和,也即下图中红框内数的和。

excel-fun_sumproduct01

这种多条件求和可以用SUMPRODUCT函数。

先来看看SUMPRODUCT的基础语法:

SUMPRODUCT(array1, [array2], [array3], ...)

其中填入的各array维度数必须一致,该函数的结果就是将各array对应下标的数相乘最后求和,即如下:

\sum_{j=1}^n{\prod_{i=1}^k {A_i[j]}}


这是SUMPRODUCT的基础用法,它还可以多条件求和

SUMPRODUCT((条件A1)*(条件A2)*(条件A3)*...*(条件An)*(数据列1),(条件B1)*(条件B2)*(条件B3)*...*(条件Bn)*(数据列2),...)

这样在数据列前加上条件限制就只会选出满足条件的数据来计算,但是要注意,不满足条件的位置会变成0。

上面的公式可以退化成这样:

SUMPRODUCT((条件A1)*(条件A2)*(条件A3)*...*(条件An))

这样就变成了统计满足条件的数据的个数了。

下面这个例子就是统计年龄为11岁的男生的个数:

excel-fun_sumproduct02

最后回到我们原来的问题上来,用下面这个公式就可以搞定了:

SUMPRODUCT((B1:B5="")*(A1:A5))+SUM(B1:B5)

excel-fun_sumproduct03

推荐阅读:用EXCEL的sumproduct函数做条件统计

SUMIF、COUNTIF和SUMPRODUCT函数在统计中的应用