ExcelBanter - View Single Post - IRR, with cash flows in non-adjacent cells

joeu2004

"Nick Ng" <Nick wrote:
What I would like to do is find the IRR for each year, assuming I can sell
the property that year for $20,000. So, what is the IRR if I sell it in
Year
3, Year 4, etc.

Assume the Cash Flow values are in B2:B7, the IRR results are in C2:C7, and
the sale price ($20,000) is in D2 (not shown).

The IRR function is one of the few that permit us to use the union reference
operator, (range,range,...). Starting in C2 and copying down, enter:

=IRR( ($B$2:B2,$D$2) )

Note the careful use of absolute and relative references; and here, the
inner parentheses are not optional.

Also note....

As you may know, the IRR function assumes that all net cash flows occur at
the same frequency -- for example, on the same date every year.

So, for example, if you sold the property in year 0, you would have to
represent that as a single cash flow of $10,000 ($20,000 - 10,000).

Not only is that meaningless to the Excel IRR() function (it returns a #NUM
error), but also it is meaningless in a pencil-and-paper computation.

So the formula above assumes that the property is sold in the period
following the last net cash flow in column B, or that net cash flows in
column B occur at the beginning of the period, and the sale, if any, occurs
at the end.

If you would prefer to work with actual dates, use XIRR.

----- original message -----

"Nick Ng" <Nick wrote in message
...
I'm trying to create an IRR table for property investments with a 5 year
time
horizon, based on a series of negative cash outflows (representing the
initial outlay and subsequent monthly loan payments), followed by a cash
inflow in the future when I sell the asset.

My table looks something like this:

Year Cash Flow IRR
0 -10,000
1 -1,000
2 -1,000
3 -1,000
4 -1,000
5 -1,000

What I would like to do is find the IRR for each year, assuming I can sell
the property that year for $20,000. So, what is the IRR if I sell it in
Year
3, Year 4, etc.

Thanks in advance!