• 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 Challenge

A

anonymguy

New Member
Hello,

I am trying to calculate the XIRR for a set of stocks in my portfolio. I would like the XIRR to be calculated and shown in a summary table where I can also see the Net Asset Value (NAV) as of today. The trades are stored in a separate table where I update as I add a new position to my holdings. Both the stocks themselves and the trades will be constantly expanding and changing so I would like the formula to take this into account. This also makes it not feasible to have the "closing balance" stored at the end of the table to allow the XIRR to calculate properly. Attached is a spreadsheet which shows what I am trying to achieve. This is a follow-on from https://chandoo.org/forum/threads/calculating-xirr-on-certain-entries-in-a-list.39636/

Ideally, I would like to append a value to an array of calculated values, something like: {values calculated by a formula; value I reference from another cell}

Thank you Haz and Somendra Misra for your help so far.
 

Attachments

  • XIRR Challenge 2.xlsx
    18.4 KB · Views: 51
Hi,

Excel 2013 and later, array formula**:

=XIRR(IF(INDEX(1-MUNIT(1+COUNTIF(TBL_TRADES[Ticker],[@Ticker])),,1+COUNTIF(TBL_TRADES[Ticker],[@Ticker])),INDEX(TBL_TRADES[Transaction Value],N(IF(1,MODE.MULT(IF(TBL_TRADES[Ticker]=[@Ticker],ROW(TBL_TRADES[Ticker])-MIN(ROW(TBL_TRADES[Ticker]))+{1,1}))))),-[@[NAV TODAY]]),IF(INDEX(1-MUNIT(1+COUNTIF(TBL_TRADES[Ticker],[@Ticker])),,1+COUNTIF(TBL_TRADES[Ticker],[@Ticker])),INDEX(TBL_TRADES[Date],N(IF(1,MODE.MULT(IF(TBL_TRADES[Ticker]=[@Ticker],ROW(TBL_TRADES[Ticker])-MIN(ROW(TBL_TRADES[Ticker]))+{1,1}))))),TODAY()))

Regards
 
Hi,

If you have the latest Excel version, the task is quite easy in that with formulas, otherwise single formula will be quite complicated as suggested above by @XOR LX

Regards,
 
Somendra Misra said:
Hi,

If you have the latest Excel version, the task is quite easy in that with formulas, otherwise single formula will be quite complicated as suggested above by @XOR LX

Regards,
Click to expand...

Indeed, the formulas are getting quite complex (thank you @XOR LX ). I installed Power Pivot but am just as stuck as I was before (sorry if I am being thick here, but I do not really understand Power Pivot or how a Pivot Table will help me here).

In the end, I ended up creating 2 new tables using Queries & Connections. I added a new column cell for Last Price in TBL_SUM then appended it to TBL_Trades which I saved as TBL_MASTER and will hide in my workbook (I changed a few of the column names so they would append).

I then use:

=XIRR(INDEX(TBL_MASTER[Transaction Value],SMALL(IF(TBL_MASTER[Ticker]=[@Ticker],ROW($A$1:INDEX(A:A,COUNTA(TBL_MASTER[Ticker]))),9^9),ROW($A$1:INDEX(A:A,COUNTIF(TBL_MASTER[Ticker],[@Ticker]))))),INDEX(TBL_MASTER[Date],SMALL(IF(TBL_MASTER[Ticker]=[@Ticker],ROW($A$1:INDEX(A:A,COUNTA(TBL_MASTER[Ticker]))),9^9),ROW($A$1:INDEX(A:A,COUNTIF(TBL_MASTER[Ticker],[@Ticker]))))))

It is not elegant, but it works. I feel there must be an easier way. I was intrigued by @Chihiro 's formula from the other thread: =XIRR(Filter(TBL_MASTER,[Ticker]=[@Ticker]),[Transaction Value],[Date]) but that does not seem to work. Most probably because I do not realy understand how to use Power Pivot.
 
It works for me using the workbook provided. Perhaps you've attempted to adapt it to a different workbook? If so, I recommend that you post it.

Regards
 
Hei folks, I Got this tip at another forum - loved it. Short and efficient - please advise me if that worked for you too!

=XIRR(IF(1-FREQUENCY(9^9,(TBL_TRADES[Transaction Value]*IF(TBL_TRADES[Ticker]=[@Ticker],1,0))),(TBL_TRADES[Transaction Value]*IF(TBL_TRADES[Ticker]=[@Ticker],1,0)),-[@[NAV TODAY]]),IF(1-FREQUENCY(9^9,TBL_TRADES[Date]),TBL_TRADES[Date],TODAY()))

the trick is that Frequency function returns an array that has one more element than the input array , so we can add the current NAV and today()
 
Last edited:
Not knowing anything about finance, I struggled through refactoring the calculation using MS365 beta

Code: 
= LET(
  filteredDate, FILTER(Date, Ticker=[@Ticker]),
  filteredValue, FILTER(Transaction_Value, Ticker=[@Ticker]),
  XIRR(filteredValue,filteredDate))

Then I turned to the help page and read
"XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value"
which at least explained why I agreed with the #NUM! error value.

I am just thankful that I do not have to contend with the tools and techniques of traditional Excel!
 
It was getting late. I have returned to the formulas and found the FREQUENCY and MUNIT functions designed to augment the parameter list with the current valuations. For me, I have access to Charles William's FastExcel which, amongst other things, provides a function VSTACK.
Code: 
= LET(
    augmentedDate,
        VSTACK(
            FILTER(Date, Ticker=[@Ticker]),
            TODAY()),
    augmentedValue,
        VSTACK(
            FILTER(Transaction_Value, Ticker=[@Ticker]),
            -[@[NAV TODAY]]),
    XIRR(augmentedValue, augmentedDate) )
All very nice for me but not much use to others.

The obvious way to augment the parameter lists using dynamic arrays is to generate an index array and use the final value as a test
Code: 
= SEQUENCE(1+transactionCount) = 1+transactionCount

In use, this becomes
Code: 
= LET(
      filteredDate, FILTER(Date, Ticker=[@Ticker]),
       filteredValue, FILTER(Transaction_Value, Ticker=[@Ticker]),
       transactionCount, ROWS(filteredValue),
       current?, SEQUENCE(1+transactionCount) = 1+transactionCount,
       augmentedDate, IF(current?, TODAY(), filteredDate ),
       augmentedValues, IF(current?, -[@[NAV TODAY]], filteredValue),
       IRR, XIRR(augmentedValues, augmentedDate),
      IRR )

It is not essential to use LET. The earlier formulas can either be nested within later formulas in place of the variable that was assigned to them or a defined Name can be used to hold the formula. The latter strategy tends to proliferate Names but works well enough. I used it in the past to evade the implicit intersection that were inserted to grid formulas by traditional Excel.
 
Hi Peter!
The solution seems to be perfct!!
The only thing is that I am having a hard time to understand why the LET function is not available in my Excel (365). I am still waiting a Microsoft answer...
 
LET is a beta release function that I hope will be entering service sometime over the coming year. To access it now, your Excel needs to be set to insider channel either by you, if the license is a personal one, or by company IT if it is a business version. To see your setup, you need to go to the file Ribbon Tab (backstage) and then look at your account settings.

It is possible to move the names from the LET formula to defined Names (either workbook scope or individual sheet). Typically, I prepare each formula on the worksheet grid and, once it checks out, I copy it to the 'Refers to' box under Name Manager. It is just more longwinded than having the formula visible on the sheet.

The attached performs the same calculation with workbook-scoped Names (I hope ;)).
 

Attachments

  • XIRR Challenge(PB).xlsx
    19.9 KB · Views: 7
Just in case LET does come your way!70401
Even an error trap for a missing ticker in the summary table!

p.s. Now I wish I had used NOT(found) in place of = 1- found
 

Attachments

  • XIRR with LET.xlsx
    18 KB · Views: 17
Last edited:
You must log in or register to reply here.
Back
Top