Working with arrays as arguments

[HB]

Hello

I am trying to calculate the IRR of a series of cashflows stored
sequentially, except that I need to append a final term to the cash flows
within the formula itself.
i.e. In my formula :
=IRR('Cash Flows'!E9:AE9,0.1)
I need to modify the first argument and add a new term from another cell in
my excel sheet. The first argument in the formula above is an array of
numbers, so basically what I am trying to do is dynamically add a new term at
the end of the array.

I tried variations like:
=IRR({'Cash Flows'!E9:AE9}&{C20},0.1) ; where C20 represents the term I want
to append but none of them seem to work.

It is not an option for me to store the number in another excel sheet (i.e.
I cant enter the number in AF9 and then process the formula with E9:AF9)

Would appreciate it if anyone can help me out with this.

Thanks
HB

 

[Jacob Skaria]

If C20 is in the same sheet 'Cash Flows', try with a named range.

--From menu Insert>Name>Define
Names in workbook: myRange
Refers to: ='Cash Flows'!$E$9:$AE$9,'Cash Flows'!$C$20

Hit OK

--Try the formula
=IRR(myRange,0.1)


If this post helps click Yes

 

[HB]

Thanks Jacob, but unfortunately the cells are in different sheets...

Ill check if I can transfer some of the cells into the same sheet, but in
the meanwhile if anyone has an idea how to combine ranges in different sheet
it would be really helpful...

 

[Lori Miller]

Maybe this entered using CTRL+SHIFT+ENTER:

=IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:AE9)),CF!E9:AE9,C20),0.1)

where "CF" is the cash flow sheet.
If both ranges are on the same sheet also try:=IRR((E9:AE9,C20),0.1)

 

[Jacob Skaria]

Lori, does that need to be array entered?

Maybe this entered using CTRL+SHIFT+ENTER:

=IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:AE9)),CF!E9:AE9,C20),0.1)

where "CF" is the cash flow sheet.
If both ranges are on the same sheet also try:=IRR((E9:AE9,C20),0.1)

 

[Lori Miller]

I think it needs array-entry, but it should have been a horizontal array:

=IRR(IF(TRANSPOSE(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:AE9))),
CF!E9:AE9,C20),0.1)

 

[Bernd P]

Hello HB,

Enter
=IRR(TRANSPOSE('Cash Flows'!E9:AE9),0.1)
normally (no array-formula). IRR expects a vertical array input.

Regards,
Bernd