Excel三招,算清投资复利【含智能定投精算】

发布于: 雪球转发:673回复:368喜欢:4080
一、入门篇【^】
“一次性投资”计算年收益、用^即可(英文输入法shift+6)。该符号Excel中表示次方,例如5^3代表5的三次方:5×5×5 = 5^3 = 125
图1:^ 计算式实例

常识:房贷是月复利。例房贷利率6%,利息成本=(1+0.06/12)/^12-1= 6.168%
【^】只是基础,非一次性投入(比如定投)、不定期不定额投入等.. 往下看~~
-------------------------------------------------------------------------------------------------------


二、基础篇【IRR】
定投五年、每月1000(总投入6万),终值12万求年复利。5年总资金翻倍?会用^的朋友简单一拉=2^(1/5)-1=14.87%,对么?定投资金、并非期初一次性投入,其实答案为28.13%!99%投资者,算不清定投年化收益。得搬出IRR公式了~
图2:IRR实例(月投1000元,10期后终值11,000元,求年化)

① B列输入月投金额1000元,C12输入终值11,000元;
② C列数据=0-B列,得出实际现金流-1000元;
③ C13格,输入=IRR(C2:C12),得出(单期)月复利;
④ C14格,输入=(1+C13)^12-1,折算成年复利。

图3:定期不定额的智能定投,怎么办?直接改数即可,中途资金转出也无妨(如第6期取现700元):

--------------------------------------------------------------------------------------------------------


三、进阶篇【XIRR】
IRR参数只有『期』的概念,每一期可以是年、月或天(可用^折算成年化收益)。XIRR比IRR更牛之处在于,可精确定义资金进出日期。看图说话~
图4:照搬图3【IRR】例子,每月具体日期有小变动。再看看差异~


注:XIRR直接得出年化数据,不用再折算。

图5:资金进出,可以是任意日期

顺便说一句,别被某些信用卡分期坑了。刷卡分12期免息、手续费0.67%*12月,年息成本真的只有8%年么?好好用XIRR再算算哟~
--------------------------------------------------------------------------------------------------------

加送球友两个福利~ 定投(定期定额)快捷公式,一般人我不告诉他哟 [俏皮]
图6:【PMT】设定目标终值/年复利/年限/现有资金,求每月需再投资金?


图7:【FV】设定目标月投资金/年复利/年限/现有资金,求到期后终值?




大道至简的定投策略:八年搞定200万【年化28%】 
网页链接
“净值法”管理你的账户(附图)
网页链接
--------------------------------------------------------------------------------------------------------
转载请注明出处  Blog.sina.com/Lagom【Lagom投资】

@今日话题 @明日之星 @ETF拯救世界 @自由老木头 @DAVID自由之路 
@骑行夜幕的统计客 @上兵伐谋zgz @OnlyETF

精彩讨论

Lagom投资2015-10-12 20:25

IRR有其局限性。期数多/且资金变动较大时,可能迭代20次仍未结果。


加个guess预判值即可。
例:=IRR(B1:B100)后,显示【#DIV/0!】。根据经验,预判该周期内月复利为1%(12.7%年化)左右,则公式改为=IRR(B1:B100,1%)

只要预判的不很离谱,都不会再显示【# DIV/0!】、而给出精确复利值!
多guess几个预判值,总会判断对区间的。注:Excel默认值为10%,自定义也可设成负数(亏损)。

天鹰_Eric2015-10-12 00:37

以后就用xirr函数,方便多了,不用每次都凑数字了,谢谢。

郁闷的鞋套-2016-04-13 20:01

我刚赞助了这篇帖子 1 雪球币,也推荐给你。

全部讨论

清醒了2016-01-10 00:35

净值法管理和计算投资复利

hongyan8225772016-01-02 20:31

请教一下,看了您的excel精算,我也操作了一下!作为小白,我自己打算每周记录一下收益率,刚好一年开始了!现在已经有一部分账户投资资金,然后 每个月定期投入不定额的资金,本年度总收益率怎么计算?

相见不如怀念1232015-12-30 21:35

以后有时间了也弄个看看

taungoo2015-12-27 08:40

2015.12.27 感谢 Blog.sina.com/Lagom【Lagom投资】分析,定投Excel分析公式。

缘于稀饭2015-12-25 18:06

慢慢的就好了2015-12-25 13:37

没看先顶!感谢分享。我刚赞助了这篇帖子 6 雪球币,也推荐给你。

lmsd2015-12-19 17:35

定投复利计算法

十八层地狱往上爬2015-12-15 22:27

转发一下,好好学习。

Nemo尼莫2015-12-13 21:04

多谢

Lagom投资2015-12-13 20:59

去年12月31日、至今年12月31日,才是一整年!