IRR worksheet function

  • Thread starter Thread starter Pat Jennings
  • Start date Start date
P

Pat Jennings

It appears that the function require the values be contained in a row or
column, with the last value the future amount. Is there a way to use te
function, having the investment values in one column and the future value in
another column? I would like to run multiple IRR calculations using
sequential investment amounts and corresponding future values.
 
The values need to be in a continuous range. Could you use a simple formula
(like = B2) to enter one of the values in that range?
 
Pat Jennings said:
It appears that the function require the values be contained in a row or
column, with the last value the future amount. Is there a way to use te
function, having the investment values in one column and the future value in
another column? I would like to run multiple IRR calculations using
sequential investment amounts and corresponding future values.
Click to expand...

IRR takes a reference to a series of regularly spaced cashflows as its first
argument. Any of them could have either positive or negative sign or be
zero. If you mean you have a series of negative values (investments) with a
*single* final positive (return) cashflow, then why can't you make the cell
just below the final negative a reference to the positive value? As in, B100
the final negative value, C3 the positive value, so B101 has the formula
=C3. If you want to do a lot of these, with returns in C2:C100, then give B1
the formula

=INDEX(C2:C100,X99)

and vary the entry in X99 to index the desired return.

If you believe this just won't work, how about giving us some details about
what's where, and perhaps someone would be able to figure something out that
might work.
 
If you do it correctly Bernard's suggestion has to work.

In any case, IRR doesn't require the values to be in a contiguous
range. Just bracket the discontiguous range with parenthesis.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
OK, I guess I am not explaining my situation glearly and am not
understanding your explanation.
Here are the three columns in my worksheet. The first indicates the payment
stream (years). The second, the payment amounts each year. The third
represents the value to be received in the year. I am interested in learning
the IRR at each year and placing those values in the fourth column.

1 1274
2 1274
3 1274
4 1274
5 1274
6 1274
7 1274 104,125
8 1274 105,814
9 1274 107,783
10 1274 109,963
11 1274 112,369
12 1274 114,979
13 1274 117,778
14 1274 120,755
15 1274 123,899
16 1274 127,154
17 1274 130,519
18 1274 134,081
 
Pat Jennings said:
OK, I guess I am not explaining my situation glearly and am not
understanding your explanation.
Here are the three columns in my worksheet. The first indicates the payment
stream (years). The second, the payment amounts each year. The third
represents the value to be received in the year. I am interested in learning
the IRR at each year and placing those values in the fourth column.

1 1274
2 1274
3 1274
4 1274
5 1274
6 1274
7 1274 104,125
8 1274 105,814
9 1274 107,783
10 1274 109,963
11 1274 112,369
12 1274 114,979
13 1274 117,778
14 1274 120,755
15 1274 123,899
16 1274 127,154
17 1274 130,519
18 1274 134,081
Click to expand...
....

If this table were in A1:C18, and if the 3rd column (C) values were the the
values to be received in any of these years assuming that each was the only
return (in other words I'm assuming, e.g., 105,814 return in year 8 *or*
107,783 return in year 9, but not both), then try this array formula in D7.

D7:
=IRR(IF(ROW(INDIRECT("R1:R[+1]",0))<=ROW(),-B$1:B7,C7))

Select D7 and fill down into D8:D18.
 
I copied the formula to the cell. The formula is referencing the correct
cells. The result returned is #NUM! What am I doing wrong?

Your assumption that the return is a singular year event is correct.

Harlan Grove said:
Pat Jennings said:
OK, I guess I am not explaining my situation glearly and am not
understanding your explanation.
Here are the three columns in my worksheet. The first indicates the payment
stream (years). The second, the payment amounts each year. The third
represents the value to be received in the year. I am interested in learning
the IRR at each year and placing those values in the fourth column.

1 1274
2 1274
3 1274
4 1274
5 1274
6 1274
7 1274 104,125
8 1274 105,814
9 1274 107,783
10 1274 109,963
11 1274 112,369
12 1274 114,979
13 1274 117,778
14 1274 120,755
15 1274 123,899
16 1274 127,154
17 1274 130,519
18 1274 134,081
Click to expand...
...

If this table were in A1:C18, and if the 3rd column (C) values were the the
values to be received in any of these years assuming that each was the only
return (in other words I'm assuming, e.g., 105,814 return in year 8 *or*
107,783 return in year 9, but not both), then try this array formula in D7.

D7:
=IRR(IF(ROW(INDIRECT("R1:R[+1]",0))<=ROW(),-B$1:B7,C7))

Select D7 and fill down into D8:D18.
Click to expand...
 
Pat Jennings wrote...
I copied the formula to the cell. The formula is referencing the
correct cells. The result returned is #NUM! What am I doing
wrong?
Click to expand...
...
Harlan Grove said:
. . . then try this array formula in D7.

D7:
=IRR(IF(ROW(INDIRECT("R1:R[+1]",0))<=ROW(),-B$1:B7,C7))
Click to expand...
...

This formula *must* be entered as an array formula, which means yo
must hold down [Ctrl] and [Shift] keys before pressing the [Enter] key
 
Fantastic! Thank you.
hgrove > said:
Pat Jennings wrote...
I copied the formula to the cell. The formula is referencing the
correct cells. The result returned is #NUM! What am I doing
wrong?
Click to expand...
..
Harlan Grove said:
. . . then try this array formula in D7.

D7:
=IRR(IF(ROW(INDIRECT("R1:R[+1]",0))<=ROW(),-B$1:B7,C7))
Click to expand...
..

This formula *must* be entered as an array formula, which means you
must hold down [Ctrl] and [Shift] keys before pressing the [Enter] key.
Click to expand...
 
Well, now I have two new problems. To test Harlan Grove's formula, I
entered the dat starting in cell A1. Therefore, I did not have cells in
which to enter column headings; year, investment, future value, IRR. I
tried to add rows at teh top. That led to errors, #VALUE!, in every IRR
calculation cell, other than the first cell. Why is that?

Second problem is that Icarried out the IRR calculations for a longer period
of time that that which I included in my earlier posting. After a
significant length of time, the calculations produced #NUM!, then later in
time, #DIV/0! I surmise that this is due to the fact that there are
significant iterations. When I calculae the IRR without the offset array
(all values in the same column), I need to enter a "GUESS" value in order
for the calculation to produce a meaningful result. I tried to enter a
GUESS value in the Harlan Grove formulae, but an error message indicated
that too many values fo rthe formulae were being input. Any suggestions?


Pat Jennings said:
Fantastic! Thank you.
hgrove > said:
Pat Jennings wrote...
I copied the formula to the cell. The formula is referencing the
correct cells. The result returned is #NUM! What am I doing
wrong?
Click to expand...
..
Harlan Grove said:
. . . then try this array formula in D7.

D7:
=IRR(IF(ROW(INDIRECT("R1:R[+1]",0))<=ROW(),-B$1:B7,C7))
Click to expand...
..

This formula *must* be entered as an array formula, which means you
must hold down [Ctrl] and [Shift] keys before pressing the [Enter] key.
Click to expand...
Click to expand...
 
Pat Jennings said:
Well, now I have two new problems. To test Harlan Grove's formula, I
entered the dat starting in cell A1. Therefore, I did not have cells in
which to enter column headings; year, investment, future value, IRR. I
tried to add rows at teh top. That led to errors, #VALUE!, in every IRR
calculation cell, other than the first cell. Why is that?
Click to expand...

Generalize the formula. If the investments were in col X beginning with X99,
and the return for the 10th year in Z108, and the IRR for the 10th year
return in AF10 (just to really mix things up), enter the following *array*
formula in AF10.

=IRR(IF(ROW(X$99:X109)>ROW(Z108),Z108,-X$99:X108))

Fill down as needed.
Second problem is that Icarried out the IRR calculations for a longer period
of time that that which I included in my earlier posting. After a
significant length of time, the calculations produced #NUM!, then later in
time, #DIV/0! I surmise that this is due to the fact that there are
significant iterations. When I calculae the IRR without the offset array
(all values in the same column), I need to enter a "GUESS" value in order
for the calculation to produce a meaningful result. I tried to enter a
GUESS value in the Harlan Grove formulae, but an error message indicated
that too many values fo rthe formulae were being input. Any suggestions?
Click to expand...

Show the *EXACT* formula you tried to use. IRR takes one or two arguments,
and something like

=IRR(IF(ROW(X$99:X309)>ROW(Z308),Z308,-X$99:X308),-0.9)

should work, and note that initial guesses of -0.9 or -0.5 usually make IRR
return nonerror results even for rather flaky cashflows.
 
Back
Top