Dynamic ranges in IRR (and other) functions (Excel 2000)

[ChrisGreaves]

(follows on from "Why can't I calculate an excellent rate of return?")
Now that I understand more about IRR, I am prepared to make use of it.
The equipment has an initial cost (a negative value to be used as Term0 - the first of a series of values) and a constant payback/revenue value (to be used as the remaining values).
The IRR takes as its argument a vector of values.
If the period is fixed (i.e. always 20 years), then i just load the 1+20 cells as the argument.
But when the period can be varied, the number of values in the argument varies.

I have used the OFFSET function to return a range of cells.
Is there a more accurate way? In this specific case VBA is not an option. I had a VBA function, it worked well, but messed up my dynamic formatting code.
Is there a more concise way? The method presented here depends on my extrapolating enough data cells to accommodate the maximum likely period expressed by the user.
I have a feeling that I should be able to generate a dynamic array as the function argument, but I lack knowledge of the mechanism.


In the attached workbook, column "K" shows the plain-Jane method of IRR.
I figure the first 4 results are #NUM! because the initial investment outweighs the accumulation of returns.
The remainder of column K shows us reaching a break-even point at around 10 years, which I would expect since 10x$1,079 is approximately 10,690.

Cell I3 is the specialite-de-la-maison.
It calculates the IRR based on the "Lifespan (Years)", currently set to 20.
Thus the issued sheet shows 7.885% for 20 years.

Cell B2 holds the lifespan.
Change the lifespan to some integral value between 0 and 20 and cell I3 should report the IRR value for the corresponding row in column K.

Cell G3 is just a proving-ground for the OFFSET method, to prove to myself that I really can use OFFSET in this way in an Excel function which everyone can understand.

Cell E3 is just the result of asking for an OFFSET range. I'm not sure what it proves (except that it doesn't deliver an error)

[HansV]

OFFSET returns a range. OFFSET(B3,0,0,21,1) returns a 20 rows by 1 column range. By placing the formula =OFFSET(B3,0,0,21,1) in a single cell, you return the value of the first cell in that 20x1 range.
If you select a 20x1 range, enter the formula OFFSET(B3,0,0,21,1) and press Ctrl+Shift+Enter to make it an array formula, you'll see a copy of the range B3:B23.
Functions such as SUM can take a range as argument, so =SUM(OFFSET(B3,0,0,21,1)) works in a single cell.

[ChrisGreaves]

..., so =SUM(OFFSET(B3,0,0,21,1)) works in a single cell.

Hans, thanks for (what I take as) the confirmation.

There seems to be no equivalent of the Word/VBA String function, which in Excel might be described as "Returns an array containing a repeating value of the length specified."

[HansV]

There is a rather artificial one, but you wouldn't be able to use it with IRR because IRR expects a single array as first argument; you need to specify the initial layout and the array of returns separately.

The following formula returns an array of (value of B2) copies of (value of B4):

=ROW(INDIRECT("1:"&B2))/ROW(INDIRECT("1:"&B2))*$B$4