投资测算表中常用EXCEL函数

NO.09/22

昨天新芽投拓群里有人@我：“芽总，下次文章能不能写一下测算表常用函数”。

好吧，安排！其实《测算红宝书》中关于测算模型搭建章节有讲到。

这里给大家做几个常见EXCEL函数进行简单的梳理。

IRR函数

用途：返回由数值代表的一组现金流的内部收益率。

语法：IRR(values,guess)

参数：values为数组或单元格的引用，包含用来计算返回的内部收益率的数字。guess为对函数irr计算结果的估计值。

示例：如果A1=-70,000（初期成本费用），A2=12,000（第1年的净收入），A3=15,000（第2年的净收入），A4=18,000（第3年的净收入），A5=21,000（第4年的净收入），A6=26,000（第5年的净收入）

MIRR函数

用途：返回某一期限内现金流的修正内部收益率。

语法：MIRR(values,finance_rate,reinvest_rate)

参数：values为一个数组或对包含数字的单元格的引用（代表着各期的一系列支出及收入，其中必须至少包含一个正值和一个负值，才能计算修正后的内部收益率）。finance_rate为现金流中使用的资金支付的利率。reinvest_rate为将现金流再投资的收益率。

示例：如果A1=-120,000（初期成本费用），A2=39,000（第1年的净收益），A3=30,000（第2年的净收益），A4=21,000（第3年的净收益），A5=37,000（第4年的净收益），A6=46,000（第5年的净收益），A7=10%（120,000贷款额的年利率），A8=12%（再投资收益的年利率）

XIRR函数

用途：返回一组现金流的内部收益率，这些现金流不一定定期发生。若要计算一组定期现金流的内部收益率，可以使用IRR函数。

语法：XIRR(values,dates,guess)

参数：values与dates中的支付时间相对应的一系列现金流。dates是与现金流支付相对应的支付日期表。guess是对函数XIRR计算结果的估计值。

示例：如果A1=-10,000，A2=2,750，A3=4,250，A4=3,250，A5=2,750，B1=2008-1-1，B2=2008-3-1，B3=2008-10-30，B4=2009-2-15，B5=2009-4-1

NPV函数

用途：用贴现率和一系列未来支出（负值）和收益（正值）来计算一项投资的净现值。

语法：NPV(rate,value1,[value2],...)

参数：rate，某一期间的贴现率；Value1, value2, ...在时间上必须具有相等间隔，并且都发生在期末。NPV 使用 value1, value2,... 的顺序来说明现金流的顺序。一定要按正确的顺序输入支出值和收益值。

示例：假设初期投资200,000，而预测未来五年中各年的收入分别为20,000、40,000、50,000、80,000和120,000。假定每年的贴现率是8%（相当于通贷膨胀率或竞争投资的利率）。假设该电器店的营业到第六年时，要重新装修门面，估计要付出40,000。则六年后电器经销店投资的净现值为：

HLOOKUP、VLOOKUP函数

HLOOKUP函数和VLOOKUP函数其实是一类函数，具体区别在于VLOOKUP函数是纵向查找函数，HLOOKUP函数是横向查找函数。这里只讲VLOOKUP。

这里给大家举个例子：比如，想要统计独栋别墅、保障房、公寓这四个业态的可售货值。

常规的做法是怎样的呢？从规划指标表中去找出对应业态的可售面积，再从销售预测表中找出对应业态的售价，再做乘积求和算出对应部分业态的总货值。这种人工方法在遇到项目业态繁多的时候，费时费力，且极容易出错。

为了教学方便，新芽这里简化一下案例，如下：

语法：VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

比如：我们查找独栋别墅的面积

公式：=VLOOKUP(A14,A2:C10,2,FALSE)

第一步：查找目标——独栋别墅；

第二步：查找范围——A2：C10；

第三步：反值列数——我们是从B列中找对应的值；

第四步：是否精准查找——精准查找为1/TRUE，模糊查找为0/FALSE。