Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
I have a series of monthly transactions over several years. I have used the
XIRR function to find the ROR for each month, each quarter, and each calendar year. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
Your expectation of XIRR is correct. It returns an annualized rate for the
period specified. Also -400% is impossible. The most you can lose is 100%. My best guess is that it's a data error, but without seeing the data, it's impossible to tell. Regards, Fred. "Don Kline" wrote in message ... I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
"Don Kline" wrote:
I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. That's a neat trick, since XIRR only returns the annualized rate. I hope that is what you mean. But I wonder if you are misinterpreting the results from XIRR. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? Yes. But I cannot tell you what that is because I'm not a mindreader. If you post an example, you might get some help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
I have a XLS file I can send you. I don't know how to attach the XLS file to
the discussion. Please advise how I can get this to you. "Fred Smith" wrote: Your expectation of XIRR is correct. It returns an annualized rate for the period specified. Also -400% is impossible. The most you can lose is 100%. My best guess is that it's a data error, but without seeing the data, it's impossible to tell. Regards, Fred. "Don Kline" wrote in message ... I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
I have a XLS file I can send you. I don't know how to attach the XLS file to
the discussion. Please advise how I can get this to you. "JoeU2004" wrote: "Don Kline" wrote: I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. That's a neat trick, since XIRR only returns the annualized rate. I hope that is what you mean. But I wonder if you are misinterpreting the results from XIRR. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? Yes. But I cannot tell you what that is because I'm not a mindreader. If you post an example, you might get some help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
"Don Kline" wrote:
I have a XLS file I can send you. I don't know how to attach the XLS file to the discussion. Please advise how I can get this to you. I received your XLS file in email. I sent you my comments. I will post an update to this thread later today so that others might be able to contribute their thoughts, too. ----- original message ----- "Don Kline" wrote in message ... I have a XLS file I can send you. I don't know how to attach the XLS file to the discussion. Please advise how I can get this to you. "JoeU2004" wrote: "Don Kline" wrote: I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. That's a neat trick, since XIRR only returns the annualized rate. I hope that is what you mean. But I wonder if you are misinterpreting the results from XIRR. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? Yes. But I cannot tell you what that is because I'm not a mindreader. If you post an example, you might get some help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
Don acknowledged in email that the -400% that he observed was due to human
error. Nonetheless, for the cash flow stream that he sent me (below), XIRR() returns about 0.0000002980%. That is obviously incorrect, since XNPV() returns about -43,338.86, which is not even close to zero. The root cause is the need for a "guess" argument (Don did not have one), and a correct "guess" argument at that. Reducing the irregular daily cash flows to monthly net cash flows (see below), we see a series of negative cash flows (investments) and one positive cash flow (return). Since the sum of the investments (about -136K) is much larger than the return (about 96K), we expect a negative IRR. So I tried a "guess" of -1%, and XIRR() returned -24.83%. Plugging the exact IRR into XNPV() using the daily cash flow, the result is indeed close to zero. QED. Don still wonders, reasonably, why XIRR() returns such an obviously incorrect rate when the "guess" was missing. I invite people like Fred Smith and Myrna Larson to comment on the behavior of the Newton-Raphson method of approximation, or whatever method Excel might use. We all know that these methods can take a wrong turn under adverse conditions. But some details might be interesting. In any case, I would call this a defect in the XIRR() implementation. No, I am not disparaging your beloved approximation method. I presume that it is working as well as might be expected. Instead, I am disparaging the witless MS programmer who does not seem to know how to write an "if" statement just before exiting the XIRR() function, of the form: "if there has not been an error and the XNPV with the last result is not close to zero, return an error, per the function specification". I am referring to the XIRR Help page, which states: "If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned". Now, we might argue about what "close to zero" means exactly. But I am sure that -43,338.86 does not meet anyone's reasonable definition. So clearly, the XIRR() result does not "work", even it was found in fewer than 100 iterations. If XIRR() had returned the #NUM! error, as it should in this case, Don might still have been perplexed. But at least his question would have been much less mysterious; probably something to the effect of: "how in the heck am I supposed to know what ``guess`` should be?", and "why does Excel need this, but my HP 12C does not?". We've dealt with such questions before. The following is Don's original data and my monthly net cash flow approximation. Don's cash flow (my apologies for the poor alignment; there are too many to adjust manually): 3/27/2006 -3,994.58 4/1/2006 871.42 5/1/2006 871.52 5/3/2006 -9,728.80 6/1/2006 870.51 6/2/2006 -4,864.40 7/1/2006 870.04 7/5/2006 -4,864.40 8/1/2006 869.06 8/3/2006 -4,864.40 9/1/2006 868.53 9/6/2006 -4,864.40 10/1/2006 868.53 10/4/2006 -4,924.67 11/1/2006 867.95 11/6/2006 -4,966.81 12/1/2006 866.79 12/4/2006 -4,966.81 1/1/2007 866.19 1/8/2007 -4,966.81 2/1/2007 866.19 2/5/2007 -4,966.81 3/1/2007 910.86 3/5/2007 -4,864.40 4/1/2007 910.20 4/5/2007 -4,864.40 5/1/2007 909.34 5/3/2007 -4,864.40 6/1/2007 908.50 6/4/2007 -4,864.40 7/1/2007 908.02 7/5/2007 -4,864.40 8/1/2007 907.87 8/3/2007 -4,864.40 9/1/2007 906.91 9/4/2007 -4,864.40 10/1/2007 905.77 10/3/2007 -4,924.67 11/1/2007 905.17 11/5/2007 -4,966.81 12/1/2007 904.60 12/6/2007 -4,966.81 1/1/2008 904.60 1/7/2008 -4,966.81 2/1/2008 904.79 2/6/2008 -4,966.81 3/1/2008 942.92 3/6/2008 -4,864.40 4/1/2008 941.97 4/3/2008 -4,864.40 5/1/2008 940.92 5/5/2008 -4,864.40 6/1/2008 940.48 6/2/2008 -4,864.40 7/1/2008 941.24 7/2/2008 -4,864.40 8/1/2008 941.24 8/4/2008 -4,864.40 9/1/2008 940.68 9/5/2008 -4,864.40 10/1/2008 942.05 10/6/2008 -4,924.67 11/1/2008 944.31 11/3/2008 -4,966.81 12/1/2008 945.74 12/3/2008 -4,966.81 12/28/2008 92,520.92 Monthly net cash flow approximation (my apologies for reversing the columns): -3,123.16 4/1/2006 -8,857.28 5/1/2006 -3,993.89 6/1/2006 -3,994.36 7/1/2006 -3,995.34 8/1/2006 -3,995.87 9/1/2006 -4,056.14 10/1/2006 -4,098.86 11/1/2006 -4,100.02 12/1/2006 -4,100.62 1/1/2007 -4,100.62 2/1/2007 -3,953.54 3/1/2007 -3,954.20 4/1/2007 -3,955.06 5/1/2007 -3,955.90 6/1/2007 -3,956.38 7/1/2007 -3,956.53 8/1/2007 -3,957.49 9/1/2007 -4,018.90 10/1/2007 -4,061.64 11/1/2007 -4,062.21 12/1/2007 -4,062.21 1/1/2008 -4,062.02 2/1/2008 -3,921.48 3/1/2008 -3,922.43 4/1/2008 -3,923.48 5/1/2008 -3,923.92 6/1/2008 -3,923.16 7/1/2008 -3,923.16 8/1/2008 -3,923.72 9/1/2008 -3,982.62 10/1/2008 -4,022.50 11/1/2008 -4,021.07 12/1/2008 92,520.92 1/1/2009 ----- original message ----- "Don Kline" wrote in message ... I have a XLS file I can send you. I don't know how to attach the XLS file to the discussion. Please advise how I can get this to you. "JoeU2004" wrote: "Don Kline" wrote: I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. That's a neat trick, since XIRR only returns the annualized rate. I hope that is what you mean. But I wonder if you are misinterpreting the results from XIRR. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? Yes. But I cannot tell you what that is because I'm not a mindreader. If you post an example, you might get some help. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
Joe,
Glad you were able to help Don. My comments a As is usual, a more reasonable guess solved the problem. Why can't users figure out there's a reason XIRR has a guess parameter? Because they're users -- they just want it to work. Why didn't MS do a better job of programming XIRR? Because they're programmers. It meets the spec, so why put in extra work. My bet is that over 90% of the errors in using XIRR can be fixed by using a guess of 10% when the total cash flow is positive, and -10% when it's negative. In fact, I always use a guess of 10%*sign(sum(cashflows)). It would have been so simple for MS to do the same, but unfortunately, they didn't. Newton-Raphson can't be blamed for poor programming on the part of MS. Any calculation of the ROI for irregular investments must use iteration to find the result. There are several iteration algorithms available; NR just happens to be the fastest, which is why everyone uses it. As it's been explained to me, it's like finding the edge of the lake from a starting point on the shore. You calculate the tangent of the curve you are on as the next starting point. You continue until you find the lake. Unfortunately, if you start in a hollow, you go off in the wrong direction, and never converge towards the lake. In this situation, you need to pick a different guess. It doesn't have to be a *better* guess, just different (using my analogy, somewhere out of the hollow). As we both agree, MS itself could have programmed XIRR to pick a different guess, and try again. They could easily have calculated a guess based on the ratio of the total positive cash flows to the total negative. But they didn't. The silver lining to their laziness is that you and I get to pontificate on the machinations of XIRR. Regards, Fred "JoeU2004" wrote in message ... Don acknowledged in email that the -400% that he observed was due to human error. Nonetheless, for the cash flow stream that he sent me (below), XIRR() returns about 0.0000002980%. That is obviously incorrect, since XNPV() returns about -43,338.86, which is not even close to zero. The root cause is the need for a "guess" argument (Don did not have one), and a correct "guess" argument at that. Reducing the irregular daily cash flows to monthly net cash flows (see below), we see a series of negative cash flows (investments) and one positive cash flow (return). Since the sum of the investments (about -136K) is much larger than the return (about 96K), we expect a negative IRR. So I tried a "guess" of -1%, and XIRR() returned -24.83%. Plugging the exact IRR into XNPV() using the daily cash flow, the result is indeed close to zero. QED. Don still wonders, reasonably, why XIRR() returns such an obviously incorrect rate when the "guess" was missing. I invite people like Fred Smith and Myrna Larson to comment on the behavior of the Newton-Raphson method of approximation, or whatever method Excel might use. We all know that these methods can take a wrong turn under adverse conditions. But some details might be interesting. In any case, I would call this a defect in the XIRR() implementation. No, I am not disparaging your beloved approximation method. I presume that it is working as well as might be expected. Instead, I am disparaging the witless MS programmer who does not seem to know how to write an "if" statement just before exiting the XIRR() function, of the form: "if there has not been an error and the XNPV with the last result is not close to zero, return an error, per the function specification". I am referring to the XIRR Help page, which states: "If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned". Now, we might argue about what "close to zero" means exactly. But I am sure that -43,338.86 does not meet anyone's reasonable definition. So clearly, the XIRR() result does not "work", even it was found in fewer than 100 iterations. If XIRR() had returned the #NUM! error, as it should in this case, Don might still have been perplexed. But at least his question would have been much less mysterious; probably something to the effect of: "how in the heck am I supposed to know what ``guess`` should be?", and "why does Excel need this, but my HP 12C does not?". We've dealt with such questions before. The following is Don's original data and my monthly net cash flow approximation. Don's cash flow (my apologies for the poor alignment; there are too many to adjust manually): 3/27/2006 -3,994.58 4/1/2006 871.42 5/1/2006 871.52 5/3/2006 -9,728.80 6/1/2006 870.51 6/2/2006 -4,864.40 7/1/2006 870.04 7/5/2006 -4,864.40 8/1/2006 869.06 8/3/2006 -4,864.40 9/1/2006 868.53 9/6/2006 -4,864.40 10/1/2006 868.53 10/4/2006 -4,924.67 11/1/2006 867.95 11/6/2006 -4,966.81 12/1/2006 866.79 12/4/2006 -4,966.81 1/1/2007 866.19 1/8/2007 -4,966.81 2/1/2007 866.19 2/5/2007 -4,966.81 3/1/2007 910.86 3/5/2007 -4,864.40 4/1/2007 910.20 4/5/2007 -4,864.40 5/1/2007 909.34 5/3/2007 -4,864.40 6/1/2007 908.50 6/4/2007 -4,864.40 7/1/2007 908.02 7/5/2007 -4,864.40 8/1/2007 907.87 8/3/2007 -4,864.40 9/1/2007 906.91 9/4/2007 -4,864.40 10/1/2007 905.77 10/3/2007 -4,924.67 11/1/2007 905.17 11/5/2007 -4,966.81 12/1/2007 904.60 12/6/2007 -4,966.81 1/1/2008 904.60 1/7/2008 -4,966.81 2/1/2008 904.79 2/6/2008 -4,966.81 3/1/2008 942.92 3/6/2008 -4,864.40 4/1/2008 941.97 4/3/2008 -4,864.40 5/1/2008 940.92 5/5/2008 -4,864.40 6/1/2008 940.48 6/2/2008 -4,864.40 7/1/2008 941.24 7/2/2008 -4,864.40 8/1/2008 941.24 8/4/2008 -4,864.40 9/1/2008 940.68 9/5/2008 -4,864.40 10/1/2008 942.05 10/6/2008 -4,924.67 11/1/2008 944.31 11/3/2008 -4,966.81 12/1/2008 945.74 12/3/2008 -4,966.81 12/28/2008 92,520.92 Monthly net cash flow approximation (my apologies for reversing the columns): -3,123.16 4/1/2006 -8,857.28 5/1/2006 -3,993.89 6/1/2006 -3,994.36 7/1/2006 -3,995.34 8/1/2006 -3,995.87 9/1/2006 -4,056.14 10/1/2006 -4,098.86 11/1/2006 -4,100.02 12/1/2006 -4,100.62 1/1/2007 -4,100.62 2/1/2007 -3,953.54 3/1/2007 -3,954.20 4/1/2007 -3,955.06 5/1/2007 -3,955.90 6/1/2007 -3,956.38 7/1/2007 -3,956.53 8/1/2007 -3,957.49 9/1/2007 -4,018.90 10/1/2007 -4,061.64 11/1/2007 -4,062.21 12/1/2007 -4,062.21 1/1/2008 -4,062.02 2/1/2008 -3,921.48 3/1/2008 -3,922.43 4/1/2008 -3,923.48 5/1/2008 -3,923.92 6/1/2008 -3,923.16 7/1/2008 -3,923.16 8/1/2008 -3,923.72 9/1/2008 -3,982.62 10/1/2008 -4,022.50 11/1/2008 -4,021.07 12/1/2008 92,520.92 1/1/2009 ----- original message ----- "Don Kline" wrote in message ... I have a XLS file I can send you. I don't know how to attach the XLS file to the discussion. Please advise how I can get this to you. "JoeU2004" wrote: "Don Kline" wrote: I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. That's a neat trick, since XIRR only returns the annualized rate. I hope that is what you mean. But I wonder if you are misinterpreting the results from XIRR. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? Yes. But I cannot tell you what that is because I'm not a mindreader. If you post an example, you might get some help. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
"Fred Smith" wrote:
The silver lining to their laziness is that you and I get to pontificate on the machinations of XIRR. <g As we both agree, MS itself could have programmed XIRR to pick a different guess, and try again. Where in my "pontification" do you think I said anything like that. I explicitly did not disparage the XIRR approximation algorithm. On the contrary, I wrote: "I presume that it is working as well as might be expected". Why can't users figure out there's a reason XIRR has a guess parameter? Because the XIRR() function fails to let them know when they should. If you are saying that we should always provide "guess", well, the Excel documentation disagrees with you, not in silence, but by explicit instruction. It states: "In most cases you do not need to provide guess for the XIRR calculation". Newton-Raphson can't be blamed for poor programming on the part of MS. I explicitly said as much, too. Why didn't MS do a better job of programming XIRR? Because they're programmers. Spoken by someone who apparently knows nothing about computer programming, at least not professionally. I was a professional programmer and system architect for 35 years. Part of my responsibility, in effect, was to specify what can expect of programmers. It meets the spec I have quoted Excel's specs. I think it is clear that you are incorrect. It says: "The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0". Don provides an example where that specification is not met: the rate of return calculated by XIRR is not a rate that causes the XNPV to be zero. All I was saying is: XIRR() should return an error when its algorithm computes a discount rate that does not cause the NPV to be (close to) zero. That does not disparage the algorithm. It is to be expected that the algorithm will miss the mark sometimes, as you say. ----- original message ----- "Fred Smith" wrote in message ... Joe, Glad you were able to help Don. My comments a As is usual, a more reasonable guess solved the problem. Why can't users figure out there's a reason XIRR has a guess parameter? Because they're users -- they just want it to work. Why didn't MS do a better job of programming XIRR? Because they're programmers. It meets the spec, so why put in extra work. My bet is that over 90% of the errors in using XIRR can be fixed by using a guess of 10% when the total cash flow is positive, and -10% when it's negative. In fact, I always use a guess of 10%*sign(sum(cashflows)). It would have been so simple for MS to do the same, but unfortunately, they didn't. Newton-Raphson can't be blamed for poor programming on the part of MS. Any calculation of the ROI for irregular investments must use iteration to find the result. There are several iteration algorithms available; NR just happens to be the fastest, which is why everyone uses it. As it's been explained to me, it's like finding the edge of the lake from a starting point on the shore. You calculate the tangent of the curve you are on as the next starting point. You continue until you find the lake. Unfortunately, if you start in a hollow, you go off in the wrong direction, and never converge towards the lake. In this situation, you need to pick a different guess. It doesn't have to be a *better* guess, just different (using my analogy, somewhere out of the hollow). As we both agree, MS itself could have programmed XIRR to pick a different guess, and try again. They could easily have calculated a guess based on the ratio of the total positive cash flows to the total negative. But they didn't. The silver lining to their laziness is that you and I get to pontificate on the machinations of XIRR. Regards, Fred "JoeU2004" wrote in message ... Don acknowledged in email that the -400% that he observed was due to human error. Nonetheless, for the cash flow stream that he sent me (below), XIRR() returns about 0.0000002980%. That is obviously incorrect, since XNPV() returns about -43,338.86, which is not even close to zero. The root cause is the need for a "guess" argument (Don did not have one), and a correct "guess" argument at that. Reducing the irregular daily cash flows to monthly net cash flows (see below), we see a series of negative cash flows (investments) and one positive cash flow (return). Since the sum of the investments (about -136K) is much larger than the return (about 96K), we expect a negative IRR. So I tried a "guess" of -1%, and XIRR() returned -24.83%. Plugging the exact IRR into XNPV() using the daily cash flow, the result is indeed close to zero. QED. Don still wonders, reasonably, why XIRR() returns such an obviously incorrect rate when the "guess" was missing. I invite people like Fred Smith and Myrna Larson to comment on the behavior of the Newton-Raphson method of approximation, or whatever method Excel might use. We all know that these methods can take a wrong turn under adverse conditions. But some details might be interesting. In any case, I would call this a defect in the XIRR() implementation. No, I am not disparaging your beloved approximation method. I presume that it is working as well as might be expected. Instead, I am disparaging the witless MS programmer who does not seem to know how to write an "if" statement just before exiting the XIRR() function, of the form: "if there has not been an error and the XNPV with the last result is not close to zero, return an error, per the function specification". I am referring to the XIRR Help page, which states: "If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned". Now, we might argue about what "close to zero" means exactly. But I am sure that -43,338.86 does not meet anyone's reasonable definition. So clearly, the XIRR() result does not "work", even it was found in fewer than 100 iterations. If XIRR() had returned the #NUM! error, as it should in this case, Don might still have been perplexed. But at least his question would have been much less mysterious; probably something to the effect of: "how in the heck am I supposed to know what ``guess`` should be?", and "why does Excel need this, but my HP 12C does not?". We've dealt with such questions before. The following is Don's original data and my monthly net cash flow approximation. Don's cash flow (my apologies for the poor alignment; there are too many to adjust manually): 3/27/2006 -3,994.58 4/1/2006 871.42 5/1/2006 871.52 5/3/2006 -9,728.80 6/1/2006 870.51 6/2/2006 -4,864.40 7/1/2006 870.04 7/5/2006 -4,864.40 8/1/2006 869.06 8/3/2006 -4,864.40 9/1/2006 868.53 9/6/2006 -4,864.40 10/1/2006 868.53 10/4/2006 -4,924.67 11/1/2006 867.95 11/6/2006 -4,966.81 12/1/2006 866.79 12/4/2006 -4,966.81 1/1/2007 866.19 1/8/2007 -4,966.81 2/1/2007 866.19 2/5/2007 -4,966.81 3/1/2007 910.86 3/5/2007 -4,864.40 4/1/2007 910.20 4/5/2007 -4,864.40 5/1/2007 909.34 5/3/2007 -4,864.40 6/1/2007 908.50 6/4/2007 -4,864.40 7/1/2007 908.02 7/5/2007 -4,864.40 8/1/2007 907.87 8/3/2007 -4,864.40 9/1/2007 906.91 9/4/2007 -4,864.40 10/1/2007 905.77 10/3/2007 -4,924.67 11/1/2007 905.17 11/5/2007 -4,966.81 12/1/2007 904.60 12/6/2007 -4,966.81 1/1/2008 904.60 1/7/2008 -4,966.81 2/1/2008 904.79 2/6/2008 -4,966.81 3/1/2008 942.92 3/6/2008 -4,864.40 4/1/2008 941.97 4/3/2008 -4,864.40 5/1/2008 940.92 5/5/2008 -4,864.40 6/1/2008 940.48 6/2/2008 -4,864.40 7/1/2008 941.24 7/2/2008 -4,864.40 8/1/2008 941.24 8/4/2008 -4,864.40 9/1/2008 940.68 9/5/2008 -4,864.40 10/1/2008 942.05 10/6/2008 -4,924.67 11/1/2008 944.31 11/3/2008 -4,966.81 12/1/2008 945.74 12/3/2008 -4,966.81 12/28/2008 92,520.92 Monthly net cash flow approximation (my apologies for reversing the columns): -3,123.16 4/1/2006 -8,857.28 5/1/2006 -3,993.89 6/1/2006 -3,994.36 7/1/2006 -3,995.34 8/1/2006 -3,995.87 9/1/2006 -4,056.14 10/1/2006 -4,098.86 11/1/2006 -4,100.02 12/1/2006 -4,100.62 1/1/2007 -4,100.62 2/1/2007 -3,953.54 3/1/2007 -3,954.20 4/1/2007 -3,955.06 5/1/2007 -3,955.90 6/1/2007 -3,956.38 7/1/2007 -3,956.53 8/1/2007 -3,957.49 9/1/2007 -4,018.90 10/1/2007 -4,061.64 11/1/2007 -4,062.21 12/1/2007 -4,062.21 1/1/2008 -4,062.02 2/1/2008 -3,921.48 3/1/2008 -3,922.43 4/1/2008 -3,923.48 5/1/2008 -3,923.92 6/1/2008 -3,923.16 7/1/2008 -3,923.16 8/1/2008 -3,923.72 9/1/2008 -3,982.62 10/1/2008 -4,022.50 11/1/2008 -4,021.07 12/1/2008 92,520.92 1/1/2009 ----- original message ----- "Don Kline" wrote in message ... I have a XLS file I can send you. I don't know how to attach the XLS file to the discussion. Please advise how I can get this to you. "JoeU2004" wrote: "Don Kline" wrote: I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. That's a neat trick, since XIRR only returns the annualized rate. I hope that is what you mean. But I wonder if you are misinterpreting the results from XIRR. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? Yes. But I cannot tell you what that is because I'm not a mindreader. If you post an example, you might get some help. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
Clarification....
"JoeU2004" wrote: the Excel documentation disagrees with you, not in silence, but by explicit instruction. It states: "In most cases you do not need to provide guess for the XIRR calculation". [....] "The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0" Arguably, these may or may not be product "specifications" per se. Most user documentation these days is written by someone who has an English degree, and sometimes not even that :-(. Gone are the days when documentation was written by the product developer, or at least by a "tech writer" who used to develop similar products, who had access to source code or hardware drawings, and who was even expected to "test" his/her documentation against the product ("what a concept"!). (I'm talking about at large corporations like MS.) So someone might reasonably argue that the Excel Help pages are not product "specifications" per se, but simply one writer's interpretation of how he/she thought the product works. Hopefully, that is based on internal documentation (true specifications) written by the product developer. But sometimes, it is the result of the writer's own usage of the product. But usually, the product developer is responsible for reviewing the writer's documentation. And usually, the mistakes that get by are errors of omission, not assertive statements that the product this or that. In my experience, end users tend to treat the word of the user documentation as a product specification. (I am speaking from painful experience :-<.) All that aside, I think it is common sense that the XIRR() __function__ should not return a rate that fails to cause the (X)NPV to be close to zero. And again, I am not saying that the internal __algorithm__ should not converge on such invalid rates. It might; that's the nature of the beast. I am simply saying that any invalid rates found by the algorithm should be hidden from the user, substituting an error condition instead. I hope my intention are clearer. ----- original message ----- "JoeU2004" wrote in message ... "Fred Smith" wrote: The silver lining to their laziness is that you and I get to pontificate on the machinations of XIRR. <g As we both agree, MS itself could have programmed XIRR to pick a different guess, and try again. Where in my "pontification" do you think I said anything like that. I explicitly did not disparage the XIRR approximation algorithm. On the contrary, I wrote: "I presume that it is working as well as might be expected". Why can't users figure out there's a reason XIRR has a guess parameter? Because the XIRR() function fails to let them know when they should. If you are saying that we should always provide "guess", well, the Excel documentation disagrees with you, not in silence, but by explicit instruction. It states: "In most cases you do not need to provide guess for the XIRR calculation". Newton-Raphson can't be blamed for poor programming on the part of MS. I explicitly said as much, too. Why didn't MS do a better job of programming XIRR? Because they're programmers. Spoken by someone who apparently knows nothing about computer programming, at least not professionally. I was a professional programmer and system architect for 35 years. Part of my responsibility, in effect, was to specify what can expect of programmers. It meets the spec I have quoted Excel's specs. I think it is clear that you are incorrect. It says: "The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0". Don provides an example where that specification is not met: the rate of return calculated by XIRR is not a rate that causes the XNPV to be zero. All I was saying is: XIRR() should return an error when its algorithm computes a discount rate that does not cause the NPV to be (close to) zero. That does not disparage the algorithm. It is to be expected that the algorithm will miss the mark sometimes, as you say. ----- original message ----- "Fred Smith" wrote in message ... Joe, Glad you were able to help Don. My comments a As is usual, a more reasonable guess solved the problem. Why can't users figure out there's a reason XIRR has a guess parameter? Because they're users -- they just want it to work. Why didn't MS do a better job of programming XIRR? Because they're programmers. It meets the spec, so why put in extra work. My bet is that over 90% of the errors in using XIRR can be fixed by using a guess of 10% when the total cash flow is positive, and -10% when it's negative. In fact, I always use a guess of 10%*sign(sum(cashflows)). It would have been so simple for MS to do the same, but unfortunately, they didn't. Newton-Raphson can't be blamed for poor programming on the part of MS. Any calculation of the ROI for irregular investments must use iteration to find the result. There are several iteration algorithms available; NR just happens to be the fastest, which is why everyone uses it. As it's been explained to me, it's like finding the edge of the lake from a starting point on the shore. You calculate the tangent of the curve you are on as the next starting point. You continue until you find the lake. Unfortunately, if you start in a hollow, you go off in the wrong direction, and never converge towards the lake. In this situation, you need to pick a different guess. It doesn't have to be a *better* guess, just different (using my analogy, somewhere out of the hollow). As we both agree, MS itself could have programmed XIRR to pick a different guess, and try again. They could easily have calculated a guess based on the ratio of the total positive cash flows to the total negative. But they didn't. The silver lining to their laziness is that you and I get to pontificate on the machinations of XIRR. Regards, Fred "JoeU2004" wrote in message ... Don acknowledged in email that the -400% that he observed was due to human error. Nonetheless, for the cash flow stream that he sent me (below), XIRR() returns about 0.0000002980%. That is obviously incorrect, since XNPV() returns about -43,338.86, which is not even close to zero. The root cause is the need for a "guess" argument (Don did not have one), and a correct "guess" argument at that. Reducing the irregular daily cash flows to monthly net cash flows (see below), we see a series of negative cash flows (investments) and one positive cash flow (return). Since the sum of the investments (about -136K) is much larger than the return (about 96K), we expect a negative IRR. So I tried a "guess" of -1%, and XIRR() returned -24.83%. Plugging the exact IRR into XNPV() using the daily cash flow, the result is indeed close to zero. QED. Don still wonders, reasonably, why XIRR() returns such an obviously incorrect rate when the "guess" was missing. I invite people like Fred Smith and Myrna Larson to comment on the behavior of the Newton-Raphson method of approximation, or whatever method Excel might use. We all know that these methods can take a wrong turn under adverse conditions. But some details might be interesting. In any case, I would call this a defect in the XIRR() implementation. No, I am not disparaging your beloved approximation method. I presume that it is working as well as might be expected. Instead, I am disparaging the witless MS programmer who does not seem to know how to write an "if" statement just before exiting the XIRR() function, of the form: "if there has not been an error and the XNPV with the last result is not close to zero, return an error, per the function specification". I am referring to the XIRR Help page, which states: "If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned". Now, we might argue about what "close to zero" means exactly. But I am sure that -43,338.86 does not meet anyone's reasonable definition. So clearly, the XIRR() result does not "work", even it was found in fewer than 100 iterations. If XIRR() had returned the #NUM! error, as it should in this case, Don might still have been perplexed. But at least his question would have been much less mysterious; probably something to the effect of: "how in the heck am I supposed to know what ``guess`` should be?", and "why does Excel need this, but my HP 12C does not?". We've dealt with such questions before. The following is Don's original data and my monthly net cash flow approximation. Don's cash flow (my apologies for the poor alignment; there are too many to adjust manually): 3/27/2006 -3,994.58 4/1/2006 871.42 5/1/2006 871.52 5/3/2006 -9,728.80 6/1/2006 870.51 6/2/2006 -4,864.40 7/1/2006 870.04 7/5/2006 -4,864.40 8/1/2006 869.06 8/3/2006 -4,864.40 9/1/2006 868.53 9/6/2006 -4,864.40 10/1/2006 868.53 10/4/2006 -4,924.67 11/1/2006 867.95 11/6/2006 -4,966.81 12/1/2006 866.79 12/4/2006 -4,966.81 1/1/2007 866.19 1/8/2007 -4,966.81 2/1/2007 866.19 2/5/2007 -4,966.81 3/1/2007 910.86 3/5/2007 -4,864.40 4/1/2007 910.20 4/5/2007 -4,864.40 5/1/2007 909.34 5/3/2007 -4,864.40 6/1/2007 908.50 6/4/2007 -4,864.40 7/1/2007 908.02 7/5/2007 -4,864.40 8/1/2007 907.87 8/3/2007 -4,864.40 9/1/2007 906.91 9/4/2007 -4,864.40 10/1/2007 905.77 10/3/2007 -4,924.67 11/1/2007 905.17 11/5/2007 -4,966.81 12/1/2007 904.60 12/6/2007 -4,966.81 1/1/2008 904.60 1/7/2008 -4,966.81 2/1/2008 904.79 2/6/2008 -4,966.81 3/1/2008 942.92 3/6/2008 -4,864.40 4/1/2008 941.97 4/3/2008 -4,864.40 5/1/2008 940.92 5/5/2008 -4,864.40 6/1/2008 940.48 6/2/2008 -4,864.40 7/1/2008 941.24 7/2/2008 -4,864.40 8/1/2008 941.24 8/4/2008 -4,864.40 9/1/2008 940.68 9/5/2008 -4,864.40 10/1/2008 942.05 10/6/2008 -4,924.67 11/1/2008 944.31 11/3/2008 -4,966.81 12/1/2008 945.74 12/3/2008 -4,966.81 12/28/2008 92,520.92 Monthly net cash flow approximation (my apologies for reversing the columns): -3,123.16 4/1/2006 -8,857.28 5/1/2006 -3,993.89 6/1/2006 -3,994.36 7/1/2006 -3,995.34 8/1/2006 -3,995.87 9/1/2006 -4,056.14 10/1/2006 -4,098.86 11/1/2006 -4,100.02 12/1/2006 -4,100.62 1/1/2007 -4,100.62 2/1/2007 -3,953.54 3/1/2007 -3,954.20 4/1/2007 -3,955.06 5/1/2007 -3,955.90 6/1/2007 -3,956.38 7/1/2007 -3,956.53 8/1/2007 -3,957.49 9/1/2007 -4,018.90 10/1/2007 -4,061.64 11/1/2007 -4,062.21 12/1/2007 -4,062.21 1/1/2008 -4,062.02 2/1/2008 -3,921.48 3/1/2008 -3,922.43 4/1/2008 -3,923.48 5/1/2008 -3,923.92 6/1/2008 -3,923.16 7/1/2008 -3,923.16 8/1/2008 -3,923.72 9/1/2008 -3,982.62 10/1/2008 -4,022.50 11/1/2008 -4,021.07 12/1/2008 92,520.92 1/1/2009 ----- original message ----- "Don Kline" wrote in message ... I have a XLS file I can send you. I don't know how to attach the XLS file to the discussion. Please advise how I can get this to you. "JoeU2004" wrote: "Don Kline" wrote: I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. That's a neat trick, since XIRR only returns the annualized rate. I hope that is what you mean. But I wonder if you are misinterpreting the results from XIRR. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? Yes. But I cannot tell you what that is because I'm not a mindreader. If you post an example, you might get some help. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
"XIRR() __function__ should not return a rate that fails to cause the (X)NPV
to be close to zero." I agree. When this happens, XIRR should return #NUM instead (as Help states), instead of a fictitious number. This would make it easier to determine the required fix, which is almost always a better guess. When XIRR returns a fictitious number, the fix isn't nearly as obvious. Sorry if I offended you with my previous post. It was unintended. Regards, Fred. "JoeU2004" wrote in message ... Clarification.... "JoeU2004" wrote: the Excel documentation disagrees with you, not in silence, but by explicit instruction. It states: "In most cases you do not need to provide guess for the XIRR calculation". [....] "The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0" Arguably, these may or may not be product "specifications" per se. Most user documentation these days is written by someone who has an English degree, and sometimes not even that :-(. Gone are the days when documentation was written by the product developer, or at least by a "tech writer" who used to develop similar products, who had access to source code or hardware drawings, and who was even expected to "test" his/her documentation against the product ("what a concept"!). (I'm talking about at large corporations like MS.) So someone might reasonably argue that the Excel Help pages are not product "specifications" per se, but simply one writer's interpretation of how he/she thought the product works. Hopefully, that is based on internal documentation (true specifications) written by the product developer. But sometimes, it is the result of the writer's own usage of the product. But usually, the product developer is responsible for reviewing the writer's documentation. And usually, the mistakes that get by are errors of omission, not assertive statements that the product this or that. In my experience, end users tend to treat the word of the user documentation as a product specification. (I am speaking from painful experience :-<.) All that aside, I think it is common sense that the XIRR() __function__ should not return a rate that fails to cause the (X)NPV to be close to zero. And again, I am not saying that the internal __algorithm__ should not converge on such invalid rates. It might; that's the nature of the beast. I am simply saying that any invalid rates found by the algorithm should be hidden from the user, substituting an error condition instead. I hope my intention are clearer. ----- original message ----- "JoeU2004" wrote in message ... "Fred Smith" wrote: The silver lining to their laziness is that you and I get to pontificate on the machinations of XIRR. <g As we both agree, MS itself could have programmed XIRR to pick a different guess, and try again. Where in my "pontification" do you think I said anything like that. I explicitly did not disparage the XIRR approximation algorithm. On the contrary, I wrote: "I presume that it is working as well as might be expected". Why can't users figure out there's a reason XIRR has a guess parameter? Because the XIRR() function fails to let them know when they should. If you are saying that we should always provide "guess", well, the Excel documentation disagrees with you, not in silence, but by explicit instruction. It states: "In most cases you do not need to provide guess for the XIRR calculation". Newton-Raphson can't be blamed for poor programming on the part of MS. I explicitly said as much, too. Why didn't MS do a better job of programming XIRR? Because they're programmers. Spoken by someone who apparently knows nothing about computer programming, at least not professionally. I was a professional programmer and system architect for 35 years. Part of my responsibility, in effect, was to specify what can expect of programmers. It meets the spec I have quoted Excel's specs. I think it is clear that you are incorrect. It says: "The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0". Don provides an example where that specification is not met: the rate of return calculated by XIRR is not a rate that causes the XNPV to be zero. All I was saying is: XIRR() should return an error when its algorithm computes a discount rate that does not cause the NPV to be (close to) zero. That does not disparage the algorithm. It is to be expected that the algorithm will miss the mark sometimes, as you say. ----- original message ----- "Fred Smith" wrote in message ... Joe, Glad you were able to help Don. My comments a As is usual, a more reasonable guess solved the problem. Why can't users figure out there's a reason XIRR has a guess parameter? Because they're users -- they just want it to work. Why didn't MS do a better job of programming XIRR? Because they're programmers. It meets the spec, so why put in extra work. My bet is that over 90% of the errors in using XIRR can be fixed by using a guess of 10% when the total cash flow is positive, and -10% when it's negative. In fact, I always use a guess of 10%*sign(sum(cashflows)). It would have been so simple for MS to do the same, but unfortunately, they didn't. Newton-Raphson can't be blamed for poor programming on the part of MS. Any calculation of the ROI for irregular investments must use iteration to find the result. There are several iteration algorithms available; NR just happens to be the fastest, which is why everyone uses it. As it's been explained to me, it's like finding the edge of the lake from a starting point on the shore. You calculate the tangent of the curve you are on as the next starting point. You continue until you find the lake. Unfortunately, if you start in a hollow, you go off in the wrong direction, and never converge towards the lake. In this situation, you need to pick a different guess. It doesn't have to be a *better* guess, just different (using my analogy, somewhere out of the hollow). As we both agree, MS itself could have programmed XIRR to pick a different guess, and try again. They could easily have calculated a guess based on the ratio of the total positive cash flows to the total negative. But they didn't. The silver lining to their laziness is that you and I get to pontificate on the machinations of XIRR. Regards, Fred "JoeU2004" wrote in message ... Don acknowledged in email that the -400% that he observed was due to human error. Nonetheless, for the cash flow stream that he sent me (below), XIRR() returns about 0.0000002980%. That is obviously incorrect, since XNPV() returns about -43,338.86, which is not even close to zero. The root cause is the need for a "guess" argument (Don did not have one), and a correct "guess" argument at that. Reducing the irregular daily cash flows to monthly net cash flows (see below), we see a series of negative cash flows (investments) and one positive cash flow (return). Since the sum of the investments (about -136K) is much larger than the return (about 96K), we expect a negative IRR. So I tried a "guess" of -1%, and XIRR() returned -24.83%. Plugging the exact IRR into XNPV() using the daily cash flow, the result is indeed close to zero. QED. Don still wonders, reasonably, why XIRR() returns such an obviously incorrect rate when the "guess" was missing. I invite people like Fred Smith and Myrna Larson to comment on the behavior of the Newton-Raphson method of approximation, or whatever method Excel might use. We all know that these methods can take a wrong turn under adverse conditions. But some details might be interesting. In any case, I would call this a defect in the XIRR() implementation. No, I am not disparaging your beloved approximation method. I presume that it is working as well as might be expected. Instead, I am disparaging the witless MS programmer who does not seem to know how to write an "if" statement just before exiting the XIRR() function, of the form: "if there has not been an error and the XNPV with the last result is not close to zero, return an error, per the function specification". I am referring to the XIRR Help page, which states: "If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned". Now, we might argue about what "close to zero" means exactly. But I am sure that -43,338.86 does not meet anyone's reasonable definition. So clearly, the XIRR() result does not "work", even it was found in fewer than 100 iterations. If XIRR() had returned the #NUM! error, as it should in this case, Don might still have been perplexed. But at least his question would have been much less mysterious; probably something to the effect of: "how in the heck am I supposed to know what ``guess`` should be?", and "why does Excel need this, but my HP 12C does not?". We've dealt with such questions before. The following is Don's original data and my monthly net cash flow approximation. Don's cash flow (my apologies for the poor alignment; there are too many to adjust manually): 3/27/2006 -3,994.58 4/1/2006 871.42 5/1/2006 871.52 5/3/2006 -9,728.80 6/1/2006 870.51 6/2/2006 -4,864.40 7/1/2006 870.04 7/5/2006 -4,864.40 8/1/2006 869.06 8/3/2006 -4,864.40 9/1/2006 868.53 9/6/2006 -4,864.40 10/1/2006 868.53 10/4/2006 -4,924.67 11/1/2006 867.95 11/6/2006 -4,966.81 12/1/2006 866.79 12/4/2006 -4,966.81 1/1/2007 866.19 1/8/2007 -4,966.81 2/1/2007 866.19 2/5/2007 -4,966.81 3/1/2007 910.86 3/5/2007 -4,864.40 4/1/2007 910.20 4/5/2007 -4,864.40 5/1/2007 909.34 5/3/2007 -4,864.40 6/1/2007 908.50 6/4/2007 -4,864.40 7/1/2007 908.02 7/5/2007 -4,864.40 8/1/2007 907.87 8/3/2007 -4,864.40 9/1/2007 906.91 9/4/2007 -4,864.40 10/1/2007 905.77 10/3/2007 -4,924.67 11/1/2007 905.17 11/5/2007 -4,966.81 12/1/2007 904.60 12/6/2007 -4,966.81 1/1/2008 904.60 1/7/2008 -4,966.81 2/1/2008 904.79 2/6/2008 -4,966.81 3/1/2008 942.92 3/6/2008 -4,864.40 4/1/2008 941.97 4/3/2008 -4,864.40 5/1/2008 940.92 5/5/2008 -4,864.40 6/1/2008 940.48 6/2/2008 -4,864.40 7/1/2008 941.24 7/2/2008 -4,864.40 8/1/2008 941.24 8/4/2008 -4,864.40 9/1/2008 940.68 9/5/2008 -4,864.40 10/1/2008 942.05 10/6/2008 -4,924.67 11/1/2008 944.31 11/3/2008 -4,966.81 12/1/2008 945.74 12/3/2008 -4,966.81 12/28/2008 92,520.92 Monthly net cash flow approximation (my apologies for reversing the columns): -3,123.16 4/1/2006 -8,857.28 5/1/2006 -3,993.89 6/1/2006 -3,994.36 7/1/2006 -3,995.34 8/1/2006 -3,995.87 9/1/2006 -4,056.14 10/1/2006 -4,098.86 11/1/2006 -4,100.02 12/1/2006 -4,100.62 1/1/2007 -4,100.62 2/1/2007 -3,953.54 3/1/2007 -3,954.20 4/1/2007 -3,955.06 5/1/2007 -3,955.90 6/1/2007 -3,956.38 7/1/2007 -3,956.53 8/1/2007 -3,957.49 9/1/2007 -4,018.90 10/1/2007 -4,061.64 11/1/2007 -4,062.21 12/1/2007 -4,062.21 1/1/2008 -4,062.02 2/1/2008 -3,921.48 3/1/2008 -3,922.43 4/1/2008 -3,923.48 5/1/2008 -3,923.92 6/1/2008 -3,923.16 7/1/2008 -3,923.16 8/1/2008 -3,923.72 9/1/2008 -3,982.62 10/1/2008 -4,022.50 11/1/2008 -4,021.07 12/1/2008 92,520.92 1/1/2009 ----- original message ----- "Don Kline" wrote in message ... I have a XLS file I can send you. I don't know how to attach the XLS file to the discussion. Please advise how I can get this to you. "JoeU2004" wrote: "Don Kline" wrote: I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. That's a neat trick, since XIRR only returns the annualized rate. I hope that is what you mean. But I wonder if you are misinterpreting the results from XIRR. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? Yes. But I cannot tell you what that is because I'm not a mindreader. If you post an example, you might get some help. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR over several years
"Fred Smith" wrote in message
... "XIRR() __function__ should not return a rate that fails to cause the (X)NPV to be close to zero." I agree. When this happens, XIRR should return #NUM instead (as Help states), instead of a fictitious number. [....] Sorry if I offended you with my previous post. It was unintended. No offense taken. I was just frustrated because I had tried to make this very point several times in two threads, but I was not making myself understood. Anyway, I'm glad to see we are on the same page now. Thanks for posting back. ----- original message ----- "Fred Smith" wrote in message ... "XIRR() __function__ should not return a rate that fails to cause the (X)NPV to be close to zero." I agree. When this happens, XIRR should return #NUM instead (as Help states), instead of a fictitious number. This would make it easier to determine the required fix, which is almost always a better guess. When XIRR returns a fictitious number, the fix isn't nearly as obvious. Sorry if I offended you with my previous post. It was unintended. Regards, Fred. "JoeU2004" wrote in message ... Clarification.... "JoeU2004" wrote: the Excel documentation disagrees with you, not in silence, but by explicit instruction. It states: "In most cases you do not need to provide guess for the XIRR calculation". [....] "The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0" Arguably, these may or may not be product "specifications" per se. Most user documentation these days is written by someone who has an English degree, and sometimes not even that :-(. Gone are the days when documentation was written by the product developer, or at least by a "tech writer" who used to develop similar products, who had access to source code or hardware drawings, and who was even expected to "test" his/her documentation against the product ("what a concept"!). (I'm talking about at large corporations like MS.) So someone might reasonably argue that the Excel Help pages are not product "specifications" per se, but simply one writer's interpretation of how he/she thought the product works. Hopefully, that is based on internal documentation (true specifications) written by the product developer. But sometimes, it is the result of the writer's own usage of the product. But usually, the product developer is responsible for reviewing the writer's documentation. And usually, the mistakes that get by are errors of omission, not assertive statements that the product this or that. In my experience, end users tend to treat the word of the user documentation as a product specification. (I am speaking from painful experience :-<.) All that aside, I think it is common sense that the XIRR() __function__ should not return a rate that fails to cause the (X)NPV to be close to zero. And again, I am not saying that the internal __algorithm__ should not converge on such invalid rates. It might; that's the nature of the beast. I am simply saying that any invalid rates found by the algorithm should be hidden from the user, substituting an error condition instead. I hope my intention are clearer. ----- original message ----- "JoeU2004" wrote in message ... "Fred Smith" wrote: The silver lining to their laziness is that you and I get to pontificate on the machinations of XIRR. <g As we both agree, MS itself could have programmed XIRR to pick a different guess, and try again. Where in my "pontification" do you think I said anything like that. I explicitly did not disparage the XIRR approximation algorithm. On the contrary, I wrote: "I presume that it is working as well as might be expected". Why can't users figure out there's a reason XIRR has a guess parameter? Because the XIRR() function fails to let them know when they should. If you are saying that we should always provide "guess", well, the Excel documentation disagrees with you, not in silence, but by explicit instruction. It states: "In most cases you do not need to provide guess for the XIRR calculation". Newton-Raphson can't be blamed for poor programming on the part of MS. I explicitly said as much, too. Why didn't MS do a better job of programming XIRR? Because they're programmers. Spoken by someone who apparently knows nothing about computer programming, at least not professionally. I was a professional programmer and system architect for 35 years. Part of my responsibility, in effect, was to specify what can expect of programmers. It meets the spec I have quoted Excel's specs. I think it is clear that you are incorrect. It says: "The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0". Don provides an example where that specification is not met: the rate of return calculated by XIRR is not a rate that causes the XNPV to be zero. All I was saying is: XIRR() should return an error when its algorithm computes a discount rate that does not cause the NPV to be (close to) zero. That does not disparage the algorithm. It is to be expected that the algorithm will miss the mark sometimes, as you say. ----- original message ----- "Fred Smith" wrote in message ... Joe, Glad you were able to help Don. My comments a As is usual, a more reasonable guess solved the problem. Why can't users figure out there's a reason XIRR has a guess parameter? Because they're users -- they just want it to work. Why didn't MS do a better job of programming XIRR? Because they're programmers. It meets the spec, so why put in extra work. My bet is that over 90% of the errors in using XIRR can be fixed by using a guess of 10% when the total cash flow is positive, and -10% when it's negative. In fact, I always use a guess of 10%*sign(sum(cashflows)). It would have been so simple for MS to do the same, but unfortunately, they didn't. Newton-Raphson can't be blamed for poor programming on the part of MS. Any calculation of the ROI for irregular investments must use iteration to find the result. There are several iteration algorithms available; NR just happens to be the fastest, which is why everyone uses it. As it's been explained to me, it's like finding the edge of the lake from a starting point on the shore. You calculate the tangent of the curve you are on as the next starting point. You continue until you find the lake. Unfortunately, if you start in a hollow, you go off in the wrong direction, and never converge towards the lake. In this situation, you need to pick a different guess. It doesn't have to be a *better* guess, just different (using my analogy, somewhere out of the hollow). As we both agree, MS itself could have programmed XIRR to pick a different guess, and try again. They could easily have calculated a guess based on the ratio of the total positive cash flows to the total negative. But they didn't. The silver lining to their laziness is that you and I get to pontificate on the machinations of XIRR. Regards, Fred "JoeU2004" wrote in message ... Don acknowledged in email that the -400% that he observed was due to human error. Nonetheless, for the cash flow stream that he sent me (below), XIRR() returns about 0.0000002980%. That is obviously incorrect, since XNPV() returns about -43,338.86, which is not even close to zero. The root cause is the need for a "guess" argument (Don did not have one), and a correct "guess" argument at that. Reducing the irregular daily cash flows to monthly net cash flows (see below), we see a series of negative cash flows (investments) and one positive cash flow (return). Since the sum of the investments (about -136K) is much larger than the return (about 96K), we expect a negative IRR. So I tried a "guess" of -1%, and XIRR() returned -24.83%. Plugging the exact IRR into XNPV() using the daily cash flow, the result is indeed close to zero. QED. Don still wonders, reasonably, why XIRR() returns such an obviously incorrect rate when the "guess" was missing. I invite people like Fred Smith and Myrna Larson to comment on the behavior of the Newton-Raphson method of approximation, or whatever method Excel might use. We all know that these methods can take a wrong turn under adverse conditions. But some details might be interesting. In any case, I would call this a defect in the XIRR() implementation. No, I am not disparaging your beloved approximation method. I presume that it is working as well as might be expected. Instead, I am disparaging the witless MS programmer who does not seem to know how to write an "if" statement just before exiting the XIRR() function, of the form: "if there has not been an error and the XNPV with the last result is not close to zero, return an error, per the function specification". I am referring to the XIRR Help page, which states: "If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned". Now, we might argue about what "close to zero" means exactly. But I am sure that -43,338.86 does not meet anyone's reasonable definition. So clearly, the XIRR() result does not "work", even it was found in fewer than 100 iterations. If XIRR() had returned the #NUM! error, as it should in this case, Don might still have been perplexed. But at least his question would have been much less mysterious; probably something to the effect of: "how in the heck am I supposed to know what ``guess`` should be?", and "why does Excel need this, but my HP 12C does not?". We've dealt with such questions before. The following is Don's original data and my monthly net cash flow approximation. Don's cash flow (my apologies for the poor alignment; there are too many to adjust manually): 3/27/2006 -3,994.58 4/1/2006 871.42 5/1/2006 871.52 5/3/2006 -9,728.80 6/1/2006 870.51 6/2/2006 -4,864.40 7/1/2006 870.04 7/5/2006 -4,864.40 8/1/2006 869.06 8/3/2006 -4,864.40 9/1/2006 868.53 9/6/2006 -4,864.40 10/1/2006 868.53 10/4/2006 -4,924.67 11/1/2006 867.95 11/6/2006 -4,966.81 12/1/2006 866.79 12/4/2006 -4,966.81 1/1/2007 866.19 1/8/2007 -4,966.81 2/1/2007 866.19 2/5/2007 -4,966.81 3/1/2007 910.86 3/5/2007 -4,864.40 4/1/2007 910.20 4/5/2007 -4,864.40 5/1/2007 909.34 5/3/2007 -4,864.40 6/1/2007 908.50 6/4/2007 -4,864.40 7/1/2007 908.02 7/5/2007 -4,864.40 8/1/2007 907.87 8/3/2007 -4,864.40 9/1/2007 906.91 9/4/2007 -4,864.40 10/1/2007 905.77 10/3/2007 -4,924.67 11/1/2007 905.17 11/5/2007 -4,966.81 12/1/2007 904.60 12/6/2007 -4,966.81 1/1/2008 904.60 1/7/2008 -4,966.81 2/1/2008 904.79 2/6/2008 -4,966.81 3/1/2008 942.92 3/6/2008 -4,864.40 4/1/2008 941.97 4/3/2008 -4,864.40 5/1/2008 940.92 5/5/2008 -4,864.40 6/1/2008 940.48 6/2/2008 -4,864.40 7/1/2008 941.24 7/2/2008 -4,864.40 8/1/2008 941.24 8/4/2008 -4,864.40 9/1/2008 940.68 9/5/2008 -4,864.40 10/1/2008 942.05 10/6/2008 -4,924.67 11/1/2008 944.31 11/3/2008 -4,966.81 12/1/2008 945.74 12/3/2008 -4,966.81 12/28/2008 92,520.92 Monthly net cash flow approximation (my apologies for reversing the columns): -3,123.16 4/1/2006 -8,857.28 5/1/2006 -3,993.89 6/1/2006 -3,994.36 7/1/2006 -3,995.34 8/1/2006 -3,995.87 9/1/2006 -4,056.14 10/1/2006 -4,098.86 11/1/2006 -4,100.02 12/1/2006 -4,100.62 1/1/2007 -4,100.62 2/1/2007 -3,953.54 3/1/2007 -3,954.20 4/1/2007 -3,955.06 5/1/2007 -3,955.90 6/1/2007 -3,956.38 7/1/2007 -3,956.53 8/1/2007 -3,957.49 9/1/2007 -4,018.90 10/1/2007 -4,061.64 11/1/2007 -4,062.21 12/1/2007 -4,062.21 1/1/2008 -4,062.02 2/1/2008 -3,921.48 3/1/2008 -3,922.43 4/1/2008 -3,923.48 5/1/2008 -3,923.92 6/1/2008 -3,923.16 7/1/2008 -3,923.16 8/1/2008 -3,923.72 9/1/2008 -3,982.62 10/1/2008 -4,022.50 11/1/2008 -4,021.07 12/1/2008 92,520.92 1/1/2009 ----- original message ----- "Don Kline" wrote in message ... I have a XLS file I can send you. I don't know how to attach the XLS file to the discussion. Please advise how I can get this to you. "JoeU2004" wrote: "Don Kline" wrote: I have a series of monthly transactions over several years. I have used the XIRR function to find the ROR for each month, each quarter, and each calendar year. That's a neat trick, since XIRR only returns the annualized rate. I hope that is what you mean. But I wonder if you are misinterpreting the results from XIRR. The client now wants a rate from inception. My expectation was I would get an annual rate for the entire period. However I feel that a -400% ROR is not accurate. Is there anything different I need to do when the period is greater than one year? Yes. But I cannot tell you what that is because I'm not a mindreader. If you post an example, you might get some help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change a list of 2 digit years to 4 digit years? | Excel Worksheet Functions | |||
How do I subtract dates to get a number in years or years & month | Excel Discussion (Misc queries) | |||
Convert years to years and days | Excel Discussion (Misc queries) | |||
Overlay 4 years of data as a line on 4 years of columns for several x category labels | Charts and Charting in Excel | |||
Change Xcel Amortization from 30 years to 40 years? | Excel Discussion (Misc queries) |