XIRR function considering data from different columns

[kekko]

Hello everyone,
I please need your help in order to calculate the IRR of an investment at different dates (XIRR formula), the problem is that XIIR funcion allows only two columns of arguments (values and date). Data are organized as follows:

column A: dates

column B: cash outflows

column C: fair values of the investment at that date; it means that the fair values are "balance sheet" positions and do not correspond to increases/decreases of the period and at every date should be calculated the XIRR at that date (Column D), telling to the formula to consider as simulated cash collection only the fair value from last date.

Please find attached the example file.

Is there anyone that could help me?

Many thanks,
Francesco

 

[Chihiro]

It isn't clear to me what you are trying to calculate here.
XIRR calculates IIR for cash flows over schedule which isn't periodic (regular interval).

But from your description.... you are trying to calculate IRR at each line. If that is the case...
I can't think of good method to accomplish this via standard Excel formula. Other than generating new series (table) for each subset.

I believe it is possible to do using DAX. If you have access to it.

 

[kekko]

I'm trying to calculate the IRR at each date I have a fair value of an investment.
I've data in 3 columns: date, cash outfows/inflows, fair value of the investment.
How can I do it? Please refer to the attached excel file.

 

[Peter Bartholomew]

Is this the sort of thing you are looking for?


It is a 365 insider solution, so some refactoring would likely be needed.

 

[kekko]

Is this the sort of thing you are looking for?
View attachment 79001

It is a 365 insider solution, so some refactoring would likely be needed.

Yes!but I have two problems:

(1) if I enable editing, your formula disappears, except for the first row (with error)...


(2) I do not understand your formula (that I can see if I do not enable editing).

Could you please help me with this? sorry to be so bad in excel...

Many thanks

 

[kekko]

Peter, I forget: what do you exactly mean with "It is a 365 insider solution, so some refactoring would likely be needed."?

Thanks again

 

[p45cal]

Could you do a manual calculation to show us for certain the values you're expecting?

 

[Peter Bartholomew]

Excel 365 is a subscription based version of Excel that is updated far more regularly than traditional Excel with its 3 yearly update cycle. The insider version is something one signs up for in order to receive beta test functionality on a weekly basis. The function VSTACK is such a function and I used it to append a 'fair value' to the bottom of a column of investments. Similarly I used it to add a date (I made it one day after the investment date).
The #NAME? error is because you do not have the functions I have used. It shows in the first cell only because a dynamic array formula is defined in a single cell but displays the results over adjacent cells.

I am a retired aeronautics research engineer, so lack the background to fully understand the usage of the finance functions; so really my question was, "is this the sort of calculation you were looking for?" Also, what version of Excel are you using? If it is 365, then I can probably get something to work; if it is legacy Excel, it will have to be someone else. Either way, defining the problem more closely (ideally with some hand-calculated results) would help.

 

[Peter Bartholomew]

For any 365 users, this was the formula I used

= MAP(date, investment, fairValues,
     LAMBDA(d,inv,f,
       LET(
         dateRng,  initDate:d,
         dates,    VSTACK(dateRng,d+1),
         valueRng, initInvest:inv,
         values,   VSTACK(valueRng,f),
         XIRR(values,dates)
       )
     )
   )

The key points of interest are that MAP returns cell references, which are then used to build ranges of preceding dates and investments. VSTACK is used to append the the current date and valuation to the lists, though SEQUENCE/INDEX might provide an alternative approach. Whether the calculation itself is what is required is not for me to judge.

 

[Peter Bartholomew]

@p45cal
The attached has one of the possible calculations as a helper range for checking by the OP.

 

[Chihiro]

Using DAX.

1. Load table to PQ. Then unpivot value columns.
2. Add following DAX Measure.

IRR :=
VAR date_Max =
    MAX ( Table1[date] )
VAR neg_Flow =
    FILTER (
        ALL ( Table1 ),
        Table1[Attribute] = "investment (cashouts)"
            && Table1[date] <= date_Max
    )
VAR pos_Flow =
    FILTER ( Table1, Table1[Attribute] = "Fair Values" && Table1[date] = date_Max )
VAR temp_Table =
    UNION ( neg_Flow, pos_Flow )
RETURN
    IFERROR ( XIRR ( temp_Table, [Value], [date], 0.0 ), 0 )

Use pivot table to show result.


EDIT: You can adjust guess value argument of XIRR as needed.

 

[Chihiro]

@Peter Bartholomew
I just realized, MAP, LAMBDA, LET all made it into Current Release (Version 2204, build 15128.20224).
Now, I should start experimenting with them again.

I'm hoping VSTACK comes to Current Release soon as well. Using TEXTJOIN and FILTERXML is not ideal to generate union of values.

 

[kekko]

Excel 365 is a subscription based version of Excel that is updated far more regularly than traditional Excel with its 3 yearly update cycle. The insider version is something one signs up for in order to receive beta test functionality on a weekly basis. The function VSTACK is such a function and I used it to append a 'fair value' to the bottom of a column of investments. Similarly I used it to add a date (I made it one day after the investment date).
The #NAME? error is because you do not have the functions I have used. It shows in the first cell only because a dynamic array formula is defined in a single cell but displays the results over adjacent cells.

I am a retired aeronautics research engineer, so lack the background to fully understand the usage of the finance functions; so really my question was, "is this the sort of calculation you were looking for?" Also, what version of Excel are you using? If it is 365, then I can probably get something to work; if it is legacy Excel, it will have to be someone else. Either way, defining the problem more closely (ideally with some hand-calculated results) would help.

Thanks Peter, yes I use Office 365 but not the insider version. Please find enclosed the file with manual calculation (resumed in column E)



Thi is exactly the calculation I was looking for: infact, as you can see, the manually calculated results are the same of yours (the only small difference is that for fair value I've considered the same date of the last cash out).
Is there any more simple formula to use with the standard Office 365? Hopefully something more easy to understand for me

 

[kekko]

Could you do a manual calculation to show us for certain the values you're expecting?

Hi p45cal, yes, please find attached the manually calculated IRR (see column E). Thanks

 

[Peter Bartholomew]

@Chihiro. The good thing about Lambda functions is that you can 'roll your own'. Good as the array shaping and text functions may be, it is also quite possible to replace VSTACK by a more limited Appendλ function.

= LAMBDA(arr,val,
     LET(
        n, ROWS(arr),
        k, SEQUENCE(n+1),
        IF(k<=n,arr,val)
     )
  )

When I get bored with Lambda functions, I see I will have to try to master DAX. I found your formula and understood it; authoring such a formula would be entirely another matter! At the moment 'M' is a bit of a stretch for me.

 

[p45cal]

Is there any more simple formula to use with the standard Office 365?

This will be anathema to Peter! To get a standalone formula for Excel 365 (outsider) (no Names) using Peter's lambda, in cell D2:

=XIRR(LAMBDA(arr,val,LET(n, ROWS(arr),k, SEQUENCE(n+1),IF(k<=n,arr,val)))(B$2:B2,C2),LAMBDA(arr,val,LET(n, ROWS(arr),k, SEQUENCE(n+1),IF(k<=n,arr,val)))(A$2:A2,A2))

copy down.
Using the same lambda in a Name (I called it Stuff (should have called it Appendλ)), the formula becomes:

=XIRR(stuff(B$2:B2,C2),stuff(A$2:A2,A2))

also copied down.
See attached in columns D and F.

 

[Chihiro]

Just for the hell of it. Single LET version using FILTERXML.

=LET(dates,FILTERXML("<a><d>"&TEXTJOIN("</d><d>",TRUE,$A$2:A2)&"</d><d>"&A2&"</d>"&"<v>"&TEXTJOIN("</v><v>",TRUE,$B$2:B2)&"</v><v>"&C2&"</v></a>","//d"),vals,FILTERXML("<a><d>"&TEXTJOIN("</d><d>",TRUE,$A$2:A2)&"</d><d>"&A2&"</d>"&"<v>"&TEXTJOIN("</v><v>",TRUE,$B$2:B2)&"</v><v>"&C2&"</v></a>","//v"),XIRR(vals,dates))

 

[kekko]

Peter, Chihiro y P45cal, sorry for the delay but thank you very much for all for your replies! They were all really helpful, and very much appreciated; I finally used the last one from Chihiro, as it seems the one that works with my excel version.

 

[bosco_yip]

Peter, Chihiro y P45cal, sorry for the delay but thank you very much for all for your replies! They were all really helpful, and very much appreciated; I finally used the last one from Chihiro, as it seems the one that works with my excel version.

Or,

You could using Filterxml function only without Let function

In D2, formula copied down :

=XIRR(FILTERXML("<a><v>"&TEXTJOIN("</v><v>",,B$2:B2,C2)&"</v></a>","//v"),FILTERXML("<a><d>"&TEXTJOIN("</d><d>",,A$2:A2,A2)&"</d></a>","//d"))



Regards

 

[kekko]

Hi guys, here I am with another similar question related to the same file and topic: how can I implement the XIRR formula direcly in a pivot table?
What I'm trying to do is to create a dashboard, where with a slicer it's possible to select only part of the investements (i.e. funds 1 and 3 of the attached file).
So, while thanks to your help I was able to calculate the IRR for each investement at every moment, and I was able to do so for the whole pack of investments consolidating data with a query, what I need now is to represent in a pivot chart the IRR calculted at every reference date for the selected investments (throuhg a slicer). All of this, considering that in the consolidated table, the same date could appear for two or more cashflows, so in these cases, the instances of that dates should all present tha same same IRR.

Many thanks in advance!
Francesco

 

[vletm]

kekko
As You've read from

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Start a new post every time you ask a question, even if the theme is similar.

 

[kekko]

Ok

kekko
As You've read from

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Start a new post every time you ask a question, even if the theme is similar.

Ok, I'll do it vletm.
Thanks

 

[kekko]

Using DAX.

1. Load table to PQ. Then unpivot value columns.
2. Add following DAX Measure.

IRR :=
VAR date_Max =
    MAX ( Table1[date] )
VAR neg_Flow =
    FILTER (
        ALL ( Table1 ),
        Table1[Attribute] = "investment (cashouts)"
            && Table1[date] <= date_Max
    )
VAR pos_Flow =
    FILTER ( Table1, Table1[Attribute] = "Fair Values" && Table1[date] = date_Max )
VAR temp_Table =
    UNION ( neg_Flow, pos_Flow )
RETURN
    IFERROR ( XIRR ( temp_Table, [Value], [date], 0.0 ), 0 )

Use pivot table to show result.
View attachment 79006

EDIT: You can adjust guess value argument of XIRR as needed.

Hi Chihiro,
I tried using your solution, as I want the IRR to be dinamically calculated in a pivot in order to be able to select the funds on which apply the calculation with a slicer. I downloaded your file, trying replicating it on my file (applying on a table resulting from a query made with PQ), but I cannot get your results, as the IRR is 0% all the time. Please see the attached file.

Could you please help me with this? I would be very appreciated!

Many thanks,
Francesco

 

[vletm]

kekko
You wrote #22 reply: Ok, I'll do it
This thread is closed now.