XIRR
- Thread starter grcshekar
- Start date
- Status
An example here
XIRR with IF condition
Could you please help me with the formula for calculating XIRR from a set of cash flows based on a particular identifier? To compute XIRR that fits the conditions, the formula should use the precise trend length. In the attached file, I computed XIRR by manually picking the trend length, and...
chandoo.org
XIRR is the name of an Excel function.
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.
The Village Idiot
New Member
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:
In cell O11:
In cells P11:R15 (P11 is the example cell):
In cells P20:R20 (P20 is the example cell):
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):
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):
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 ...
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)))/365The # 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-1This 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 ...
pecoflyer
Well-Known Member
grcshekar
Please, reread and follow Forum Rules:
Please, reread and follow Forum Rules:
Site Rules - New Users - Please Read
Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
- 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.
Peter Bartholomew
Well-Known Member
Using Excel 365 I got numbers from
@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?
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%)
)@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?
The Village Idiot
New Member
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.
Peter Bartholomew
Well-Known Member
Thank you for such a clear explanation. I will remember that for future use.
Peter Bartholomew
Well-Known Member
I have looked at the sample sheet and do not get the same results. Could well be my error.
where
Code:
= LET(
securityDetail, TAKE(Table1,,3),
distinctSecurity, UNIQUE(securityDetail),
distinctIRR, BYROW(distinctSecurity, myXIRR),
HSTACK(distinctSecurity, distinctIRR)
)
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)
)
@grcshekar
You have cross-posted again to another forum w/o providing links: XIRR modification to ignore some columns (excelforum.com)
As you've already been previously warned in this thread about this, and continued to break the rules, this thread will be closed. Please remember to follow all rules to avoid future incidents.
You have cross-posted again to another forum w/o providing links: XIRR modification to ignore some columns (excelforum.com)
As you've already been previously warned in this thread about this, and continued to break the rules, this thread will be closed. Please remember to follow all rules to avoid future incidents.
- Status