量化入门系列：如何用EXCEL做一个基金定投的回测模型

量化入门系列：如何用EXCEL做一个基金定投的回测模型

本系列通过实例来介绍量化的入门知识，适合零基础的初学者。本文的量化环境基于Python和AKShare数据接口，安装教程见文末附录。

前几篇文章介绍了如何计算指数的估值百分位，能不能用估值百分位做一个增强型的基金定投策略：当估值低时多投些，当估值高时少投些。听起来很有道理，那到底有没有效呢？这时量化的作用就体现出来了，我们可以用历史数据来验证这个策略。本篇先介绍如何用excel做一个基础版的基金定投回测模型，下一篇再介绍如何用Python实现以及如何利用估值百分位来做个增强版的定投策略。

为了方便研究，我们将定投的标的设定为沪深300指数，指数本身是不能购买的，但这不影响研究结果。首先我们要获取沪深300的历史行情数据，有了历史行情数据才能进行回测。

程序运行前先要导入需要的库：

import akshare as ak # 导入AKShare数据源
import pandas as pd # 导入pandas库
import datetime # 导入datetime库

我们用AKShare的 ak.stock_zh_index_daily() 数据接口来获取沪深300的历史行情数据：

hs300_df = ak.stock_zh_index_daily(symbol=”sh000300″) # 取沪深300的历史行情数据

然后我们选一个定投的开始日期，这里选2015年6月1日，为什么选这个日期呢？因为这天是上轮牛市的顶点，我们测试下，从一个最糟糕的时点开始定投，到底是盈是亏，所以我们筛选2015年6月1日之后的行情数据：

hs300_df = hs300_df[hs300_df[‘date’]>datetime.date(2015,6,1)] # 筛选2015年6月1日之后的数据

查看下 hs300_df 的内容：

print (hs300_df)

这个DataFrame表格有6列：日期（date）、开盘价（open）、最高价（high）、最低价（low）、收盘价（close）、成交量（volume）。我们是用excel来做回测，需要把数据导出到excel，只用一行代码就能实现：

hs300_df.to_excel(‘hs300.xlsx’) # 将DataFrame表格导出为excel文件

然后就能在同一文件夹里找到 ‘hs300.xlsx’ 这个文件了。用excel做的回测模型是这样的：

逐列讲解一下：

1. ‘date’ 和 ‘close’ ：这两列是刚才导出的数据，由于定投回测只用到日期（date）和收盘价（close）这两列数据，其他列删除了。
2. ‘每期定投金额’：设定为每天投100元。
3. ‘累计定投金额’：为‘每期定投金额’的累计值。
4. ‘每期定投数量’：为每期定投的100元能买到多少股，这里注意要扣除手续费，即：

每期定投数量 = 每期定投金额 ÷ 当天的收盘价 × (1－交易手续费率)

此处手续费的扣除跟实际交易有些差异，但影响很小，就简化处理了。

5. ‘累计持有数量’：为‘每期定投数量’的累计值。
6. ‘累计持仓净值’：所持有的全部基金当天收盘后的价值，即：

累计持仓净值 = 累计持有数量 × 当天的收盘价

7. ‘现金流’：用来计算年化收益率，后面讲。

至此，一个定投的量化模型就完成了，是不是很简单。我们来分析下回测的结果（下图隐藏了中间的时段）：

在定投开始的2015年6月2日，沪深300的收盘价为5161.87元，在定投结束的2021年11月8日，收盘价为4848.18元。如果你是一次性买入，那到现在还没回本。

整个定投总共投了1567期，累计投出156700元，累计持仓净值为198654.90元。定投的作用显现出来了，尽管现在指数收盘价还低于定投开始日，但定投已盈利 198654.90元－156700元 = 41954.90元。

把‘累计定投金额’和‘累计持仓净值’这两条曲线画出来：

蓝线为累计定投金额，也就是资金成本；红线为累计持仓净值。当红线在蓝线之下时，表明投资出现亏损，反之就是盈利。可以直观的看到，盈利的时段比亏损的时段要多，而且亏损的幅度并不算大，在2020年5月份之后就再也没有亏损过。

我们再来算下定投的收益率：

总收益率 = (累计持仓净值－累计定投金额) ÷ 累计定投金额 = 26.77%

这个收益率看起来不高，但其实不准确，因为资金成本并不是一次性投入的，而是分次投入的，有的资金占用的时间长，有的资金占用的时间短，这时要计算现金流的内部收益率，这才是真实的收益率。

用excel的 XIRR(values, dates) 函数可以很便捷的计算出内部收益率。XIRR函数有两个必需的参数：values 为现金流序列，也就是excel表格中 ‘现金流’ 这一列；dates 为与现金流序列相对应的日期序列。

现金流序列就是每期投出和收回的现金，投出用负数表示，收回用正数表示。我们每期投出100元，所以每期的现金流是 -100 元；在最后一期这项投资了结，卖出所有持仓的基金收回现金，所以最后一期现金流为正数。

用XIRR函数计算出的内部收益率（即年化收益率）是7.33%，看似不高，但要知道这是从上轮牛市的顶点开始定投的，这个收益已经很不错了。