投资收益率计算模板
虽然网上有一些投资收益率计算的软件和小程序，券商软件也可查看投资收益，但在实际使用中，可能仍然会遇到一些问题，比如：
券商软件的数据统计周期有一定限制，时间长了可能连净投入资金是多少都搞不清楚，收益率也是一笔糊涂账。
小程序数据存储于平台内部，如不提供数据的导入导出功能，可能存在数据安全性风险。
有多个证券、基金投资账户时不便计算投资总收益。
不同投资收益率计算方法结果差异很大。
因此，基于常用文件格式、记录方便、数据易保存，同时最好考虑多种算法、方便本地和云端同时存储的投资收益统计方法显得很有必要。本文提供一种考虑绝对收益率法、内部收益率法、单位份额净值法三种方法的EXCEL收益率计算模板，供大家参考。关于三种方法的详细差异，可参考《如何计算投资收益率》一文。
一、计算模板
计算模板如下：
以最后一行为例，各列计算公式如下：
累积投入（元）：D10 = SUM ( B$2:B10 )
累积收益（元）：E10 = IF ( E9 = "累积收益(元)", 0, IF ( $A10 = 0, E9, C10 - D10 ) )
单位净值：F10 = IF ( OR ( F9 = "单位净值", F9 = "" ), 1, IF ( OR ( $A10 = "", C9 = 0 ), F9, ( C10 - B10 ) / C9 * F9 ) )
绝对收益率(%)：G10 = IF ( D10 <= 0, 0, E10 / D10 * 100 )
净值累积收益率(%)：H10 = ( F10 - F$2 ) / F$2 * 100
净值年化收益率(%)：I10 = IF ( OR ( I9 = "净值年化收益率(%)", $A10 - $A$2 = 0 ), 0, IF ( $A10 = "", I9, ( ( F10 / F$2 ) ^ ( 365 / ( $A10 - $A$2 ) ) - 1 ) * 100 ) )
XIRR年化收益率(%)：J10 { = IF ( $A10 = "", J9, XIRR ( IF ( 1 * MID ( REPT ( 1, ROW ( B9 ) - ROW ( B$2 ) + 1 ) & 0, ROW ( INDIRECT ( "1:" & ROW ( B9 ) - ROW ( B$2 ) +2 ) ), 1), B$2:B9, B10-C10 ), $A$2:$A10 ) * 100 ) }
二、带基准行的计算模板
有时我们需要指定收益率的分析起止时间，此时需采用带基准行的模板，见下图：
以最后一行为例，各列计算公式如下：
累积投入（元）：D10 = SUM ( B$3:B10 )
累积收益（元）：E10 = IF ( E9 = "累积收益(元)", 0, IF ( $A10 = 0, E9, C10 - D10 ) )
单位净值：F10 = IF ( OR ( F9 = "单位净值", F9 = "" ), 1, IF ( OR ( $A10 = "", C9 = 0 ), F9, ( C10 - B10 ) / C9 * F9 ) )
绝对收益率(%)：G10 = IF ( D10 <= 0, 0, E10 / D10 * 100 )
净值累积收益率(%)：H10 = ( F10 - INDIRECT ( ADDRESS ( B$1, COLUMN ( F10 ), 1 ) ) ) / INDIRECT ( ADDRESS ( B$1, COLUMN ( F10 ), 1 ) ) * 100
净值年化收益率(%)：I10 = IF ( OR ( I9 = "净值年化收益率(%)", $A10 - INDIRECT ( ADDRESS ( B$1, COLUMN ( $A10 ), 1 ) ) = 0 ), 0, IF ( $A10 = "", I9, ( ( F10 / INDIRECT ( ADDRESS ( B$1, COLUMN ( F10 ), 1 ) ) ) ^ ( 365 / ( $A10 - INDIRECT ( ADDRESS ( B$1, COLUMN ( $A10 ), 1 ) ) ) ) - 1 ) * 100 ) )
XIRR年化收益率(%)：J10 { = IF ( $A10 = "", J9, XIRR ( IF ( 1 * MID ( REPT ( 1, ROW ( B9 ) - ROW ( B$3 ) + 1 ) & 0, ROW ( INDIRECT ( "1:" & ROW ( B9 ) - ROW ( B$3 ) + 2 ) ), 1 ), B$3:B9, B10 - C10 ) , $A$3:$A10 ) * 100 ) }
三、使用说明
使用说明如下：
红色为输入数据，蓝色为自动计算输出数据。
基准行：必填项，为单位份额净值法收益率计算起始数据所在行号。
日期：必填项，日期栏为空时计算结果重复上一行值。
资金投入：选填项，投入填正值，转出填负值，起始数据不能为零，没有资金投入时可为空。
总资产：必填项，为当前日期投资账户总资产额。
累积投入：累积总投入资金。
累积收益：累积总收益额，如果是初始行则为0，如果日期栏为空则与上一行为0。
单位净值：初始行或该列上一行为空时值为1，否则当该行日期为空或上一行总资产为0时值与上一行相同，其他情况下根据相邻两行资金投入、总资产数据进行计算。
绝对收益率(%)：如果累积投入小于等于0则赋值为0，否则根据该行累积投入与累积收益数据进行计算。
净值累积收益率(%)：由该行与起始行单位净值计算。
净值年化收益率(%)：如果为起始行或日期与基准日期相同则赋值为0，否则如果日期为空则值与上一行相同，其他情况下根据该行与基准行日期、单位净值数据进行计算。
XIRR年化收益率(%)：如果日期为空则值与上一行相同，否则根据日期、资金投入、总资产数据进行计算。
由于资金投入与总资产在不同的列，为了对数据进行拼接，XIRR年化收益率列使用了“数组公式”功能。公式中的花括号不是手动输入的，而是应选择该单元格，按 F2，再按 Ctrl+Shift+Enter。
实际使用时，可以使用腾讯文档，每周对投资情况进行记录，在云端进行存储，并定期进行本地备份，可避免数据丢失，同时方便在手机、电脑等多个终端随时查看。但遗憾的是，腾讯文档目前还只有IRR函数，没有XIRR函数，同时不具备“数组公式”功能，希望未来能有功能更新。
—THE END—