我有一张 Excel 表格,用于跟踪我的金融投资。我有一个字段用于输入我投资组合的最新当前值,还有一个表格用于输入我存入的款项(日期、存款、投资组合的当前值,...)。
我想计算的是这些投资的预计年利率。我不能只计算(利润/原值)/年或类似的数字,因为全年的额外存款会改变一切。
如果我投资了 100 美元,并且在将近一年的时间里增长到 150 美元,但是最后一天我又存入了 200 美元,我仍然希望看到 50% 的年利率(或略低于该利率),而不是 16%(50 美元/300 美元)。
因此,它应该像银行一样计算一些利息(但是是相反的):考虑到资金的投资时间。
我不知道如何在 Excel 中执行此操作。这可以用标准公式完成吗?还是需要 VBA?
答案1
经过一些尝试,我想我找到了解决方案:
在我的存款表中我计算:距上次存款已过去 # 天和自上次存款以来的增长(%)。
我还计算了自上次存款以来的天数, 和自上次存款以来的增长(%)。
每日平均增幅为:
(growth (%) since last deposit + SUM(growth (%) since previous deposit))/(# of days since last deposit + SUM(# days since previous deposit))
将此数字乘以 365,我就得到了平均年增长率。
答案2
您需要使用 XIRR 函数。
为此,您必须将所有日期(存款和取款/当前值)放在一列中,并将所有相应的现金流放在相邻列中。如下所示:
+------------+----------+
|Date |Cash flow |
+------------+----------+
|2020-05-12 | 1200|
|2020-07-18 | 1500|
|2020-10-12 | -2900|
+------------+----------+
然后使用 XIRR 函数计算您的回报。作为第一个参数,指定值(第二列)和日期作为第二个参数。
就像是
=XIRR(B2:B4, A2:A4)