Calculating XIRR on certain entries in a list

[GroovyDroovy]

Hello,

Please refer to attached excel for samples/reference.

I am trying to create a file where I can enter a series of cash flows for two projects as one intertwined list and be able to calculate the XIRR for both of them separately.

The end goal would be for me to have a long list of stock transactions but be able to calculate the XIRR for each stock as a dashboard.

In the attached excel:

• Column B:D - show Security A's dates and transactions.
  • In cell D15, I've calculated XIRR using normal function
• Column F:H - show Security B's dates and transaction
  • In cell H12, I've calculated XIRR using normal function
  • In cell H15, I started testing our nesting IF functions and XIRR and it seems to calculate correct
  • Note: Array entered. CTRL+SHIFT+ENTER
• Column J:L - combined data on both securities
  • In Cell K17, I nested IF and XIRR function is working correctly for security A
  • However, in Cell K18, it is not working for Security B
  • In addition, if you change cell L6 from A->B, the calculation happens correctly for B but not A
    • Basically, it seems to only work for the security listed on top

Hopefully this makes sense and someone can help me figure this out. Please let me know if you need more info.

Thanks!!

 

[Haz]

The first value from your cash flows have to be negative, when you check against B the first value (A) is set to 0, giving wrong results. The reverse happens when you change L6 to B.
Instead of using 0 in your IF, try to use an extremely small negative number (-10^-99)

 

[Chihiro]

Alternately if you have PowerPivot.

You can add measure like...

Rate of Return A:=XIRR(Filter(Table1,[Type]="A"),[Payment],[Date])

However, in your sample file, in the combined range, you are missing 3/9/2015, -200 for B (added in above image).

Edit: Ah, I had missed the drop down to change A/B. In that case, I'd probably pass on the selection as variable to PowerQuery and load as separate table for PowerPivot. Bit more complicated than using small negative.

 

[GroovyDroovy]

Hi Chihiro - Thanks for getting back. I unfortunately don't have PowerPivot. However, it seems that changing the 0 to a small negative number is working.

Really appreciate the guidance!!

 

[GroovyDroovy]

Hi Haz,

That worked...thank you! I even added a third security randomly and got it to work.

Thoughts on why the 0 vs the -'ve number solves it?

 

[Chihiro]

It's because of this. From MS document.
XIRR(values, dates, [guess])

Values Required. A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive and one negative value.

 

[GroovyDroovy]

That part makes sense to me in terms of how the cash flows need to be organized. Gotta start negative and have at least one pay out for XIRR to be calculable.

I'm curious about the the suggestion to change the 0 in the IF statement to a negative number instead. How is that fixing the error?

Thank you!

 

[Chihiro]

Because array starts with 0 if first cell is false. By replacing with small negative, you avoid this issue.

 

[Haz]

When you check IF(L6:L17 = "B") and the first value is A you get your results as:
{0, -2000, -3600, 0, -200, 0, 0, 2200, 0, -800, 7500, 0}
Calculating XIRR with a starting value of 0 gives you a incredible small number, that Excel shows as 0.

Changing 0 to -0.00000...1 gives you a starting number that is close to 0 but negative.

 

[GroovyDroovy]

Got it...thanks for the lesson!

 

[Haz]

You could also use INDEX select just the As and Bs and calculate without having to use this trick, but my formula isn't pretty.
There's probably a formula that's does it better in newer versions of Excel, but I'm using 2007.

=XIRR(INDEX($K$6:$K$16,N(IF(1,SMALL(IF($L$6:$L$16=J17,ROW($A$1:INDEX(A:A,COUNTA($L$6:$L$16))),9^9),ROW($A$1:INDEX(A:A,COUNTIF($L$6:$L$16,J17))))))),INDEX($J$6:$J$16,N(IF(1,SMALL(IF($L$6:$L$16=J17,ROW($A$1:INDEX(A:A,COUNTA($L$6:$L$16))),9^9),ROW($A$1:INDEX(A:A,COUNTIF($L$6:$L$16,J17))))))))

 

[anonymguy]

Hello,

read through this post and at least took comfort that I was not the only one having problems with this :/ I tried looking through the solutions discussed, but am already having issues seeing how they would work in the context of my particular spreadsheet design as the current Net Asset Value is on a different sheet from teh trades. I tried recreating a Google Sheets workbook in Excel but am getting stuck on the XIRR calculation.

Google Sheets example: https://docs.google.com/spreadsheets/d/17kl9l5S90RHlLvotd26j8c_wcpG2lKuIBZthDMcIvAE/edit#gid=0
Formula: =XIRR(STITCH(ARRAYFORMULA(IF(tblTradesAJB[Ticker]=[@Ticker],tblTradesAJB[Cashflow],0)),[@[NAV GBP]]),STITCH(tblTradesAJB[Date],TODAY()))

The STITCH formula allows to merge the contextual array with the NAV from the other sheet as well as the current date to the date list.

I am not sure how to do this in Excel (or if it is even possible). Have tried recreating the workbook (attached) but have no idea how to get XIRR to calculate. I tried working through the example above and got as far as (results in a #N/A):
=XIRR(INDEX(tblTradesAJB[Cashflow],N(IF(1,SMALL(IF(tblTradesAJB[Ticker]=[@Ticker],ROW($A$1:INDEX(A:A,COUNTA(tblTradesAJB[Ticker]))),9^9),ROW($A$1:INDEX(A:A,COUNTIF(tblTradesAJB[Ticker],[@Ticker]))))))),INDEX(tblTradesAJB[Date],N(IF(1,SMALL(IF(tblTradesAJB[Ticker]=[@Ticker],ROW($A$1:INDEX(A:A,COUNTA(tblTradesAJB[Ticker]))),9^9),ROW($A$1:INDEX(A:A,COUNTIF(tblTradesAJB[Ticker],[@Ticker]))))))))

Any help would be greatly appreciated (and save the little hair I have left on my head).

 

[vletm]

anonymguy
You should open a new thread.

 

[Somendra Misra]

Hi Chihiro - Thanks for getting back. I unfortunately don't have PowerPivot. However, it seems that changing the 0 to a small negative number is working.

Really appreciate the guidance!!

Hi,

I would also suggest you to download Power Pivot, it is available for Excel 2013 and above, and for Excel 2010, use below link to download it. It's free and quite useful.

https://www.microsoft.com/en-in/download/details.aspx?id=102

Alternatively, if you keep security data separate, you can use below simple formula to get result

For Security A --> =XIRR(INDEX(($B$6:$D$11,$F$6:$H$11),,3,1),INDEX(($B$6:$D$11,$F$6:$H$11),,1,1),0.2)
For Security B --> =XIRR(INDEX(($B$6:$D$11,$F$6:$H$11),,3,2),INDEX(($B$6:$D$11,$F$6:$H$11),,1,2),0.2)

Regards,

 

[anonymguy]

Hi,

I would also suggest you to download Power Pivot, it is available for Excel 2013 and above, and for Excel 2010, use below link to download it. It's free and quite useful.

https://www.microsoft.com/en-in/download/details.aspx?id=102

Alternatively, if you keep security data separate, you can use below simple formula to get result

For Security A --> =XIRR(INDEX(($B$6:$D$11,$F$6:$H$11),,3,1),INDEX(($B$6:$D$11,$F$6:$H$11),,1,1),0.2)
For Security B --> =XIRR(INDEX(($B$6:$D$11,$F$6:$H$11),,3,2),INDEX(($B$6:$D$11,$F$6:$H$11),,1,2),0.2)

Regards,

Thank you Somendra Misra. I would rather do via formula if possible so that it is more universal than relying on Power Pivot. I don't think the formulas you provided would work in my case as the trades for Security A and Security B would be in the same table and mixed together (so not in different areas). Further, there is some additional complexity around getting an estimated closing value for the XIRR calculation. And finally, I will eventually have N securities which would make for a lot of tables and copying of formulas.

As suggested by vletm, I have opened a new thread here: https://chandoo.org/forum/threads/xirr-challenge.44435/ where I probably explained the situation a little better.