• 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

Status
Not open for further replies.

grcshekar

New Member
Hi,

I am trying to do XIRR computation in attached file.

1. I have put formula in I3.
2. This should not take empty cells into consideration
3. It should match C3 with C11:C52

How to change this formula to achieve this please
 

Attachments

  • PortfolioTracker.xlsx
    805 KB · Views: 10
Please explain what XIRR means and use meaningful titles so that we avoid wasting time opening threads we do not understand. thc
 
XIRR meaning in mutual fund is to calculate returns on investments where there are multiple transactions taking place in different times. Full form of XIRR is Extended Internal Rate of Return.
 
An example here

 
I am trying to do XIRR computation in attached file.
1. I have put formula in I3.
2. This should not take empty cells into consideration
3. It should match C3 with C11:C52

XIRR requires signed cash flows. The choice of signs is arbitrary. But you must use opposite signs for inflows and outflows.

XIRR tolerates empty cells in the middle and the end of the cash flows. It does not tolerate empty cells in the beginning.

But beware: XIRR returns a bogus numerical value, namely +/-2.98E-09, if there are empty cells in the beginning. That might look like zero due to cell formatting; but it is not. Threat that value as an error.

XIRR requires at least one negative value and one positive value. All of your values in G11:G26 are negative.

But they should be, because they are inflows. You are missing the "outflows" (current value) in the XIRR formulas, namely the values in I11:I26.

Just to test feasibility and to be sure we understand your requirements, copy G11:G26 and paste-value starting in N11, and copy I11:I26 and paste-value starting in N27. Similarly copy-and-paste-value corresponding dates (D11:D26 and J11:J26) into O11 and O27.

Then, note that =XIRR(N11:N42, O11:O42) formatted as Percentage displays -46.26% (rounded).

That is your "overall" XIRR in I6. But of course, we must implement it more cleverly, due to the way that you organized your data. TBD.

And I do understand that eventually, you want to calculate the XIRR for each fund. TBD.
 
From your spreadsheet, I did the calculations to the right of your table.
NOTE: This assumes you have the UNIQUE and FILTER functions available to you.

In cell P10: =TRANSPOSE(UNIQUE(FILTER(C11:C52,C11:C52<>"")))
In cell O11: =UNIQUE(FILTER(D11:D52,D11:D52<>""))

In cells P11:R15 (P11 is the example cell): =SUMPRODUCT(($C$11:$C$52=P$10)*($D$11:$D$52=$O11),($G$11:$G$52))

In cells P20:R20 (P20 is the example cell): =XIRR(INDEX(P11:p15,MATCH(TRUE,P11:p15<>0,0)):INDEX(P11:p15,ROWS(P11:p15)),INDEX($O11#,MATCH(TRUE,P11:p15<>0,0)):INDEX($O11#,ROWS(P11:p15)))

These are the XIRR results, but those are annualized. Since your investments are less than one year, you should un-annualize them:

P21:R21 (P21 is example cell): =(MAX($O11#)-INDEX($O11#,MATCH(TRUE,P11:p15<>0,0)))/365

The # after the range in O is the due to the dynamic array as a result of the UNIQUE/Filter functions.

P22:R22 (P22 is example cell): =(1+P20)^P21-1

This will give the unannualized results.

If you add a total column in S for instance, which is the SUM of the values in P through R, you can get a portfolio XIRR.

If you put the calculations to the left (for the total) and above, you can make it dynamic (as detailed, it is partially dynamic).


EDIT: The smiley emoji recognized in the forum as a colon and the letter P
Moderator Note: You could use ICODE - tags to avoid those ...
 
grcshekar
Please, reread and follow Forum Rules:
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Using Excel 365 I got numbers from
Code:
= MAP(reportFund, MyXIRR)

where MyXIRR(fund) is a Lambda function with the formula
= LET(
    fundDetails, FILTER(investmentDetails, investmentFund=fund),
    fundDates,   TOCOL(CHOOSECOLS(fundDetails, 1, 7),,1),
    fundValues,  TOCOL(CHOOSECOLS(fundDetails, 4, 6),,1),
    XIRR(fundValues, fundDates, -20%)
  )
83215

@The Village Idiot
Unlike me, you appear to know something about the implementation of XIRR. Is it simply a case of converting the resulting annual rate to a daily rate or is something more subtle involved?
 
Using Excel 365 I got numbers from
Code:
= MAP(reportFund, MyXIRR)

where MyXIRR(fund) is a Lambda function with the formula
= LET(
    fundDetails, FILTER(investmentDetails, investmentFund=fund),
    fundDates,   TOCOL(CHOOSECOLS(fundDetails, 1, 7),,1),
    fundValues,  TOCOL(CHOOSECOLS(fundDetails, 4, 6),,1),
    XIRR(fundValues, fundDates, -20%)
  )
View attachment 83215

@The Village Idiot
Unlike me, you appear to know something about the implementation of XIRR. Is it simply a case of converting the resulting annual rate to a daily rate or is something more subtle involved?

Very nice! That is clean. Well done. My numbers match yours.

For rates of return, the general convention is to provide the holding period return (not annualized) for periods of less than one year, and to annualize for periods greater than one year. The reasoning is that if you annualize for periods less than one year, you are projecting/implying growth rates into the future whereas for periods of greater than a year, you are basically reporting a one year average return over the [multi-year] period.

In this case, the results are over 6 days, so to infer a yearly return over six days is basically compounding 60 times, and is unlikely to come to fruition.

Since XIRR is always annualized, we have to find the 6 day return (geometric, not arithmetic). (1 + XIRR)^(days/days_in_year)-1.

You can then turn it to an average daily return, but that should be specified if you do so.
 
Very nice! That is clean. Well done. My numbers match yours.

For rates of return, the general convention is to provide the holding period return (not annualized) for periods of less than one year, and to annualize for periods greater than one year. The reasoning is that if you annualize for periods less than one year, you are projecting/implying growth rates into the future whereas for periods of greater than a year, you are basically reporting a one year average return over the [multi-year] period.

In this case, the results are over 6 days, so to infer a yearly return over six days is basically compounding 60 times, and is unlikely to come to fruition.

Since XIRR is always annualized, we have to find the 6 day return (geometric, not arithmetic). (1 + XIRR)^(days/days_in_year)-1.

You can then turn it to an average daily return, but that should be specified if you do so.
Thank you for such a clear explanation. I will remember that for future use.
 
Finally I got an example to be clearly explain what I need.

Example Sheet : XIRR Sample Sheet
My Sheet : PortfolioTracker

Only difference is that in My sheet I do not have asset class and subtype

Now how to write XIRR in I3, I4 and I5
 

Attachments

  • XIRR Sample Sheet.xlsx
    13.9 KB · Views: 7
  • PortfolioTracker.xlsx
    807.8 KB · Views: 6
I have looked at the sample sheet and do not get the same results. Could well be my error.
Code:
= LET(
    securityDetail,   TAKE(Table1,,3),
    distinctSecurity, UNIQUE(securityDetail),
    distinctIRR,      BYROW(distinctSecurity, myXIRR),
    HSTACK(distinctSecurity, distinctIRR)
  )
where
Code:
myXIRR(selectedSecurity)
= LET(
    securityDetail,   TAKE(Table1,,3),
    filterCriterion,  BYROW(securityDetail = selectedSecurity, LAMBDA(b, AND(b))),
    singleSecurity,   FILTER(Table1, filterCriterion),
    dates,            TOCOL(CHOOSECOLS(singleSecurity,4,6)),
    amounts,          TOCOL({-1,1}*CHOOSECOLS(singleSecurity,5,7)),
    XIRR(amounts, dates)
 )
83244
 

Attachments

  • XIRR Sample Sheet.xlsx
    21.1 KB · Views: 8
Status
Not open for further replies.
Back
Top