Hi
In the sample sheet, Column J3,J4 and J5 I am using XIRR which is just XIRR=(values, dates) with matching criteria. Not sure why it is giving #Value Error
Hi
In the sample sheet, Column J3,J4 and J5 I am using XIRR which is just XIRR=(values, dates) with matching criteria. Not sure why it is giving #Value Error
Since you did not respond to my comments in a similar thread in another forum (click here, user joeu2004), I am relunctant to contribute here. So I'll try to make my comments brief.
1. You get a #VALUE error because it appears that you did not array-enter the formula (press ctrl+shift+Enter instead of just Enter).
So you have an implicit intersection operation, but the row where the formula is entered (row 3) is not within the referenced ranges (rows 11:56).
Of course, that is not you intent, in the first place.
2. After you correct #1, you get a #VALUE error because the formula references H40:H46 in a numeric expression, but those formulas return the null string ("").
3. After you correct #1 and #2, your formula displays 5248.25% (absurd) because you multiply XIRR by 100, but format the result as Percentage.
4. In J4:J5, the corrected formula displays 0%. The actual value is 2.98E-09 (rounded).
That is misleading. You can use SUMPRODUCT (*) to calculate the NPV and confirm that 2.98E-09 is not a valid IRR because the NPV is not (nearly) zero.
As I explained in my response in the other forum, 2.98E-09 should be treated as an error; interpret it the same as #NUM.
XIRR (and XNPV?) does not tolerate empty cells (and zero) in the beginning of the cash flow series -- for no good reason, IMHO.
(*) The SUMPRODUCT alternative to XNPV is as follows (row 4), based on the math formula in the XIRR help page:
It can be entered normally (just press Enter, as usual). The reference to E12 is a kludge; but it works for row 4 and 5.Please Login or Register to view this content.
-----
A word to the wise....
1. Your method of calculating the individual CAGRs is debatable.
You are calculating the rate of return of the sum of the current valuations from the date of the earliest investment.
I believe the correct calculation is the TWR.
2. Your method of calculating the overall average XIRR and CAGR is debatable.
You are calculating the simple average of the individual averages. In general, "the average of partial averages does not equal the average of the whole".
If your intent is to calculate portfolio rates of return, the usual calculation is the weighted average based on the investment ("allocation"), not current valuation.
(But technically, that might not be mathematically correct, either.)
3. Technically, your YEARFRAC formulas in column N are incorrect.
You are calculating YEARFRAC(endDate, startDate).
The correct usage is YEARFRAC(startDate, EndDate).
You are lucky that YEARFRAC is so forgiving. Most functions would return an error.
4. It is usually misleading to annualize sub-annual rates of return.
For example, a 1% daily gain is a compounded annualized rate of return of 3678% (absurd). Even a simple annualized rate of return of 365% is misleading.
That is why most brokerages report a simple rate of return over the period if the period is sub-annual.
Last edited by curiouscat408; 03-04-2023 at 08:13 PM. Reason: Item #4
Can you please suggest TWR formula re placing XIRR
Definitely, your suggestion is more valuable
Last edited by grcshekar; 03-05-2023 at 12:51 AM.
"XIRR (and XNPV?) does not tolerate empty cells (and zero) in the beginning of the cash flow series -- for no good reason, IMHO."
As a kind of reaction on that statement
L3:L5 = IRR on an excel 365 (with Filter),sorry for your 2010
J3:J5 = IRR on an older system with auxiliary cells starting in P
PS. i have no financial skills
Last edited by bsalv; 03-05-2023 at 01:23 AM.
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Both showing value error
L3:L5 are errors because you don't have Excel 365.
the formulas in P3:AV5, do they show errors ?
Aggregate is a excel-function since 2010, so it should be okay with your version.
Can you kindly replace my formula in Column J itself
Welcome (back) to the forum.
Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...
But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.
Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:
1) if you have cross-posted, and
2) the URL of the cross posts.
That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.
(Note: this requirement is not optional. No help to be offered until the link(s) is/are provided.)
Is it cross-posted anywhere else?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks