#NUM error with IRR -- but only in array formula. Weird.

[jdbutters]

This is my first post here so I hope I am inside the rules. I cannot find the solution to this problem anywhere.

I have a spreadsheet with the price of a stock in one column and projected future earnings in another. Projected future earnings runs for 300 years (the reason is not important -- it is somebody's else's sheet). At each historical point for which I have a stock value, up to the latest observation, I want to find the IRR of future earnings.

To find the IRR at each point, I need a new array that has the current stock price on the date in question (negative, obviously) and all of the future earnings in one column. To do this, I have used the following formula, entered as an array formula:

{=INDEX(A57:D$300,ROW(A57:A$300)-ROW(A56),IF(ROW(A57:A$300)-ROW(A57)+3>4,4,ROW(A57:A$300)-ROW(A57)+3))}

The first value in this array is the negative starting value (from column 3) and the rest of the values are the positive future earnings (from column 4).

Now, when I put this array into a range of cells, so that I can see the array in full, and then apply the IRR formula to that range of cells, I get the answer I am expecting. However, if I change the formula to:

{=IRR(INDEX(A57:D$300,ROW(A57:A$300)-ROW(A56),IF(ROW(A57:A$300)-ROW(A57)+3>4,4,ROW(A57:A$300)-ROW(A57)+3)))}

... then I get a #NUM error.

Any help much appreciated!

 

[joeu2004]

I have a spreadsheet with the price of a stock in one column and projected future earnings in another. Projected future earnings runs for 300 years (the reason is not important -- it is somebody's else's sheet). At each historical point for which I have a stock value, up to the latest observation, I want to find the IRR of future earnings.

To find the IRR at each point, I need a new array that has the current stock price on the date in question (negative, obviously) and all of the future earnings in one column.

It is unclear whether you are using IRR correctly, in the first place. This works as intended only if "future earnings" are reinvested dividends and other distributions. Is that what you mean?

If so, the IRR parameter should be an array with the initial stock value (negative number), periodic reinvested dividends and other distributions at regular intervals (positive number or zero), and ending stock value (positive number).

That is not what you describe.

To do this, I have used the following formula, entered as an array formula:

{=INDEX(A57:D$300,ROW(A57:A$300)-ROW(A56),
IF(ROW(A57:A$300)-ROW(A57)+3>4,4,ROW(A57:A$300)-ROW(A57)+3))}

The first value in this array is the negative starting value (from column 3) and the rest of the values are the positive future earnings (from column 4).

Now, when I put this array into a range of cells, so that I can see the array in full, and then apply the IRR formula to that range of cells, I get the answer I am expecting.

I believe that is misleading you. To demonstrate, select the same range of cells, type the formula modified below (to have the same effect), but press ctrl+Enter instead of ctrl+shift+Enter to propagate a non-array-entered formula (short cut for copy and paste).

=INDEX(A$57:D$300, ROW(A57:A$300)-ROW(A$56),
IF(ROW(A57:A$300)-ROW(A$57)+3>4,4,ROW(A57:A$300)-ROW(A$57)+3))

Note that the result is the same. I don't believe that form of INDEX expression returns an array, even when array-entered, any more than AND(ROW(A1:A10)=2) returns an array when array-entered. I believe it has to do with the internal implementation of INDEX.

PS: Please explain the intent of your formula. As written, it returns only column number 3 (first row) or 4. So at a minimum, it would could be rewritten as follows:

=INDEX(A57:D$300, ROW(A57:A$300)-ROW(A56), IF(ROW(A57:A$300)<>ROW(A57),4,3)

Alternatively:

=INDEX(A57:D$300, ROW(A57:A$300)-ROW(A56), (ROW(A57:A$300)<>ROW(A57))+3)

And the INDEX range could be C57:$D300, and the column parameter could be +1 instead of +3.

However, if I change the formula to:

{=IRR(INDEX(A57:D$300,ROW(A57:A$300)-ROW(A56),
IF(ROW(A57:A$300)-ROW(A57)+3>4,4,ROW(A57:A$300)-ROW(A57)+3)))}

... then I get a #NUM error.

Hopefully, that explains the #NUM error: your expression is effectively IRR(value), not IRR(array), notwithstanding the fact that you array-entered the formula.

I believe there is an efficient way to write the desired IRR parameter using an INDEX(...):INDEX(...) form. I would need to think more about it, but I am running out of time.

For now, use IRR(OFFSET(...)). Again, I need more time. But hopefully, you can figure it out yourself. Of course, that is inefficient because OFFSET is a volatile function, and you have a large number of formulas. But hopefully, you can get by with that until a better formula comes along.

On the other hand, I'm relunctant to invest in the effort because I suspect IRR is not the right function to use in the first place.

Again, please clarify what you mean by "future earnings". And explain what you think the IRR result represents for your purposes.

Gotta go!

 

[joeu2004]

Errata....

It is unclear whether you are using IRR correctly, in the first place. This works as intended only if "future earnings" are reinvested dividends and other distributions. Is that wat you mean?

If so, the IRR parameter should be an array with the initial stock value (negative number), periodic reinvested dividends and other distributions at regular intervals (positive number or zero), and ending stock value (positive number).

That is not what you describe.

If dividends and other distributions are not reinvested, they would be positive numbers. If they are reinvested, they would be negative numbers.

 

[joeu2004]

Errata....

I believe there is an efficient way to write the desired IRR parameter using an INDEX(...):INDEX(...) form. I would need to think more about it, but I am running out of time. For now, use IRR(OFFSET(...)).

Sorry about the incessant postings. Busy! No time to check everything, as I usually do.

INDEX(...):INDEX(...) and OFFSET(...) alone will not work for you because you are pulling together values from different places.

However, IRR is one of the few functions I know that supports the union range operator: (range,range,...).

From your original posting, it appears that column C contains the current investment value as a negative number, and column D contains the "future earnings", hopefully as I defined them. I'll assume column B contains the current investment value as a positive number.

Then something like the following might work for you.

=IRR((C57,D57:D$299,B$300))

When I say "work", I mean it returns some value. Whether or not it is a valid number for your purposes remains to be seen. I suspect it is not. GIGO!

Caveat: The IRR algorithm, in general and in Excel in particular, is not stable if there are multiple IRRs. That is often the case when the signs of the cash flows change repeatedly. So IRR might return an error or even an unreasonable number if the "future earnings" in column D is a mix of reinvested (negative) and non-reinvested (positive) distributions.

 

[jdbutters]

Then something like the following might work for you.

=IRR((C57,D57:D$299,B$300))

Thank you very much indeed for your postings. This must be a matter of INDEX not returning an array (though, when I enter the formula (without IRR) into a range of cells as an array, I do get the numbers I expected, so I don't fully understand what is going on).

Still, your method of inputting a range is just the thing! I had no idea that any functions could take an input like this. Normally it is not easy to put ranges together.

Thank you for your comments on the philosophy of all this. I am working through a sheet that someone else has made and the philosophy is his. The numbers I am trying to calculate have been hard-coded -- I wonder if the chap has done goal seek on every one. Having tried to calculate it in a more straightforward way, I can see why!

 

[jdbutters]

PS: Please explain the intent of your formula. As written, it returns only column number 3 (first row) or 4. So at a minimum, it would could be rewritten as follows:

=INDEX(A57:D$300, ROW(A57:A$300)-ROW(A56), IF(ROW(A57:A$300)<>ROW(A57),4,3)

Alternatively:

=INDEX(A57:D$300, ROW(A57:A$300)-ROW(A56), (ROW(A57:A$300)<>ROW(A57))+3)

Ah, yes, you're quite right. I don't know why I did it in that strange way -- just chucking it together I suppose.

Thanks again.