| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
XIRR
Hello,
I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#2
|
|||
|
|||
|
Hi
post the exact formula you have used and the values in the referenced cells -- Regards Frank Kabel Frankfurt, Germany Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#3
|
|||
|
|||
|
We are not mind readers. Give examples.
Jerry Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#4
|
|||
|
|||
|
Here is the Raw date one column dates (starting at b column, line 2 = dates
by month ), another one with one investments invest1 giving me an XIRR of -10.5%, the 2nd one Invest2 is giving me 0.0% (which is wrong), the respective fomulas are =XIRR(F36:IV36,F$2:IV$2) and =XIRR(E37:DG37,E$2:DG$2) Dates IRR Currency Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 Nov-07 Dec-07 Jan-08 Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08 Aug-08 Sep-08 Oct-08 Nov-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Dec-08 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Jan-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Feb-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Mar-09 Apr-09 Apr-09 Apr-09 Apr-09 Apr-09 Apr-09 Apr-09 Apr-09 Apr-09 Apr-09 -10.5% Invest1 Equivalent USD (0) (2,000,000) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1,000,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.0% Invest2 Equivalent USD (1,995,277) (2,000,000) - - - - 163,953 - (2,092,790) - - - - - - - - 172,911 68,472 (196,707) - - (5,550,825) - 114,130 - - 28,496 28,496 28,496 219,026 (179,021) - - 22,593 22,593 22,593 22,593 26,810 - (100,723) (213,634) 45,186 3,045 - 45,186 - 45,186 - - - - - 48,000 - - 3,498 34,064 - - - 34,064 (390,237) 33,047 - 82,852 - - 3,498 - - - 35,257 35,257 - - 6,262,988 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Thank you again! Gilles "Gilles" wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#5
|
|||
|
|||
|
Here is the raw data, investment 1=-10% Investment=0.0% which is wrong!:
Thank you, IRR Currency Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 -10.5% Investment1 Equivalent USD (0) (2,000,000) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1,000,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.0% Investment2 Equivalent USD (1,995,277) (2,000,000) - - - - 163,953 - (2,092,790) - - - - - - - - 172,911 68,472 (196,707) - - (5,550,825) - 114,130 - - 28,496 28,496 28,496 219,026 (179,021) - - 22,593 22,593 22,593 22,593 (21,555) 46,341 (100,723) (213,634) 45,186 3,045 - 45,186 - 45,186 - - - - - 48,000 - - 3,498 34,064 - - - 34,064 (390,237) 33,047 - 82,852 - - - - - - 35,257 35,257 - - 6,353,025 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "Jerry W. Lewis" wrote: We are not mind readers. Give examples. Jerry Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#6
|
|||
|
|||
|
Here is the raw data, investment 1=-10% Investment=0.0% which is wrong!: Thank you, the formulas are =XIRR(G48:DH48,G$2:DH$2) [started at g because if I start at F, it gives me 0.0%!] the other formula for investment 2 is =XIRR(F49:DH49,F$2:DH$2) IRR Currency Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 -10.5% Investment1 Equivalent USD (0) (2,000,000) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1,000,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.0% Investment2 Equivalent USD (1,995,277) (2,000,000) - - - - 163,953 - (2,092,790) - - - - - - - - 172,911 68,472 (196,707) - - (5,550,825) - 114,130 - - 28,496 28,496 28,496 219,026 (179,021) - - 22,593 22,593 22,593 22,593 (21,555) 46,341 (100,723) (213,634) 45,186 3,045 - 45,186 - 45,186 - - - - - 48,000 - - 3,498 34,064 - - - 34,064 (390,237) 33,047 - 82,852 - - - - - - 35,257 35,257 - - 6,353,025 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "Jerry W. Lewis" wrote: We are not mind readers. Give examples. Jerry Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles "Frank Kabel" wrote: Hi post the exact formula you have used and the values in the referenced cells -- Regards Frank Kabel Frankfurt, Germany Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#7
|
|||
|
|||
|
We're getting there, Giles. Now we know what the formula is. Now tell us
what the data looks like. What's in g48:dh48 and g$2:dh$2? -- Regards, Fred Please reply to newsgroup, not e-mail "Gilles" wrote in message ... Here is the raw data, investment 1=-10% Investment=0.0% which is wrong!: Thank you, the formulas are =XIRR(G48:DH48,G$2:DH$2) [started at g because if I start at F, it gives me 0.0%!] the other formula for investment 2 is =XIRR(F49:DH49,F$2:DH$2) IRR Currency Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 -10.5% Investment1 Equivalent USD (0) (2,000,000) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1,000,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.0% Investment2 Equivalent USD (1,995,277) (2,000,000) - - - - 163,953 - (2,092,790) - - - - - - - - 172,911 68,472 (196,707) - - (5,550,825) - 114,130 - - 28,496 28,496 28,496 219,026 (179,021) - - 22,593 22,593 22,593 22,593 (21,555) 46,341 (100,723) (213,634) 45,186 3,045 - 45,186 - 45,186 - - - - - 48,000 - - 3,498 34,064 - - - 34,064 (390,237) 33,047 - 82,852 - - - - - - 35,257 35,257 - - 6,353,025 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "Jerry W. Lewis" wrote: We are not mind readers. Give examples. Jerry Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles "Frank Kabel" wrote: Hi post the exact formula you have used and the values in the referenced cells -- Regards Frank Kabel Frankfurt, Germany Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#8
|
|||
|
|||
|
Dear Fred, the g48:dh48 is the cash flows of the investments ie the
investment1 and investment2 cash flows here below; the g$2:dh$2 are the dates for the investemnts. The data is put in rows. If you want, just copy paste them in rows, and see what it gives you. Thanks, Gilles "Fred Smith" wrote: We're getting there, Giles. Now we know what the formula is. Now tell us what the data looks like. What's in g48:dh48 and g$2:dh$2? -- Regards, Fred Please reply to newsgroup, not e-mail "Gilles" wrote in message ... Here is the raw data, investment 1=-10% Investment=0.0% which is wrong!: Thank you, the formulas are =XIRR(G48:DH48,G$2:DH$2) [started at g because if I start at F, it gives me 0.0%!] the other formula for investment 2 is =XIRR(F49:DH49,F$2:DH$2) IRR Currency Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 -10.5% Investment1 Equivalent USD (0) (2,000,000) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1,000,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.0% Investment2 Equivalent USD (1,995,277) (2,000,000) - - - - 163,953 - (2,092,790) - - - - - - - - 172,911 68,472 (196,707) - - (5,550,825) - 114,130 - - 28,496 28,496 28,496 219,026 (179,021) - - 22,593 22,593 22,593 22,593 (21,555) 46,341 (100,723) (213,634) 45,186 3,045 - 45,186 - 45,186 - - - - - 48,000 - - 3,498 34,064 - - - 34,064 (390,237) 33,047 - 82,852 - - - - - - 35,257 35,257 - - 6,353,025 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "Jerry W. Lewis" wrote: We are not mind readers. Give examples. Jerry Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles "Frank Kabel" wrote: Hi post the exact formula you have used and the values in the referenced cells -- Regards Frank Kabel Frankfurt, Germany Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#9
|
|||
|
|||
|
When you really want help, let us know. If you just want to believe that
Excel is screwing up, we'll leave you alone. -- Regards, Fred Please reply to newsgroup, not e-mail "Gilles" wrote in message ... Dear Fred, the g48:dh48 is the cash flows of the investments ie the investment1 and investment2 cash flows here below; the g$2:dh$2 are the dates for the investemnts. The data is put in rows. If you want, just copy paste them in rows, and see what it gives you. Thanks, Gilles "Fred Smith" wrote: We're getting there, Giles. Now we know what the formula is. Now tell us what the data looks like. What's in g48:dh48 and g$2:dh$2? -- Regards, Fred Please reply to newsgroup, not e-mail "Gilles" wrote in message ... Here is the raw data, investment 1=-10% Investment=0.0% which is wrong!: Thank you, the formulas are =XIRR(G48:DH48,G$2:DH$2) [started at g because if I start at F, it gives me 0.0%!] the other formula for investment 2 is =XIRR(F49:DH49,F$2:DH$2) IRR Currency Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 -10.5% Investment1 Equivalent USD (0) (2,000,000) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1,000,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.0% Investment2 Equivalent USD (1,995,277) (2,000,000) - - - - 163,953 - (2,092,790) - - - - - - - - 172,911 68,472 (196,707) - - (5,550,825) - 114,130 - - 28,496 28,496 28,496 219,026 (179,021) - - 22,593 22,593 22,593 22,593 (21,555) 46,341 (100,723) (213,634) 45,186 3,045 - 45,186 - 45,186 - - - - - 48,000 - - 3,498 34,064 - - - 34,064 (390,237) 33,047 - 82,852 - - - - - - 35,257 35,257 - - 6,353,025 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "Jerry W. Lewis" wrote: We are not mind readers. Give examples. Jerry Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles "Frank Kabel" wrote: Hi post the exact formula you have used and the values in the referenced cells -- Regards Frank Kabel Frankfurt, Germany Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#10
|
|||
|
|||
|
Dear Fred, I am just trying to understand why I have a 0% Irr on certain cash
flows, while I am not supposed to, please find here after another set of data that gives me this thing, one investment IRR gives me 0% while it should not. Please copy this data set on Excel, replace the formulas of IRR with these ones =XIRR(D4:IV4,D$1:IV$1) for investment X =XIRR(D7:IV7,D$1:IV$1) for investment Y Row 1 = dates row 4 = cash flows of investment x row 7 = cash flow of investment y (some rows have been hidden and are not in the calculation ) Raw data: IRR Investment Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 8.7% X (0) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - (10,000,000) - - - - 500,000 - - - - - 500,000 - - - - - 500,000 (2,000) - - - - 600,000 - - 5,500,000 - - - - - - - - - 4,500,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.000000298% Y (0) - - - - - - - - - - - - - - - - - - - - - (5,865,103) - 128,866 - - 26,316 26,316 26,316 26,316 - - - 20,408 20,408 20,408 20,408 - - - - 40,816 - - 40,816 - 40,816 - - - - - - - - - 30,769 - - - 30,769 - 30,769 - 30,769 - - - - - - 32,258 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 3,000,000 Thanks in advacne Gilles "Fred Smith" wrote: When you really want help, let us know. If you just want to believe that Excel is screwing up, we'll leave you alone. -- Regards, Fred Please reply to newsgroup, not e-mail "Gilles" wrote in message ... Dear Fred, the g48:dh48 is the cash flows of the investments ie the investment1 and investment2 cash flows here below; the g$2:dh$2 are the dates for the investemnts. The data is put in rows. If you want, just copy paste them in rows, and see what it gives you. Thanks, Gilles "Fred Smith" wrote: We're getting there, Giles. Now we know what the formula is. Now tell us what the data looks like. What's in g48:dh48 and g$2:dh$2? -- Regards, Fred Please reply to newsgroup, not e-mail "Gilles" wrote in message ... Here is the raw data, investment 1=-10% Investment=0.0% which is wrong!: Thank you, the formulas are =XIRR(G48:DH48,G$2:DH$2) [started at g because if I start at F, it gives me 0.0%!] the other formula for investment 2 is =XIRR(F49:DH49,F$2:DH$2) IRR Currency Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 -10.5% Investment1 Equivalent USD (0) (2,000,000) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1,000,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.0% Investment2 Equivalent USD (1,995,277) (2,000,000) - - - - 163,953 - (2,092,790) - - - - - - - - 172,911 68,472 (196,707) - - (5,550,825) - 114,130 - - 28,496 28,496 28,496 219,026 (179,021) - - 22,593 22,593 22,593 22,593 (21,555) 46,341 (100,723) (213,634) 45,186 3,045 - 45,186 - 45,186 - - - - - 48,000 - - 3,498 34,064 - - - 34,064 (390,237) 33,047 - 82,852 - - - - - - 35,257 35,257 - - 6,353,025 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "Jerry W. Lewis" wrote: We are not mind readers. Give examples. Jerry Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles "Frank Kabel" wrote: Hi post the exact formula you have used and the values in the referenced cells -- Regards Frank Kabel Frankfurt, Germany Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
|
#11
|
|||
|
|||
|
Gilles,
It's still very difficult for me to decipher your data from your post. As far as I can tell, you have a series of months, and a series of cash flows, a lot of which are zero. This is not the kind of data you need to feed XIRR. Here's what you need to do: 1. Create a range with only the dates (not months, but actual dates) of each cash flow. 2. Create a similar range with only the actual cash flows. As far as I can tell, you have about 8 cash flows -- that's all you need to feed to XIRR. 3. Give this information to XIRR. If you're still having trouble, reply with the data you are passing to XIRR -- the actual series of dates, and the actual cash flows. I'll show you how to format the function arguments. XIRR does work when fed the proper data. -- Regards, Fred Please reply to newsgroup, not e-mail "Gilles" wrote in message ... Dear Fred, I am just trying to understand why I have a 0% Irr on certain cash flows, while I am not supposed to, please find here after another set of data that gives me this thing, one investment IRR gives me 0% while it should not. Please copy this data set on Excel, replace the formulas of IRR with these ones =XIRR(D4:IV4,D$1:IV$1) for investment X =XIRR(D7:IV7,D$1:IV$1) for investment Y Row 1 = dates row 4 = cash flows of investment x row 7 = cash flow of investment y (some rows have been hidden and are not in the calculation ) Raw data: IRR Investment Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 8.7% X (0) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - (10,000,000) - - - - 500,000 - - - - - 500,000 - - - - - 500,000 (2,000) - - - - 600,000 - - 5,500,000 - - - - - - - - - 4,500,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.000000298% Y (0) - - - - - - - - - - - - - - - - - - - - - (5,865,103) - 128,866 - - 26,316 26,316 26,316 26,316 - - - 20,408 20,408 20,408 20,408 - - - - 40,816 - - 40,816 - 40,816 - - - - - - - - - 30,769 - - - 30,769 - 30,769 - 30,769 - - - - - - 32,258 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 32,258 - - 3,000,000 Thanks in advacne Gilles "Fred Smith" wrote: When you really want help, let us know. If you just want to believe that Excel is screwing up, we'll leave you alone. -- Regards, Fred Please reply to newsgroup, not e-mail "Gilles" wrote in message ... Dear Fred, the g48:dh48 is the cash flows of the investments ie the investment1 and investment2 cash flows here below; the g$2:dh$2 are the dates for the investemnts. The data is put in rows. If you want, just copy paste them in rows, and see what it gives you. Thanks, Gilles "Fred Smith" wrote: We're getting there, Giles. Now we know what the formula is. Now tell us what the data looks like. What's in g48:dh48 and g$2:dh$2? -- Regards, Fred Please reply to newsgroup, not e-mail "Gilles" wrote in message ... Here is the raw data, investment 1=-10% Investment=0.0% which is wrong!: Thank you, the formulas are =XIRR(G48:DH48,G$2:DH$2) [started at g because if I start at F, it gives me 0.0%!] the other formula for investment 2 is =XIRR(F49:DH49,F$2:DH$2) IRR Currency Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 -10.5% Investment1 Equivalent USD (0) (2,000,000) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1,000,000 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0.0% Investment2 Equivalent USD (1,995,277) (2,000,000) - - - - 163,953 - (2,092,790) - - - - - - - - 172,911 68,472 (196,707) - - (5,550,825) - 114,130 - - 28,496 28,496 28,496 219,026 (179,021) - - 22,593 22,593 22,593 22,593 (21,555) 46,341 (100,723) (213,634) 45,186 3,045 - 45,186 - 45,186 - - - - - 48,000 - - 3,498 34,064 - - - 34,064 (390,237) 33,047 - 82,852 - - - - - - 35,257 35,257 - - 6,353,025 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "Jerry W. Lewis" wrote: We are not mind readers. Give examples. Jerry Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles "Frank Kabel" wrote: Hi post the exact formula you have used and the values in the referenced cells -- Regards Frank Kabel Frankfurt, Germany Gilles wrote: Hello, I am having a wierd thing: using the XIRR for different investment analysis, some of them give me a answer, others do not, I get a result 0.0% !!! for no reason, can anyone please help????? Thank you, Gilles |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| My XIRR function has dropped out, analysis toolpak doesn't fix. | Excel Worksheet Functions | |||
| XIRR Function Error | Excel Worksheet Functions | |||
Linear Mode
