Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
I have a question regarding IRR.
I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
I tried an example with CF switching from positive to negative and back and
forth; IRR and MIRR worked for me just fine. What results are you expecting? Have you tried =XIRR(). HTH, Ryan--- -- RyGuy "gotahavit" wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
On Thu, 22 Jan 2009 14:21:01 -0800, gotahavit
wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. Why do you think there is a requirement for Excel's IRR function that it must be the case that "the initial investment is negative and all subsequent cash flows are positive." ?? What does "cannot get a return number from Excel" mean? Do you get a blank? Do you get an Error message? If so, what is the message or error value? HELP states that there must be at least one positive and one negative cash flow; but I see no requirement there such as you write. And I've analyzed numerous cash flows where the "signs switch". Either you are using an inappropriate "guess", or there is something peculiar about the values you are using. But without more specific information, it would be difficult to advise you further. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
I tried the following CFs:
5000 -2400 3034 222 -5743 9000 When I use IRR, I get #NUM! Does it work for you? "ryguy7272" wrote: I tried an example with CF switching from positive to negative and back and forth; IRR and MIRR worked for me just fine. What results are you expecting? Have you tried =XIRR(). HTH, Ryan--- -- RyGuy "gotahavit" wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
"Ron Rosenfeld" wrote: On Thu, 22 Jan 2009 14:21:01 -0800, gotahavit wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. Why do you think there is a requirement for Excel's IRR function that it must be the case that "the initial investment is negative and all subsequent cash flows are positive." ?? What does "cannot get a return number from Excel" mean? Do you get a blank? Do you get an Error message? If so, what is the message or error value? HELP states that there must be at least one positive and one negative cash flow; but I see no requirement there such as you write. And I've analyzed numerous cash flows where the "signs switch". Either you are using an inappropriate "guess", or there is something peculiar about the values you are using. But without more specific information, it would be difficult to advise you further. --ron I already replied to one of the other people who replied to my question, but when I use the following CF stream, I get an error message (#NUM!). 5000 -2400 3034 222 -5743 9000 I have had the same problem in the past with different CF numbers, so when I tried to look into it, I thought it was because of the multiple IRR issue that arises from switching signs. Can you get IRR to work using these numbers? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
Perhaps the following extract from Excel help for IRR may be useful:
"Remarks IRR is closely related to NPV, the net present value function. The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value. The following formula demonstrates how NPV and IRR are related: NPV(IRR(B1:B6),B1:B6) equals 3.60E-08 [Within the accuracy of the IRR calculation, the value 3.60E-08 is effectively 0 (zero).]" I would suggest that you experiment with a range of guesses for IRR and see what you get for NPV(IRR_guess,B1:B6) with your values in B1:B6. -- David Biddulph "gotahavit" wrote in message ... I tried the following CFs: 5000 -2400 3034 222 -5743 9000 When I use IRR, I get #NUM! Does it work for you? "ryguy7272" wrote: I tried an example with CF switching from positive to negative and back and forth; IRR and MIRR worked for me just fine. What results are you expecting? Have you tried =XIRR(). HTH, Ryan--- -- RyGuy "gotahavit" wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
when I use the following CF stream, I get an error message (#NUM!).
Hi. For me, I find it easier to switch the point of reference. Change the sign of all your numbers so that the first payment is an outflow (negative). Unless I am mistaken, you have a large negative return. If you last payment is changed from -9000 to -2500, you will see a negative return of about -47% (Both from Excel, and a custom function) When you start decreasing your value to -9000, the return goes to a very large negative number and causes an error. = = = HTH :) Dana DeLouis gotahavit wrote: "Ron Rosenfeld" wrote: On Thu, 22 Jan 2009 14:21:01 -0800, gotahavit wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. Why do you think there is a requirement for Excel's IRR function that it must be the case that "the initial investment is negative and all subsequent cash flows are positive." ?? What does "cannot get a return number from Excel" mean? Do you get a blank? Do you get an Error message? If so, what is the message or error value? HELP states that there must be at least one positive and one negative cash flow; but I see no requirement there such as you write. And I've analyzed numerous cash flows where the "signs switch". Either you are using an inappropriate "guess", or there is something peculiar about the values you are using. But without more specific information, it would be difficult to advise you further. --ron I already replied to one of the other people who replied to my question, but when I use the following CF stream, I get an error message (#NUM!). 5000 -2400 3034 222 -5743 9000 I have had the same problem in the past with different CF numbers, so when I tried to look into it, I thought it was because of the multiple IRR issue that arises from switching signs. Can you get IRR to work using these numbers? Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
I tried a few combinations of things; large initial +CF and subsequent -CF,
then large initial -CF and subsequent +CF. As the negative IRR increased more and more, I encountered errors. I'm with Dana on this! The result is hugely negative. Bad investment. Excel is not telling you how bad it is, but it's very bad and you should avoid it at all costs. I couldn't get greater than -50% return. Anyway, why would you want to! -50% return; anyone interested? DJIA vaporized? Economy is f****d! I think the thing speaks for itself. HTH, Ryan--- -- RyGuy "Dana DeLouis" wrote: when I use the following CF stream, I get an error message (#NUM!). Hi. For me, I find it easier to switch the point of reference. Change the sign of all your numbers so that the first payment is an outflow (negative). Unless I am mistaken, you have a large negative return. If you last payment is changed from -9000 to -2500, you will see a negative return of about -47% (Both from Excel, and a custom function) When you start decreasing your value to -9000, the return goes to a very large negative number and causes an error. = = = HTH :) Dana DeLouis gotahavit wrote: "Ron Rosenfeld" wrote: On Thu, 22 Jan 2009 14:21:01 -0800, gotahavit wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. Why do you think there is a requirement for Excel's IRR function that it must be the case that "the initial investment is negative and all subsequent cash flows are positive." ?? What does "cannot get a return number from Excel" mean? Do you get a blank? Do you get an Error message? If so, what is the message or error value? HELP states that there must be at least one positive and one negative cash flow; but I see no requirement there such as you write. And I've analyzed numerous cash flows where the "signs switch". Either you are using an inappropriate "guess", or there is something peculiar about the values you are using. But without more specific information, it would be difficult to advise you further. --ron I already replied to one of the other people who replied to my question, but when I use the following CF stream, I get an error message (#NUM!). 5000 -2400 3034 222 -5743 9000 I have had the same problem in the past with different CF numbers, so when I tried to look into it, I thought it was because of the multiple IRR issue that arises from switching signs. Can you get IRR to work using these numbers? Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
On Thu, 22 Jan 2009 19:33:01 -0800, gotahavit
wrote: I already replied to one of the other people who replied to my question, but when I use the following CF stream, I get an error message (#NUM!). 5000 -2400 3034 222 -5743 9000 I have had the same problem in the past with different CF numbers, so when I tried to look into it, I thought it was because of the multiple IRR issue that arises from switching signs. Can you get IRR to work using these numbers? I do not believe that you can. Others may be able to explain this better, but I'll try. IRR is the rate for which the NPV is zero. However, certain types of cash flows have multiple instances where the NPV is zero (more likely with "switching sign" data) and so IRR is not valid. If you were to graph NPV vs discount rate for the above series of cash flows, you would see that the graph crosses the 0 line at two points -- a highly negative and a highly positive rate. You could certainly analyze your investment by looking at total outflow vs total inflow, but I don't think you can do a valid IRR with that data. --ron |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
On Thu, 22 Jan 2009 19:33:01 -0800, gotahavit
wrote: "Ron Rosenfeld" wrote: On Thu, 22 Jan 2009 14:21:01 -0800, gotahavit wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. Why do you think there is a requirement for Excel's IRR function that it must be the case that "the initial investment is negative and all subsequent cash flows are positive." ?? What does "cannot get a return number from Excel" mean? Do you get a blank? Do you get an Error message? If so, what is the message or error value? HELP states that there must be at least one positive and one negative cash flow; but I see no requirement there such as you write. And I've analyzed numerous cash flows where the "signs switch". Either you are using an inappropriate "guess", or there is something peculiar about the values you are using. But without more specific information, it would be difficult to advise you further. --ron I already replied to one of the other people who replied to my question, but when I use the following CF stream, I get an error message (#NUM!). 5000 -2400 3034 222 -5743 9000 I have had the same problem in the past with different CF numbers, so when I tried to look into it, I thought it was because of the multiple IRR issue that arises from switching signs. Can you get IRR to work using these numbers? Thanks. I found this interesting discussion regarding IRR's: http://members.tripod.com/~Ray_Martin/DCF/nr7aa003.html --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
As a matter of interest, what are the two values of discount rate which give
NPV = 0? -- David Biddulph "Ron Rosenfeld" wrote in message ... On Thu, 22 Jan 2009 19:33:01 -0800, gotahavit wrote: I already replied to one of the other people who replied to my question, but when I use the following CF stream, I get an error message (#NUM!). 5000 -2400 3034 222 -5743 9000 I have had the same problem in the past with different CF numbers, so when I tried to look into it, I thought it was because of the multiple IRR issue that arises from switching signs. Can you get IRR to work using these numbers? I do not believe that you can. Others may be able to explain this better, but I'll try. IRR is the rate for which the NPV is zero. However, certain types of cash flows have multiple instances where the NPV is zero (more likely with "switching sign" data) and so IRR is not valid. If you were to graph NPV vs discount rate for the above series of cash flows, you would see that the graph crosses the 0 line at two points -- a highly negative and a highly positive rate. You could certainly analyze your investment by looking at total outflow vs total inflow, but I don't think you can do a valid IRR with that data. --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
On Sat, 24 Jan 2009 14:00:01 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote: As a matter of interest, what are the two values of discount rate which give NPV = 0? -- David Biddulph They are very large numbers -- approximately + or - 5368709120% But I suspect it is the complex nature of the cash flows that make IRR inappropriate. --ron |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
(IRR()... I get an error message (#NUM!).
5000 -2400 3034 222 -5743 9000 Hi. Just some thoughts. Without doing any math, we see that you have a lot of cash flows in the same direction as your first payment. This indicates a large negative return. Large negative returns cause problems math-wise for reasons below. I would make sure my data was correct. (ie is it -9000?) First...Excel's IRR has some "known" issues. It is not really that good. It has been brought up before where it fails, when it really shouldn't. For the sake of a demo, let's change the 9000 to 2500. If we use IRR(data, -.41) we get -47% If we use IRR(data, -.40) we get #num! If we use IRR(data, -.39) we get -26% Well, Excel really shouldn't return an error. All it had to do was decide on either number. Again, not a great algorithm by Excel. If we solve the basic equation, there are 3 real solutions, and 2 complex: -1.9410621, -0.47409967, -0.25722072, -0.92380875 - 1.1637655 I, -0.92380875 + 1.1637655 I Excel couldn't handle the switch, and returned an error. Let's look at the basic underlying equation: 5000 - 2400/(r + 1) + 3034/(r + 1)^2 + 222/(r + 1)^3 - 5743/(r + 1)^4 + 2500/(r + 1)^5 When we use Excel, or perhaps a better method like the Newton method to arrive at a solution that set the above to zero, what happens when we have negative values? As 'r approaches -1, the values trend toward infinity. (error for Excel) The slope is very high, and can be an issue in a custom function if not careful. Just on the other side of -1 the slope is approaching infinity, but in the opposite direction. Excel has other issues, and this is another one it can not handle very well. This is why a solution to IRR in this case is not really meaningful. If we put back your 9000, and solve the equation, I show only 1 real solution using machine precision. However, I don't think a rate of -2.0996 is meaningful. -2.0996540900510077, -1.0761593698610863 - 1.2447059494677744*I, -1.0761593698610863 + 1.2447059494677744*I, -0.1340135851134098 - 0.5501448705825116*I, -0.1340135851134098 + 0.5501448705825116*I = = = HTH :) Dana DeLouis gotahavit wrote: "Ron Rosenfeld" wrote: On Thu, 22 Jan 2009 14:21:01 -0800, gotahavit wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. Why do you think there is a requirement for Excel's IRR function that it must be the case that "the initial investment is negative and all subsequent cash flows are positive." ?? What does "cannot get a return number from Excel" mean? Do you get a blank? Do you get an Error message? If so, what is the message or error value? HELP states that there must be at least one positive and one negative cash flow; but I see no requirement there such as you write. And I've analyzed numerous cash flows where the "signs switch". Either you are using an inappropriate "guess", or there is something peculiar about the values you are using. But without more specific information, it would be difficult to advise you further. --ron I already replied to one of the other people who replied to my question, but when I use the following CF stream, I get an error message (#NUM!). 5000 -2400 3034 222 -5743 9000 I have had the same problem in the past with different CF numbers, so when I tried to look into it, I thought it was because of the multiple IRR issue that arises from switching signs. Can you get IRR to work using these numbers? Thanks. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
They are very large numbers -- approximately + or - 5368709120%
Hi. I may be wrong, but here goes. Excel's IRR can not handle this, but if we tell it to start a hair to the left of the discontinuity line, we can get the only real solution. This is not the compete function I use, but should work. Again, the solution is not very meaningful. ? MyIRR([A1:A6],-1.001) -2.09963276604736 Function MyIRR(Rng, Optional Guess As Double) '// = = = = = = = = = = = = = = = = = '// Assumes a vertical array of data '// By: Dana DeLouis '// = = = = = = = = = = = = = = = = = Dim v Dim dr() Dim R Dim OldRte Dim Ct As Long Dim J As Long With WorksheetFunction 'Make 1-Dimensional v = .Transpose(Rng.Value) For J = 1 To UBound(v) v(J) = CSng(v(J)) Next J 'Make an exact derivative ReDim dr(1 To UBound(v) - 1) For J = 1 To UBound(dr) dr(J) = -J * v(J + 1) Next J If IsMissing(Guess) Then R = 0.1 Else R = Guess End If Do OldRte = R R = R - .SeriesSum(1 + R, 0, -1, v) / _ .SeriesSum(1 + R, -2, -1, dr) Ct = Ct + 1 Loop While OldRte < R And Ct <= 40 End With MyIRR = R End Function Again, Excel's IRR can not do this. :~ = = = HTH Dana DeLouis Ron Rosenfeld wrote: On Sat, 24 Jan 2009 14:00:01 -0000, "David Biddulph" <groups [at] biddulph.org.uk wrote: As a matter of interest, what are the two values of discount rate which give NPV = 0? -- David Biddulph They are very large numbers -- approximately + or - 5368709120% But I suspect it is the complex nature of the cash flows that make IRR inappropriate. --ron |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
OOps...I didn't see the program caution (other program). It is very
rare that we go 40 loops. It is usually 7-8. I went back and changed it to 50. Loop While OldRte < R And Ct <= 50 The loop is there to prevent cycling between two ending solutions due to precision. Now with: ? MyIRR([A1:A6],-1.001) -2.09965409005101 with checks with the only real solution: -2.0996540900510077 -1.0761593698610863 +- 1.2447059494677744*I -0.1340135851134098 +- 0.5501448705825116*I Again, I told it to start a hair to the left of the discontinuity line. = = = Dana DeLouis |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
On Jan 24, 5:32*am, Ron Rosenfeld wrote:
If you were to graph NPV vs discount rate for the above series of cash flows, you would see that the graph crosses the 0 line at two points -- a highly negative and a highly positive rate. How did you use a "highly negative" discount rate? The terms of the NPV formula include the factor 1/(1+i)^n. If i = -100%, the factor is incalculable. If i < -100%, we can calculate the factor only for even-numbered cash flows. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
Oops....
On Jan 24, 9:31*am, I wrote: The terms of the NPV formula include the factor 1/(1+i)^n. If i = -100%, the factor is incalculable. *If i < -100%, we can calculate the factor only for even-numbered cash flows. That's only half right (i = -100%). No problem with i < -100% as long n = 0 and n = 1. (When I say "no problem", I mean the factor can be calculated. I'm not sure it makes sense for the sign of the denominator to alternate.) |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
On Sat, 24 Jan 2009 09:31:04 -0800 (PST), joeu2004
wrote: On Jan 24, 5:32*am, Ron Rosenfeld wrote: If you were to graph NPV vs discount rate for the above series of cash flows, you would see that the graph crosses the 0 line at two points -- a highly negative and a highly positive rate. How did you use a "highly negative" discount rate? The terms of the NPV formula include the factor 1/(1+i)^n. If i = -100%, the factor is incalculable. If i < -100%, we can calculate the factor only for even-numbered cash flows. I just inserted a negative number into the cell reference I used for the RATE argument in the NPV function: =NPV(cell_ref,$A$1:$A$6) --ron |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
On Sat, 24 Jan 2009 09:31:04 -0800 (PST), joeu2004
wrote: On Jan 24, 5:32*am, Ron Rosenfeld wrote: If you were to graph NPV vs discount rate for the above series of cash flows, you would see that the graph crosses the 0 line at two points -- a highly negative and a highly positive rate. How did you use a "highly negative" discount rate? The terms of the NPV formula include the factor 1/(1+i)^n. If i = -100%, the factor is incalculable. If i < -100%, we can calculate the factor only for even-numbered cash flows. I just put a negative value into the NPV formula. I believe others have posted a better analysis of the problem than my feeble (and inaccurate) attempt. --ron |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
Errata....
On Jan 24, 9:48*am, joeu2004 wrote: Oops.... On Jan 24, 9:31*am, I wrote: The terms of the NPV formula include the factor 1/(1+i)^n. If i = -100%, the factor is incalculable. *If i < -100%, we can calculate the factor only for even-numbered cash flows. That's only half right (i = -100%). *No problem with i < -100% as long n = 0 and n = 1. I should have said __integral__ n = 1. I don't know how we would compute (1+i)^n without using log(1+i) for non-integral n. I should probably note that my concerns about 0<n<1 and non-integral n relate more to the computability of XIRR. Although the discussion here is about IRR, my mind keeps segueing to XIRR. |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR
On Thu, 22 Jan 2009 14:21:01 -0800, gotahavit
wrote: I have a question regarding IRR. I am trying to figure out the IRR for an investment (actually, this has come up a number of times in the past). Unfortunately, it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. There are a couple of times when the cash flow signs switch. I understand the multiple IRR issue, but it still doesn't help that I cannot get a return number from Excel. I have to imagine that these cases, where the negatives and positives switch more than once, is common. It is difficult to just say "well, there is no IRR". I still need to know what the rate of return would be. What can I do? Please help. Thanks. I think I misunderstood your problem. If your problem is to generate an annualized return figure for data which does not have an IRR that Excel can compute, I suppose you'd have to generate some kind of approximation. For your series of cash flows, it appears as if you are "investing" a total of $8143 and getting back a total of $17,256 (negative vs positive flows). So your gross return is $9,113 over five time-periods. It is interesting that if you ignore the first $5000 payment you receive, the IRR on the remaining cash flow calculates to 44% --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|