XIRR - Rolling Portfolio Performance Calculation

[Kaulson]

Hi Folks,

Great forum! This is my first post, but I've used this resource in the past. Here's an issue I haven't been able to solve with the usual googling / forum lurking:

I have ~monthly portfolio snapshots that include total portfolio value, contributions, and the date. It's a bit of a trouble to arrange the data to use XIRR as the XIRR function wants one array of values and one of dates. I'm not able to figure out how to use Choose() or another function to aggregate the arrays. I cannot use IRR because the data isn't necessarily in equal periods of time.

GOAL: I would like to keep entering monthly data and have a cell in each monthly column that calculates the portfolio rate of return for the year through that month (or some other period)

I've created a very simple example. The top ~half of the spreadsheet replicates my data. The yellow cells represent where I'd like to add a formula to calculate return.

The purple cells are what I have to do to calculate using XIRR which is in the green cells. Unfortunately, this work-around isn't really sustainable as I have much more data than this simplistic spreadsheet.

Would love someone to point me the right direction on this. Do I need to educate myself more about the array functions, would that solve this?

Thanks in advance!

Keith

 

[bosco_yip]

Try,
In D8, array formula (ctrl+Shift+Enter) copied across right :

=XIRR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,$C5+$C6,$D6:D6,-D5)&"</b></a>","//b"),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,0+TEXT($C4:D4,"mm/yy"),0+TEXT(D4,"mm/yy"))&"</b></a>","//b"),3%)

 

[Peter Bartholomew]

To declare an interest. I am interested in developing dynamic array solutions that employ defined names or LET; that is Excel 365 only!

p.s. I fully accept that this looks nothing like Excel as we know it!

 

[Kaulson]

@bosco_yip - Thanks so much! This was incredibly helpful and elegant, I need to spend some more time parsing through it, as I'm not completely clear on the mechanisms yet.

One slight issue I noticed when using this across years and with dates late in the month: Because the formula uses "mm/yy", there's some weird stuff that happens that might be related to the regional date settings. I changed this to "mm/dd/yy" in both places in the formula, which appears to resolve those problems, at least for my regional settings (North America). I believe this improves the accuracy, as the day of the month the contributions are made has a small but material impact on the rate of return.

Here's the revised formula for D8:

=XIRR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,$C5+$C6,$D6:D6,-D5)&"</b></a>","//b"),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,0+TEXT($C4:D4,"mm/dd/yy"),0+TEXT(D4,"mm/dd/yy"))&"</b></a>","//b"),3%)

 

[bosco_yip]

......... I changed this to "mm/dd/yy" .................

Hi,

I think this part of your changes "…...0+TEXT($C4:D4,"mm/dd/yy"),0+TEXT(D4,"mm/dd/yy")…..." which is equal to "…...$C4:D4,D4…..." and should not meet with your specification.

So, try to use post #3 Peter's idea, adopt the EOMONTH function in replacement,

Then,

the array formula become :

=XIRR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,$C5+$C6,$D6:D6,-D5)&"</b></a>","//b"),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,1+EOMONTH(+$C4:D4,-1),1+EOMONTH(+D4,-1))&"</b></a>","//b"),3%)

Edit :

Maybe, try this type of date format for USA regional settings

=XIRR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,$C5+$C6,$D6:D6,-D5)&"</b></a>","//b"),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,0+TEXT($C4:D4,"yy/mm"),0+TEXT(D4,"yy/mm"))&"</b></a>","//b"),3%)


Regards