网络基金市场中心

XIRR理财收益界的万能计算公式--妈妈再也不用担心我算不清楚帐了

清姝小栈2018-10-17 09:05:05

公众号:清姝小栈(qingshuxiaozhan)

联系人:清姝(qingshuxiaozhan1)

以前清姝写过excel的计算公式,不过可能那篇文章写的有些枯燥,或者有很多新粉丝没看过那篇文章,依旧很多人问清姝怎么算收益,某笔投资是否划算之类的问题,今天清姝挑出来最重要的一个公式,多举例子帮大家梳理怎么用excel算收益。


更多公式戳这里

巧用excel这四个公式,理财计算不求人

一、公式简介


今天介绍的是基本是收益计算的万用公式XIRR,他可以计算单一品种投资的收益,基本是有时间有金额他就能搞定,是咱们进行理财计算中用的最普遍的计算公式。


1语法

XIRR(values,dates,guess)

翻译成中文是

XIRR(现金流,日期流,预估值)


Values :   与 dates 中的支付时间相对应的一系列现金流。首次支付是可选的,并与投资开始时的成本或支付有关。如果第一个值是成本或支付,则它必须是负值。所有后续支付都基于 365 天/年贴现。系列中必须包含至少一个正值和一个负值。

Dates :   与现金流支付相对应的支付日期表。第一个支付日期代表支付表的开始。其他日期应迟于该日期,但可按任何顺序排列。

输入时间的时候注意最好用“-”或者“/”来连接年月入,不要用汉字,不然容易出错。

Guess    对函数 XIRR 计算结果的估计值,一般情况下咱们空着就可以了


2调用方法


先点击菜单栏公式--插入公式

在公式调用中属于XIRR,点击搜索出来的XIRR公式点击转到或者确定

在调用出来的公式对话框中,选择excel表格中对应的日期或者现金流的列,点击确定计算结果就会显示出来


如果操作熟练,在excel上直接输入“=XIRR()“里面填现金流和日期流用“,”逗号隔开也可以。


二、实用案例


只说公式大概没有感觉,清姝举几个例子大家感受一下怎么用,最好在电脑前操作一遍就更清楚了。


1分期实际收益率


办过信用卡经常会收到银行分期电话,基本上清姝都会拒绝,因为基本没有划算的。

假设我们账单10000元,看一眼清姝最新的账单如果分期,手续费如下

为了简单好算分成3期,那么本金每期需要还3333.33,手续费每期74元,则每期需要还3407.33。

假设7月1日分期,第一行第一列写上2017-7-1,第二列写上10000,分期相当于我们得到一万元

第二行第一列写上2017-8-1,第二列写上-3407.33,代表我们需要为了分期支付3407.33

第二行第一列写上2017-9-1,第二列写上-3407.33,代表我们需要为了分期支付3407.33

第二行第一列写上2017-10-1,第二列写上-3407.33,代表我们需要为了分期支付3407.33


都写好后,我们调出神器XIRR,点选日期流和现金流,点击确定就会出计算结果。



相当于14%的利息了,你还敢轻易分期么?


2不等额分期


来咱们再强化一下分期计算方法

粉丝7月10日发的分期截图让清姝算利息

依旧是第一列写日期,第二类写金额这张图很简单,按照金额抄就好了,调出XIRR简单清楚,相当于10.2%的利息


3回测场外基金定投收益


清姝经常会计算基金的收益率之类的,可能很多人会好奇怎么算出来的,其实就是用天天基金定投计算器算的(

http://data.eastmoney.com/money/calc/FundCalcDTSY.html



比如我们算算今年定投易方达上证50(110003)半年的收益率


上面的11.47%是简单收益率,实际上算是时间的概念的真正年化收益率需要用XIRR来计算。

把下面的明细复制粘贴到excel中,用分列的方法把星期从日期中分出去,将1000替换成-1000(代表我们实际支出了1000),最后一列填入计算日期2017-7-1,和当时的投资资金情况

调出XIRR计算出实际收益率为45.5%,是不是很惊人


4计算场内基金定投收益率


场外的基金还算好算,用IRR就可以算,不用动用XIRR,但是场内基金每次投入金额都不太一样,真的需要XIRR来计算了

这是清姝投资恒生ETF(159920)的真实数据,清姝每次交易都会把日期金额进行记录,这样方便年底核算收益情况

目前的收益率,也是调出XIRR来计算

只要坚持记录,你还怕年底算不清楚自己的收益率究竟多少么?


5计算p2p的真实收益率


比如清姝之前推荐的团贷网,7月搞的活动

和清姝六月推送的京东卡力度差不多,投资一万元3个月新手标12%,可以使用18元的新手标红包,第二个月5个工作日可以得到100元京东卡,3个月后收到本息10300,而且团贷网是有提现费的,坚持签到3个月保持超级会员提现费千分之零点五也就是5.15元,而且前一个月保持一万元投资金额赠送3元提现券,实际提现费2.15元(实际上如果坚持签到还有很多团币抵提现金额,很可能不需要提现费)


是不是好复杂?团贷网套路太深了。实际收益率为多少呢?

假设7月28日投资一万元,第一列写2017-7-28,金额-9982(用了18元红包)

8月7日第5个工作日收到100元京东卡短信,第二列写2017-8-7和100

10月28日,咱们看到10300本息到账,申请提现,10月29日收到10297.85,好第三列写上2017-10-29,10297.85


来看一下XIRR收益率


对于一个排名前20的网贷平台,收益率17.54%收益率还是很不错的。

当然必须要走清姝的链接才有京东卡拿,而且一定要看好额度和时间要求,投资一万元3个月才有100京东卡。这个月好在投资5000元一个月有50京东卡,上个月可是只有10元现金红包而已。

说到这里了,不扔个二维码好像对不起自己,记得扫描二维码或者阅读原文走清姝的链接首次投资才能拿京东卡哦。

什么?上个月投过了。换成老公/老婆/老爸/老妈的手机号和身份证重新投啊,新手标三月个标很充足,不像上个月清姝无奈选的资产标,这可是12%和9.6%的区别啊


6返还型保险有多少收益?


平安赢越一生:

0岁宝宝为例:每年投入·10万元,投入3年,共入30万。

【小学教育金】

一年级: 5000二年级: 5000

三年级: 5000四年级: 5000

五年级: 5000六年级: 5000

【初中教育金】

一年级: 5000二年级: 5000

三年级: 5000

【高中教育金】

一年级: 15000二年级: 15000

三年级: 15000

【大学教育金】

一年级: 30000二年级: 30000

三年级: 30000四年级: 30000

已取21万

【硕士教育金】

一年级:50000二年级:50000

已取31万

【结婚】

再领10万元

孩子无论你多大,父母都爱你。

【退休金】

60岁开始,每月领取6000元,1年领12次,

可以领到85岁


【祝寿金】

85岁时,双倍领回保费60万元。


看起来很美好,实际呢?


首先我们假设16年9月1日宝宝出生开始购买这款保险,每年缴费10万元缴费3年。到2023年9月1日宝宝上小学开始拿到保险返还金。假设宝宝上了研究生并且研究生一毕业就结婚了,(这是收益率最高的理想状态)我们按照保险内容将时间、情况和现金变化情况分别在列在三列中。



大学毕业的时候还是负收益,研究生二年级拿到钱才刚刚转正,结婚拿到礼金只有1.59%的收益率。理财二十几年年化收益率不到1.6%,这还是账面利率,减去通货膨胀实际利率肯定是负的了,看来这款保险抗通胀肯定是没法指望了。



哦,对了,无灾无难的活到60岁退休还可以领保险金呢,但实际上效果依旧很让人失望,在60岁开始领取的时候,收益率依旧只有1.6%。好消息是随着年龄不断增长,收益率还是在增加的,如果能坚持活到85岁拿到60万祝寿金收益率能够达到4.2%。


买这一类保险一点要让孩子无灾无难的85岁才能抵得上国债收益。


基本上清姝想到的用excel计算收益就这么几类,还有什么不会算的欢迎留言