Calculate XIRR after removing blanks from input table and appending today's date & today's value.

[polarwoc]

Greetings, folks! Any assistance in solving this would be appreciated.

Portfolio-MC Widget.xlsx
	B	C	D	E	F	G	H
3	Date	NAV	Units	Amount	Value		XIRR
4	15-02-2023	2673.4501	104.7283433	-280000	-279986
5	27-02-2023	2678.8416	-7.465540329	20000	20000
6	15-03-2023	2687.2583	-7.442157682	20000	20000
7	04-04-2023	2699.2109	-3.704601223	10000	10000
8
9
10
11
12
13
CR-STP

Cell Formulas
Range	Formula
C4:C13	C4	=IFERROR(INDEX( URL_List[navValue], MATCH( 1, ($B$2=MID(URL_List[URL_List],84,12))*(B4=URL_List[navDate]),0 ) ),"")
D4:D13	D4	=IFERROR(E4*-0.99995/C4,"")
F4:F12	F4	=IF(ISNUMBER([@Amount]),IF([@Amount]<0,[@Amount]*0.99995,[@Amount]),"")

I have the above table with 10 rows. I need to calculate XIRR as of today, so I need to
1) redact all blank cells in the table's Date and Value columns
2) add today's date to the date column at the end while adding 230,000 to the Value column at the end (This value is sitting on another sheet, and can be referenced by cell address)
3) the thus formed array should be entered as input to XIRR, and it would resemble =XIRR({-279986,20000,20000,10000,230000},{"15-02-2023","27-02-2023","15-03-2023","04-04-2023","10-04-2023"})

Note: Assuming today is 10 Apr 2023 and today's value is 230,000.

How do I calculate XIRR?

 

[Domenic]

I have made the following assumptions...

1) The name of your table is Table1.

2) Cell B2 on Sheet2 contains the value 230,000.

Here's the formula...

=LET(data,FILTER(Table1,(Table1[Date]<>"")*(Table1[Value]<>"")),XIRR(VSTACK(INDEX(data,0,5),Sheet2!$B$2),VSTACK(INDEX(data,0,1),TODAY())))

For older versions of Excel...

=XIRR(FILTERXML("<root><item>"&TEXTJOIN("</item><item>",TRUE,Table1[Value])&"</item><item>"&Sheet2!$B$2&"</item></root>","//item"),FILTERXML("<root><item>"&TEXTJOIN("</item><item>",TRUE,Table1[Date])&"</item><item>"&TODAY()&"</item></root>","//item"))

Change the name of the Table and cell reference accordingly.

By the way, I would suggest that you update your profile to include your version of Excel. This way members here can offer the most efficient solution.

Hope this helps!

 

[polarwoc]

Thank you so much for the response.

=LET(data,FILTER(Table1,(Table1[Date]<>"")*(Table1[Value]<>"")),XIRR(VSTACK(INDEX(data,0,5),Sheet2!$B$2),VSTACK(INDEX(data,0,1),TODAY())))

This looks so elegant and efficient. I could marvel at it and use this when I upgrade to Office 365.

=XIRR(FILTERXML("<root><item>"&TEXTJOIN("</item><item>",TRUE,Table1[Value])&"</item><item>"&Sheet2!$B$2&"</item></root>","//item"),FILTERXML("<root><item>"&TEXTJOIN("</item><item>",TRUE,Table1[Date])&"</item><item>"&TODAY()&"</item></root>","//item"))

This does the job for me. This solution was way above my knowledge realm. My search for a solution spanning almost 6 days ends. So, thank you once again.

 

[Domenic]

You're very welcome, I'm glad I could help.

And thanks for your feedback!

Cheers!