或许有些好学的童鞋会有疑问。用Excel公式计算基金定投年化收益率时，为什么有些情况只能用xirr，有些情况只能用XIRR？你看百度的回答，还是一知半解。三个Excel公式之间有什么联系？如何学习如何在生活和学习中灵活运用？......提出了一系列的问题，今天我通过具体的例子给大家讲解一下。
01 | xirr、irr和RETE公式的语法
先学习三个Excel公式的语法，再讲解例题。
XIRR(值，日期，[猜测])；Values是一系列按日期支付的现金流，Dates是现金流支付对应的支付日期计划，Guess是函数XIRR计算结果的估计值，可填可不填。
IRR(值，[猜测])；Values是一系列应付计划按日期的现金流，Guess是函数XIRR的估计值，可以填也可以不填。XIRR与IRR相比，IRR不需要输入现金流支付的日期，但在分析具体问题时，我们通常会把现金流支付的日期列在一起，以便于理解。
RATE(Nper，Pmt，Pv，Fv，Type，Guess)；Nper是总投资期或贷款期，即投资或贷款的总支付期数。期数可以根据实际情况以年、月、周为单位，但输出结果是该期对应的利率。比如期数以月为单位，计算的利率就是月利率。Pmt是每期应收取(或支付)的金额，在整个投资期或支付期内不可更改。金额是固定的，分为正、负两种，正费负支。Pv是一系列未来付款的总现值(现值)，FV是未来值或最后一次付款后的可用现金余额， FV=0。Type用于指定付款时间在期初还是期末，Type=1，付款在期初；Type=0，支付在期末。
02 |计算定投年利率
这里通过同一个例子，用三个Excel公式来深刻理解它们之间的区别和联系。
例:2019年，小明每月月初以100元定投一只基金。在12次定投结束时，小明从账户中取出1440，计算小明基金定投的年化收益率。
在这个例子中，简单的利率公式(1440-1200)/1200不能用来计算小明基金定投的年化收益率。因为每笔投资的时间点不一样，第一期投入的100块钱总共投资12个月，而最后一期投入的100块钱有一个月的投资期，所以资金的时间成本不一样。[/S2]
03 | XIRR
用XIRR公式计算，日期需要具体到某个日期，日期输入需要使用“DATE”函数。比如2019年1月1日的，用date函数输入“= date (2019，1，1)”的格式，金额的初始月支出为1000。年底收到1440元为“+1440”，用XIRR计算的利率就是年利率，不需要再换算。本例中，小明的基金投资年化收益率为38.92%。
04 |内部收益率
用IRR公式计算，需要签署现金的金额来表示现金的流入或流出。每月定投100元，意味着每月支出100元(-100)，年末总收1440元(+1440)。虽然IRR公式不需要输入时间序列，但是为了更好的理解每个时间点对应的现金流量，通常会列出时间轴。
IRR的时间序列与XIRR完全不同。一年中的十二个月被列为时间轴，0表示第一个月的开始，1表示第一个月的结束，12表示第十二个月的结束。IRR计算的利率是指这一期的利率需要进一步换算成年利率。在这个例子中，时间间隔是月，所以计算出来的利率是月利率，需要用有效年利率公式换算，年利率由“(1+月利率)12-1”得到。
在这个例子中，IRR公式计算出小明基金定投的月利率为2.77%，年利率为38.87%。接近XIRR公式的结果，存在一定误差。
XIRR按实际天数计算。IRR是将一年分成12等份。从1月到12月，每个月的实际天数不同，所以计算结果会略有偏差。
05 |利率
按费率公式计算，每月初100元的定投期限为一年，即已定投12个月，Nper为12，每期Pmt为-100；定投前没有持有基金，所以现值(Pv)为0。2019年底赎回基金拿到1440元，最终价值(Fv)为+1440。定投时间为每月初，Type=1。RATE IRR和RATE IRR计算出来的利率都是周期区间的利率，所以还需要进一步变换有效年利率公式。费率时间序列是现金流对应的时间点，主要列出投资期内的投资次数。在本例中，IRR公式计算出小明基金定投的月利率为2.77%，年利率为38.87%，与IRR公式的结果完全一致。
06 |摘要
从上图可以看出，两个相似但不完全相同的答案是用xirr、irr、RATE三个公式计算出来的。xirr计算的年化利率为38.92%，irr和rate计算的年利率为38.87%，两者年利率相差0.05%，偏差较小
XIRR按实际天数计算。IRR是将一年分成12等份。从1月到12月，每个月的实际天数不同，所以计算结果会略有偏差。
IRR和RETA时间序列是用时间轴表示的，没有准确考虑每个月的具体日子。比较适合在没有现金流的特定日子计算年化利率。
07 |适用范围
1。xirr公式适用范围广，必要条件是现金流发生的具体日期，年利率计算准确
与IRR和利率公式相比，XIRR公式的限制更少，计算出的利率是三个公式中最准确的。适用于时间间隔相同或不同，每期投资金额固定或不固定，但必须知道固定投资日期的具体日期(xx，xx，xx，XX，XX，XX，XX)的情况。如果不知道具体日期，就不能用这个公式计算年利率。XIRR公式计算的结果是年利率，不需要进一步换算。只要知道确切的日期和对应的现金流，就可以计算出这笔投资的年利率，包括定期定投、定期定投、不定期定投和不定期定投。
2。IRR公式适用于时间间隔必须相同，每期投资额固定或不固定的情况
IRR公式适用于投资金额固定或不固定，时间间隔相同如每周定投，每月定投，(定期定投，定期定投)。如果固定投资时间间隔不固定或不规则，则不能使用IRR公式。【/s2/】为了更好的理解题目的意思，便于整理现金流，往往会列出时间轴对应的现金流。以期末定投为例，因为该时点发生了两笔现金流支出或收入，所以需要对该时点的现金流进行梳理。IRR公式的结果是这个时间区间的利率需要用有效年利率公式换算成年利率。
3。费率公式的适用时间间隔相同，每期投资额固定
费率公式中有很多限制，只适用于同一时间区间，比如周定投、月定投，还要求投资金额固定不变，即(定期定额定投)。类型需要根据实际情况选择，在定投开始前不持有任何份额，现值(PV=0)，最后一份的价值为终值。【/S2/】利率公式的结果是，这个时间区间的利率需要用有效年利率公式换算成年利率。无论初始还是最终定投，IRR公式和费率公式计算的结果都是一致的。
利率函数可以灵活变化知道四个条件，可以改成PMT函数计算固定利率下贷款的等额分期金额；NPER函数根据固定利率和等额分期付款计算投资或贷款的期数；FV函数返回基于固定利率和等额分期付款的投资的未来值；PV函数[/s2/]返回一项投资未来一系列还款的当前总值(或一次性还款的现值)。
作者:定投白手起家
链接:https://xueqiu.com/1426546532/137108730
来源:雪球
版权归作者所有。商业转载请联系作者授权，非商业转载请注明出处。
