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

polarwoc

New Member
Joined
Apr 3, 2023
Messages
9
Office Version
  1. 2021
Platform
  1. Windows
Greetings, folks! Any assistance in solving this would be appreciated.

Portfolio-MC Widget.xlsx
BCDEFGH
3DateNAVUnitsAmountValueXIRR
415-02-20232673.4501104.7283433-280000-279986
527-02-20232678.8416-7.4655403292000020000
615-03-20232687.2583-7.4421576822000020000
704-04-20232699.2109-3.7046012231000010000
8   
9   
10   
11   
12   
13  
CR-STP
Cell Formulas
RangeFormula
C4:C13C4=IFERROR(INDEX( URL_List[navValue], MATCH( 1, ($B$2=MID(URL_List[URL_List],84,12))*(B4=URL_List[navDate]),0 ) ),"")
D4:D13D4=IFERROR(E4*-0.99995/C4,"")
F4:F12F4=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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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...

Excel 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...

Excel Formula:
=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!
 
Last edited:
Upvote 1
Solution
Thank you so much for the response.

Excel Formula:
=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.

Excel Formula:
=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.
 
Upvote 0
You're very welcome, I'm glad I could help.

And thanks for your feedback!

Cheers!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top