Errata....

I wrote:
bear in mind that that makes an equally-misleading assumption that the
sale occurs at the same time as the costs, thereby potentially increasing
the IRR artificially. IMHO, there is no more-wrong or
more-right answer if you use IRR.

Duh, except that we always net cash flows with IRR. (Brain fart!)


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

"JoeU2004" wrote in message
...
"Nick Ng" wrote:
I tried out your method, but it seems to tack the sale on to the next
period

That should come as no surprise since I explained that. (See "Also
note").


hence increasing the period (and decreasing IRR).
[....]
Is there any way I could add the $20,000 to the cash flow in year 4,
and compute IRR based on that?

Sure. But bear in mind that that makes an equally-misleading assumption
that the sale occurs at the same time as the costs, thereby potentially
increasing the IRR artificially. IMHO, there is no more-wrong or
more-right answer if you use IRR.

As I explained, the better approach would be to use "actual" dates with
XIRR. By "actual" dates, I mean, for example, assuming costs are at the
beginning of the period and the sale is in the middle of the period (or
vice versa; no difference).

In any case, to do what you want, I would suggest creating a "net cost"
column C, starting in C3 (not C2) and copying down:

=20000+B3

That allows for the per-period costs to vary. Then compute the IRR in
column D, starting D3 (not D2) and copying down:

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

Again, be careful with the absolute and relative references.

The reason for not doing this for D2 (year 0) is that the IRR makes no
sense with only one cash flow, and in fact IRR returns the #NUM error. Of
course, the formula would be different in that case, namely: =IRR(C2).


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

"Nick Ng" wrote in message
...
Hi Joe,

Thanks for the prompt reply!

I tried out your method, but it seems to tack the sale on to the next
period, hence increasing the period (and decreasing IRR). For example,
assumming the sale occured in year 4, cash flow would look like the
following:

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

IRR = 11%

Using your method, IRR = 9%, which I assume is because the $20,000 inflow
occurred in year 5.

Is there any way I could add the $20,000 to the cash flow in year 4, and
compute IRR based on that?

THanks again.



"JoeU2004" wrote:

"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!