I enjoyed and learned from your video "A look at Excel's XIRR function" although I got to it a bit late (April 2016), especially the Newton derivation. (I would have liked to see the actual formulas for f(x) and f'(x)). All this just to say that the Google Sheet (free online version) computes XIRR correctly. Thank you for the video
In my previous comment I should said I used the actual XNPV expression along with goal seek. This way I can now find solutions for 250+ entries without NR. The XNPV expression is a simple sum and date order should not matter. Wonder why the XNPV function depends on date order!
Thanks for this useful video. It has helped to clarify some issues I was encountering. My financial institution recommends using the Money Weighted Method to track the investment returns. I have been using the XIRR function for this purpose. I however started to question its accuracy when some of my accounts had negative cash flows and the XIRR function was returning ridiculously high returns. As others have stated, it does not appear that the XIRR function is reliable. Is it advisable to use it in cases where there are no negative cash flows?
Thanks for your response. Can you explain what you mean by false zeros? Using XNPV and finding the rate(s) that make it zero will remove dependence on order of data. Wonder if Excel does this? I used the XNPV + GoalSeek instead of NR.
I used XIRR to calculate my investment returns over the past year. I had five deposits and one withdrawal at irregular intervals. Excel said my return for the year was 21.5%, while my broker (Interactive Brokers) said it was 20.92%. I thought this was strange which is why I'm here and more confused than ever.
As far as I recall, IRR is not defined if there is more than one switch from negative to positive in the cash flows. Your examples have more than one switch, hence the results are unreliable.
what do you recommend for a simple investment return formula? i.e. $100,000 investment in the stock market in jul of 2014. Add $30,000 every 6 months. 7/2014 $100,000 1/2015 $30,000 7/2015 214,000 (need to know rate of return at this time period) 1/2016 $30,000 7/20/16 $326,000 (need to know rate of return at this time period)
Been using this software for past few years - and would not recommend it to anyone. There are a lot more reliable and less expensive systems available!
I enjoyed and learned from your video "A look at Excel's XIRR function" although I got to it a bit late (April 2016), especially the Newton derivation. (I would have liked to see the actual formulas for f(x) and f'(x)). All this just to say that the Google Sheet (free online version) computes XIRR correctly. Thank you for the video
In my previous comment I should said I used the actual XNPV expression along with goal seek. This way I can now find solutions for 250+ entries without NR. The XNPV expression is a simple sum and date order should not matter. Wonder why the XNPV function depends on date order!
I am watching this in 2024
Thanks for this useful video. It has helped to clarify some issues I was encountering. My financial institution recommends using the Money Weighted Method to track the investment returns. I have been using the XIRR function for this purpose. I however started to question its accuracy when some of my accounts had negative cash flows and the XIRR function was returning ridiculously high returns. As others have stated, it does not appear that the XIRR function is reliable. Is it advisable to use it in cases where there are no negative cash flows?
Thanks for your response. Can you explain what you mean by false zeros? Using XNPV and finding the rate(s) that make it zero will remove dependence on order of data. Wonder if Excel does this? I used the XNPV + GoalSeek instead of NR.
I used XIRR to calculate my investment returns over the past year. I had five deposits and one withdrawal at irregular intervals. Excel said my return for the year was 21.5%, while my broker (Interactive Brokers) said it was 20.92%. I thought this was strange which is why I'm here and more confused than ever.
Extremely confused lol
Thanks. I understand this. As long it doesn't give a wrong answer I will assume #Num! is a non solution.
Great, so how can you get an accurate irr in excel when the dates are random and not 91 days apart?
his dates are not random though lol
Hello, I need to confirm the IRR and XIRR on a project I'm working on and would like to know if you would provide me with that service.
Sure. Send an e-mail to cflock@westclintech.com
Very useful video! So XIRR looks not reliable.
Sir I'm doing same. But I'm getting #num error. Pls help me what went wrong
I was able to replicate the errors in my 2007 Excel but Google Docs renders the correct results using the same data and formulas.
As far as I recall, IRR is not defined if there is more than one switch from negative to positive in the cash flows. Your examples have more than one switch, hence the results are unreliable.
0167 Karianne Gateway
lol why are we fussing about IRR and XIRR with non-conventional cash flow though
which part of the cash flow is unvonentional? Do you mean because it begins with negative value?
what do you recommend for a simple investment return formula?
i.e. $100,000 investment in the stock market in jul of 2014. Add $30,000 every 6 months.
7/2014 $100,000
1/2015 $30,000
7/2015 214,000 (need to know rate of return at this time period)
1/2016 $30,000
7/20/16 $326,000 (need to know rate of return at this time period)
Been using this software for past few years - and would not recommend it to anyone. There are a lot more reliable and less expensive systems available!
+investor -- Like? Otherwise your comment is pointless.