Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
irr function
Guys, I really need some help. I am stuck with this problem that should not
happened and in my understanding should happened the other way around. Could somebody help me. I include some scenario that I can't solved. Thanks Is there is an error in the formula or else? Scenario 1 1 01/01/2006 -5,00E+08 2 01/02/2006 5,00E+07 3 01/03/2006 5,00E+07 4 01/04/2006 5,00E+07 5 01/05/2006 5,00E+07 6 01/06/2006 5,00E+07 7 01/07/2006 5,00E+07 8 01/08/2006 5,00E+07 9 01/09/2006 5,00E+07 10 01/10/2006 0,00E+00 11 01/11/2006 0,00E+00 12 01/12/2006 0,00E+00 13 01/01/2007 -1,00E+07 14 01/02/2007 0,00E+00 15 01/03/2007 0,00E+00 16 01/04/2007 0,00E+00 17 01/05/2007 0,00E+00 18 01/06/2007 0,00E+00 19 01/07/2007 0,00E+00 20 01/08/2007 0,00E+00 21 01/09/2007 0,00E+00 22 01/10/2007 0,00E+00 23 01/11/2007 8,00E+07 24 01/12/2007 0,00E+00 -9,8335% = irr function) Scenario 2 1 01/01/2006 -5,00E+08 2 01/02/2006 5,00E+07 3 01/03/2006 5,00E+07 4 01/04/2006 5,00E+07 5 01/05/2006 5,00E+07 6 01/06/2006 5,00E+07 7 01/07/2006 5,00E+07 8 01/08/2006 5,00E+07 9 01/09/2006 5,00E+07 10 01/10/2006 0,00E+00 11 01/11/2006 0,00E+00 12 01/12/2006 0,00E+00 13 01/01/2007 -1,00E+07 14 01/02/2007 0,00E+00 15 01/03/2007 0,00E+00 16 01/04/2007 8,00E+07 17 01/05/2007 0,00E+00 18 01/06/2007 0,00E+00 19 01/07/2007 0,00E+00 20 01/08/2007 0,00E+00 21 01/09/2007 0,00E+00 22 01/10/2007 0,00E+00 23 01/11/2007 0,00E+00 24 01/12/2007 0,00E+00 -11,8678% = irr function |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
irr function
Michael wrote...
.... Is there is an error in the formula or else? Scenario 1 1 01/01/2006 -5,00E+08 2 01/02/2006 5,00E+07 3 01/03/2006 5,00E+07 4 01/04/2006 5,00E+07 5 01/05/2006 5,00E+07 6 01/06/2006 5,00E+07 7 01/07/2006 5,00E+07 8 01/08/2006 5,00E+07 9 01/09/2006 5,00E+07 10 01/10/2006 0,00E+00 11 01/11/2006 0,00E+00 12 01/12/2006 0,00E+00 13 01/01/2007 -1,00E+07 14 01/02/2007 0,00E+00 15 01/03/2007 0,00E+00 16 01/04/2007 0,00E+00 17 01/05/2007 0,00E+00 18 01/06/2007 0,00E+00 19 01/07/2007 0,00E+00 20 01/08/2007 0,00E+00 21 01/09/2007 0,00E+00 22 01/10/2007 0,00E+00 23 01/11/2007 8,00E+07 24 01/12/2007 0,00E+00 -9,8335% = irr function) These are monthly cashflows, and you seem to be converting the monthly IRR of 0.82% into an annual equivalent by multiplying by 12. Better to convert it into the effective annual rate, (1+IRR)^12-1, -9.40%. Scenario 2 1 01/01/2006 -5,00E+08 2 01/02/2006 5,00E+07 3 01/03/2006 5,00E+07 4 01/04/2006 5,00E+07 5 01/05/2006 5,00E+07 6 01/06/2006 5,00E+07 7 01/07/2006 5,00E+07 8 01/08/2006 5,00E+07 9 01/09/2006 5,00E+07 10 01/10/2006 0,00E+00 11 01/11/2006 0,00E+00 12 01/12/2006 0,00E+00 13 01/01/2007 -1,00E+07 14 01/02/2007 0,00E+00 15 01/03/2007 0,00E+00 16 01/04/2007 8,00E+07 17 01/05/2007 0,00E+00 18 01/06/2007 0,00E+00 19 01/07/2007 0,00E+00 20 01/08/2007 0,00E+00 21 01/09/2007 0,00E+00 22 01/10/2007 0,00E+00 23 01/11/2007 0,00E+00 24 01/12/2007 0,00E+00 -11,8678% = irr function Presumably you're wondering why the IRR is worse (larger magnitude neg fative number) even though the last positive cashflow comes sooner and all other cashflows remain the same. Simple answer: IRRs almost never make sense when they're negative. The simple fact that they are negative should be sufficient to show the cashflows that they model are either very bad (when the NOMINAL ending cumulative balance is negative) or very good (when the nominal ending cumulative balance is positive). For both your cashflows, the nominal ending cumulative balances are negative, so both represent BAD deals. If your goal is to determine which is worse, then common sense will be more meaningful than IRRs. The technical, mathematical reason scenario 2's IRR is a larger magnitude negative number is actually because it does happen sooner AND there are nothing but zeros afterwards. Excel uses an iterative technique to solve for IRRs, and zeros after the last nonzero cashflow are effectively ignored. That means that scenario 1 is effectively a 23 period cashflow and scenario 2 a 16 period cashflow. The cumulative loss is realized earlier in scenario 2, so the underlying polynomial Excel needs to solve is lower order, and that lead to the larger magnitude negative number for scenario 2's IRR. You also have multiple sign changes, and that adds to the difficulties. But this distracts from the fundamental fact: IRR is unreliable, and it's NEVER an ordinal measure. You can't use IRRs to rank cashflows in any sensible way unless you're dealing with bond-equivalent cashflows: an initial negative cashflow followed by nothing but positive cashflows. Even then it's unreliable. Consider -1000 at time 0 and +150 at the next 11 periods vs -1000 at time 0 and +2696.72 11 periods later. Both have the same IRR. Which is riskier? In almost all cases in which discounted cashflow analysis is used, the cashflows are uncertain, but those closer to the beginning tend to be less variable than those closer to the end. In the two examples above, the 11 periods of 150 payback are much less risky than the single 2696.72 payback 11 periods after the initial outflow. IRR fails to rank these two cashflows by riskiness. All it says is that if you IGNORE the underlying riskiness, they offer the same interest rate-equivalent payback. How's that useful? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
irr function
Harlan Grove wrote:
These are monthly cashflows, and you seem to be converting the monthly IRR of 0.82% into an annual equivalent by multiplying by 12. Better to convert it into the effective annual rate, (1+IRR)^12-1, -9.40%. Personally, I agree with you. But the fact is, many people (academia and practitioners alike) simply multiply by 12. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
irr function
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |