Excel - IRR - inflow first, followed by only outflows

Asked By Mark on 18-Jun-08 11:42 AM
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.

gcant replied on 18-Jun-08 03:13 PM
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 replied on 18-Jun-08 03:56 PM
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
gcant replied on 19-Jun-08 10:32 AM
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

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

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 replied on 20-Jun-08 10:14 AM
That makes sense now.  Thanks for your help.