Greetings, folks! Any assistance in solving this would be appreciated.
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?
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?