IRR, with cash flows in non-adjacent cells

[Nick Ng]

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!

 

[JoeU2004]

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]

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]

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

 

[JoeU2004]

Errata....

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

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

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.

 

[Nick Ng]

Joe, you are the man! I've been looking for an answer for a week, and this is
a perfect solution. Thanks heaps!

One last Q, I tried the same formula with XIRR,

=XIRR(($B$2:B6,$D$2),A2:A6)

B2:B6 being the cashflow
D2 being the sale price
A2:A6 being the date range (using the DATE function)

but it returned a #VALUE error. What am I doing wrong?

-Nick

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

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]

I tried the same formula with XIRR,
=XIRR(($B$2:B6,$D$2),A2:A6)
[....]
but it returned a #VALUE error. What am I doing wrong?

For one thing, the date parameter does not represent the same number of
cells as the cash-flow parameter.

But even if you correct for that, it appears that XIRR does not really
support the union reference operator, at least not with more than one range,
which defeats its purpose. For example, the following, which is what you
might have intended, still results in a #VALUE error:

=XIRR( ($B$2:B6,$D$2), ($A$2:A6,$A$6) )

In any case, that is not how I intended to use XIRR for this problem, in the
first place.

But on second thought, perhaps XIRR would not be a good approach if you want
a table of answers for a sale in each year, for the very reason that XIRR
seems to require parameters that are each one contiguous range or an array.

For what it's worth, the following demonstrates how I would use XIRR for a
sale in year 4. The first column is column B starting in B2; the second
column is column C starting in C2. The dates are artificial.

1/1/2010 -10000
1/1/2011 -1000
1/1/2012 -1000
1/1/2013 -1000
1/1/2014 -1000
6/1/2014 20000

=XIRR(B2:B7,A2:A7)

Since XIRR gives you the flexibility of adjusting the timing of cash flows,
you can even compute the IRR for a sale in year 0, which would be, for
example:

1/1/2010 -10000
6/1/2010 20000

=XIRR(B2:B3,A2:A3)


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

Joe, you are the man! I've been looking for an answer for a week, and this
is
a perfect solution. Thanks heaps!

One last Q, I tried the same formula with XIRR,

=XIRR(($B$2:B6,$D$2),A2:A6)

B2:B6 being the cashflow
D2 being the sale price
A2:A6 being the date range (using the DATE function)

but it returned a #VALUE error. What am I doing wrong?

-Nick

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

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.



:

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

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!