投资收益率EXCEL计算模板及详解

大家好，我是微光。

今天给大家分享一个投资收益率EXCEL计算模板。模板与且慢小账本、有知有行App的计算原理、使用方法均类似。我自己已使用近两年，体验还算不错，分享给有需要的朋友参考。

其实模板在一年多前和大家分享过，详见公众号前期文章《如何计算投资收益率》和《投资收益率统计模板》。这次进行更新，一是增加了多账户模板，以满足有多个投资账户朋友的使用需求；二是前期有朋友在使用过程中有些疑问，这里对计算过程和使用方法进行详细说明。

一、 模板简介

先说说模板的特点：

1. 三种方法、不同口径：提供绝对收益率、内部收益率（XIRR年化收益率）、净值年化收益率三种计算方法，可满足不同统计口径需求。

2. 常用格式、平台通用：采用最常用的EXCEL表格格式，PC、平板、手机多种终端均可使用，多平台适应。

3. 记录简单、使用方便：每次仅需记录日期、资金投入（可省略）、总资产几个简单数据，其余自动计算，使用方便。

4. 本地存储、云端备份：可本地存储编辑，也可上传至腾讯文档等云平台，多地备份，无需担心数据丢失。

5. 共享导出、灵活便捷：采用腾讯文档，通过小程序或网页端可随时在线编辑，还能与他人共享，也可定期导出至本地，使用灵活便捷。

6. 单多账户、同时支持：同时提供单账户及多账户计算模板，采用多账户模板时，可分别统计各独立账户及合并账户收益情况。

以单账户为例，模板如下：

其中：

1. 模板中的绝对收益率体现实际收益情况，直观方便，但未体现时间等因素的影响。

这种方法比较简单。说成大白话，就是总共投入多少钱，挣了多少钱，挣钱占投入比例是多少。这种方法不考虑时间因素及中间的变化过程，直观方便，但反映的信息也不太全面。

2. 模板中的「内部收益率（XIRR年化收益率）」与有知有行APP中的「资金加权收益率」相似，都体现了进出资金的时间效应，但二者不完全相同。

「内部收益率（XIRR年化收益率）」本质是将资金的流入流出及期末资产按统一折现率折现到起点，根据总和为零来迭代计算收益率。这种方法考虑了资金流入流出的影响，未考虑总资产（或收益）变化过程的影响。

根据有知有行App的解释，其中的「资金加权收益率」是将投入资金按使用时间长度占总时间长度比率加权求总，再由此计算收益率，可知等效资金与时间是线性关系。

XIRR按统一折现率将资金折现到同一时间点，等效资金与时间为复利增长关系。二者处理方法类似，但个人认为逻辑上XIRR的算法更为合理。

这类方法说成大白话，就是只关心什么时间投入了多少资金，以及最后总资是多少，由此来计算收益率。至于总资产在投资期间是如何变化的，不关心，也不影响最终的收益率结果。

3. 模板中的「净值累积/年化收益率」与有知有行App里的「时间加权收益率」算法相同，本质是各时间段的收益率累计相乘，即通常的基金净值算法。

需注意的是，净值累积/年化收益率，或时间加权收益率，不仅考虑了资金流入流出的影响，还考虑了总资产或收益变化过程的影响（其实只有资金进出时间点的过程总资产及期末总资产对收益率结果有影响，其他时间点的总资产变化对收益率结果其实也无影响）。不要误以为名字叫时间加权，就认为没有考虑资金进出的影响。

说成大白话，就是不仅关心什么时间投入了多少资金，最后总资是多少，还关心总资产在投资期间是如何变化的，这些都会影响最终的收益率结果。

我认为净值法是衡量投资水平较为客观的一种统计方式，推荐采用。

关于各种收益率的详细计算原理，以及为什么收益率高不一定挣得多、收益率为正不一定挣了钱等，感兴趣的朋友可参见《如何计算投资收益率》一文。三种方法的主要对比见下图。

二、 使用说明

（一）单账户模板

其中红色为输入数据，蓝色为自动计算输出数据，每行进行一次记录。

日期和总资产列必须连续记录，中间不能有空行；资金投入列可为空，但首行数据不能为零，投入资金为正，转出资金为负。

净值基准行号表示净值累计收益率和净值年化收益率从该行开始统计，比如要统计某一年度收益率时可以用到。

需要注意的是，腾讯文档暂不支持XIRR函数，XIRR年化收益率列数据会显示为“#REF!”。只有本地EXCEL才能计算。

为了使资金投入和总资产在不同的列输入以方便记录，XIRR年化收益率计算列采用了“数组公式”对数据进行拼接，这是模板中有一定难度、同时也是比较巧妙的地方，有兴趣的朋友可看后文解释。

注意XIRR年化收益率计算列公式中的花括号不是手动输入的，如果从腾讯文档下载，本地EXCEL打开时可能会显示“#NUM!”，这时应选中该单元格，按【F2】编辑单元格，再按【Ctrl+Shift+Enter】计算更新。

同时，可以记录同期沪深300等指数的数据，以便对比。

其他数据结果均自动计算。需要增加表格长度时，选中最后一行A至M列，用鼠标向下拉伸即可。

（二）多账户模板

表格中还提供了一个含3个账户的模板，直接输入数据便可自动计算各账户及总账户结果。

如需增加账户数量，在合并账户前插入9列，从其他账户拷贝过来即可，数据需拷两行，XIRR年化收益率的首行数据不计算。

多账户模板中各账户的起始记录时间可以不一样，数据前面允许有空行，但第1个时间点至少需要一个账户有数据。

对于前面有空行的数据，需更改XIRR年化收益率计算公式中的起始行号，如某个账户起始有数据行号为7，则将该公式中的所有“$3”改为“$7”。

如增加账户，还需要将合并账户中的资金投入和总资产计算公式改为各个账户的相应值相加。

好了，介绍完了。模板可通过文末链接获取，祝大家使用愉快。

对方法和过程感兴趣的朋友，可以接着往下看。

三、 函数介绍

为了满足功能和使用需求，模板中用到了一些平时不常用的函数。结合模板中的函数使用情况，对相关函数进行介绍。

（一）IF

1. 函数语法

IF(logical_test,value_if_true, value_if_false)

判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。

2. 使用说明

比如，IF($A20=0, E19, C20-D20)，表示如果$A20=0，则结果为E19，否则为C20-D20。

（二）OR

1. 函数语法

OR(logical1, logical2, ....)

如果任一参数为TRUE，即返回TRUE；只有当所有参数值均为FALSE时才返回FALSE。

2. 使用说明

比如，OR(F19="单位净值", F19="")，表示如果F19为“单位净值”或表格为空时，返回TRUE。

（三）ROW

1. 函数语法

ROW(reference)

返回一个引用的行号。

2. 使用说明

比如，ROW(B$3)，返回单元格行号3。由于是绝对引用，行号不随内容拷贝和下拉填充而变化。

（四）COLUMN

1. 函数语法

COLUMN(reference)

返回一个引用的列号。

2. 使用说明

比如，COLUMN(F20)，返回单元格列号6。

（五）ADDRESS

1. 函数语法

ADDRESS(row_num,column_num, abs_num, a1, sheet_text)

创建一个以文本方式对工作簿中某一单元格的引用。

2. 使用说明

举例说明：

ADDRESS(I$1,COLUMN(F20),1)

I$1单元格的数据为3，COLUMN(F20)返回值为6，第3个参数1表示绝对引用，因此ADDRESS函数的返回值为：$F$3。

注意I$1为函数内部的绝对引用，不管该公式拷贝至哪，均采用第1行的数；而第3个参数绝对引用表示ADDRESS返回的文本地址是否带$。

（六）INDIRECT

1. 函数语法

INDIRECT(ref_text,a1)

返回文本字符串所指定的引用。

2. 使用说明

该函数立即对引用进行计算，并显示其内容。它可与ADDRESS函数联合使用。

举例说明：

INDIRECT(ADDRESS(I$1,COLUMN(F20),1))

由前文可知 ADDRESS(I$1, COLUMN(F20), 1) = $F$3，因此函数等价于INDIRECT($F$3,1)，第2个参数1表示常见的A1样式引用，$F$3单元格的数据为1，因此返回值为1。

INDIRECT和ADDRESS联合起来实现的功能是，获得指定行或指定列的某数据，该行号或列号在某一个单元格中输入指定。

（七）数组函数

1. 数组函数简介

数组函数是EXCEL中的高级用法。我们通常的函数，是将数据与数据进行运算，返回结果为一个数据。采用数组函数计算时，是将数组与数组进行运算，返回结果为数组。

EXCEL中的数组为二维数组，其数组运算类似于Matlab中的点运算，即将相同维度数组的对应元素进行相应运算。EXCEL允许维度不同，当有一个方向长度为1时，其他位置取值与其保持一致。比如9*1的列数据与1*9行数据相乘，可以得到9*9的矩阵。

数组函数的操作方法为，选中结果所在单元格或单元格组，在公式栏输入公式，并按【Ctrl+Shift+Enter】，这时数组函数外面会加上一对花括号{}，并生成计算结果。

在结果单元格的公式栏按【F9】，可以显示数组的值。选中结果单元格，并按【Ctrl+/】，会同时选中数组函数的所有单元格。

数组函数输出结果也可以放至一个单元格，这时只显示数组的第一个数据。

2. 使用说明

具体举例来说明。为便于大家理解这里稍微介绍详细一些。

如图所示表格，选中C2至C4单元格，然后在编辑栏输入“=A2:A4*B2:B4”。

接着同时按下【Ctrl+Shift+Enter】组合键，便有了计算结果，同时编辑栏的公式被一对花括号所包围。

选中结果单元格，点击编辑栏，再按【F9】，会显示数组计算结果。

有些函数的输入数据为数组，输出为数据，比如SUM函数，这时也可以用数组函数。

选中C5单元格，输入“=SUM(A2:A4*B2:B4)”，然后按【Ctrl+Shift+Enter】，就会生成计算结果20。

最后再来看一个输入数组维度不同的例子，由9*1的列数据与1*9行数据生成9*9的九九乘法表，如下所示。

（八）REPT

1. 函数语法

REPT(text,number_times)

根据指定次数重复文本。可用REPT在一个单元格中重复填写一个文本字符串。

2. 使用说明

比如，REPT("ab",3)，返回值为：ababab

再来看一个复杂一点的例子：

REPT(1,ROW(B19)-ROW(B$3)+1)&0

重复字符1，重复的次数为 ROW(B19) - ROW(B$3) + 1 = 17 次，&符号可连接字符串，即17个1后面再加一个0，整个函数的返回值为111111111111111110。

（九）MID

1. 函数语法

MID(text,start_num, num_chars)

从文本字符串中指定的起始位置起返回指定长度的字符。

2. 使用说明

举例：

1 * MID(REPT(1, ROW(B19) - ROW(B$3) + 1) & 0, ROW( INDIRECT( "1:" & ROW(B19) - ROW(B$3) + 2)), 1)

MID函数本身很简单，但这个例子混入了很多其他函数，理解起来相对困难。

下面分步来解读：

（1）首先，这是一个数组函数，返回结果为一个数组。

（2）由前面可知，第1个参数 REPT( 1, ROW(B19) - ROW(B$3) + 1) & 0 返回结果为字符串 111111111111111110。

（3）第2个参数ROW(INDIRECT( "1:" & ROW(B19) - ROW(B$3) + 2))是数组。

（4）其中，"1:" & ROW(B19) - ROW(B$3) + 2的值为字符串“1:18”，注意&运算符的优先级低于加减号。

（5）INDIRECT("1:" & ROW(B19) - ROW(B$3) + 2)返回括号内文本1:18的引用，由于这里没有列号，返回结果实际是整个表格第1至18行所有数据的引用。

（6）INDIRECT外面加上ROW函数，返回第1至18行数据的行号，因此ROW(INDIRECT("1:" & ROW(B19) - ROW(B$3) + 2))的最终返回结果={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}。

（7）所以，MID(REPT(1, ROW(B19) - ROW(B$3) + 1) & 0, ROW( INDIRECT( "1:" & ROW(B19) - ROW(B$3) + 2)), 1)函数的含义是，从111111111111111110这个字符串中分18次取数，返回一个18*1的数组，第i次取数的起始位置为i，取数长度为1，返回结果={"1";"1";"1";"1";"1";"1";"1";"1";"1";"1";"1";"1";"1";"1";"1";"1";"1";"0"}。

（8）最后，注意MID函数前面还有一个1*，这是将MID函数返回的字符串数组转换为数值数组，最终结果 = {1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0}。

（十）实现数组拼接

最后来看看本模板中最复杂的函数：

IF(1 * MID(REPT(1, ROW(B19) - ROW(B$3) + 1) & 0, ROW( INDIRECT( "1:" & ROW(B19) - ROW(B$3) + 2)), 1), B$3:B19, B20 - C20)

这是一个综合函数，是整个模板中最难理解、同时也是编写最为巧妙的部分。通过数组函数、条件判断、文本重复、引用计算、字符串读取、数值转换等函数相结合，巧妙实现了不同列的数组拼接，满足了模板的简洁输入需求。

下面进行解读：

（1）这是一个IF条件判断函数，也是一个数组函数。

（2）根据前面可知，IF函数的第1个变量是一个长度为18的数组，计算结果={1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0}。

（3）第2个变量B$3:B19是一个长度为17的数组。

（4）第3个变量是一个数据B20-C20。

（5）返回结果为18*1的数组，前17次判断条件为真，取值为第2个变量；第18次判断条件为假，取值为第3个变量。

（6）最后返回的数组，正是XIRR函数计算所需要的数据列数组。

（十一）XIRR

1. 函数语法

XIRR(values,dates, guess)

返回现金流计划的内部回报率。

2. 使用说明

举例：

XIRR( IF( 1 * MID( REPT( 1, ROW(B19) - ROW(B$3) + 1) & 0, ROW( INDIRECT( "1:" & ROW(B19) - ROW(B$3) + 2)), 1), B$3:B19, B20 - C20), $A$3:$A20)

XIRR第1个参数为数据列，第2个参数为时间，第3个为结果初估值（可省略）。详细说明可《如何计算投资收益率》一文，这里不再赘述。

四、 模板详解

下面结合单账户模板，对各数据进行详细说明。计算公式以最后一行第20行为例。

（一）日期

记录日期，标题下第1行需有数，数据需连续填写，不能有空行。还未填到的行为空，计算结果中如判断到日期为空，则采用上一行结果。

（二）资金投入

记录当前日期资金投入及取出情况，投入为正，取出为负，数据可不连续填写，允许有空行，无资金进出时为空或填写0。

（三）总资产

记录当前日期总资产（市值）情况，数据需连续填写，不能有空行。

（四）累积投入

1. 计算公式

=SUM(B$3:B20)

2. 计算逻辑

对资产投入列求和。起始位置固定为第3行，多账户模板有空行时，空行数据不影响计算结果。

（五）累积收益

1. 计算公式

=IF( E19 = "累积收益(元)", 0, IF($A20 = 0, E19, C20 - D20))

2. 计算逻辑

（1）如果上一行内容为"累积收益(元)"（标题行），则返回0。

（2）否则，如果该行日期列为0（空），则返回上一行值。

（3）其他情况下，为总资产与累积投入之差。

（六）单位净值

1. 计算公式

=IF( OR( F19="单位净值", F19=""), 1, IF( OR( $A20=0, C19=0), F19, (C20 - B20) / C19 * F19))

2. 计算逻辑

（1）如果上一行内容为“单位净值”（标题行），或者内容为空，则返回1。

（2）否则，如果该行日期列为0（空），或者上一行总资产为0（这里主要是避免资金全部取出时分母为0出现BUG），则返回上一行值。

（3）其他情况下，返回去除资金投入影响后的总资产增长率与上一行净值乘积结果：(C20-B20)/C19*F19。

（七）绝对收益率

1. 计算公式

=IF(D20<=0, 0, E20/D20*100)

2. 计算逻辑

（1）如果累积投入小于等于0（避免除数为0或资金全部取出后收益率出现负值），则返回0。

（2）否则，返回累积收益与累积投入计算的绝对收益率结果：E20/D20*100。

（八）净值基准行

计算净值累积收益率及净值年化收益率的起始行号。

（九）净值累积收益率

1. 计算公式

= (F20 - INDIRECT( ADDRESS( I$1, COLUMN(F20), 1))) / INDIRECT( ADDRESS( I$1, COLUMN(F20), 1)) * 100

2. 计算逻辑

（1）返回该行与净值起始行相比，单位净值的增长百分比。

（2）因单位净值已进行了日期为空行的赋值处理，这里无需再列分支进行处理，直接计算即可。

（十）净值年化收益率

1. 计算公式

=IF(OR(I19="净值年化收益率(%)", $A20 - INDIRECT(ADDRESS(I$1, COLUMN($A20), 1))=0), 0, IF($A20="", I19, ((F20 / INDIRECT(ADDRESS(I$1, COLUMN(F20), 1))) ^ (365 / ($A20 - INDIRECT(ADDRESS(I$1, COLUMN($A20), 1)))) - 1) * 100))

2. 计算逻辑

这里逻辑相对复杂一些。

（1）如果上一行内容为"净值年化收益率(%)"（标题行），或者该行日期与基准行日期之差为0（其实即同一行），则返回0。

（2）否则，如果该行日期为空，则返回上一行数据。

（3）其他情况下，反馈由当前行与基准行单位净值、日期计算的年化收益率结果。

（十一） XIRR年化收益率

1. 计算公式

{=IF($A20="", J19, XIRR(IF(1 * MID(REPT(1, ROW(B19) - ROW(B$3) + 1) & 0, ROW(INDIRECT("1:" & ROW(B19) - ROW(B$3) + 2)), 1), B$3:B19, B20-C20), $A$3:$A20) * 100)}

2. 计算逻辑

该函数为数组函数。

（1）如果日期行为空，则返回上一行数据。

（2）否则，返回由数据初始行（注意不是净值基准行，XIRR必须对所有数据进行计算）至当前行的日期列、资金投入与期末总资产合成列的XIRR内部收益率计算结果。

（十二）沪深300指数

记录当前日期的沪深300指数。

（十三）沪深300基准行

计算沪深300指数累积涨跌及年化收益率的起始行号。

（十四）沪深300累积涨跌

1. 计算公式

=IF( K20=0, L19, (K20 - INDIRECT( ADDRESS( M$1, COLUMN(K20), 1))) / INDIRECT( ADDRESS(M$1, COLUMN(K20), 1)) * 100)

2. 计算逻辑

（1）如果当前行沪深300指数为0（空），则返回上一行数据。

（2）否则，返回该行与基准行相比，沪深300指数的增长百分比情况。

（十五）沪深300年化收益率

1. 计算公式

=IF(OR(M19="年化收益率(%)", ROW($A20)=M$1), 0, IF($A20="", M19, ((K20 / INDIRECT( ADDRESS(M$1, COLUMN(K20), 1))) ^ (365 / ($A20 - INDIRECT( ADDRESS(M$1, COLUMN(A20), 1)))) - 1) * 100))

2. 计算逻辑

（1）如果上一行内容为"年化收益率(%)"（初始行），或者该行行号与基准行数据一致，则返回0。

（2）否则，如果该行日期为空，则返回上一行数据。

（3）其他情况下，返回由根据沪深300基准行与当前行的日期、沪深300指数计算的年化收益率结果。

五、 模板链接及二维码

长按下面二维码可获取小企鹅自家的腾讯文档收益率模板： 

也可通过以下链接打开：

【腾讯文档】投资收益计算模板【公众号：微光探索】

https://docs.qq.com/sheet/DTkNvUVFzUnd3TlBv

同时，还可向公众号发送「收益率模板」获取EXCEL版本下载地址。

不对之处，欢迎大家指正，如有意见和建议，可联系我修正完善。

以上。

—— THE END ——

留言请戳下方视频号