在陆金所上投资的人越来越多,也有很多人想知道自己的这些钱投下去,获得的实际年化利率到底是多少。由于陆金所没有提供这种数据,因此我们只能借助于我的帐户中的一些数据来手动计算一下了。下面会详细讲到我的思路。
计算年化收益率大概的思路是,根据资金投资和回款时间,以及当前的总资产及收益,根据XIRR公式来计算。具体分为下面几步:
1. 获取资金投资和回款记录。
依次选择 我的帐户->资金记录->资金明细,然后把时间范围选择为从第一笔资金投资开始,再选择 下载Excel文件,会导出transaction-history.xls 文件。
打开transaction-history.xls文件,在 资金记录 页面,依次选择Ctrl+A -> Data -> Filter,然后在第二列的下拉框中勾选 充值 和 取现 两项,获取充值和取现的记录。如下图。
在该页面中,选择Ctrl+ A -> Ctrl + C,然后再新建一个页面,取名为充值&取现,在充值&取现中选择Ctrl+V,将充值和取现记录单独记录成一个页面。
2. 计算年化收益率
接下来是在充值&取现 页面,计算年化收益率了。要做下面几件事:
1) 将 时间 列的日期和时间分成两列。这样做的目的是,后续用XIRR公式计算年化收益率时,只需要日期即可,不需要也不能包含具体的时间,因此需要将具体时间单独成一列。
首先,在第一列和第二列之间插入一列,如下图。
然后,依次选择Data->Text to Columns->Delimited->Space->Finish,就将日期和时间分成两列了。如下图。
2) 将D列 和E列的数字都coverto number。只有这样,才能对充值和取现的资金进行数学运算。
3) 按时间先后顺序重排资金流。在充值&取现 页面,最近的一笔资金流向是在第一行,最远的一笔资金流向是在最后一行,在实际计算年化收益率时,需要将颠倒过来,即最近的一笔资金流向应该在最后一行。
选择Ctrl+A -> Data->Sort,就可以了。
4) 将D列和E 列中的空白栏都填0。因为下面会将这两列的数据统一到一列中。
可以用查找和替换功能实现,操作方式见下图,选择replaceall。
5) 资金流向统一到一列。后续使用XIRR公式时,只能针对一列的数据来计算。
新增一列,取名叫资金流向。然后在H2这栏,输入 =0-D2+E2,见下图,然后类比到整列。
6) 增加一行,用来标记当前总资产。使用XIRR公式时,都需要有最后一行来标记当前的资产有多少。
参看陆金所->我的帐户->总资产,获得总资产数据。然后增加一行,在资金流向这一栏,填入该数据。
7) 计算收益率。
增加一列,取名XIRR,然后使用=XIRR(H2:H143,A2:A143) 来计算出年化收益率,见下图。
3. 总资产数据的校正
上图中可以看出,使用XIRR计算的收益率为0,是哪里出问题了呢?这是因为有下面几个原因:
1) P2P项目起息日到当前日的收益。安e项目是一个月还一次款,收益会计算到总资产中,但是还没到一个月时,由于还没有还款,这部分利息收入是不会计算在总资产中的,需要把这笔钱计算到总资产中。
2) 使用了e享计划。即一笔稳赢安e,e享出去后,有10%的资金是被抵押了,而这笔钱,陆金所是不会计算在总资产中。我们需要把这笔钱算出来,计算到总资产中。另外需要特别强调的是,e享出去的项目,这部分利息收入,也是需要计算的。
对于第一点,依次选择 我的帐户->我的理财->P2P->下载Excel文件,然后再增加一列截至当前的利息收益,其数据的计算公式见下图。最后再把整列的数据加起来,计算出 总的利息收益,这就是P2P项目起息日到当前日的收益。
对于第二点,依次选择 我的帐户->我的e享计划->下载Excel文件,然后再增加4列,见下图。这4列的计算公式分别见下图。
最后把这两部分算出来的金额都加到充值&取现 页面的总资产这栏中,那么就可以通过XIRR算出年化收益率了。
另外,补充一句,如果想获取收益总额的数据,只要在充值&取现 页面,将资金流向这一列的数据全加起来就可以了(使用SUM公式)。