利用 Excel 进行理财计算

2017-06-09 16:21:31 已有 人阅读 信息源:简书出品方:清姝
字体大小:
欢迎在《租赁视界》阅读该文。此文出处为租赁视界 http://www.tjrzzl.com/

编辑excel可是理财投资好助手,巧用其中的计算函数,能帮我们解决很多问题。比如是每月定投还是每周定投好啦,股票实际收益率是多少啦,别争论数据说话就好了。ed0租赁视界

终值函数FV公式ed0租赁视界

主要用来计算复利计算,适用于已知本金、时间和利息。ed0租赁视界

求最终受益的公式:ed0租赁视界

FV( rate,nper,pv,pmt,type)ed0租赁视界
ed0租赁视界

终值(本利和)=(利率,支付总期数,初值,每期投入,模式)ed0租赁视界

利率为单期利率,期数为投资总期数,初始资金,模式1代表每期一开始支付,0代表期i末支付。ed0租赁视界

举个例子

我们假设基金定投的收益率12%,每年投资1.2万每年投资10年,是每个月投1000投资120个月和每周投240投资500周(每年投资50周国庆和过年跳过不投)受益究竟差多少。 调出excel中FV函数(清姝使用的是WPS表格,和微软的excel基本相同)ed0租赁视界

1.jpg

月投每期利率为12%/12=1%;期数 120,初值为0 ,每期投入12000/10=1000(注意金额要填写负的),模式填1或者不填都可以。ed0租赁视界

2.jpg

周投每期利率为12%/50=0.24%,期数500,初值为0,每期投入12000/500=240,模式1或者不填都可以ed0租赁视界

3.jpg

实际上周投和月投的基金定投区别不大,如果资金来源比较单一只有工资,只设置工资日后一日为定投日就好了,没必要设定周投ed0租赁视界

内部收益率公式IRRed0租赁视界

IRR函数返回由数值代表的一组现金流的内部收益率。但是要注意,IRR只能计算固定时间间隔的收益率。ed0租赁视界

IRR(values,guess)ed0租赁视界

收益率=(现金流,预估值)ed0租赁视界

现金流为一组数据,必须有正有负。ed0租赁视界

结果估计这个是用来矫正数据的就是精确度空着就行 IRR虽然能够计算出收益率,但是有个缺点就是时间间隔必须相同,以年为单位、月为单位、周为单位都可以,但是要记得乘以对应的周期数才是年化收益率。ed0租赁视界

举个例子ed0租赁视界

我们在1月1日用信用卡刷卡1.2万,然后选择分12期还款方式,信用卡的手续费为0.6%,总共手续费为12000*0.6%*12=846元,每期还款12000/12+846/12=1072。将刷卡12000和每期还款1072组成一列用IRR计算每期利率,实际信用卡还款利率为每月利率*12最终13.03%是不是远高于想象的0.6%*12的7.2%.ed0租赁视界

4.jpg
 

再举一个例子

小a,男,25岁。购买30万保额人寿+重疾保险投保30年。ed0租赁视界

消费型保险缴费大约在2000元左右,到期不返还ed0租赁视界

储蓄型保险在8000元左右,到期返还全部保费加10%的收益及264000元ed0租赁视界

实际上的储蓄型保险就是每年缴纳的8000元减去2000元的保障部分保费,用6000元投资最终收益为264000元,那么这三十年的收益率是多少呢?ed0租赁视界

5.jpg

买储蓄保险只有2.37%的收益率,这结果清姝只能呵呵了ed0租赁视界

不连续数据收益率公式XIRRed0租赁视界

IRR计算定投或者还信用卡还好,但是我们在投资的时候,单笔不是固定时间的投资就不能用了,这个时候我们就要清楚更加高级一点的公式XIRR,比IRR多引入了一行时间,就可以计算任意时间的收益率了。ed0租赁视界

XIRR(values, dates, guess)ed0租赁视界

收益率=(现金流,时间,预估值)ed0租赁视界

现金流依旧是要求有正有负,时间是对应的时间,结果估计空着就行ed0租赁视界

举个例子 建设我们在2013年3月1日买入了一只股票花了20000,在2013年11月12日卖了10000,在14年5月7日又补仓了20000,在2015年6月12日卖出63215元,求这只股票的实际收益率为多少。(数据都是清姝瞎编的,你们注意看计算方法)ed0租赁视界

将现金流和时间对应排列成2列,调用XIRR函数,选择对应的单元格组拖拉出来就可以了ed0租赁视界

6.jpg

有50%的年化收益率呢,相当不错ed0租赁视界

再举一个例子ed0租赁视界

在投资某平台P2P的时候,45天标为6.8%,使用满10000减100元券实际充值9900元,2016年9.1日充值投标,9.3日审核通过计算日期,10.18资金及利息10083.83元(10000+10000*0.68%*45/365)并且申请提现,10.19资金回到银行卡,实际上的投标收益率为多少?ed0租赁视界

我们实际上付款是9月1日付款9900元,10.19收到10083.83元,使用XIRR函数选对应的时间和金额ed0租赁视界

7.jpg

感觉还可以啦,在平台安全的情况下,新注册平台都会有一些新手券也不失一个提升收益率的好办法,就是考察平台太消耗时间成本,大家算好了时间和投资收益的平衡点。ed0租赁视界

现金再投资收益率MIRRed0租赁视界

XIRR基本可以满足我们绝大部分收益的计算了,不过如果我们有两款产品同时使用,需要算出综合收益率,就只能用MIRR了,不过MIRR有个缺点,和IRR一样只能计算固定间隔时间的收益率。ed0租赁视界

MIRR(values, finance_rate, reinvest_rateed0租赁视界

收益率=(现金流,投资率,再投资利率)ed0租赁视界

举个例子ed0租赁视界

两款产品,一款等额本息年化收益率8.4%等额本息产品,投资十万每月收到8716元,每月收到的钱再投资利息3.6%的活期理财产品;另一款年化收益率7%的到期一次性到期还本,哪个更划算。 我们使用MIRR公式,注意利率要/12,得出每期利率要*12ed0租赁视界

8.jpg

是不是结果有点意外,还不如7%的收益率高?因为等额本息的方式本金在减少,看似利率很高实际没有拿到这么多的钱。ed0租赁视界

欢迎在《租赁视界》阅读该文。此文出处为租赁视界 http://www.tjrzzl.com/

重要声明:所注明”信源:租赁视界“的图文音视频资料版权均属所有,转载时须注明“来源:租赁视界”。 除非特别注明,本站所有文章均不代表本站观点。报道中出现的商标属于其合法持有人。 本网所载文章系出于传递更多信息之目的,不希望被转载的媒体或个人可与我们联系删除。请遵守理性,宽容,换位思考的原则。