• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

XIRR function considering data from different columns

Status
Not open for further replies.

kekko

New Member
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
 

Attachments

Chihiro

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

New Member
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.
 

kekko

New Member
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!o_O:eek:but I have two problems:

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

(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

New Member
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

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

Peter Bartholomew

Well-Known Member
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

Well-Known Member
For any 365 users, this was the formula I used
Code:
= 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.
 

Chihiro

Excel Ninja
Using DAX.

1. Load table to PQ. Then unpivot value columns.
2. Add following DAX Measure.
Code:
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.
79006

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

Attachments

Last edited:

Chihiro

Excel Ninja
@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

New Member
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)

79010

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 :)
 

Attachments

Peter Bartholomew

Well-Known Member
@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.
Code:
= 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.
 

Attachments

p45cal

Well-Known Member
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:
Code:
=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:
Code:
=XIRR(stuff(B$2:B2,C2),stuff(A$2:A2,A2))
also copied down.
See attached in columns D and F.
 

Attachments

Chihiro

Excel Ninja
Just for the hell of it. Single LET version using FILTERXML.
Code:
=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

New Member
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

Excel Ninja
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"))

79386

Regards
 
Last edited:

kekko

New Member
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
 

Attachments

vletm

Excel Ninja
kekko
As You've read from
  • Start a new post every time you ask a question, even if the theme is similar.
 

kekko

New Member
Ok
kekko
As You've read from
  • Start a new post every time you ask a question, even if the theme is similar.
Ok, I'll do it vletm.
Thanks
 

kekko

New Member
Using DAX.

1. Load table to PQ. Then unpivot value columns.
2. Add following DAX Measure.
Code:
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
 

Attachments

Status
Not open for further replies.
Top