作者：小5君还是和上一期一样，以做某项基金数据处理相关的操作为载体学习如何使用Excel实用主义出发，上一期是基金筛选，这一期就来个很多人都可能需要的定投模板吧为了顺带说明一些别的问题，我以天弘中证计算机指数A（001629）作为示例数据。
定投需要的基础数据本质上只需要两列：一列是日期，一列是单位净值，或者确切的说应该是归一化单位净值简单解释一下这个归一化单位净值其实如果一个基金从来没有分过红的话，单位净值和归一化净值应该是一致的但是如果一个基金中途分过红的话（我默认定投的时候选择的都是红利再投资），大家可以想一下用单位净值会有什么问题？曾经有过分红经历的小伙伴可能知道分红当天单位净值看上去会“暴跌”，持有的份额看上去也会亏很多钱，但实际上这完全是虚惊一场，系统在第二天会以增加份额的形式补上那个暴跌。
份额是补上了，但是单位净值这边会出现了一个大缺口（因为分红而表面上大跌），这个时候就需要归一化处理了操作很简单，就是利用当天的实际涨跌幅并基于分红上一日的单位净值把分红当日的理论单位净值算出来就行了请注意这个算出来的理论单位净值不是累计净值，我把这个叫做归一化净值。
为了不喧宾夺主，这个话题我不多讲了，其实之前说到的第二列数据不管归不归一的都可以通过每日涨跌幅算出来，那要不我先假设你们都看懂了我假设嗖的一声就有了天弘中证计算机指数A（001629）的数据，这个指数基金没有分过红，所以单位净值可以直接拿来用。
定投的基本概念这种我就不解释了，定投模板里面大致要包含的要素我列个基本的：定投金额是指每月固定某日的单笔定投金额定投总金额是指累计的定投总金额定投份额是指每月固定某日的单笔定投金额购买到的定投基金份额定投总份额是指累计的定投基金总份额。
总市值是指持有份额的市值绝对收益率是指持有总市值基于投入金额的收益率年化收益率是基于绝对收益率换算过去的年化收益率我知道这里肯定又有人出来说或者问内部收益率IRR或者XIRR的事了，不用问了，今天打死我也不会说，以后万一讲Private Equity的时候再说吧。
累计定投期数就是累计定投了多少期单位持有成本就是定投的份额的成本，主要用来和净值做个对比参考着看看我们一个一个来具体用Excel算第一个定投金额，以每月第一个交易日定投一期1000元为例这个其实可以通过一个if条件公式简单的完成，不过这里需要用到第一列的日期，简单来说就是如果这一行的日期月份减去上一行的日期月份等于1或者-11，那么这一天就是这个月的第一个交易日，投1000，否则就赋空值，写成Excel公式大概长成这样：。
其中的Month(Date)函数返回的是一个日期的月份值，OR()函数就是或者的意思，空值这里用了“”表示填完一个之后记得选中这个格子右下角双击鼠标左键填满整个这一列第二个定投总金额，简单来说就是把当前列之前的所有单期定投金额加起来，比如第10行的定投总金额就等于第2行到第10行加起来的总额。
这个操作还是很简单的，只要上一列的定投总金额值加下一行的定投金额值就行了（不过需要把定投金额的空值赋个0，要不加“”会报错）这里我介绍一种新的方法，就是结合用Sum()函数和InDirect(ref_text)函数来完成这个加总的方式。
Indirect()函数是个很强大很有趣的函数，会对引用进行计算，并显示内容这里只需要用它的最最基本的功能就行了，就是定位到这一行的值，这样再用Sum()函数的基本功能就可以了比如上面的第10行的定投总额就可以表示成SUM($C$2:INDIRECT("C"&ROW()))，意思就是把第二行到第十行的定投金额加总。
这里的$C$2加了两个美元$符号表示起始行C2保持不变，下同这里的Row()函数就是取这一行的行数，比如这个例子在第十行，Row()函数返回的值就是10讲完这里，后面的就很简单了第三个定投份额等于定投金额/(1+申购费率)/净值。
第四个定投总份额同理用Sum()函数和InDirect(ref_text)函数完成对定投份额的加总。
第五个总市值等于总份额*净值。这里我没有去掉赎回费，如果算上的话，总的赎回市值等于总份额*净值*（1-赎回费率），大家可以自行添加完成。第六个绝对收益率等于总市值/定投总金额-1。
第七个年化收益率等于(1+绝对收益率)^(365/定投天数)-1。其中的定投天数等于这一行的日期减去起始日期$A$2。
第八个累计定投期数等于定投总金额/定投金额。
第九个单位持有成本等于定投总金额/定投总份额。
不知道大家有没有注意到，在以上的好几列中，并不是直接公式一套这么来玩的，而是都用了if()函数，这个也算是一个简单的小技巧，就是通过最最简单的if()函数来做一个预处理，基本上都在处理一些特殊格子值的问题，比如最最常见的空白格。
这个大家可以自己试下，看看处理之前和之后的区别，就能明白了定投模版的Excel部分讲到这里就基本大功告小成了：
都讲到这里了我就基于得到的数据简单画两个图，额外讲一点关于定投的东西，以此继续增强一下大家对定投的信心。
上图是天弘中证计算机指数A（001629）的净值走势和单位持有成本的复合图（如何用Excel画图后续我应该会讲几期），可以看到这个指数基金开始的时点运气并不是很好：2015年7月底，正好股灾开始阶段，而且还是股灾至今受伤最重的板块之一——计算机板块。
毫无悬念的，至今这个指数基金都是负的，而且负的还很有点厉害：截至上周五5月4日的净值为0.723，也就是说快3年了指数基金的绝对收益还亏27.70%但是这并不说明这个指数基金就不能买，相反，通过定投的方式定投至今（截至5月4日）的绝对收益仅仅小亏2.46%，而且一度在今年的3、4月中有翻红过。
这些从蓝底色的单位持有成本不断下降并趋近于（甚至低于）净值就能看出来
上图是定投001629至今的绝对收益率情况，看起来更加明显一些以上两个图又一次证明了定投的威力：哪怕开始在2015年那样的股灾高点，哪怕是最悲催的计算机板块，依然可以用定投轻松穿越熊熊熊假以时日，一旦牛一下哪怕是小牛一下，赚钱都基本不是问题。
比如今年以来计算机板块的那么一小个反弹，就直接让整个定投翻红了其实这里还有一个隐藏很深的知识点，那就是：天下没有不好的指数基金，只有不好的投资策略和方法。然后就真的可以结束了。
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。