如何在excel和google表中动态计算XIRR [英] how to calculate XIRR dynamically in excel and in google sheets
问题描述
我正在以下列格式计算XIRR数据：
I am trying to calculate XIRR for data that I have in the below format
PurchaseDate Script No.ofunits PurchNAVrate NetAmount ForXIRR TotalReturn
17/11/2014 A 2241 33 75000 -75000 96000
8/1/2015 B 53 649 35000 -35000 43000
14/1/2015 B 75 658 50000 -50000 61500
14/10/2014 C 2319 32 75000 -75000 108000
8/1/2015 D 318 109 35000 -35000 40000
14/1/2015 D 450 110 50000 -50000 57000
8/6/2015 D 175 114 20000 -20000 22000
我尝试了很多不同的排列，组合，但不能计算每个基金的XIRR。有没有办法我可以使用偏移或搜索。
这是一个动态列表，根据购买或出售的新基金不断变化。
I tried lots of different permutations and combinations but not able to calculate XIRR for each Fund. Is there a way I can do it perhaps using offset or search. This is a dynamic list which keeps getting changed based on new funds that are bought or sold
Values for Fund A should be around 14%
Values for Fund B should be around 13%
Values for Fund C should be around 21%
Values for Fund D should be around 8%
更新
鉴于@ xor-lx提到的解决方案在ms中工作非常好excel但不是google表，我现在已经基于@kyle建议修改了我的数据结构，现在我可以为每个基金整体计算XIRR。参考以下
Update Given that solution mentioned by @xor-lx was working very nicely in ms excel but not in google sheets, I have now modified the structure of my data based on @kyle advice and now I can calculate XIRR easily for each fund as a whole. Refer below
PurchaseDate Today Script No.ofunits PurchNAVrate NetAmount ForXIRR TotalReturn XIRR
17/11/14 31/08/2016 A 2241 33 75000 -75000 96000 "=XIRR(G2:H2,A2:B2)"
Total for above fund "=XIRR(G2:H3,A2:B3)"
8/1/2015 31/08/2016 B 53 649 35000 -35000 43000 "=XIRR(G4:H4,A4:B4)"
14/1/2015 31/08/2016 B 75 658 50000 -50000 61500 "=XIRR(G5:H5,A5:B5)"
Total for above fund "=XIRR(G4:H6,A4:B6)"
14 Oct 14 31/08/2016 C 2319 32 75000 -75000 108000 "=XIRR(G7:H7,A7:B7)"
Total for above fund "=XIRR(G7:H8,A7:B8)"
8 Jan 15 31/08/2016 D 318 109 35000 -35000 40000 "=XIRR(G9:H9,A9:B9)"
14 Jan 15 31/08/2016 D 450 110 50000 -50000 57000 "=XIRR(G10:H10,A10:B10)"
8/6/2015 31/08/2016 D 175 114 20000 -20000 22000 "=XIRR(G11:H11,A11:B11)"
Total for above fund "=XIRR(G9:H12,A9:B12)"
推荐答案
假设您的表位于 A1：G8 （带有第1行中的标题）并且您选择的基金，例如B，在 J2 ，数组公式**：
Assuming your table is in A1:G8 (with headers in row 1), and that your Fund of choice, e.g. "B", is in J2, array formula**:
= XIRR INDEX（F：G，N（IF（1，MODE.MULT（IF（B $ 2：B $ 8 = J2，{1,1} * ROW（B $ 2：B 8 $））））），N（IF（1 ，{1,2}））），选择（{1,2}，INDEX（A：A，N（IF（1，MODE.MULT（IF（B $ 2：B $ 8 = J2，{1,1} * ROW（B $ 2：B $ 8）））））），TODAY（）））
J3 ， J4 等。
我倾向于更喜欢这个设置涉及 OFFSET ;不仅仅是这样（因此更有效率），更重要的是非挥发性的。
I tend to prefer this to set-ups involving OFFSET; not only is it briefer (and therefore more efficient), but also, more importantly, non-volatile.
如果您有兴趣，请参阅这里：
See here for more if you're interested:
https://excelxor.com/2016/02/16/criteria-with-statistical-functions-growth-linest-logest-trend/
注意
**数组公式不以与标准公式相同的方式输入。而不是按ENTER键，您首先按住CTRL和SHIFT，然后只按ENTER。如果您已经正确完成，您会注意到Excel在公式周围放置大括号{}（尽管不要手动插入这些）。
这篇关于如何在excel和google表中动态计算XIRR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
