IRR and NPV
Hi,
IRR, according to explanation in Excel, is of a similar concept with NPV. But if my cash flow starts from say 2009 onwards (and any cash flows between 2007-2009 are 0), IRR gives me the same result whether or not I choose cells starting from 2007 or 2009. But with NPV function, the results are different. How can I get the right IRRs for different periods with the IRR function? Thanks! tom |
IRR and NPV
Hi,
I add an example, hoping that it will stimulate replies:) A B C D E F year 2006 2007 2008 2009 2010 - - -9,024,420 -580,864 -85,079 Equity IRR 9.60% 9.60% 9.60% (actually the table goes to 2029) Shouldn't IRR starting from different years - assuming that the cashflow in the starting year (2006 or 2007) are both zero -, both ending in 2029 give different results? "tom ossieur" wrote: Hi, IRR, according to explanation in Excel, is of a similar concept with NPV. But if my cash flow starts from say 2009 onwards (and any cash flows between 2007-2009 are 0), IRR gives me the same result whether or not I choose cells starting from 2007 or 2009. But with NPV function, the results are different. How can I get the right IRRs for different periods with the IRR function? Thanks! tom |
IRR and NPV
tom ossieur wrote:
IRR, according to explanation in Excel, is of a similar concept with NPV. They are __related__, not "similar". The IRR is the rate that causes the NPV to be zero. But if my cash flow starts from say 2009 onwards (and any cash flows between 2007-2009 are 0), IRR gives me the same result whether or not I choose cells starting from 2007 or 2009. There are many ways to try to explain this. Mathematically, if you look at the formula on the Excel NPV help page, it might become "obvious" to you. The Excel formula is: NPV = 0 = CF1/(1+r)^1 + CF2/(1+r)^2 + CF3/(1+r)^3 + CF4/(1+r)^4 + .... If the first k terms (e.g. CF1 through CF3 in your case) are zero, then the IRR (r) must be chosen so that the later N-k terms sum to zero. No matter how many zero cash flows you put in front of them, the IRR will be the same since 0/(1+r)^x is zero for any x. Put another way, we can multiply the equation by (1+r)^3 (if the first 3 terms are zero) to convert it to the following equivalent equation: NPV = 0 = CF1/(1+r)^(-2) + CF2/(1+r)^(-1) + CF3/(1+r)^0 + CF4/(1+r)^1 + ..... Dropping the first three terms, which are zero, we get: NPV = 0 = CF4/(1+r)^1 + .... So the IRR is unaffect by the initial zero cash flows. In effect, the IRR is the rate that causes the NPV to be zero, starting with the first non-zero cash flow. But with NPV function, the results are different. I am not sure what you mean. The NPV can be any value you want, depending on the rate that you choose. And yes, given the same chosen rate, adding more initial cash flows of zero will change the NPV (if it is non-zero) because the value of the first non-zero cash flow -- CFn/(1+r)^n -- will change. However, there is only one rate for which the NPV will be zero. (Well, that might be an exaggeration mathematically speaking. Perhaps I should say that we hope there is only one __practical__ rate.) How can I get the right IRRs for different periods with the IRR function? "For different periods"? The question does not make sense to me. The IRR is "the" rate for all periods of the series of cash flows. You can find the IRR for different series of cash flows, for example composed of fewer and fewer consecutive periods. But I am not sure that is what you mean. |
IRR and NPV
tom ossieur wrote:
I add an example, hoping that it will stimulate replies:) Patience is a virtue that you should learn. In any case, the example does not help to understand whatever it is you are trying to do. A B C D E F year 2006 2007 2008 2009 2010 - - -9,024,420 -580,864 -85,079 Equity IRR 9.60% 9.60% 9.60% In a later thread, you replace the first term (-9,024,420) with zero. I will assume that is your intention, which forestalls some other criticisms of this example. (actually the table goes to 2029) The example is not useful to me. First, it is difficult to see how the IRR values should align with the cash flows. That is not your fault: my newsreader does not do a good job of presenting columnarized data. Second, it might be helpful to see the formulas that you use to compute the IRR. They should not all be the same, unless they align with the zero (and "-") cash flows. If they are indeed all the same through the year 2029, I suspect that you made one of several possible mistakes. Some human errors that come to mind a (1) perhaps you used an absolute cell reference (e.g. $B$3) for the starting cash flow; or (2) perhaps you copied the IRR formula while you had manual calculation enabled; or (3) perhaps the numbers are such that the IRR __appears__ to be the same (but it is not) up to the 4th fractional digit; or (4) perhaps the IRR formula is completely wrong altogether; or .... Third, in order to check your results, we need to see __all__ cash flows all the way through 2029. Obviously that would be tedious to show in a row. I suggest that you present the data in a column. Shouldn't IRR starting from different years - assuming that the cashflow in the starting year (2006 or 2007) are both zero -, both ending in 2029 give different results? I hope I answered that question in my response to the first article that you posted in the thread. If not, please write back with more questions. |
IRR and NPV
Hi Joe(?)!
Thank you very much for your detailed answer! and I knwo patience is a virtue, though sometimes it is difficult i posted this question for a friend and many thanks from her - and me - for these answers once again! tom " wrote: tom ossieur wrote: I add an example, hoping that it will stimulate replies:) Patience is a virtue that you should learn. In any case, the example does not help to understand whatever it is you are trying to do. A B C D E F year 2006 2007 2008 2009 2010 - - -9,024,420 -580,864 -85,079 Equity IRR 9.60% 9.60% 9.60% In a later thread, you replace the first term (-9,024,420) with zero. I will assume that is your intention, which forestalls some other criticisms of this example. (actually the table goes to 2029) The example is not useful to me. First, it is difficult to see how the IRR values should align with the cash flows. That is not your fault: my newsreader does not do a good job of presenting columnarized data. Second, it might be helpful to see the formulas that you use to compute the IRR. They should not all be the same, unless they align with the zero (and "-") cash flows. If they are indeed all the same through the year 2029, I suspect that you made one of several possible mistakes. Some human errors that come to mind a (1) perhaps you used an absolute cell reference (e.g. $B$3) for the starting cash flow; or (2) perhaps you copied the IRR formula while you had manual calculation enabled; or (3) perhaps the numbers are such that the IRR __appears__ to be the same (but it is not) up to the 4th fractional digit; or (4) perhaps the IRR formula is completely wrong altogether; or .... Third, in order to check your results, we need to see __all__ cash flows all the way through 2029. Obviously that would be tedious to show in a row. I suggest that you present the data in a column. Shouldn't IRR starting from different years - assuming that the cashflow in the starting year (2006 or 2007) are both zero -, both ending in 2029 give different results? I hope I answered that question in my response to the first article that you posted in the thread. If not, please write back with more questions. |
All times are GMT +1. The time now is 03:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com