Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR - inflow first, followed by only outflows
I have a financial situation where I am receiving a cash inflow first, then
in the next 4 years will only be outflows. How do I properly do an IRR calculation with this? Or can I even do it? I can tell the value that comes out of the IRR function is not correct. Is it as simple as taking the opposite of the IRR value excel gives me? Something tells me no, but I'm not sure. Simple example would be Year 1 - inflow 1500 Year 2 - outflow 250 Year 3 - outflow 250 Year 4 - outflow 250 Year 5 - outflow 250 This tells me IRR is -14.3% which is not correct. Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR - inflow first, followed by only outflows
The result provided is correct. In order to have a positive result in an IRR
calculation the undiscounted sum of the outflows have to exceed the undiscounted amount of the initial inflows. The problem here is that the other party in this transaction is not even recoverying their initial investment. Conversely, even though the IRR is NEGATIVE you would be very fortunate if you could get someone to give you $1500 and allow you to get off with paying you only $1000 back at a rate of $250 per year. To help illustrate what's going on, keep the annual payments at 250, extend the number of years you must pay the lender/financier to seven, and measure the IRR at the end of each year. When the total outflows equal the total inflows (Year 6) the IRR would be Zero. Only if the payments continued beyond Year 6 would the IRR become positive. Similarly increase the annual payments to $500 and observe that the IRR is zero once the third payment is received (i.e. the total outflows = total inflows = $1500). I hope that helps. "Mark O" wrote: I have a financial situation where I am receiving a cash inflow first, then in the next 4 years will only be outflows. How do I properly do an IRR calculation with this? Or can I even do it? I can tell the value that comes out of the IRR function is not correct. Is it as simple as taking the opposite of the IRR value excel gives me? Something tells me no, but I'm not sure. Simple example would be Year 1 - inflow 1500 Year 2 - outflow 250 Year 3 - outflow 250 Year 4 - outflow 250 Year 5 - outflow 250 This tells me IRR is -14.3% which is not correct. Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR - inflow first, followed by only outflows
I guess the problem is I'm trying to flip around the point of view. Is there
a way to model an IRR to show the benefit of someone receiving that as an inflow and only paying smaller amounts in the out years? I have vastly simplfied the scenario, but it is based on a working capital scenario with the company selling off a large chunk of inventory (the cash inflow) and doing an oursource scenario that will have a much smaller incremental cost over the subsequent years. Any thoughts greatly appreciated "gcanty" wrote: The result provided is correct. In order to have a positive result in an IRR calculation the undiscounted sum of the outflows have to exceed the undiscounted amount of the initial inflows. The problem here is that the other party in this transaction is not even recoverying their initial investment. Conversely, even though the IRR is NEGATIVE you would be very fortunate if you could get someone to give you $1500 and allow you to get off with paying you only $1000 back at a rate of $250 per year. To help illustrate what's going on, keep the annual payments at 250, extend the number of years you must pay the lender/financier to seven, and measure the IRR at the end of each year. When the total outflows equal the total inflows (Year 6) the IRR would be Zero. Only if the payments continued beyond Year 6 would the IRR become positive. Similarly increase the annual payments to $500 and observe that the IRR is zero once the third payment is received (i.e. the total outflows = total inflows = $1500). I hope that helps. "Mark O" wrote: I have a financial situation where I am receiving a cash inflow first, then in the next 4 years will only be outflows. How do I properly do an IRR calculation with this? Or can I even do it? I can tell the value that comes out of the IRR function is not correct. Is it as simple as taking the opposite of the IRR value excel gives me? Something tells me no, but I'm not sure. Simple example would be Year 1 - inflow 1500 Year 2 - outflow 250 Year 3 - outflow 250 Year 4 - outflow 250 Year 5 - outflow 250 This tells me IRR is -14.3% which is not correct. Thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR - inflow first, followed by only outflows
Based on your last post I'm guessing that your company requires you to
determine a four-year IRR for a project of this sort and it requires (or at least seeks) a 4-year IRR that exceeds your company's cost of capital. A requirement like this makes sense when you are investing money and hoping that you don't have to wait 5 years or more for the stream of subsequent inflows to make the investment worthwhile. However, your situation is akin to that of a borrower who borrows $1500 and only needs to make four annual payments of $250 each. In effect, you're borrowing at a negative 14% interest rate. I suspect that you are probably struggling with the fact that you appear to have an unfathomably attractive situation. If we continue to approach this scenario as if you are borrowing $1500, and if your IRR is essentially your interest rate, your objective SHOULD BE a low IRR. In fact, any IRR that is lower than your cost of capital should be attractive to you. Now the catch. As I said before, companies often want an attractively high milestone IRR (in your case a 4-year IRR) because they want to make sure the payoff isn't too far off into the future. Your issue isn't when you will get the payoff because you're getting the cash inflow up front. As a result, a 4-year IRR may not be nearly as relevant for you. What you may need to be more cautious about is the liability beyond Year 4. If your incremental cash outflows will continue at a rate of $250 per year for an extended period of time you may likely encounter a point in time when the calculated IRR will be HIGHER than your company's cost of capital. A milestone IRR at 10 years may indicate that selling your inventory and outsourcing is not such a good idea (financially). In closing, the NEGATIVE IRR already shows you the benefit of receiving the inflow but ONLY because your initial cashflow is an INFLOW, and perhaps because you are using an investor's convention (the 4-year milestone IRR) to evaluate a borrower's opportunity. There is no way I know of to flip the IRR to make this look attractive. If, however, you flip your point of view from that of an investor to one of a borrower you may find the negative IRR a more valuable metric. I hope that helps. "Mark O" wrote: I guess the problem is I'm trying to flip around the point of view. Is there a way to model an IRR to show the benefit of someone receiving that as an inflow and only paying smaller amounts in the out years? I have vastly simplfied the scenario, but it is based on a working capital scenario with the company selling off a large chunk of inventory (the cash inflow) and doing an oursource scenario that will have a much smaller incremental cost over the subsequent years. Any thoughts greatly appreciated "gcanty" wrote: The result provided is correct. In order to have a positive result in an IRR calculation the undiscounted sum of the outflows have to exceed the undiscounted amount of the initial inflows. The problem here is that the other party in this transaction is not even recoverying their initial investment. Conversely, even though the IRR is NEGATIVE you would be very fortunate if you could get someone to give you $1500 and allow you to get off with paying you only $1000 back at a rate of $250 per year. To help illustrate what's going on, keep the annual payments at 250, extend the number of years you must pay the lender/financier to seven, and measure the IRR at the end of each year. When the total outflows equal the total inflows (Year 6) the IRR would be Zero. Only if the payments continued beyond Year 6 would the IRR become positive. Similarly increase the annual payments to $500 and observe that the IRR is zero once the third payment is received (i.e. the total outflows = total inflows = $1500). I hope that helps. "Mark O" wrote: I have a financial situation where I am receiving a cash inflow first, then in the next 4 years will only be outflows. How do I properly do an IRR calculation with this? Or can I even do it? I can tell the value that comes out of the IRR function is not correct. Is it as simple as taking the opposite of the IRR value excel gives me? Something tells me no, but I'm not sure. Simple example would be Year 1 - inflow 1500 Year 2 - outflow 250 Year 3 - outflow 250 Year 4 - outflow 250 Year 5 - outflow 250 This tells me IRR is -14.3% which is not correct. Thanks in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR - inflow first, followed by only outflows
That makes sense now. Thanks for your help.
"gcanty" wrote: Based on your last post I'm guessing that your company requires you to determine a four-year IRR for a project of this sort and it requires (or at least seeks) a 4-year IRR that exceeds your company's cost of capital. A requirement like this makes sense when you are investing money and hoping that you don't have to wait 5 years or more for the stream of subsequent inflows to make the investment worthwhile. However, your situation is akin to that of a borrower who borrows $1500 and only needs to make four annual payments of $250 each. In effect, you're borrowing at a negative 14% interest rate. I suspect that you are probably struggling with the fact that you appear to have an unfathomably attractive situation. If we continue to approach this scenario as if you are borrowing $1500, and if your IRR is essentially your interest rate, your objective SHOULD BE a low IRR. In fact, any IRR that is lower than your cost of capital should be attractive to you. Now the catch. As I said before, companies often want an attractively high milestone IRR (in your case a 4-year IRR) because they want to make sure the payoff isn't too far off into the future. Your issue isn't when you will get the payoff because you're getting the cash inflow up front. As a result, a 4-year IRR may not be nearly as relevant for you. What you may need to be more cautious about is the liability beyond Year 4. If your incremental cash outflows will continue at a rate of $250 per year for an extended period of time you may likely encounter a point in time when the calculated IRR will be HIGHER than your company's cost of capital. A milestone IRR at 10 years may indicate that selling your inventory and outsourcing is not such a good idea (financially). In closing, the NEGATIVE IRR already shows you the benefit of receiving the inflow but ONLY because your initial cashflow is an INFLOW, and perhaps because you are using an investor's convention (the 4-year milestone IRR) to evaluate a borrower's opportunity. There is no way I know of to flip the IRR to make this look attractive. If, however, you flip your point of view from that of an investor to one of a borrower you may find the negative IRR a more valuable metric. I hope that helps. "Mark O" wrote: I guess the problem is I'm trying to flip around the point of view. Is there a way to model an IRR to show the benefit of someone receiving that as an inflow and only paying smaller amounts in the out years? I have vastly simplfied the scenario, but it is based on a working capital scenario with the company selling off a large chunk of inventory (the cash inflow) and doing an oursource scenario that will have a much smaller incremental cost over the subsequent years. Any thoughts greatly appreciated "gcanty" wrote: The result provided is correct. In order to have a positive result in an IRR calculation the undiscounted sum of the outflows have to exceed the undiscounted amount of the initial inflows. The problem here is that the other party in this transaction is not even recoverying their initial investment. Conversely, even though the IRR is NEGATIVE you would be very fortunate if you could get someone to give you $1500 and allow you to get off with paying you only $1000 back at a rate of $250 per year. To help illustrate what's going on, keep the annual payments at 250, extend the number of years you must pay the lender/financier to seven, and measure the IRR at the end of each year. When the total outflows equal the total inflows (Year 6) the IRR would be Zero. Only if the payments continued beyond Year 6 would the IRR become positive. Similarly increase the annual payments to $500 and observe that the IRR is zero once the third payment is received (i.e. the total outflows = total inflows = $1500). I hope that helps. "Mark O" wrote: I have a financial situation where I am receiving a cash inflow first, then in the next 4 years will only be outflows. How do I properly do an IRR calculation with this? Or can I even do it? I can tell the value that comes out of the IRR function is not correct. Is it as simple as taking the opposite of the IRR value excel gives me? Something tells me no, but I'm not sure. Simple example would be Year 1 - inflow 1500 Year 2 - outflow 250 Year 3 - outflow 250 Year 4 - outflow 250 Year 5 - outflow 250 This tells me IRR is -14.3% which is not correct. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|