Working with arrays as arguments
 

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

Working with arrays as arguments
 

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

--From menu InsertNameDefine
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
---------------
Jacob Skaria


"HB" wrote:

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

Working with arrays as arguments
 

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

"Jacob Skaria" wrote:

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

--From menu InsertNameDefine
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
---------------
Jacob Skaria


"HB" wrote:

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

Working with arrays as arguments
 

Maybe this entered using CTRL+SHIFT+ENTER:

=IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:A E9)),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)

"HB" wrote:

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

"Jacob Skaria" wrote:

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

--From menu InsertNameDefine
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
---------------
Jacob Skaria


"HB" wrote:

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

Working with arrays as arguments
 

Lori, does that need to be array entered?

"Lori Miller" wrote:

Maybe this entered using CTRL+SHIFT+ENTER:

=IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:A E9)),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)

"HB" wrote:

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

"Jacob Skaria" wrote:

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

--From menu InsertNameDefine
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
---------------
Jacob Skaria


"HB" wrote:

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

Working with arrays as arguments
 

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

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

"Jacob Skaria" wrote:

Lori, does that need to be array entered?

"Lori Miller" wrote:

Maybe this entered using CTRL+SHIFT+ENTER:

=IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:A E9)),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)

"HB" wrote:

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

"Jacob Skaria" wrote:

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

--From menu InsertNameDefine
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
---------------
Jacob Skaria


"HB" wrote:

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

Working with arrays as arguments
 

Hello HB,

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

Regards,
Bernd